11 basic Excel tricks that will change your life

Even regular users of Microsoft
Excel might not be getting as much out of it as they can. Improve
your Excel efficiency and proficiency with these basic shortcuts
and functions that absolutely everyone needs to know.

1. Jump from worksheet to worksheet with Ctrl + PgDn and Ctrl + PgUp

2. Jump to the end of a data range or the next data range with Ctrl + Arrow

3. Add the Shift key to select data

4. Double click to copy down

5. Use shortcuts to quickly format values

6. Lock cells with F4

When copying formulas in Excel, sometimes you want
your input cells to move with your formulas BUT SOMETIMES YOU
DON'T. When you want to lock one of your inputs you need to put
dollar signs before the column letter and row number. Typing in
the dollar signs is insane and a huge waste of time. Instead,
after you select your cell, hit F4 to insert the dollar signs and
lock the cell. If you continue to hit the F4 key, it will cycle
through different options: lock cell, lock row
number, lock column letter, no lock.

7. Summarize data with CountIF and SumIF

CountIF will count the number of times a value appears in a
selected range. SumIF will add up values in a range when the
value in a corresponding range matches your criteria.

COUNTIF(range,criteria)

source

Business Insider/Sara SIlverstein

The first input is the range of values you want to count in. The
second input is the criteria, or particular value, you are
looking for. Here we are counting the number of stories in column
B written by the selected author.

SUMIF(range,criteria,sum range)

source

Business Insider/Sara Silverstein

Here we want to count the total number of views for each
author. Our sum range is different from the range with the
authors' names, but the two ranges are the same size. We are
adding up the number of views in column E when the author name in
column B matches the selected name.

8. Pull out the exact data you want with VLOOKUP

source

Business Insider/Sara SIlverstein

VLOOKUP looks for a value in the leftmost column of a data
range and will return any value to the right of it. Here we have
a list of law schools with school rankings in the first column.
We want to use VLOOKUP to create a list of the top 5 ranked
schools.

VLOOKUP(lookup value,data range,column
number,type)

The first input is the lookup value. Here we use the
ranking we want to find. The second input is the data range that
contains the values we are looking up in the leftmost column and
the information we're trying to get in the columns to the right.
The third input is the column number of the value you want to
return.

We want the school name, and this is in the second column
of our data range. The last input tells Excel if you want an
exact match or an approximate match. For an exact match write
FALSE or 0.

9. Use & to combine text strings

Here we have a column of first names and last names. We can
create a column with full names by using &. In Excel, &
joins together two or more pieces of text. Don't forget to put a
space between the names. Your formula will look like this =[First
Name]&" "&[Last Name]. You can mix cell references with
actual text as long as the text you want to include is surrounded
by quotes.

10. Clean up text with LEFT, RIGHT and LEN

These text formulas are great for cleaning up data.

LEFT(text string, number of characters)

source

Business Insider/Sara Silverstein

Here we have state abbreviations combined with state names with a
dash in between. We can use the LEFT function to return the state
abbreviation. LEFT grabs a specified number of characters from
the start of a text string. The first input is the text string.
The second input is the number of characters you want. In our
case, we want the first two characters.

LEN(text string)

source

Business Insider/Sara Silverstein

If you want to pull the names of the states out of this text
string you have to use the RIGHT function. RIGHT grabs a
specified number of characters from the right end of a text
string.

But how many characters on the right do you want? All but three,
since the state names all come after the state's two-letter
abbreviation and a dash. This is where LEN comes in handy. LEN
will count the number of characters or length of the text string.

RIGHT(text string,number of characters)

source

Business Insider/Sara Silverstein

Now you can use a combination of RIGHT and LEN to pull out the
state names. Since we want all but the first three characters, we
take the length of our string, subtract 3, and pull that many
characters from the right end of the string.

11. Generate random values with RAND

RAND()

You can use RAND() function to generate a random value between 0
and 1. D0 not include any inputs, just leave the parentheses
empty. New random values will be generated every time the
workbook recalculates. You can force it to recalculate by hitting
F9. But be careful. It also recalculates when you make other
changes to the workbook.