Back when I was working as a project lead, everyday my project manager would ask me the same question.
“Chandoo, whats the progress?”
He was so punctual about it, even on days when our coffee machine wasn’t working.
As you can see, tracking progress is an obsession we all have. At this very moment, if you pay close attention, you can hear mouse clicks of thousands of analysts and managers all over the world making project progress charts.
So today, lets talk about best charts to show % progress against a goal.
Please download example file and keep it handy while reading the rest of this tutorial.
Data for these charts
For all these charts, we will use below data:
Chart #1: Conditional Formatting Icons + % values
This is my all time favorite. It is very easy to implement and works really well.
All you have to do is,
- Select the % completion data
- Go to Home > Conditional Formatting > Icon sets
- Select 3 traffic lights
- Edit the rule as shown below:
- Done!
Why you should use this?
- Very easy to set up.
- Scalable. Works the same when you have 20 or 200 or 2000 items to track.
- Looks great
Keep in mind:
- The traffic lights in Excel are not great for color-blind people.
- The traffic lights do not look good when printed in black-and-white (or gray scale)
Related: Never show simple numbers in your dashboards
Chart #2: Conditional Formatting Data Bars
Another easy and quick answer.
- Select % completion data
- Go to Home > Conditional Formatting > Data bars
- Select Solid Fill if available.
- Done!
- Extra step: Adjust maximum bar size to 100% so that you can see relative progress better.
Why you should use this?
- Very easy to set up.
- Scalable. Works the same when you have 20 or 200 or 2000 items to track.
Keep in mind:
- By default the maximum value in your data takes 100% of the cell width. So make sure you set this to 100% for better depiction of progress.
Chart #3: In-cell bar charts
If for some reason you cannot use databars, then rely on in-cell bar charts. These are simple to setup and works great in many situations where conditional formatting may not be an option.
- Assuming your % data is in A1,
- In adjacent cell (B1), write = REPT(“|”, A1*100)
- You will get a lot of pipe symbols | in this cell.
- Select the cell and change font to Playbill
- Adjust font size and color if needed.
- Done!
Why you should use this?
- Very easy to set up.
- Scalable. Works the same when you have 20 or 200 or 2000 items to track.
- Can be handy when making dashboards or reports (where conditional formatting may have limitations)
Keep in mind:
- The font & size has impact on how in-cell chart is displayed. Use either Playbill or Script fonts.
Chart #4: Pies
Conditional formatting pie charts are a simple alternative to show % progress data.
The process is same as traffic light icons. Make sure you adjust pie icon settings as per your taste.
Why you should use this?
- Very easy to set up.
- Scalable. Works the same when you have 20 or 200 or 2000 items to track.
Keep in mind:
- Pie chart icons have only 5 stops. So they are not really pies.
- Not everyone likes pie charts. Make sure your boss / customers dig them.
Chart #5: Color scales or heat maps
When you have a lot of items to track, your focus is really on which items are lagging (or leading). In such cases, a color scale (also known as heatmap) can work very well. It colors cells based on their value. For example, the darker a cell color is, the more that particular project is done and vice-versa.
Why you should use this?
- Very easy to set up.
- Scalable. Works the same when you have 20 or 200 or 2000 items to track.
Keep in mind:
- Make sure the color starting & end points are well contrasted. Else the color scale looks bland.
- By default color scales show the values too. To hide them use ;;; custom cell formatting code (how to).
Chart #6: Thermometer charts
This is my favorite technique. It works very well for data like this.
Tutorial on how to create thermometer charts.
Why you should use this?
- Easy to understand
- Scalable. Works the same when you have 20 or 200 or 2000 items to track.
Keep in mind:
- If any value is more than 100% the chart may not explain it properly.
- Make sure the axis min & max are set to 0 and 1 respectively.
- You need a dummy column with 100% in it to show outline of thermometer.
Download Examples
Click here to download example workbook. It contains all these charts.
Special bonus for you:
As a bonus, the download workbook also has 5 step tracker to make you awesome in Excel. Go ahead and download now.
What is your favorite chart to show % progress?
My most favorite chart is thermometer. The next is traffic light icon-set.
What about you? Which of these 6 is your favorite? Please share your chart in the chart. If you use something else altogether, please tell me. I am eager to learn from you.
More on comparison charts
Just like my project manager, I am sure your manager too loves tracking & comparison. If so, please go thru below articles to learn few more tricks to impress her.
- Us vs. Them – compare one value with many using interactive chart
- Best charts to compare budget with actual values
- Indicating lower & upper bounds on a chart
- Customer service dashboard – a case study in comparison
- Exploring Flu trends in excel chart – a case study in heat maps for comparison
Now if you excuse me, I have to report to my new project manager: my wife. She is asking me about the progress of taking down Christmas lights. And I am still at 9%.
17 Responses to “Budget vs. Actual Profit Loss Report using Pivot Tables”
Good Work, Yogesh & Chandoo! Thanks.
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
Hi Yogesh and Chandoo,
Thank you for sharing your knowledge!
You guys are great!
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
Thanks a lot for sharing the most powerful tool worldwide "knowledge"
Warm greetings from Peru
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
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
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!
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.
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
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?
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.
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.
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
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.
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
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