Software

Four ways to speed up Excel calculation time

Complex formulas and repeated references can bog Excel down in a hurry. These basic rules will help optimize your workbook operations.

How, when, and what Excel calculates is a huge subject. In general, cell references and calculation operations are the main performance vampires. Reasonable formulas and even lots of data don't usually slow things down. Complex formulas and repetitive references are the real culprits. Here are a few basic guidelines that should help you avoid calculation bottlenecks:

  • Avoid complex and array formulas. Use more rows and columns to store intermediate values and use fewer complex calculations.
  • Reduce the number of references in each formula to the bare minimum. Copied formulas are notorious for repeating references and calculations. Move repeated calculations to a cell and reference that cell in the original formula.
  • Always use the most efficient function possible: Sort data before performing lookups; minimize the number of cells in SUM and SUMIF; replace a slow array with a user-defined function, and so on.
  • Avoid volatile functions if possible. Excel recalculates these functions with each recalculation, even if nothing has changed. Too many volatile functions (RAND(), NOW(), TODAY(), and so on) can slow things down.

Miss an Excel tip?

Check out the Microsoft Excel archive and catch up on other Excel tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

24 comments
adjoe
adjoe

This relly motivate me that there is still and other way which to be use in solving Excel problem. First I need more enlightment and I also have a problem. I try to work on my Excel worksheet I have try all I could but I can't. Please this is the worksheet. GOD'S MERCY AND CO. NIGERIA LIMITED S/NO EMPLOY NUMBER EMPLOYEE NAME BASIC HOUSE ALL. TRA. ALL MEAL ALL. GROSS PAY TAX LOAN TOTAL DED. NET PAY REMARK 1 E036 PETER OKAFOR 5000 500 2 E019 OLUCHI WILLIAMS 4500 800 3 E027 FRANSISCA GODWILL 7000 300 E009 HOPEWELL ADEOLA 10000 850 E012 AJOSE ADEOGUN 8500 200 E032 JOLAYEMI AJOSE 9850 560 E021 OLATUNDE JOHN 7600 900 E020 ADEOLA ODEKU 8200 670 E017 ANIMASHAUN TUNDE 12000 430 E014 BIODUN ADEWARA 7800 450 Please if you properlly the higher figurs are under BASIC while the lowe figures aer under LOAN, PLEASE if arrange proper you get it thanks. Please you can sent me the answer to thses Email onijoe@yahoo.com

medler1
medler1

Macros, particularly ones which copy and paste across a multi-sheet workbook will hog resources. Any containing conditional loops can be really slow. One solution is to turn off screen refresh early in the routine and only turn it back on when returning control. Looks smarter too.

bobprickett
bobprickett

It's obvious the author does not work in a business environment that requires complex formulas and numerous references.

camilloi
camilloi

Summary: Formulae: Simple (reduce references) Functions: Efficient (minimize volatiles) Thanks!

william.r.hamilton
william.r.hamilton

I work with Excel files containing cumulative YTD data on worksheets that eventually exceed the old 64K limit (in Excel 2003)on the number of rows. Three helpful hints I've found are (1) turn off automatic recalculation (in Excel 2007, just add it to the Quick Access Toolbar). Just hit f-9 any time you want to recalculate; and (2) use the - option to freeze the header + first two data rows, even in pivot tables,(columns, too, if it's important to your work), then drag the scroll bar all the way down. That way, you don't have 'toggle' back and forth from the bottom to the top of your worksheet--just do all your work in the first cell beneath the header and copy/paste it all the way down; and (3) If you have to navigate a lot of worksheets, right click the worksheet scroll arrows at the lower left to pop up a window listing all your worksheets so you can directly navigate to any one.

neilb
neilb

Get a faster system :)

LocoLobo
LocoLobo

For example let's do some chemistry. Starting with a simple calculation for standard concentration ignoring units: Concentration = Weight * Purity / Volume The calculation for diluting this standard references the previous cell. If you do multiple dilutions you have cell 10 referencing cell 9 referencing cell 8, etc all the way back to the original weighing of your neat standard. Are you saying it would be less "complex" to have each cell directly reference the original? Even if that is faster, it seems to me it would make tracing calculation problems (errors) much harder. Or am I wrong? Either way thanks for the thought. I have noticed that Excel slows down when you have too many (the number seems to vary) worksheets within the same file. We have worked out ways around that but they create other problems.

mw00110011
mw00110011

I have a habit of opening Excel in stores selling computers, putting a '=rand()' formule into cell A1, then copying it through the entire range of the first sheet and waiting for the recalc to finish. The first time I did that on a machine running Excel 2007 (not realizing the gigantic increase in rows and columns) it complained and quit, even with 3 GB of ram. I'm sure someone wanted super-deep worksheets - MS has complied! The old Access rule is, never let the end user see the table - using Excel as an open table database seems to fall under the same caution. I like getting data into a structured container (excel sheet, access table, text file) and using some external code to further calculate and display results so I can keep user keystrokes outside the data. Also, learning how to combine and simplify cell formulas through VBA functions cleans up the sheets and does seem to speed up operations.

rfaass
rfaass

you can only recalculate the answer (=cell) you need, selection.recalc

Excelmann
Excelmann

I am currently using Excel files w/up to a half million rows per sheet (18 million filled cells) and up to 175 MB in size. My page file reaches 1.5 GB. It takes 3 minutes just to save the largest file, and Excel blocks out other apps while saving. Any ideas on maximizing Excel speed would be greatly appreciated.

dkidd23
dkidd23

My rule of thumb for a complex formula is longer than 25 characters, more than 3 cell references, more than two nested If statements, or more than 3 functions in the same formula. If it meets any one of these criteria then I create a custom function VBA. It will speed up the calculation time significantly.

ssharkins
ssharkins

By complex, I mean any formula/expression that requires numerous calculations. These are just guidelines though and not meant to make some other situation worse, as you're suggesting with trace. For the most part, these suggestions are for very large spreadsheets. In this case, if you find that the trade-off is worth it, just document your decision well.

kevaburg
kevaburg

I thought maybe he was referring to complex math such as Floating Point for example. Or using unnecessary significant places in calculations to increase the time taken to perform even basic calculations. Something that does slow worksheets down significantly though is workbook linking through multiple cell references. I found it better that when necessary, try to refer to only a single cell when making the link. To be honest though, most of my workbooks that I create do not suffer from performance problems when run on modern multi-core PCs.

gjlinker
gjlinker

Hi, Excel recalculates the workbook on saving. You can switch this off in the calculation options. Also, Excel likes to be in the foreground. I noticed a drop of performance to about 30% when Excel is not on top of other apps. Hope this helps, Gerrit-Jan Linker Developer of litLIB the Excel power functions pack: www.oraxcel.com/projects/litlib

kevaburg
kevaburg

I would at this stage recommend migrating your spreadsheet to a database solution. Be it Access, MySQL, OracleExpress or MSSQL Express, you will get much better performance especially if you happen to be querying the data that is being stored there.

neilb
neilb

Disconnect from the Internet and unload your AV software when working on the big spreadsheets. Should help you load and save quicker, anyway.

LocoLobo
LocoLobo

When a spreadsheet gets to big save it as a new file and do the new calculations there. The basic template is divided into pages (worksheets), standards concentration calculations, sample handling calculations, and analysis (calibration curves and sample concentrations) pages. A cell in one of the analysis pages will refer to several cells (sample weights, calibration formula, etc) on the analysis page, which refer back to their respective sample & standard calculation pages. The idea is to carry as many significant figures as we can through the calculations and round down at the final result. Over the years we have settled for an analysis spreadsheet with about 6-7 pages. Any more and you start to notice the time it takes to save the workbook.

p@re
p@re

I also noticed that conditional formatting was slowing down the reports (Excel 2007) when you paste the formats. These CF are copied one more time every times you paste in formats. You may end with 10s or 100s of the same formatting formulas for each cell of the sheet... That's too bad...

Arcturus16a
Arcturus16a

I'm interested in learning more about MS Query. I use Excel 2007 and would like to incorporate MS Query in some of my work. Can you provide a link where I can learn more? How is MS Query different from MS SQL Express?

staffordd
staffordd

>>>>>>Thats a bit big >>>>>>>I would at this stage recommend migrating your spreadsheet to a database solution. Be it Access, MySQL, OracleExpress or MSSQL Express, you will get much better performance especially if you happen to be querying the data that is being stored there. We've had the "use Access vs Excel" conversation in another forum here. It went round and round. Both do a lot. Excel has limitations, and often, moving to a database IS the answer. Sometimes, you have to stick with Excel, because Access may not be as easy to configure in terms of creating queries that accurately emulate Excel formulas. Personally, I use Access for almost everything, and Excel occasionally or for small scale work. However. There is a THIRD option. USE BOTH. A spreadsheet can be a LINKED OBJECT within Access. So - attach your spreadsheet TO your database. Crunch numbers in ACCESS wherever you CAN, and then go over to Excel to crunch ONLY when Access can't cut it. Best of both worlds. People forget about linking objects, but Microsoft did their homework, and while they don't speak fluently to each other - they DO talk. HAVE FUN ! dave

Gordon Or-8
Gordon Or-8

We use a lot of ODBC data extracted from our SQL server. If you do this the time to open the file can be shortened by selecting "remove external data from worksheet before saving" in the query properties dialog. (Obviously you can leave the data there if you need it as a starting 'snapshot' the next time you open the file.)

kevaburg
kevaburg

the use of PivotCharts and PivotTables. What you seem to be describing could have an answer in one or both of these.