Software

Office challenge: List one change you can make to an Excel spreadsheet to improve its performance

In this challenge, test your Excel performance-boosting skills and learn the answer to last week's PowerPoint challenge.
Almost all of us have been saddled with a slow workbook. In particular, I remember one marketing spreadsheet that took all night to calculate. Now, sometimes there's just no getting around it -- either the data or the calculations warrant the time. But the problem is usually poor design or inefficient formulas, and you can improve performance. So this week's challenge is to list one way you can improve performance in an Excel spreadsheet. Last week we asked… How do you display a black or white screen on the fly during a PowerPoint presentation? Nigelboor was the first to respond correctly: Press B to display a black screen and press W to display a white screen. In addition, you can display a "blank" screen that defaults to the background color currently in use by pressing U. To return to the current screen, simply press the same key a second time. Amondracorp and Mliesman also knew the proper keystrokes. Michael-mps mentioned pressing the period key (.) to toggle between the current slide and a black slide. Thanks to everyone for playing along this week!

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.

14 comments
Who Am I Really
Who Am I Really

I do this as soon as the sheet is created: with formulas in place & ready for data: I Remove all but one extra column and all but two extra rows, I don't ever need all the columns up to IV and definitely don't need all the rows down to 65,536

Katyknock
Katyknock

Incorporate all reference material into the same workbook (on separate tabs) so the vlookups don't have to open a new file to operate.

Xephire
Xephire

Don't use the code created by the Macro Recorder... You can use it as the base of a macro, but you will need to tweak it a lot to optimize it. Macro Recorder is notorious to select a field before performing an action to it (bold, etc), while this is normally not needed. You can even perform actions on a field on another tab with switching to to this tab, just add the tab name and field name/cell number in the macro instead. Alex

wilson.baptista.jr
wilson.baptista.jr

Very often in a large spreadsheet you'll have formulas in several places that calculate a value the parameters used on which change only once in the full calculation, so avoid repeating those formulas along the spreadsheet, group those calculations on a section of the spreadsheet, define names for those intermediate results and use those names to refer to in the rest of the formulas. For instance, you may have a large spreadsheet the values used on which must be adjusted for inflation every day the spreadsheet is run, so define a name as the result of the inflation adjustment and reference it in the other formulas instead of calculating it on every one.

dlcartin
dlcartin

Fix 'Conditional Formatting' so that when cells (rows & columns) are deleted/inserted the 'CF' that was applied to a cell or range of cells will 'go' with the change.

zdnet
zdnet

I would suggest using a real spreadsheet application, like OpenOffice.org

jbafernandes
jbafernandes

MSExecel2007: Formulas:CalculationOptions:Manual ... then ... just press F9 any time you REALLY need to have the entire Spreadsheet Re-Calculatedd ...

t8omo1
t8omo1

Switch to manual calculation using Formulas - Calculation options - manual so only calculates when you want an answer rather than on every input/change

njconner1
njconner1

Storing formulas: This solution works best when resource intensive formulas are required when compiling data, but are not needed once table of data is fully assembled. I use this for audits between two systems - first I pull all data from both systems into one table using vlookups, then I store the formulas above and work with the static data table I've created. 1)insert a row above or below table headers (if you insert below header, hide the entire row so it doesn't get sorted in with your data) 2)copy any formulas in the table into the newly inserted row above (i.e. formula in column F would be copied into F1) 3)Once all formulas are stored in the row you inserted, select all of your data and then copy/pastespecial(values) Your entire table is now static data, and making simple changes in the workbook won't cause the entire bulk of your data to recalculate.

Laurie_G
Laurie_G

I always start new data on row 4 or 5, depending on what summary data (totals, statistics, etc) I want. If you have a table that's taller than your screen, a reader has to scroll down to see the bottom line. By keeping the summary data at the top avoids that, with a frozen screen below the header, will keep it there and update as data is entered. Add the previous trick to this, and you can enter new data top row down. Almost all my sheets work this way and are way easier to read, understand and digest than 'conventional' ways.

mikeda
mikeda

Recently discovered Excel does not share the same font capabilities as Word. It does not have All Caps, Small Caps, etc that are available in Word. Have to do copy and paste back and forth between Excel and Word to convert to All Caps or re-type in Caps Lock.

ilyaskazi
ilyaskazi

very true... I am playing with this trick since very long period for my projects using special paste as values to reflect formula areas.

njconner1
njconner1

There are formulas you can use to convert text capitalization. For all uppercase: =UPPER("Your text goes here") results in: YOUR TEXT GOES HERE Similarly you can do =LOWER("Your text goes here") results in: your text goes here or =PROPER("Your text goes here") results in: Your Text Goes Here My suggestion would be to copy all of your text into one column (say column A), and then enter your formula into B as "=UPPER(A1)" That way you can just enter your text all the way down the page, and only have write the formula once to convert all of your text.