How to show all formulas on the Excel sheet, and learn the HLOOKUP function

How to show all formulas on a sheet, and learn the HLOOKUP function

In our weekly blog, this Friday DataMinded brings you a tip, a function, and a shortcut that will help you to take your Excel skills to the next level and advance your career!

A quick win you can learn today -

When you’re working with a sheet of data in Microsoft Excel, it can often be almost impossible to see which cells are fixed numbers and which are functions, without clicking into each one. One was around this is to ask Excel to ‘show formulas’.

Go to the ‘formulas’ menu ribbon, and click the ‘show formulas’ button in the formula audit section. This will change the content of those cells containing formulas to show the formula instead of the results of the formula. Don’t forget to turn it back off again before you carry on with your work!

How to show all formulas on a sheet, and learn the HLOOKUP function

A keyboard shortcut I'm adding to my list -

F2 is one of the most useful keyboard shortcuts in Excel if you like using the keyboard instead of the mouse. It’s the equivalent of double clicking onto a cell so that you can edit it, and use it again to finish the edit. So next time you need to double click to edit in Excel, try F2 instead - it might get you there just a bit quicker.

 Check more useful keyboard shortcuts in Excel for Windows here!

A function I've found super useful this week -

I cover the VLOOKUP function extensively in our online course, Even now it’s one of the most common functions that I use day-to-day. This function allows you to return a value from another table, where two tables have a column in common.

It also has a lesser known cousin, HLOOKUP. This function is for horizontally organised tables - so instead of looking up a common column, you look up a common row between two tables. Like VLOOKUP, there’s 4 elements to the function:

  1. The lookup value - what do you want to look up

  2. The table where you want to look it up, starting with the common row

  3. The number of rows down from the common row that you wish to return

  4. Finally, a TRUE or FALSE value that tells Excel what to do when it can’t find what it’s looking for. Use FALSE to return an error or TRUE to return the closest match (we almost always use false)

If you have horizontally organised tables (particularly tables with months of the year going across the top), HLOOKUP can be a good option. Although at DataMinded we always recommend organising your data vertically where possible! It just makes everything easier.

As always, I hope you find these tips useful, and please do reply if you have any comments or questions!

- Martyn

 

Edinburgh, Scotland

November 2018