All Resources

Taming Excel – My Favourite Spreadsheet Speed Hacks

Necessity is the mother of invention. Something’s not working and we have to figure it out. It applies to all sorts of things in life.


 

Yes, that is me baking bread on the barbeque after our oven broke (the iron skillet is for steam). It kinda worked but I miss the oven. Excel tips are sometimes the same. Late at night we are battling a pesky error message and after some Google searches we find a workable answer. It’s not very elegant – it works but it’s not great. Of course, by this point it’s always late and we move on hoping to never encounter the error again.

Sometimes it’s not even an error – we find we are always repeating actions in Excel that require several steps that feel redundant. Saving time requires research – spending time to save time can be hard to budget. Using the ALT key and the keyboard commands is always recommended but there are a bunch more. This article series is meant to do just that and share some of my favourite timesavers. This article provides two of my simple favourites:

  • Quickly check a range that Excel automatically selects; and
  • Fixing all my formulas at once

Quickly Checking a Range

Lots of tools in Excel will automatically “grab” a block of data to be used by that tool. An example is Pivot Tables that will automatically select any block of data near the cursor. Ideally it grabs all the data you want and the headers for the data. But if there are some formulas and calculations underneath or beside the data, Excel may grab too many cells and include blanks and other cells that should not be selected. As I like to say, I may love Excel but I don’t trust it – you should always double check what Excel selects.

This example shows what can happen – too many rows in the range. We can delete the range selected and redo it but you can save time with a little trick – Hit Shift and use your up and down arrows to move the bottom of the selected range. If the range is good then move the selection range back to where you started. But in the case below, we would move the arrow up three rows to exclude the “junk” underneath the data. Just hit Tab to navigate away from your corrected Table/Range.

 

Fixing All My Formulas at Once

This is a great tip especially if you are prone to do what I often do – fix the first formula in a schedule and then keep fixing other formulas in the same column. If I move on I will have forgotten to copy the fixed version across the rows and now I have a mix of fixed and erroneous formulas. To avoid this I changed my process when fixing formulas:

  1. Select the first formula to fix and then CTRL + SHIFT + Right Arrow to highlight the entire row
  2. Hit F2 (CMD + U if you have a Mac) and fix the first formula
  3. Final step – THIS IS THE KEY! – CTRL + Enter.

You have now copied the fixed formula instantly into the entire row in one step and it won’t affect your formatting!

Send Us Your Favourites

Do you have a favourite timesaver or technique? Send us your favourites and we may profile it in future articles. For the top three ideas we will be awarding a Das Keyboard – one of the best keyboards on the market. Please send your ideas to dave.thomas@marqueegroup.ca – I look forward to hearing from you!