This article is written by Myles Arnott from Excel Audit
I briefly covered Excel’s Go To Special function in the Managing Spreadsheet Risk series of articles and both Chandoo and I felt that it deserved a post all of its own.
What is Go To Special?
Go To Special is a tool within Microsoft Excel that enables you to quickly select cells of a specified type within your Excel worksheet. Once you get to grips with this function and what it can be used for you will wonder how you ever lived without it.
Where do I find Go To Special?
Shortcut: F5 or CTRL + G and then click on Special…
2003: Edit > Go To
2007 & 2010:Home > Find & Select > Go To Special on the Ribbon
(Note: a cut down selection of the most useful options in Go To Special can be selected directly under Find & Select on the Ribbon in 2007 & 2010.)
Lets look at Go To Special in action
Firstly download this workbook. This is more or less the same workbook that we used in the Managing Spreadsheet Risk series, modified slightly to allow us to cover all elements of the Go To Special function. (Note that it therefore includes a lot of errors)
Here are the options on the Go To Special dialogue box:
Lets run through each of the Go to special options.
Comments
Action: Selects all cells with comments
Benefit: A quick way of finding all cells with comments, particularly useful if you want to clear all comments from your worksheet
Constants
Action: Selects all cells containing constants
Options:
Numbers: Selects all cells with constants that are numbers
Text: Selects all cells with constants that are text
Logicals: Selects all cells with constants that are logicals (TRUE or FALSE)
Benefit: The number constants in your spreadsheet should all be inputs. Highlighting all constants is a great way of checking the structure of your spreadsheet. I normally format inputs with a white background and blue font.
A great tool for auditing – select all constants and change the fill colour. This instantly gives you visibility of your model inputs and flags any inconsistencies.
Formulas
Action: Selects all cells containing formulas
Options:
Numbers:Selects all cells with formulas that return numbers
Text: Selects all cells with formulas that return text
Logicals: Selects all cells with formulas that return logicals (TRUE or FALSE)
Benefit: Highlighting all of the formulas within your spreadsheet is a great way of checking the structure and consistency of your spreadsheet.
Blanks
Action: Selects all blank cells
Benefit: A quick way to select all blank cells. This is useful if you want to quickly format all blank cells or as a way of identifying cells that look blank but actually contain a constant or formula (i.e. with white on white formatting).
(Related: Fill Blank Cells )
Current region
Action: Selects the current region
Comment: I would recommend using the shortcut CTRL + * instead
Current array
Action: Selects the entire array if the active cell is within an array
Comment: I have never used this option but would be very interested to hear if anyone has.
Objects
Action: Selects all objects (shapes, images, charts etc)
Benefit: A simple way to select all objects. This could be useful if you wanted to quickly delete all objects in the worksheet.
Row differences
Action:
Single row: Selects the cells that are different from the active cell within the selected row
Multiple rows: The comparison is made for each row independently. The cell used for comparison for each row is the cell in the same column as the active cell.
Benefit: This is a very useful auditing tool for highlighting inconsistent formulas in a row.
It also offers a quick and easy way to spot differences across multiple rows.
(Note: You can change the active cell within a selected row by pressing enter)
Column differences
Action:
Single column: Selects the cells that are different from the active cell within the selected column
Multiple columns: The comparison is made for each column independently. The cell used for comparison for each column is the cell in the same row as the active cell.
Benefit: This is a very useful auditing tool for highlighting inconsistent formulas in a column. It also offers a quick and easy way to spot differences across multiple columns.
Precedents
Action: Selects the cells that feed into the selected cell(s)
Options:
Direct only: First level precedent only
All levels: All levels of cell precedents
Benefit: Provides an alternative to Trace Precedents in the formula auditing bar. Personally I prefer using this tool to select and then colour-fill the precedent cells as it allows you to select the precedents for a range of cells rather than just one. I also find that the arrows in Trace Precedents can get a little messy.
Dependents
Action: Selects the cells that the selected cell(s) feed into
Options:
Direct only: First level dependents only
All levels: All levels of cell dependents
Benefit: As above this provides an alternative to Trace Dependents in the formula auditing bar.
Last cell
Action: Selects the last used cell within your worksheet (containing data or formatting)
Benefit: A quick way to locate your last cell. This is a very effective way of identifying the range of cells used of the worksheet.
If your simple spreadsheet suddenly becomes very large in MB terms this can be due to Excel incorrectly thinking that you are using a lot more of the cells than you actually are . A good indicator of this is that the right hand scroll bar slider becomes very small. Using Go To Special Last cell lets you quickly identify the last cell Excel thinks you are using.
Visible cells only
Action: Selects cells that are not hidden (& therefore are visible)
Benefit: Useful if you only want to change the non-hidden cells and leave the hidden cells unchanged
Conditional formats
Action: Selects all of the cells with conditional formatting applied
Options:
All: Selects all cells with conditional formatting applied
Same: Selects all cells that have the same conditional formatting as is applied to the active cell
Benefit: An easy way to quickly identify all of the cells with conditional formatting applied to them. A useful tool for understanding the formatting applied to a spreadsheet.
You need to be aware that, depending on the conditional formatting set, you may not be able to highlight the cells using a fill colour as the conditional formatting may override it.
Comment: The manage rules option within the conditional formatting menu also enables you to identify cells with conditional formatting applied.
Data validation
Action: Selects all of the cells with data validation applied
Options:
All: Selects all cells with data validation applied
Same: Selects all cells that have the same data validation as is applied to the active cell
Benefit: An easy way to quickly identify all of the cells with data validation applied to them. This is particularly useful from an auditing perspective or if you want to clear the validations in these cells.
Some considerations for Go To Special
- Go To Special only selects cells in the current worksheet rather than the whole workbook.
- Go To Special searches within the selected range, if you want to select the entire worksheet ensure that only one cell is selected
Putting this in to practice
In order to give you some examples of how to use the Go To Special tools covered above I have put together a list of actions for you to run over the attached spreadsheet. Have a play and see what you discover:
(note that the action “Select cell A1” is simply to clear the current range selected. Failing to do this will restrict the new search to the currently selected range)
1) Look for cells containing data validation and conditional formatting
Select cell A1, Go To Special, Data validation (All)
Select cell A1, Go To Special, Conditional formatting (All)
2) Check the structure of the spreadsheet
Select cell A1, Go To Special, Constants ,text, fill the selection in brown
Select cell A1, Go To Special, Constants ,numbers, fill the selection in blue
Select cell A1, Go To Special, Constants ,errors, fill the selection in purple
Select cell A1, Go To Special, Formulas (leave all options ticked), fill the selection in green
Select cell A1, Go To Special, Formulas, errors, fill the selection in red
(Note: any cells with conditional formatting will not be changed by the fill colours above)
I have recorded the above steps into a macro to give you a useful audit macro that could be adapted for future use. Click on the button on the Info tab to run the macro.
See these pages for information on macros.
3) Check the range C9:S9 for any inconsistent formulas
Select the range C9:S9, Go To Special, Row differences, fill the selection in yellow
4) Review the precedents for the formulas in row 25
Select the range C25:S25, Go To Special, Trace Precedents, Direct only
5) See if there are any charts in the spreadsheet
Select cell A1, Go To Special, Objects
6) Find the last cell
Select cell A1, Go To Special, Last cell
Added by Chandoo:
Do you use Go to Special?
I use go to special (both dialog box and keyboard shortcuts) all the time. It is a really easy way to navigate a complex workbook and quickly select what you want. My favorite uses of Go to special are, selecting blank cells, finding data validations, locking formula cells, formatting input cells (constants). To find conditional formatting I usually go to home > conditional formatting > manage rules and see all the formatting rules in current worksheet. For formula auditing I rely on audit toolbar & manual inspection of the workbook.
What about you? Have you used Go to Special? What are your favorite features? Please share using comments.
Thanks to Myles
Many thanks to Myles for compiling all the tips & sharing this with us. If you have enjoyed this article, please say thanks to Myles. You can also reach him at Excel Audit or his linkedin profile.
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,