Excel Hacks

If you cannot tell from my images, I love Excel.  It’s G-d’s gift to accountants. Actually, it’s G-d’s gift to anyone faced with financial tasks.  Like you for instance!

Excel’s divine origins notwithstanding, when you are working with a large, complicated file with multiple tabs, calculations and the like, it’s really easy for errors to creep in.  Here are some methods I’ve adopted over the years to cut those down.

Keep it together  A lot of people like to use external references to link two excel files together and pull data from one file to the other.  Avoid that urge! First, those links just increase the complexity of maintaining both files.  Second, they become corrupted faster and more thoroughly than the average Israeli politician (this is serious stuff indeed). Third, if you want to check the file, you are left jumping around multiple files, as opposed to having everything in one place.

Accept that you are an idiot. Imagine the following…. You set up a chart in excel.  Later on, you go back and add a few lines of data.  Still later, you are looking at your numbers and trying to figure out why they make no sense. Or someone points out—during your presentation—that they appear to be flat out wrong.  After a solid hour of running through every, last formula in your godforsaken file you finally discover that when you added those additional lines, you forgot to update your calculations to include them. 🙁

Has this happened to you? It’s happened to me, right down to the Sisyphean searches through a 12 sheet, highly detailed file for the error.  To avoid my ever having to suffer this torture again, I adopted a method I call “idiot lines” as they make my files “idiot proof”, with the “idiot” in this case being me.

As befits a tool for a blithering idiot 🙂 , it’s super simple. Here’s how you do it.

  • Add a line between the data section and the calculation section
  • Narrow the line height and color it dark grey or some other non-distracting color. You want it to be immediately obvious to you that data never goes here.  This is your idiot line.
  • Set up your formula to always include the idiot line.
  • If you need to add data, only add it above the idiot line.

See an example, below.

Invest in good management. Where certain rates, percentages or parameters are going to be repeated over and over again throughout the file, use the name manager to define these as opposed to regular cell references in each page or (heaven forbid) typing the amounts individually in each cell. You can then refer to them throughout the file (e.g. +100,000 /NISUSD or 5,000*ISR_BENEFITS). In comparison to cell references or typing the amounts over and over, names are faster to use, streamline parameter value updates and improve consistency. “Improve consistency” here is a diplomatic way of saying “idiot-proofs your file against you forgetting what exchange rate/ percentage rate/ multiplier/ etc. you wanted to use and ultimately using five different rates and then having to go back into each and every cell and fixing it”. Not that this has ever happened to me, mind you.

As for the other benefits….  You do not have to use a defined name to set global parameters.  You can set up parameters on a worksheet and simply link to it throughout the file. For example, you can stick your exchange rate on sheet 1, cell B2 and just link to that.  My biggest problem with this method? Eventually, you are probably going to lose the reference.  Here you are, in the zone, copying formulas across row after row of cells, and then, oops!  You forgot to make the cell reference absolute and the following cells don’t link to the right value. Instead of linking to sheet 1, cell B2, it links to sheet 1, cell C2 and then sheet 1, cell D2 and so on.  Names, on the other hand, are absolute references and do not update position as you copy them.  Updates are also easier.  For example, if you want to increase the exchange rate in a file from 3.8 to 3.5 and you are using a defined name, all you have to do is update the value of that particular name and the entire file will be automatically updated.

Happy hacking!

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.