After 30 years, Microsoft Excel remains ubiquitous in business. The world’s quarter of a billion knowledge workers on average spend half an hour in the application every day. But despite this, Excel’s full capabilities are still poorly understood. Of 100,000 workers we’ve tested over the past three years, less than half know what Conditional Formatting – an essential feature – even does.
So what are Excel’s essentials? We reviewed articles written by Excel experts and combined this with aggregated data from thousands of our customers to compile a list of the 100 most important Excel functions, features, tips, tricks and hacks, ordered by utility. Where are your favourites?
How to use it
How many do you already know? Excel experts should know 80+, proficient users 60+, average users 40+ and if you know fewer than 40, we’d class you as a beginner. Scan the list for tricks which:
- a) you agree will be useful for you
- b) you don’t yet know
- c) ideally don’t take too long to learn
If you can find a handful that fulfil these criteria, learn them and become a more powerful human!
01. Conditional Formatting
Making sense of our data-rich, noisy world is hard but vital. Used well, Conditional Formatting brings out the patterns of the universe, as captured by your spreadsheet. That’s why Excel experts and Excel users alike vote this the #1 most important feature. This can be sophisticated. But even the simplest colour changes can be hugely beneficial. Suppose you have volumes sold by sales staff each month. Just three clicks can reveal the top 10% performing salespeople and tee up an important business conversation
A cell changes colour, depending on the number entered into it. What’s going on?
- A. Conditional formatting – user-defined rules are changing the colour
- B. Error checking – Excel is automatically spotting problematic data entries
- C. Data validation – a way of controlling user input
02. Pivot Tables
At 4 hours to get to proficiency, you may be put off learning PivotTables but don’t be. Use them to sort, count, total or average data stored in one large spreadsheet and display them in a new table, cut however you want. That’s the key thing here. If you want to look only at sales figures for certain countries, product lines or marketing channels, it’s trivial. Warning: make sure your data is clean first!
Which best describes the function of PivotTables?
- A. They are a way of allowing users to enter data into Excel
- B. They are a set of formatting templates for data
- C. They allow tables of data to be summarised in a flexible way
03. Paste Special
Grabbing (ie Copying) some data from one cell and pasting it into another cell is one of the most common activities in Excel. But there’s a lot you might copy (formatting, value, formula, comments, etc) and sometimes you won’t want to copy all of it. The most common example of this is where you want to lose the formatting – the place this data is going is your own spreadsheet with your own styling. It’s annoying and ugly to plonk in formatting from elsewhere. So just copy the values and all you’ll get is the text, number, whatever the value is. The shortcut after copying the cell (Ctrl C) is Alt E S V – easier to do than it sounds.
The other big one is Transpose. This flips rows and columns around in seconds. Shortcut Alt E S E.
04. Add Multiple Rows
Probably one of the most frequently carried out activities in spreadsheeting. Ctrl Shift + is the shortcut, but actually it takes longer than just right-clicking on the row numbers on the left of the Excel display. So Right Click is our recommendation. And if you want to add more than one, select as many rows or columns as you’d like to add and then Right Click and add.
05. Absolute References ($)
Indispensable! The dollar in front of the letter fixes the column, the dollar sign in front of number fixes the row F4 toggles through the four possible combinations. Try it out with the following exercise. Type out three foods horizontally in cells B1, C1, D1 (Olives, Granola, Tomatoes) and three colours in cells A2, B2, C2 (Green, Blue, Yellow). Now type in cell B2 ‘=A2&” “&B1’. Congratulations: Green Olives! Now – and here’s the exercise – add dollar signs so that when you copy the formula across you get green everything. Or just Granola, but of different colours. Experiment!
06. Print Optimisation
Everyone has problems printing from Excel. But imagine if what you printed were always just what you intended. It IS possible. But there are a few components to this: print preview, fit to one page, adjusting margins, print selection, printing headers, portrait vs landscape and spreadsheet design. Invest the time to get comfortable with it. You’ll be carrying out this task many, many times in your working life.
Which Excel ‘View’ gives the clearest indication of how a Worksheet will print?
- A. Normal View
- B. Reading View
- C. Page Break Preview
07. Extend formula across/down
The beauty of Excel is its easy scalability. Get the formula right once and Excel will churn out the right calculation a million times. The + cross hair is handy. Double clicking it will take it all the way down if you have continuous data. Sometimes a copy and paste (either regular paste or paste formulas) will be faster for you.
08. Flash Fill
Excel developed a mind of its own in 2013. Say you have two columns of names and you need to construct email addresses from them all. Just do it for the first row and Excel will work out what you mean and do it for the rest. Pre-2013 this was possible but relied on a combination of functions (FIND, LEFT, &, etc). Now this is much faster and WILL impress people. If Flash Fill is turned on (File Options Advanced) it should just start working as you type. Or get it going manually by clicking Data > Flash Fill, or Ctrl E.
09. INDEX-MATCH
This is one of the most powerful combinations of Excel functions. You can use it to look up a value in a big table of data and return a corresponding value in that table. Let’s say your company has 10,000 employees and there’s a spreadsheet with all of them in it with lots of information about them like salary, start date, line manager etc. But you have a team of 20 and you’re only really interested in them. INDEX-MATCH will look up the value of your team members (these need to be unique like email or employee number) in that table and return the desired information for your team. It is worth getting your head around this as it is more flexible and therefore more powerful than VLOOKUPs.
10. Filters
Explore data in a table quickly. Filtering effectively hides data that is not of interest. Usually there’s a value e.g. ‘Blue cars’ that you’re looking for and Filters will bring up those and hide the rest. But in more modern versions of Excel, you can now also filter on number values (e.g. is greater than, top 10%, etc), and cell colour. Filtering becomes more powerful when you need to filter more than one column in combination e.g. both colours and vehicles to find your blue car. Alt D F F is the shortcut (easier than it sounds – give it a go). Conditional Formatting and Sorting serve related purposes. Sorting involves rearranging your spreadsheet, which is intrusive and may not be desirable. Conditional formatting brings visualisation. Filtering is fast and effective. Choose well.
11. SUM
Used to add numbers up quickly. Note there’s Autosum as well and the shortcut Alt +. One problem people face is when you’re summing over some values with errors. See IF(ISERROR()) in this list.
12. Ctrl Z
Undoing mistakes is hard in life but easy in Excel. There’s a curved backwards arrow in the top left of modern versions of Excel. Or easier still Ctrl Z. It pairs nicely with Redo (Ctrl Y) as you try to hone in on exactly the place you want to get back to.
13. Format Cells
Manual formatting (as opposed to Conditional Formatting) is also important. You can change the font face, font size, font colour, background and border of a cell to draw the user to certain areas of your spreadsheet. Use sparingly for greatest impact. Check out Styles in the Ribbon for quick and mostly non-garish formatting options.
What is the correct terminology for the colour a cell is shaded?
- A. Background colour
- B. Cell colour
- C. Fill colour
14. VLOOKUP
Quick way to search for a value in the first column of a table and return another value in that column. But it’s a poor cousin to INDEX-MATCH. You’re likely to find situations where the value you want to look up happens not to be in the first column. So invest in INDEX-MATCH. Note that there’s also HLOOKUP (Horizontal Lookup).
What is the final argument 0 doing in this formula? =VLOOKUP(F7,B1:D5,3,0)
- A. It’s making sure only entries that EXACTLY match cell F7 are looked up
- B. It’s making sure the formula finds cells containing a 0 as well as a 3
- C. It’s an error, the formula will not work
15. Ctrl C
Shortcut for copying quickly. Goes together with Ctrl V (pasting), like a horse and carriage. Ctrl X is the shortcut for cutting (moving) data around. Sometimes this is what you need instead.
16. Ctrl V
Shortcut for pasting quickly. Preceded, almost always, by Ctrl C (copy)
17. Basic Arithmetic
Add (+), subtract (-), multiply (*), divide (/). You’ll also need to use brackets sometimes. For example, if cell A1 is 35, cell A2 is 50 and you want to calculate the % increase from 35 to 50 in cell A3, it’s ‘=(A2-A1)/A1’. Without the brackets your answer would be wrong. Remember BODMAS?
What does the character ^ do in an Excel formula?
- A. Raises a quantity to a power (e.g. 3^2 is 3-squared)
- B. Reformat the characters following it as a superscript
- C. Force characters following it to be interpreted as text rather than a number
18. COUNT and COUNTA
COUNT counts numbers, not words. Use it if you need a permanent display of how many of a thing there are or if you need it for other dependent calculations. Almost as useful is COUNTA which counts any text, numbers or string but not blanks. Note that for many purposes the status tray (in the bottom right-hand corner) with count enabled will suffice.
19. Remove Duplicates
Does exactly what you’d expect. Often safer and better to grab the full array, paste them as values in another sheet / workbook and then apply Remove Duplicates. Tells you how many items got removed. Useful to have an idea of how many removals there should be. A milder variation of this is Highlight Duplicates which is one of the options in Conditional Formatting.
20. COUNTIF
Counts cells with certain properties. Properties include: being a certain number or word (most useful), being above/below certain values, being an error.
21. Options Advanced
So many things to change here. You won’t want to change more than 1% of it. But possibilities for that 1% include: default font face/size/type, default number of decimal points in cells, default number format, disable multi-threaded calculation and autorecover settings. We think Autorecover is the best of these.
Which of these can you NOT customise in Excel options?
- The default font in which text is formatted
- The number of Worksheets in a new Workbook
- The names of functions such as COUNT() and SUM()
22. Charts
There are more than 20 chart types. Most people get by with Bar, Column, Pie, Line and Scatter charts. With Bar, Column, Pie and Line charts you just need a single series of numbers to generate a chart. With a Scatter, you need two sets of corresponding data to compare (e.g. height vs weight). Spend time getting comfortable with formatting charts so you can make them quickly and consistently.
Which graph would best show the relationship between company revenue and average employee age, for a sample of 100 companies?
- A. Radar chart
- B. Pie chart
- C. Scatter chart
23. Freeze Panes
Enables you to always see a certain number of columns or rows. Usually this will just be the very top row or two. Sometimes it’s helpful to have a column or two as well. Alt W F is the shortcut. You can also split windows and move around in each but this is disorienting for most people – freezing is better! Use ‘Unfreeze Panes’ to undo this.
24. SUMIF
Adds cells with certain properties. Like COUNTIF, these properties include: being a certain number or word (most useful), being above/below certain values, not equalling a value (<>), etc.
What would the formula =SUMIF(A1:A10,”Village”,B1:B10) do?
- A. For rows where column A cells contain the word Village, it adds up the numbers in column B
- B. For rows where column B cells contain the word Village, it adds up the numbers in column A
- C. It adds together the numbers in the ranges A1:A10, B1:B10 and the named range “Village”
25. Protect Sheet
The Format Cells dialog includes a Protection tab which will probably show you that all your cells are locked, but also tells you that this has no effect until you use the Protect Sheet command in the Review ribbon. To reduce the risk of someone inadvertently trampling all over your carefully-checked
Which of these is true of ‘locked cells’?
- A. Their contents are hidden, with users needing a password to view them
- B. Cells are locked by default, users can be prevented from editing them using ‘Protect Sheet’
- C. They display the same content in all Worksheets in a Workbook
26. F4
These are two great uses of the F4 function:
1. Repeats the last action. Say you just inserted three rows and you want another three…just press F4.
2. Toggles through the various combinations of dollar signs for rows and columns, i.e. your Absolute References. It is said that this is the most frequently used shortcut in the whole of Excel. If you only learn one, learn this one!
27. Sort
Reorder a table of data by the data in one of the columns (e.g. Spend). Very quickly you can draw out patterns and insights. Create a hierarchy of prioritising criteria by first sorting one column and then another (e.g. first by Spend and then by Sales). Occasionally it’s useful to sort from left to right according to the values along a row. Take care to be clear about whether your data has row headings. And if you have some referencing formulas going up and down your spreadsheet (e.g. INDEX-MATCHES) they are likely to be rendered useless.
28. Save As (F12)
Save As, fast. This is a more useful shortcut than Ctrl S as Autosave should have you covered for saving a file as is. Save As is useful for version control e.g. you want to keep your current file but create a fork in the road for the latest version. Use Ctrl Shift S to make your Save As that bit faster.
29. Move or Copy Column / Row
Move a column by clicking on the letter(s) at the top then hover over the edge of the highlighted row and drag and drop it left or right. Ditto for rows. If you want a copy of that column (or row) hold Ctrl as you do the drag and drop. If you haven’t seen this before, try it now, for a column.
30. Ctrl (Arrows, Pg Up, etc)
Jumps you from current active cell to the last populated cell in whichever direction you’ve specified (North, South, East, West). Ctrl Home takes you to A1. Ctrl End takes you to the last (most South-Easterly, as it were) used cell in the spreadsheet.
31. IF
Introduce a simple logic to your spreadsheet. For example you have test scores for a group of students and want to know who’s passed and failed. Assume pass mark is 70. Then ‘=IF(A1> 70,”Pass”,”Fail”)’ will give you what you need. Next step might be to add Conditional Formatting on the Pass/Fails to make it clearer still. Another way of tackling this problem would be to sort the data on test score, then there would be a clear divide between >70 and <70. For power users, check out IFS (multiple criteria).
What is the syntax of the IF(A,B,C) function?
A. A and B are tested for equality, C is the result if they are equal
B. A is the condition, B is the result if the condition is met, C is the result otherwise
C. A is the condition, B is the result if the condition is met, C is the flag for Boolean algebra
32. Linking Cells (eg ‘=A1=B1’)
A nice use of Excel’s logical capability is the = feature, set between two cells. This will return TRUE if A1 is equal to B1 and FALSE otherwise. A great use of this is to check that numbers in different areas of your spreadsheet that should calculate the same number are in fact doing so. For example, in a financial model you might have two ways of building up to a forecast revenue figure. Using the equals sign in this way can check for any errors. It might even be worth having a dashboard of error checking values (maybe spruced up with some conditional formatting) to ensure that everything’s working as it should.
33. Wrap Text
Fit all your text in the cell. A clear advantage is that you can read it all. A disadvantage is that it may make your spreadsheet less attractive with varying column widths and/or row heights. A way of combatting that is to wrap and standardise the column widths or row heights by highlighting them all and then setting a fixed common width/height.
34. IF and ISERROR
Errors (#VALUE!, #####, #DIV/0!, #REF!, etc) look ugly and can stop calculations working (e.g. summing over a range of values with a single #DIV/0!). You can avoid it by using ‘=IF(ISERROR())’. This combination of functions will put a value (for blanks, entering “” should work) if there’s an error and just whatever the calculated value is otherwise e.g. =IF(ISERROR(E25),””,E25).
35. Data Validation
Limit input values to a predefined list. Makes data entry fool-proof. You just choose an option from a drop-down list rather than type. Can be a little fiddly if your predefined list changes.
How could you ensure users entered only days of the week into a cell?
- A. Use the function =WEEKDAY()
- B. Format the cell as a custom date of syntax ddd
- C. Define a Data Validation List consisting of weekdays
36. Use of ‘ (apostrophe)
Sometimes you type what looks to Excel like the start of a formula. If the first character is +, -, = etc, Excel treats the cell differently and starts looking for cells to refer to. This can be disorienting. Simply putting an apostrophe at the start of the cell puts a stop to all that and it won’t appear in the cell (try it!) Also works if you have some zeros at the start of a number (eg 0099) which you want to keep.
37. Resize Columns / Rows
There are two ways you can do this.
1. Right Click on a column (or row) and select column width if you know the exact value of the width (or height for rows) you want.
2. Hover over the edge of the column (or row) and drag to the width (height) you want.
Having consistent column widths will improve the look of your spreadsheet. Highlighting the columns and applying either of the above methods achieves that efficiently.
38. F2
Activates selected cell for editing by taking you into the formula bar at the top. Saves you clicking there with the mouse. Surprisingly useful and many Excel users’ favourite shortcut.
39. Alt Enter
Gives you a line break within an individual cell. Can be used to depict bullet points within a cell (note that if you want to open with a ‘-‘ bullet you’ll need the apostrophe before it or else Excel will think you’re trying to create a formula). Generally this trick can be used to make the information in the cell stand out more clearly.
40. Number Formats
These can be frustrating for a lot of Excel users. Turning ordinary-looking dates into long strings of seemingly meaningless numbers and ###### errors. Get on top of these once and for all by defining a custom number format set and knowing where that is. We recommend having commas separating thousands, a sensible number of decimal places (usually 0, 1, or 2) and using ‘-‘ for blanks and negative numbers in red and in brackets. Here’s an example: #,##0_);[Red](#,##0);-?. You’ll have your own preferences.
41. Layout, Design & Formatting
A good job is getting your spreadsheet to a professional standard: consistent formatting, orientated, printer friendly, spreadsheet checks, etc. A great job is telling your story: the right cells should stand out and remain so as numbers update. This is great layout, design and formatting. Aspire to that.
42. Redo (Ctrl Y)
The opposite of Ctrl Z and works brilliantly with Ctrl Z to hone in on exactly the stage of your work you want to get back to.
43. Cumulative Sum
Suppose we have some monthly marketing costs in a spreadsheet, set out vertically, from cell B3 down. And suppose we want to know how much we have cumulatively spent in any given month of the year, so that we can see at what point we hit a certain budget level e.g. £500k. This formula does what we need beautifully as we enter into cell C3, say: ‘=SUM($B$3:B3)’. Then just extend the formula down column C as far as you need to. Try it!
44. Find and Replace
1. Find, using the shortcut Ctrl F. This will find you some text within any cell in your spreadsheet.
2. Together with Replace, change the string for a different string e.g. changing ‘APPLES’ to ‘ORANGES’.
Beware of Replace – unintended consequences are very likely with this feature! For example, suppose you had some apples in your spreadsheet (as it were) and you wanted to change them to oranges. That’s fine if all you have is apples. But if you had some pineapples too you’d end with some pineoranges. Interesting concept but probably not what your spreadsheet needs. Avoid using replace in large spreadsheets in which unintended consequences are hard to see.
Like Remove duplicates, Excel will tell you how many replacements have been made – try to have an idea of what number this should be so you can sense check.
45. & and CONCATENATE
Combines the contents of cells together. So along your first row if you have Mr in A1, James in B1 and Smith in C1, ‘=A1&B1&C1’ will give you MrJamesSmith. =A1&” “&B1&” “&C1 gives Mr James Smith. But note that & is usually enough. If you need to combine many cells, CONCATENATE may be faster
46. Extend Selection
Just as Ctrl Up-Arrow will take you quickly up your spreadsheet (up to the next stopping point), Ctrl Shift Arrow will highlight all the cells from the one currently selected up to the stopping point. Works very nicely with copying and pasting a bunch of values.
47. Slicers
PivotTable slicers do the same thing as Filters – they enable you to show certain data and hide other data as required. But rather than dull drop-down menus, slicers offer nice big friendly buttons to make the whole user experience nicer and easier. As well as fast filtering, slicers also tell you what the current filtering state is so you know what’s currently in and out of the PivotTable report.
48. Ctrl Tab
Try it but just make sure you have at least two Excel files open when you do!
49. MAX, MIN
Returns the maximum or minimum value from a range of cells. Conditional formatting and sorting your data are also ways in which you can find these values. This is just a bit faster. Useful as a quick check that in a huge set of data all the values are within a sensible, realistic range. (Note that you can get maximum and minimum values from the status bar tray in the bottom right-hand corner, too.)
50. Comments
If you want to say something about a cell or some cells in a spreadsheet, insert a Comment. This is especially useful for shared documents when you’re leaving comments for others. But they can get messy quickly when they’re shown and don’t do much good when they’re hidden. They get even messier if you move rows / columns around with the comments in. So use it sparingly and try to incorporate all data into your spreadsheet. Shortcut is Shift F2.
51. FORECAST
Calculates or predicts a future value by using existing values. You can use this function to predict future sales, inventory requirements or consumer trends. From Excel 2016 onwards.
52. Insert Symbols
You can insert almost any symbol. But in our experience, the ones you’re most likely to need are: tick, cross and star. These especially useful symbols are all available in Wingdings, best accessed with Insert Symbol.
53. FIND
Looks for a given string from within a cell and tells you if it can be found. For example, if you had a thousand email addresses and you wanted to know how many of them were Gmail, you would use Find to tell you for each email whether the string ‘Gmail’ appears or not. Then count the results (see COUNT, elsewhere on this list) or use Conditional Formatting to bring these out. Note that FIND is case sensitive; Search is a non-case-sensitive alternative.
54. Customise Status Bar
You can put a lot in here, including calculations (Maximum, Minimum, Average, Sum, Count), keyboard status (Caps Lock, Num Lock, Scroll Lock) and others. Pick what you need. We recommend Sum, Count and Average only. You’ll see the values appear as you select multiple cells with relevant values.
