How To Make Your Excel Models Faster

Home/Productivity/ How To Make Your Excel Models Faster

How to Make your Excel Models Faster

Improving Calculation Performance

By Jeff Robson

Do you have a large model that you’d like to speed up?  Here are some things you can do to improve performance.

1. Replace, Remove or Minimise Volatile Functions

Volatile functions must be re-calculated every single time anything changes if your spreadsheet.  This means that they really slow things down if there are a lot of them.

Volatile functions: RAND(), NOW(), TODAY(), OFFSET(), CELL(), INDIRECT(), INFO()

Options:

  • Consider removing unnecessary volatile functions
  • Replace OFFSET and INDIRECT with INDEX wherever possible (more information)
  • Calculate the values required then replace with values

2. Data Tables

data_table

Data tables (Data > What-if Analysis > Data Table) are great for running multiple scenarios through a single financial model but your model has to be re-calculated for every cell in the table.  In a large model with a large data table, this really slows things down.

Options:

  • Change calculation to exclude Data Tables (Formulas > Calculation Options > Automatic Except Data Tables)
  • Replace with macros or Scenario Manager (Data > What-if Analysis > Scenario Manager)

3. Calculation Options

automatic_except_data_tables

If you have a large model, having your calculation option set to Automatic means that Excel will recalculate everything that needs recalculating in your model any time anything changes.

Option

  • Change your calculation option to Manual so that Excel will only recalculate when you want it to i.e. before you save or when you press F9 (recalculate), CTRL+ALT+F9 (full recalculate), or SHIFT-F9 (recalculate this sheet): Formulas > Calculation Options > Manual

4. Sort your Data

If you’re using MATCH, VLOOKUP, HLOOKUP to retrieve data from a table, sort the lookup column so it’s in order.  Your formulas will run much faster.

5. Use Excel Tables

insert_table

Rather than writing formulas that refer to an entire row or an entire column, use Excel Tables (Insert > Table), then use the table row or column.
By limiting your formulas to only those rows and columns which contain data, Excel doesn’t have to look through thousands of blank cells.  If Excel does less work, it goes faster.

Example: instead of =SUM(A:A) use =SUM(Table1

[Sales]) to add just the rows that have data.

The speed gain is even more noticeable when using a lot of SUMIFS, MATCH or other kinds of lookup functions.

6. Replace Formulas with Pivot Tables

Excel Pivot Tables are very fast at summarising large amounts of data and you don’t even need to write any formulas.

Put your source data into an Excel Table (see above), then go to Table Tools > Design > Summarize with Pivot Table.

7. Remove Unncessary Formulas

If you only need to recalculate values once and they don’t change, why ask Excel to recalculate them all the time.  Keep a copy of the formulas at the top of your table in a formula row then convert the rest of the table to values so they won’t recalculate again.
If you need formulas again, copy them from the formula row back into your table, do the calculation, then turn everything back to values again.

8. Use the XLSB File Format

The XLSB file format is generally a smaller file size and opens faster than XLSX or XLSM.

9. Remove Workbook Links

Workbook links are slow.  Not only does Excel have to open the linked workbook, but it also has to extract the data from it to do the calculation.
Remove Workbook links and replace them with a copy of the data.  You may wish to create a macro that updates the data every time the workbook is opened or when you click a button.

10. Change the Order of your Calculations

cumulative(1)

Rather than calculating the same thing many times, can you change your formulas so Excel does the calculation once, then all subsequent formulas refer to this?
If you need cumulative totals for each month (for example), calculate each month as the sum of last month plus this month, rather than the sum of everything since the start of the project.  This minimises the number of values Excel needs to calculate.

11. Get Help!

If you’re still stuck and you’d like someone to help you with your model, just contact us and we’d be happy to help you out.

2017-03-08T03:54:05+00:00 Productivity|