Does Microsoft Excel overwhelm you? You find spreadsheets dry and boring?
Spreadsheets are an incredible tool used by a professionals across industries, from high level data analysis, finance or accounting, to freelancers working out a business plan and a budget. In other words, everyone can benefit from having a MS Excel foundation.
You don't have to master all 500 functions to get the productivity benefits of Excel. In fact, according to the Harvard Business Review, having a solid understanding of just 10 functions and shortcuts can make a huge difference in saving time and improve productivity.
Without further ado, here are the 10 Excel Functions Everyone Should Know:
1. Paste Special (10 minutes to learn)
Copy and paste is one of the simplest and most used functions in Excel. But we often carry over a format we don’t want, or we copy a formula over, when instead we just want a value.
Paste Special enables you to pick which elements of the copied cell you bring over. After you’ve copied your cell (Ctr+C) hit Ctrl+Alt+V (or go to the Clipboard section of the Home ribbon, or Edit > Paste Special) to bring up Paste Special and make your selection. Alt+E+S+V is the shortcut to just paste values — probably the most common use of Paste Special.
2. Add Multiple Rows (2 minutes)
We often need to add new rows between existing rows. The shortcut (Ctrl, shift, +) is pretty handy, especially as you can toggle the + to add multiple rows. Often, just highlighting the number of rows you want to add (say 5) and using right click, insert is quicker when adding in bulk as it will add the number of rows you’ve highlighted.
3. Flash Fill (30 minutes)
Flash Fill automatically fills your data when it senses a pattern.
Suppose you have a list of product numbers in the first ten cells of column A, e.g. ‘ABC-00001’ to ‘ABC-00010’ and you only need the numbers after the ‘-’.
Establish the pattern by typing ‘00001’ into the first blank cell. If Flash Fill is turned on (File Options, Advanced) just start to type the next product number in the cell below and Flash Fill will recognize the pattern and fill down the remaining product numbers for you. Just hit the Enter key to accept. Or, get it going manually by clicking Data > Flash Fill, or Ctrl+E.
4. INDEX and MATCH (45 minutes)
INDEX and MATCH are the most widely used and most powerful tools in Excel for performing lookups. Used separately, these functions are invaluable, but it’s when you combine them that their true power is unleashed. INDEX and MATCH used in combination help you extract the data you need from a large dataset efficiently and precisely.
Here’s how these functions work:
VLOOKUP is a great function, but it has its limitations. It can only look up values from left to right. The lookup value must be on the left in the lookup table. INDEX and MATCH allows you to look up a value anywhere in the lookup table regardless of its position.
Let’s say for example you have a spreadsheet with a list of products. You have columns titled “Product Number”, “Profit”, “Product Name”, and “Revenue”. On another spreadsheet, you have a list of the product names and you want to look up how much profit each product has generated. In this scenario, we are using the product name (our lookup value) to look up the profit. The product name sits to the right of the profit and so VLOOKUP would not work. This is the perfect scenario for INDEX and MATCH.
The syntax would be:
=INDEX(Profit column,MATCH(Lookup Value,Product Name column,0))
Here’s a good way to remember how it works:
=INDEX (Column I want a return value from, MATCH (My Lookup Value, Column I want to Lookup against, Enter “0” )) (Zero gives you an exact match, you can match against less than (-1) or greater than (1) as well.)
At first and even second glance, INDEX and MATCH looks complex. It certainly needs some practice, but it’s worth getting your head around as it is more flexible and more powerful than a VLOOKUP.
5. Quick SUM (2 minutes)
This is one of the first functions you’re likely to learn in Excel — how to sum a row or column. But did you know you can select the cell at the end of a row or column and press Alt + to do this functions in seconds?
6. CTRL Z / CTRL Y (1 minutes)
Ctrl Z is a life saver to undo mistakes in Excel (and on every other Office product). What many people don’t know is that Ctrl Y does the opposite — redo. The two work in tandem very nicely, and you cycle through iterations of your work until you find the right one.
7. Remove Duplicates (10 minutes)
This formula is simple and quick to use. Remove Duplicates does exactly what you’d expect — it removes the duplicates in any given data set. Harvard's advice is "to remove the values you want to dedupe and place them in another sheet. It’s found on the Data tab in the Data Tools section of the Ribbon".
If you just want to highlight duplicates, you can do this using Conditional Formatting. The shortcut to get you there is Alt H L. (Or find it on the Home ribbon under Styles).
8. Freeze Panes (15 minutes)
Freeze Panes gets rid of the pain of scrolling down your spreadsheet and lose track of which column is which. You can freeze just the top row, first column or any number of either. Identify the columns and rows of the area you want to freeze. Then select the cell immediately to the right of those columns and beneath those rows. Go to the View tab and Freeze Panes in the Window section. Alt W F is the shortcut.
9. F4 = Absolute Reference + Repeat Previous Action (10 minutes)
There are two especially satisfying ways to use F4 in Excel. The first is when creating an Absolute Reference: F4 toggles you through the various options. The second is one that few people know about, but could seriously improve your Excel productivity. F4 repeats your last action, where available. For example, if you’ve just applied a border to one cell, use F4 to apply it to others.
10. CTRL + Arrows (5 minutes)
If you’ve found yourself scrolling through a dataset to reach the bottom of it, stop right now and start using Ctrl + the arrow keys! This simple shortcut takes you straight to the end of the data in a column or row you are using. Combine it with Ctrl, Shift to highlight/select large areas of data in seconds.