As part of Speedy Spreadsheet Week, I have emailed a few renowned Excel experts and asked them to share their tips & ideas to speedup Excel. Today, I am glad to present a collection of the tips shared by them.
Excel Speeding up & Optimization Tips by Hui
About Hui:
Hui (Ian Huitson) has been writing & contributing to Chandoo.org for more than 2 years. Many of you know him from Formula Forensics & Data table related articles on Chandoo.org. See about Hui page for more about him.
In no particular order:
- Minimize the use of Volatile Functions
- Organize your workbook layout and data methodically
- Where possible use fixed values or Named formulas instead of lookups even if the values only change rarely, flag those for manual checking
- Don’t Start equations with a + that actually adds 0.4% calculation time
- Minimize use of the Data Table command to running summaries only at the end of a project
- Review the logic of the model and all if’s or lookup choices for necessity or alternatives
- Use negatives instead of multiple positives where appropriate in conjunction with If’s and Lookups
- Learn about Conjunctive Truth Tables, they Rock for reporting
- Array formulas can do the work of dozens of normal cells, but use cautiously
- Use Named Formulas and UDF’s instead of multiple Helper Cells/Rows or Columns
- Minimize of us Conditional Formatting
- Minimize use of linked workbooks especially if over network drives
- Take an advanced Excel course like the ExcelHero Academy
- Minimize the use of Excel 2007
Links:
Excel Speeding up & Optimization Tips by George
About George:
George runs Excel Unusual, where you can learn about using Excel for engineering, simulations & games. In his work, he builds complex spreadsheet models all the time. So I asked him to share a few tactics with us. He wrote 2 articles in response to my request.
Links:
Excel Speeding up & Optimization Tips by Gregory
About George:
Gregory runs Excel Semipro, where he shares Excel tips & ideas. I asked him to contribute to the Speedy Spreadsheet Week. This is what he says,
Tips by George:
To speed up my worksheet files, I have one primary rule: do not use the OFFSET function, which is volatile and can slow things down considerably. In newer spreadsheets I use Tables and The imposing INDEX function to keep ranges automatically updated. In Excel 2003 I use an event-based approach, with named ranges, the worksheet deactivate module, and VBA to keep lists and ranges updated.
Links:
Excel Speeding up & Optimization Tips by Luke
About Luke:
Luke is one of the Excel Ninjas at Chandoo.org where he contributed more than 1000 posts. I asked Luke to share some optimization tips based on his vast experience of using Excel & helping others. This is what he suggests:
- In VB, whenever I see a line like Selection.something that’s usually an indicator that I’m using extra lines. Either I need to apply the method directly to the object instead of selecting it, or I need to use a With statement.
- With Event macros, don’t forget the all-important lines of Application.EnableEvents = False and Application.EnableEvents = True so that you don’t cause multiple events to be triggered.
- See a section of code that you’re repeating? Probably need to make this a separate Sub or Function that you can then reference from the main code.
- When building your formula page, think top-down. Cells near the top of worksheet should be referenced in formulas that are below, not vice-versa. XL likes to calculate left to right, top to bottom. Scattering cell references around makes it work harder.
- When using large amounts of data that you want to be charted, sometimes I’ll build a formula sheet within the workbook with data, and then just build another workbook that uses a data query (referencing the formula results) to generate the charts.
- This might be more along the lines of auditing a worksheet, but sometimes it’s hard to see how I’ve laid out my constants and formulas, and using a worksheet map helps bring things into focus (related: create a worksheet map)
Want to become better in Excel? Join Chandoo.org courses
Excel SchoolLearn Excel from basics to advanced level. Create awesome reports, dashboards & workbooks. |
VBA ClassesLearn VBA & Macros step-by-step. Build complex workbooks, automate boring tasks and do awesome stuff. |
Excel Speeding up & Optimization Tips by Narayan
About Narayan:
Narayan is one of the Excel Ninjas at Chandoo.org where he contributed more than 1000 posts. I asked Narayan to share some optimization tips based on his vast experience of using Excel & helping others. This is what he suggests:
Period-to-Date and Cumulative SUMs
There are two methods of doing period-to-date or cumulative SUMs. Suppose the numbers that you want to cumulatively SUM are in column A, and you want column B to contain the cumulative sum; you can do either of the following:
You can create a formula in column B such as =SUM($A$1:$A2) and drag it down as far as you need. The beginning cell of the SUM is anchored in A1, but because the finishing cell has a relative row reference, it automatically increases for each row.
You can create a formula such as =$A1 in cell B1 and =$B1+$A2 in B2 and drag it down as far as you need. This calculates the cumulative cell by adding this row’s number to the previous cumulative SUM.
For 1,000 rows, the first method makes Excel do about 500,000 calculations, but the second method makes Excel do only about 2,000 calculations.
Subtotals
Use the SUBTOTAL function to SUM filtered lists. The SUBTOTAL function is useful because, unlike SUM, it ignores the following:
Hidden rows that result from filtering a list. Starting in Excel 2003, you can also make SUBTOTAL ignore all hidden rows, not just filtered rows.
Other SUBTOTAL functions.
Using SUMPRODUCT to Multiply and Add Ranges and Arrays.
In cases like weighted average calculations, where you need to multiply a range of numbers by another range of numbers and sum the results, using the comma syntax for SUMPRODUCT can be 20 to 25 percent faster than an array-entered SUM.
{=SUM($D$2:$D$10301*$E$2:$E$10301)}
=SUMPRODUCT($D$2:$D$10301*$E$2:$E$10301)
=SUMPRODUCT($D$2:$D$10301,$E$2:$E$10301)
These three formulas all produce the same result, but the third formula, which uses the comma syntax for SUMPRODUCT, takes only about 77 percent of the time to calculate that the other two formulas need.
Dynamic Ranges
These are most often created using the OFFSET and COUNTA functions , as in the following :
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A)-1,1)
Sometimes , when data is stored in the form of records , so that all columns have data to the same extent , there may be several dynamic ranges ; say we have ORDER_ID in column A , CUSTOMER_ID in column B , and the AMOUNT in column C. Thus there may be several dynamic ranges as follows :
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A)-1,1)
=OFFSET(Sheet1!$B$1,0,0,COUNTA(Sheet1!$B:$B)-1,1)
=OFFSET(Sheet1!$C$1,0,0,COUNTA(Sheet1!$C:$C)-1,1)
These can be simplified to :
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A)-1,1)
=OFFSET(Sheet1!$B$1,0,0,COUNTA(Sheet1!$A:$A)-1,1)
=OFFSET(Sheet1!$C$1,0,0,COUNTA(Sheet1!$A:$A)-1,1)
These can then be optimized by storing the COUNTA value in a cell , and using the cell reference within the OFFSET formula :
=OFFSET(Sheet1!$A$1,0,0,Sheet1!$F$1,1)
=OFFSET(Sheet1!$B$1,0,0, Sheet1!$F$1,1)
=OFFSET(Sheet1!$C$1,0,0, Sheet1!$F$1,1)
Where Sheet1!$F$1 contains the formula : =COUNTA(Sheet1!$A:$A)-1
For more, refer to MSDN.
Resetting the USED RANGE
Pressing CTRL END will take the cursor and place it on the cell which Excel thinks is the last used cell in the worksheet.
Suppose you do this , and the cursor lands on D27 ; now navigate to any cell which is as far away as you can imagine , say AA3456 ; enter any character , even a space will do ; then clear the cell contents by pressing the DEL key.
Pressing CTRL END will now take the cursor to AA3456.
To reset the USED RANGE , go to the Immediate Window of the VBA Project , and enter the following statement :
Application.ActiveSheet.UsedRange
Your used range should now be reset to its earlier value of D27 ; pressing CTRL END will now take the cursor to D27.
Refer to this Stackoverflow discussion for more.
Excel Speeding up & Optimization Tips by Jordan
About Jordan:
Jordan runs Option Explicit, an Excel VBA blog. He shared these tips with us,
- When reading and writing to ranges, use .value2 (this is noticeable for large, iterative calculations)
- Ensure that ALL spreadsheet errors are handled. The most common errors I see ignored are #Ref errors and #Div (for dividing by zero). Use Go To Special… to find these errors and either delete them or use IFERROR to handle them. In my opinion, Excel errors are one of the biggest contributing factors to slow spreadsheets.
- When using INDEX, include the row or column number even if you don’t need it. For example, if I’m pulling data from only one column, I need only write =INDEX(A1:A10, 1) to pull the first item. However, =INDEX(A1:A10, 1, 1) appears to be a hair faster. Try it.
- Cut down on Lookup functions. In many instances, the lookup table has already encoded information in the correct order. Instead of looking up, say, Stage 2, just use INDEX on the desired column and pull from row 2.
Thanks to Hui, George, Gregory, Luke, Narayan & Jordan
Many thanks to all of you for sharing these ideas & tips so that we can speed up Excel. If you found these tips useful, say thanks to the contributors.
More on Excel Optimization & Speeding up:
Read these articles too,
- Optimization & Speeding-up Tips for Excel Formulas
- Charting & Formatting Tips to Optimize & Speed up Excel
- Optimization Tips & Techniques for Excel VBA & Macros
- Excel Optimization tips submitted by our readers
Want to become better in Excel? Join Chandoo.org courses
Excel SchoolLearn Excel from basics to advanced level. Create awesome reports, dashboards & workbooks. |
VBA ClassesLearn VBA & Macros step-by-step. Build complex workbooks, automate boring tasks and do awesome stuff. |
37 Responses to “Quickly Change Formulas Using Find / Replace”
Chandoo,
this is a really cool stuff what I use quite often. In addtion this method also could be a good choice to switch the reference type of the formulas from relative to absolute or vice versa. (just simply replace the $ in the same way).
Andras
@Andras: you are right, we can use find / replace to change references, reference types etc. Now, only if they had regex in find/ replace, we could so much more 🙂
@Tony Rose: Thank you. This is very useful and powerful feature. I even use it for cleaning up data. While formulas are good, they are not the solution for every problem. Often when I need more powerful cleanup / changing, I copy paste the stuff to text editors like notepad++ and then use their find/replace to do the dirty task.
What if i have to change the formula from ='Analysis'!C1 to 'Analysis 1'!C1?
I tried doing it using Find /Replace but could't. Encountered some errors.
And is there a way to change this using VBA???
Hi,
Did you ever get a reply to this?
Thanks
Ollie
to make your life easier, suggest you to avoid (Space) in worksheet names whenever possible. Consider (underscore) instead.
As the first formula wouldn't have the single apostrophes (since there's no space) need to include that in replace. So, search for:
Analysis
and replace with:
'Analysis 1'
This could be the most useful tips I've seen in a while. I use this all the time and can instantly change 400 formulas with a few clicks. Like so many other functions in Excel, I don't know what I would do without this one.
Keep 'em coming!
[...] on formulas: 5 areas where mouse kicks keyboard’s butt | Edit formulas in bulk using Find / Replace | Excel Formulas Online [...]
THANKS BRO
You, sir, are a god among men...
This is really cool. Your just save me hours of work. Thanks.
Thanks so much for this fix! It saved me tons of work. I'm muddling my way through and this really helped!
Oh... My... God!
This tip just saved me about 2 hours every month! I can't believe how easy it is to use. Now, can somebody tell me who I should call to get a refund for the previous 100 hours I spent manually changing formulas cell by cell?
Thanks so much!
THANK YOU!!!
THANK YOU!!!!
You saved me hours, I had a sheet that has more than 500 formulas, and i needed to replace the year in all of them, you saved me hours
Awesome info on replacing cell addresses in formulas. I have never heard about Ctrl+` before. Thank you!
I have something inside a formula like:
=sum(A1, A2*10) all over I now need to get rid of the *10 {=sume(A1, A2)} I thought to use the find replace trick above but with a blank in the replace but it then outputs just zeros. I thought I could trick it by doing *1 but then it just turns into =*1) with none of my references. Does anyone have an idea how to do this?
The Ctrl+ trick is cool.
@T
Instead of replacing with a blank try replacing
*10)
with
)
Thank you! This literally will save me hours and hours of time, and that's without losing my sanity in the process!
I have Sheet(1), Sheet(2), Sheet(3), etc ... Sheet(100).
Then there's a summary tab where I want to recap information on all those different sheets. Is there anyway to create a formula on the Summary tab to get ='Sheet(1)'!B$29 copied down for all 100 sheets without having to change each sheet # within the formula by hand?
@Brigitte
If you have a list of the sheet names in A2:A100
In B2: =INDIRECT("'"&A2&"'!$B$29")
Copy down
or if you don't have a list of the sheets names you can make it up on the fly
=INDIRECT("'sheet("&ROW()-1&")'!$B$29")
Copy down
Thanks for the suggestion. However, I copied your formula right back to my file and it didn't work. So I did it another way. I put the tab/cell reference in one cell and then did an =INDIRECT() to capture that information.
K2="'Sheet("&L2&")'!B$29" which has a value of 'Sheet(1)'!B$29
B2=INDIRECT(K2) which now has a value of 40 (contents on Sheet(1).
Thank you!!!!
Thank you ..
Hi, Out of all the formulae, I wish to replace the formula which has generated 0 value with blank space? I am unable to do it with find and replace function,
Please suggest.
Thanks.
Chandoo, you literally just saved me about 2 hours of work. I had a document with a daily report in two formats. The second formate just linked to all the appropriate cells in the other format (different sheets). This was 180 references that needed to be changed and I had to make this for a 4 week period (aka 28 different sheets at 180 references to change per sheet).
Thanks so much.
I have tried this way and without using the Ctrl-` formula view
Either way, I am trying to do something simple, but it won't let me.
I have a bunch of cells with a simple math formula like
=-(0.5*20)
various values in each cell, multiplied by 20
I simply want to change the multiplier globally from 20 to 25. But when I tell it to find *20 and replace it with *25, it replaces the entire cell contents with *25, rather than just replacing the *20 portion of the cell contents.
Can anyone assist with this? Seems so simple, but Excel isn't letting me do it.
Search/Replace 20 or 20) with a cell Reference eg A1 or A1)
Then put the value 25 in A1
By using a * in the search it replaces all the text
how to find a specific cell's value in a column & replace replace it with another cell value i actually need a method to replace a data in ca column and replace with the value i have in a specific cell can i give a [ location ] of data to what i need to find and then give row or column range to where i need to find and the given value & then give a [ location ] of data to what i want to be replace with the find and replace by row & column range & than by specific criteria and than by specific location.
please help.
how to find a specific cell’s value in a column & replace replace it with another cell's value.
i actually need a method to find a specific cell's data in a column and replace it with the value i have in a specific cell.
can i give a [ location ] of data to what i need to find and then give row or column range from where i need to find the given value & then give a [ location ] of data to what i want to be replace with.
find and replace by row & column range & than by specific criteria and than by specific location.
please help.
how to find a specific cell’s value in a column & replace it with another cell’s value.
i actually need a method to find a specific cell’s data in a column and replace it with the value i have in a specific cell.
can i give a [ location ] of data to what i need to find and then give row or column range from where i need to find the given value & then give a [ location ] of data to what i want to be replace with.
"find and replace by row & column range & than by specific criteria and than by specific location."
in more than 100 sheets in entire workbook
please help.
This is a great tool, does anyone knows an easiest way??
I'm working with a system that has over 59000 references... so every time the replace all is activated. I lose an entire day.
i actually needs to find cell number "D12" in column "D" and replace with Cell Number "B8" for example
find what = Cell Number "D12" John McNamara
find Where = in Column "D"
Replace with = Cell Number "B8" Bieber D'Souza
Replace Range = Column "D"
In which Sheet = All Sheets in Work Book (more than 100 Sheets)
Note: in every Sheet Cells Number "D12" & "B8" containing Different Employ Name but the find rang and replace rang are same in every sheet and find what cell number and replace with cell number are same also.
please help!
thank you. saved lot of time.
Thank you from the bottom of my heart!
Hi, I am trying to figure out how to use RE to find and replace several values in a column. Using find and replace does not work because of the values I am working with. I have a column with hundreds of rows that have a description of several operating systems and other info, which looks like this: Windows Server 2008 R2 Member Server Security Technical Implementation Guide; Windows 2008 Member Server Security Technical Implementation Guide; Solaris 10 10 SPARC SECURITY TECHNICAL IMPLEMENTATION GUIDE; and Windows Windows 2003 Member Server Security Technical Implementation Guide.
I need to be able to find and replace (or basically curtail the descriptions) to be Windows 2008 R2; Windows 2008; Windows 2003; and Solaris 10. BUT when I run find and replace with just *2008*, it finds every instance, including the ones with R2 at the end. I need it to only change the ones with 2008 to Windows 2008 and the ones that have 2008 R2 to Windows 2008 R2. I know it is possible, but I have no clue on how to write a macro to do this.
Thanks for your help,
Gerard
Wickedly efficient workaround. Excel really is a powerhouse program, all you have to do is dig into it. Ctl ~ exposes the formulas, and Ctl H allows for the multi edit. Brilliant, Chandoo!