How to Make a Project Progress Google Sheets and Excel Dashboard
If you've ever needed to show progress towards a project, goal, task, anything, this is a great way to do it in Excel and Google Sheets. Learn how to create a progress dashboard that utilizes check boxes, progress bars, and pie charts to show the complete picture of progress. The check boxes get marked as done, and you will see your percent to completion update along with your progress bar for each individual project. The best part is, the pie chart at the top shows the higher level, or the percent to completion of all of the projects combined. Watch the how to videos and get the formulas below to create these dashboards in no time!
How to create a project progress dashboard in Excel
1. Add your main headings and format them. I used Overall Project Progress in row 1 and Project Progress in row 13.
2. Add your Project 1 headings starting in row 16 and use the fill function to change the color so they stand out. Add borders around the Project 1 section by clicking in the top ribbon area. These can look however you choose.
3. Go to the developer tab and insert menu, then select check box and draw it in place. (If you don't have developer tab showing, right click on the top ribbon and click customize and then you will see developer in the options to add.
4. You may copy paste the check boxes and move them in place.
5. Add the formula for the percent to total. We need to count the total check boxes checked compared to the total possible check boxes.
=COUNTIF(C19:C22,"True")/COUNTA(C19:C22)
6. Right click on the check box and click the little arrow to link the cell to the cell that the check box is in and change the font where it says true or false to white so you can't see it.
7. Add the progress bar with this formula and make sure the font is Playbill.
=REPT("|",70*C15)
8. Conditional format for the colors. Go to the Home menu, Conditional Formatting, New Rule, and then click on Use a formula to determine which cells to format.
For all the progress less than 30%, we want to see red so enter this formula and change the font to red then click ok. You may adjust this percentage how you need.
=$C$15<.3
Repeat the same steps now for yellow, and enter this formula in the conditional formatting formula area.
=and($C15>.31,$C15<.67)
Repeat the same steps now for green, and enter this formula in the conditional formatting formula area.
=$C$15>.67
9. Copy and paste this whole project 1 section and link the check boxes, and then adjust the formulas in conditional formatting to match where the cells are now located. To do this, go to Conditional Formatting, then Manage Rules and change the cell references as needed.
10. In row 6, enter completed and incomplete and use these formulas. We need a percent to total again so COUNTA comes in handy.
Completed: =(C23=C33+G23+G33)/COUNTA(C19:C22,C29:C32,G19:G22,G29:G32)
Incomplete: =1-F6
11. You need to total the check boxes in each of the main four project sections, starting in C23 and then paste this formula, ensuring the correct cells are being totaled.
=COUNTIF(C19:C22,"True")
12. Do a spot check. We have 16 total check boxes in this example, so I recommend checking 8 of them, and completed and incomplete should both say 50% each. If not, revisit your formulas.
13. Change the colors and formatting of Completed and Incomplete to look nice.
14. Select Completed and Incomplete, both the words and percentages and go to Insert and choose a 3-D pie chart. Click on the title and press delete.
15. Click on each side separately and change the fill colors. We did incomplete gray and complete blue.
16. Click on the background and change fill to dark gray and the legend text to white. Right click and on Format Chart Area. Click on Text Options in the Format Legend to change text color.
17. Go to the View menu and uncheck Gridlines, then select all of the background cells and fill them light gray.
18. You have your dashboard!
How to create a project progress dashboard in Google Sheets
1. Add your main headings and format them. I used Overall Project Progress in row 1 and Project Progress in row 13.
2. Add your progress bars. Change the .3, .8, 1 as you like. I chose to only see green when a project is 1, or 100%.
=SPARKLINE(C15,{"charttype","bar";"max",1;"color1",IFS(C15<=0.3,"Red",C15<=0.8,"Orange",C15<=1,"Green")})
3. Add your Project 1 headings and use the fill function to change the color so they stand out.
4. Go to the Insert menu and select check boxes. You need four, you can drag down the lower right corner or you may copy paste the check boxes and move them in place.
5. You need to total the check boxes in each of the main four project sections, starting in C22 (We add a row later than will turn this into C23) and then paste this formula, ensuring the correct cells are being totaled.
=COUNTIF(C19:C22,"True")
6. Add borders around the Project 1 section by clicking in the top ribbon area. These can look however you choose.
7. Add the formula for the percent to total. We need to count the total check boxes checked compared to the total possible check boxes. Make the percentage larger and bold to stand out. Go up to the top ribbon to do this.
=COUNTIF(C19:C22,"True")/COUNTA(C19:C22)
8. Copy and paste this whole project 1 section, and then check the formulas, and update the project names to 2, 3, 4.
9. In row 6, enter completed and incomplete and use these formulas. We need a percent to total again so COUNTA comes in handy.
Completed: =(C23=C33+G23+G33)/COUNTA(C19:C22,C29:C32,G19:G22,G29:G32)
Incomplete: =1-F6
10. Select Completed and Incomplete, both the words and percentages and go to Insert, then Chart and choose a pie chart. Click on the title and press delete.
11. Click on each side separately and change the fill colors. We did incomplete gray and complete blue.
12. Click on Customize in the Chart Editor, you can get here by double clicking on the chart and check the 3D box, and then try 5% in the Distance From Center area under the Pie Chart menu, still in the editor.
13. Change the color of completed and incomplete to color code match to your pie chart.
14. Continue formatting in that window, changing the background of the chart gray and the legend text white.
15. Do a spot check. We have 16 total check boxes in this example, so I recommend checking 8 of them, and completed and incomplete should both say 50% each. If not, revisit your formulas.
16. Select the Date Complete cells and go to the top ribbon, changing the number format to Date. Select those same cells and the other RPAs and Done, and go to the top and add light gray borders so they are still showing when we remove gridlines.
17. Go to the View menu and uncheck Gridlines, then select all of the background cells and fill them light gray.
18. You have your dashboard!
Try Updoot free today and enter a new whole new realm of project management and progress updates!
Learn more about Excel here
All Excel formatting options explained
10 Tips for Creating Dashboards in Excel and Google Sheets
10 Excel Functions Every User Should Know