Excel Speedup & Optimization Tips by Experts [Speedy Spreadsheet Week]

Share

Facebook
Twitter
LinkedIn

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 Speedup & Optimization Tips by Experts

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:

  1. 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.
  2. 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.
  3. 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.
  4. 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.
  5. 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.
  6. 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 School

Learn Excel from basics to advanced level. Create awesome reports, dashboards & workbooks.

Click here to know more

VBA Classes

Learn VBA & Macros step-by-step. Build complex workbooks, automate boring tasks and do awesome stuff.

Click here to know more

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,

Want to become better in Excel? Join Chandoo.org courses

Excel School

Learn Excel from basics to advanced level. Create awesome reports, dashboards & workbooks.

Click here to know more

VBA Classes

Learn VBA & Macros step-by-step. Build complex workbooks, automate boring tasks and do awesome stuff.

Click here to know more

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst
Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

17 Responses to “Budget vs. Actual Profit Loss Report using Pivot Tables”

  1. Dau says:

    Good Work, Yogesh & Chandoo! Thanks.

  2. Abdul Kader says:

    Hi everybody,
    first sorry I am late to say something about this topic;actually I was waiting last part
    second I am not accountant I am an Engineer
    third """"Very Important""" the idea is not about Loss but I am sure it is profit
    Based on third it shows:
    1- How to use EXCEL
    2- How to use pivot TABLES
    3- How to collect and arrange DATA
    4- How to make reports

    Many Thanks

  3. UB says:

    Hi Yogesh and Chandoo,

    Thank you for sharing your knowledge!
    You guys are great!

  4. Alejandro says:

    thanks chandoo and yogesh, thanks for you lessons, are great!....i have a idea for a budget. I try to do it..... thanks for all

  5. SAUL ESPINOZA says:

    Thanks a lot for sharing the most powerful tool worldwide "knowledge"
    Warm greetings from Peru

  6. juanito says:

    Hi -
    This is a really great article because it's a simple and common thing you'd want to do with a pivot table but not at all obvious how to do it! So - muchas gracias to Chandoo and Yogesh!
    One thing - I couldn't get past the group error in the sample file. I would click on ungroup but it didn't seem to have any effect. I'd appreciate it if anybody has any pointers here.

    -Juanito

  7. Adam says:

    Hi Chandoo

    I am also having the group error. Can't seem to ungroup? Appreciate if you explain further on the steps required in order to get to calculated items.

    Many thanks and keep up the great work.

    Cheers
    Adam

  8. Catherine says:

    Hi Chandoo,

    I'm struggling resolving the problem depicted below:
    I have a set of data, with (among others) a "Region" field (can be APJ, EMEA, or AMS), and a "Country" field.
    Unfortunately, I need to group data by the following 4 Regions: APeJ, Japan, EMEA and AMS.

    I first tried to make a pivot with Region and Country in the rows (or columns), and then group Country data as per the above.
    Alas, as soon as I have a new Country that appear in my data set, my groupings are broken, and I have to redo the job of ungrouping, grouping etc.

    I thought I could try to use calculated item, by adding first a new column to my dataset concatenating Region_Country, and create an "APeJ" calculated item that would sum all the "APJ_*" and substract the "APJ_Japan", but again, no clue, as I can't find a way to use any wild card in those formulas.

    Given that I already found extremely helpful tips and tricks in your site that helped me manage that bunch of data, I'm pretty sure you'll have a bright idea on how I can solve that one!

    Thanks in advance for your lights!

    • Chandoo says:

      Hi Catherine...

      In such cases, I advice using an additional column in the data itself. You can set-up a grouping table else where with country in first column, region in second column. And then in the data, you can add an extra column and use VLOOKUP to fetch the region based on the country.

      Then feed this entire data (with extra column) to pivot table and use the extra column to group the data.

      • Catherine says:

        Hi Chandoo,

        Thank you for your prompt answer.
        I finally came to the same conclusion - after a rest 🙂 . I was probably too tired Friday evening (it was rather late), having spent hours in manipulating all my surveys data so as to pull rolling averages, make nice graphs and so on, and was trying to find a complex solution when there was a simple one.

        Thanks again,
        Catherine

  9. Tzu says:

    Hey,

    Great post!

    I for example have different database structure with the following fields :

    Date, Expense, Income, Sum (Income - Expense), Category (Sales, Cost of Goods and etc).

    Creating a P&L report for the whole year works great. Including gross margin % and etc.

    Though, creating P&L report by QTR/Month is becoming impossible since i get the following error : “This PivotTable report field is grouped. You cannot add calculated item to grouped filed.”

    Is there a solution for this kind of problem?
     

  10. klumsyboy says:

    Like Adam and Juanito, I also cannot ungroup.

    Would appreciate it if you can add a few more lines and a screenshot or two on where to put the mouse cursor to ungroup. 

  11. klumsyboy says:

    Hi,  I have figured out the ungrouping problem. One of the earlier steps was to group by month, if you pull the month back down to the column then right click and then select ungroup, then pull the month back up so you end up with just data source and budget/actual as the headings, then you can continue on.

  12. Kent Lau says:

    To solve the ungroup problem, my method is:
    Copy the "data" sheet to a whole new Excel workbook
    and directly work on Part 6.

    And since it is a fresh copy, Excel don't show me the "can't ungroup" problem. Hope this help.

    Thank you Yogesh for this wonderful tutorial.

    Kent, Malaysia

  13. felipe says:

    Just when i thought pivots were awesome i learn about inserting the calculated fields and that makes them more awesome. chandoo where have you been all my life.

  14. barrierone says:

    Hello - your P&L pivot version has really impressed my boss and would like to use it. I have applied it for a actual vs budget vs forecast model I have created. One problem. In your variance above the operating profit percent % variance shows 33.8% but I want it to show (0.01) point or the true diff from prior budget.

    I know I can add calculation to the side but boss would like to see it in pivot table.

    Please help
    Thanks

  15. barrierone says:

    I have a further query which may solve my above dilemma. Is it possible to add a column that calculates percent increase. So in the example above a new column would be added to show variance %.

    Any help would be appreciated.

    Thanks