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. |
46 Responses to “6 Best charts to show % progress against goal”
Chandoo, thanks for another interesting post.
One thing I'm missing is the question: What is progress, what does one want to know exactly?
I'm asking the question because I think of progress as not the same as "state of completion." Percentages/bars, etc., as shown above, are great to communicate state of completion, but less so for progress.
That's because project progress is how state of completion *relates to* the resources spent so far. Resources can be things like dollars spent, hours spent or project time passed. For example, 5% would be "good progress" in the first week of a one-year project, but terrible progress in the last week of the project.
The way I prefer to report progress is as a simple line chart with time on the x axis, and maybe a marking for the end point (and maybe an "ideal"/"as planned" line).
If it really must be a single number, you could go a EVA-ish route and divide the current % of completion by the current % of project time passed, which gives you a schedule performance index (1 or bigger than 1 = good; smaller than 1 = bad). For this, your suggested charts should work great!
I avoid 'progress' except where I can objectively assess progress, such as counting bricks laid or concrete poured. For intellectual work, I don't think that its possible to measure progress to completion with any reliability or credibility. I prefer to update forcasts of completion date, because that's where the effect of completion on dependent activities, deliverables and outturn value of the project is felt. This is also referred to as the 0-100 method. An activity is set at 0 complete until its actually finished, when it is set at 100% complete.
Hi Chandoo,
Great post! I have a preference towards thermometer charts too mainly because of the target/actual comparison.
Just an FYI...seems like the the screen shot for the pies #4 are under the #5 heading. Also the pies conditional formatting is something that doesn't accurately portray completion since the pies are segmented into quarters.
AND also a little trivia...those "pies" are called Harvey Balls, named after Harvey Poppel...
Chandoo,
I wonder. Is there a trick to unzipping your files?
I always seem to end up with a series of XML files rather than an XLSX.
Thanks a lot. 🙂
Eric~
Hi Chandoo,
Thank you again for this amazing help you are so resourcefull to make us little bit more amazing everyday.
When I click on the link on the page "http://img.chandoo.org/c/best-charts-for-goal-progress-comparison.xlsx" it is always bringing me to a zip file with all XML files without the XLSX file. I tried with mozilla and IE.
Thank you
[…] http://chandoo.org/wp/2014/03/10/best-charts-to-show-progress/?utm_source=feedburner&utm_medium=… […]
@All having trouble with download file.
1. Download the file.
2. Rename the extension as .xlsx
3. Double click or open it in Excel
Doesn't make any difference Chandoo, still end up with a zip file full of xml related files/folders
@Ian H
Download the zipped file and rename it to *.xlsx
where * is the filename
ps: Great name!
Many thanks for your help Hui but not sure why you are repeating what Chadoo said and which I first posted to because it didn't work for me. I did as he said and it didn't work, hence my post.
Chandoo says:
March 11, 2014 at 1:52 am
@All having trouble with download file.
1. Download the file.
2. Rename the extension as .xlsx
3. Double click or open it in Excel
Also, please note that we are investigating an issue with our webserver settings that may be causing this behavior. Sorry for the inconvenience. I am hoping to get this fixed in next 48 hours.
I used thermometer chart & conditional formatting using traffic lights. I just recently completed a dashboard I hope you can take a look but don't know where to send it. Thanks.
The in-cell bar charts is very interesting. This is not to be used as one can easly do manipulations by changing fonts/ font size etc
Hi..this is really helpful..
but I hve one quick ques..is it possible to hve conditional formating for chart graph based on text value and not the numbers..if I take your example project one bar should be red...if data is project 2 then it should be blue..basically we mke chart based on countries n each countries are assigned specific color...so I want a way where I can use conditionsl formating and not do it manaually each month.
You can set up conditional formatting rules to do this.
See this... it may help
http://chandoo.org/wp/2010/04/01/incell-panel-chart/
Hi Chandoo,
Great article and will be very useful.
One question - is it possible to have in-cell bar chart and the percentage complete (similar to icons)?
Try something like :
=CONCATENER(REPT("|";A1*100);REPT(" ";25-A1*25);"|")
it's quite nice
Hi Chandoo,
I am a great fan of you since i stumbled upon your blog. Your blog is very informative and insightful. I liked the way you presented the 5 steps using thermometer chart. I was very much inspired by that and tried to make my own version with 20 tasks to complete. On and after 17th step it was going downward. So I wanted to ask you that is there any limitation to thermometer chart
[…] shows us the 6 best charts to use, when you want to show your progress against a goal. There’s a sample file to download, so you can experiment on your […]
Is there any xhart is available which can show achivement percentage it may 80% or 120% means more an set target.?
Hi Chandoo,
Love your site. I have a small question regarding plotting data that contains ranking. I have 2 fields - Country, Rank. Note that i don't have the absolute values from which the rank has been calculated. So what is the best way of showing this on a graph given only the above 2 fields. Appreciate it
Regds,
Ross
@Ross
I would assign a set of simple numeric values to your ranks
Even a simple 1 to 10 makes plotting relativities easy
Dear Chandoo Sir,
Really awesome post.
Thanks.
Vignesh.V
We can always rely on Chandoo to explain to us clearly things that perhaps we already knew but weren't putting into practice the best way.
A limit I never liked about data bars was that they are monochrome - one colour for positive values, one colour for negative. So a couple of weeks ago I sat down to figure out a workaround. If anyone's interested...
http://digimac.wordpress.com/2014/06/29/multicoloured-data-bars-in-excel/
Epic fail on my part! After three months I just found out that what worked on my machine, didn't work on others.
Problem solved, more functions added.
The link above at
To hide them use ;;; custom cell formatting code (how to).
appears to be incorrect. However, using the downloaded file and selecting a cell(s) from that example provides the easy answer.
I wondered if the pies could have a color other than black and white (which, of course, would raise the color-blindness issue that you referred to with the traffic lights example).
Hi Chandoo!
Thanks for the informative post!
I have managed to understand and replicate all of the progress graphs except one, the thermo bar. I read up on the tutorial of how to create them, and I understand almost everything about the look and use of the bar, but one problem I am having is that I cannot seem to "center" the bar into the cell like you did. The reason being that even though the highest input (progress) percent is 100%, the program automatically puts in another 20%, so instead of 100% stopping at the end of the graph, it stops 20% short and I have a huge space at the end because of it.
How did you counter that problem? I have been trying for hours to fix it
@Aden
Set the Axis limits to Minimum 0 and maximum 1
Thanks. I started running a project recently, and I found your charts to be really helpful in tracking it's progress. I'm glad I found your page.
Hi Chandoo!
Great stuff for my customized project moving forward. However, when I use the blue block bars, the %ages spark up to smt like 5000% and cannot lower them nor scale them. If I input manually such as 50% without formatting a column, the bar for 50% e.g., will fill the cell completely, so that's kind of odd... what to do?
Thanks!
I guess I have the same problem. When I put 50 and click on the percentage, it is giving me 500%. Can someone help us on this. Thanks in advance
[…] http://chandoo.org/wp/2014/03/10/best-charts-to-show-progress/ […]
Hey,
Thank you for making this page. I do have one problem with the thermo graphs. Whenever I try to drag the graphs from one cell to the cell beneath it, the data remains selected on the former.
For example, if I had a thermo with a target number in A1 and an actual number in B1 with my thermo in C1, when I drag my thermo into C2, C3, etc., all of the graphs show the results from A1 and B1.
Is there a way to have these graphs update automatically as I will be regularly working in an excel file with hundred of entries?
P.S. I removed the $ symbols from 'Select Data', but that did not fix the problem.
Thanks again!
@Lisa
Not sure but it sounds like the new cells have Conditional formats applied
Select just the new cells
Select Conditional formatting, Clear Rules, Clear Rules from selected Cells
Hi Chandoo.
I am charting on some defaulter data where greater than zero is not desirable. Problem is that I have to highlight zero as target and anything above as undesirable. Seek your help
Hi Chandoo
Great post!
But I am wondering why bullet chart is not on this list. Is there a reason for its absence?
Thank you for these instructions. The bonus 5 Step Progress Meter you included would be perfect for my project. Where can I find the instructions?
Hi,
Do you know of any simple way to reduce the Data Bars padding so that they fit within the cells?
Thanks and great posy!
Regards
Appreciating the dedication you put into your website and in depth information you
provide. It's good to come across a blog every once in a while that isn't the same out of date rehashed information. Wonderful
read! I've bookmarked your site and I'm including your
RSS feeds to my Google account.
With #1 and #2, how would you also apply a red amber green to the bars (is it possible within chart formatting or would you need to utilise CF)?
I'm thinking of an in cell bar of some kind which will show against a known goal end date how far along with the goal you are (this is to be used for 'how many of the X number of people that I need to train in X timeframe, have been trained and therefore which of each training group is on track to complete on time or falling behind'.
So there would be knowns of number of people, target end date but I'd want it to reflect accurately as some groups of trainees might only have 50 in so their 50% done would be different to a group of trainees where their group had 200 people in it - but 50% would still be the same. Somewhere there'd probably need to be something which noted that there was a different volume of trainees so it could but the remaining effort to train people into context?
Hope that makes some kind of sense, I could be waffling!
[…] charts. Its got things like “Best Charts to Compare Actuals vs Targets” and “Best charts to show progress“. I love me some charts […]
Thanks a lot my dear.
very Useful it for me.
Another great post, thanks for sharing.
Chandoo, I am just starting an Excel class, and everything in the class is new to me. I am learning how to use all of these great charts but don't know what they are all used for. Thank you for your post and I think I will be able to use this down the road throughout my business career
in the above charts , Chart #2: Conditional Formatting Data Bars
->Assume if we have completed 35% of work it is showing in Blue color ,in the same cell remaining 65% of work should shows in some color , how to show?
Hi Sir,
This is Rachit and I am a big fan of you and your work. This is to request you please make a video for Beverages Sales performance data analysis in Excel.
Regards,