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.







3. Add the Shift key to select data



4. Double click to copy down

To copy a formula or value down the length of your data set, you don't need to hold and drag the mouse all the way down. Just double click the tiny box at the bottom right-hand corner of the cell.


5. Use shortcuts to quickly format values

For a number with two decimal points, use Ctrl + Shift + !. For dollars use Ctrl + Shift + $. For percentages it's Ctrl + Shift + %. The last two should be pretty easy to remember.


6. Lock cells with F4



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.



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)

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

Business Insider/Sara SIlverstein



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.



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)

Business Insider/Sara Silverstein



LEN(text string)

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)

Business Insider/Sara Silverstein



11. Generate random values with RAND




Already mastered these? Check out these lesser-known Excel shortcuts:

Get the latest Microsoft stock price here.

SEE ALSO: This is what separates the Excel masters from the wannabes

More: Excel How To Shortcut Microsoft