Little Known Ways to Help Excel Work Faster

It’s often challenging to design and build large, complex Excel models - the kind of workbook that requires dozens of sheets, each with hundreds or thousands of rows and dozens of columns. But when every sheet requires values found on other sheets you can find yourself in the sort of situation that I recently discovered.

I inherited a file that produces Profit and Loss statements for a large number of departments. The file contains sheets that receive the updated values by account number for each of actual GL values, budget values, and forecast values. The proper values for each account for each department for each fiscal period got to the right sheet with the VLOOKUP function.

And therein was the problem.

A quick check revealed that most of the about 50 sheets contain over 25,000 formulas, of which probably around 24,000 were VLOOKUPs. But not just regular VLOOKUPs but the kind which check to see if the VLOOKUP generated an #N/A error, and returned a zero value if it did, but returned the VLOOKUP value if it didn’t.

So it turns out that most of the formulas had three calculations to make, two of which were VLOOKUPs, which take longer for Excel to calculate than most other functions. This in turn meant that each sheet had closer to 75,000 calculations to make. With about well over 40 sheets built in exactly the same manner, that’s around 3,000,000 functions for Excel to resolve each time it recalculated!

Redesign to Optimize for Excel

Of course, this wasn’t something you wanted to do regularly because on the computer that was used this took well over 45 minutes. Better hope the source data is right. Of course, when is this file used? During the tight timelines of the month end reporting days.

This was, simply put, an unacceptable situation. Even though the model provided perfectly accurate results, there had to be a better, more efficient way of getting there.

Well, it only took about an hour to revise the workbook using better techniques, and it’s still not perfect, but in reality it is a real bear of a file (at around 80MB) so it might not ever recalculate very quickly. There’s a fair bit of formatting and extensive use of the outlining tools, all of which is required. The recalculation time has gone down to about 2 minutes - a performance increase of over 2,200%! While two minutes for recalculating is still not ideal, it is much more usable than how it began.

4 Sure-Fire Ways to Build Efficient Excel Reports

The processing time was cut with just a few steps:

1. Add MATCH formulas on the sheet (in an area which isn’t printed) to determine the row number in which the lookup key value can be found for each of the values within the source data. In my case I added formulas to check on actual, budget and forecast values, each of which was on its own sheet.

=MATCH(key_value,list_of_key_values,0)

2. Replace doubled up VLOOKUP functions with a formula that checks to see if a match was found, and returns a zero if none was found, but returns the value from the appropriate table if it was.

=IF(ISNA(match_formula_cell),0,INDEX(...))

3. As mentioned above, instead of using the VLOOKUP function to return the value, use the INDEX function. Excel calculates this function much more quickly than VLOOKUP.

=INDEX(data_table,match_formula_cell,column_number_to_read)

4. Optimize the definition of the source data range. The original VLOOKUPs were defined to find the data table within the entire columns on that sheet, as in $A:$Z. This requires Excel to examine every row for values. Instead, a technique often referred to as “dynamic range names” helps limit the table to only the used cells. But that’s not the best part. By using dynamic range names, you don’t need to change the included rows manually when you revise the data set. Add to or delete from the existing rows (and columns too) and Excel will automatically revise the area that your range is set up to use. Just change the data and let Excel do the rest.

Data_Table is defined as:

=OFFSET(DataSheet!$A$1,0,0,COUNTA(DataSheet!$A:$A),COUNTA(DataSheet!$1:$1))

Naturally, all of the regularly updating source data was derived and brought into Excel with the use of Monarch, extracting values from reports run from a large ERP system.

For your homework today, start with a copy of one of your workbooks which use VLOOKUP functions extensively, and duplicate the functionality of the workbook without using a single VLOOKUP function. If you don’t already have a workbook with a number of VLOOKUP functions, make one. You can build test data very quickly by exporting one of your Monarch models to an Excel file. Finally, compare how the two versions perform.

Built carefully, even very complex reporting structures can be used efficiently to tap the data found in the thousands and thousands of records that can be generated when you excel with Monarch.