Have you seen those fancy looking cleaning spreadsheets for sale on Etsy? You know… the ones with progress charts, themed designs, instant updates, and mobile compatibility? Want to know how to make your own for free in Google Sheets? Fear not… I’m here to help (and my template is available for you to download for free later in this article).
Here’s a picture of the cleaning jobs dashboard that I’ll show you how to create.
It has a list of jobs for each day of the week, with a doughnut progress bar for each day. Tick them off when they’re done and the progress bars will update immediately. Just type in the blank spaces to add more jobs. There’s also a list of ‘other jobs’ for things you need to get done during the week (not on a specific day). Finally, there’s a weekly progress bar to show you how well you’ve progressed overall during that week.
It’s easy to change the look and feel by using Google Sheets themes, and easy to make the jobs lists larger or smaller by just inserting rows! A handy little dashboard for any week long task!
Google Sheets is free, it works on your phone too, so there’s no excuse for not keeping your progress updated.
In this article, I’ll take you through each aspect, step by step. By the end, you should be able to make this yourself. You’ll also be confident enough to make improvements and changes to it to suit your needs.
Let’s go through how we accomplished this…
Changing the default grid size
By default, the cell sizes in Google Sheets are 120 pixels wide by 21 high. That’s a long rectangle which doesn’t help us create a nice-looking design. What we need is smaller cells so we can craft a dashboard as a sort of mosaic. We can merge cells together when we need more space for something.
If you’re starting with a new sheet, click on column A, hold shift, scroll across and click on column Z to select all the columns. Or alternatively type ‘A:Z‘ in the name box to select all the columns.
Then right click somewhere in the column header and select ‘Resize columns A – Z’.
We want to resize our columns to be 50 pixels wide.
Next, do the same thing for the rows. Click on the header of 1 row, scroll and click on the last row in your sheet. Or enter 1:1000 in the name box. Right click on the row header and select ‘Resize rows 1 – 1000’ and enter ‘Specify a size’ of 25 pixels high.
Now we should have a spreadsheet with nice small rectangular grid cells, which are easier to build a dashboard in, like this:
Creating daily job lists
Our lists are 5 cells wide by 15 cells high. 1 row for the header (5 cells merged), 7 for the jobs and 7 for the progress chart. The job rows themselves use 1 column for the tick box, and the other 4 cells merged for the job description.
To merge cells, select them by dragging your mouse, and then you can find the button to merge cells on the toolbar or under ‘Format > Merge cells > Merge all‘ on the menu.
Merge all 5 cells in first job list header row B2:F2. Click this new 5 column wide cell, type ‘MONDAY’ and use ‘Format > Alignment’ from the menu to center the contents vertically and horizontally.
Then, to add tick-boxes, select your tick box cells B3:B9 and click ‘Insert > Tick box‘ on the menu. Now merge all the job description cells for each row. Merge C3:F3, then C4:F4 and so on until you’ve done C9:F9.
You should now have something that looks like this:
Showing only ‘some’ grid lines
You’ll notice that our finished dashboard doesn’t look like a spreadsheet because you can only see some of the gridlines. How is that possible? Well, it’s actually accomplished by turning off ALL the grid lines in the sheet and using cell borders with a similar color for the ones you want to see.
We’ll do this now, for Monday’s cleaning chores. Open the borders button in the toolbar and select the color grey or light grey 1 from the border color selection box.
Let’s put the border around the entire list, select cells B2:F15 and click the ‘Outside borders’ button on the toolbar.
Now let’s put borders around the jobs. Select cells B2:F9 and click the ‘Outside borders’ button and then the ‘Horizontal borders’ button.
Now we’ll hide the grid lines, select ‘View > Show‘ in the menu and un-tick ‘Gridlines‘. Your job list should now look like this:
Using doughnut charts for progress
Now, before we can use a chart to show our progress, we need some data to base it on. We’re going to calculate this using a formula, and then hide the results from view behind the chart.
If a job is complete, it will have a tick AND it will have a description, so in cell B10, enter this formula:
=COUNTIFS(B3:B9,TRUE,C3:C9,"<>")
This formula means count a row IF column B is TRUE (ticked) and column C is not empty “<>”.
If a job is incomplete, it will be un-ticked AND it will have a description, so in cell B11, enter this formula:
=COUNTIFS(B3:B9,FALSE,C3:C9,"<>")
How many jobs are there in total? Those cells with have something in them, so in cell B16, enter this formula:
=COUNTIFS(C3:C9,"<>")
We also have to calculate our own percentage for the center of the doughnut chart, so merge cells C12:E14 and enter this formula into the cell:
=IFERROR(COUNTIFS(B3:B9,TRUE,C3:C9,"<>")/COUNTIF(C3:C9,"<>"),"No Jobs")
This will calculate a percentage completion figure, but return ‘No Jobs‘ if there are no cleaning jobs in the list for that day.
Use ‘Format > Alignment‘ to make the text of that cell center and middle aligned. Change it to BOLD font and size 16. Finally ‘Format > Number format > Custom number format‘ and enter 0%.
You can add headings for the data if you like, and some sample jobs to see it work. It should look like this:
Once you’re happy that the calculations work as you expect when you add new jobs, remove them, and tick and un-tick them, you can select the cells with the data in (except the percentage one) and change their text color to ‘White‘ so they disappear.
No use ‘Insert > Chart‘ on the menu. Select the doughnut chart type from the chart options toolbar.
Setup the chart like this:
- Setup tab:
- Data range: B10:C11
- Label: C10:C11
- Aggregate: Un-ticked
- Value: B10:B11
- Use row 10 as headers: Un-ticked
- Use column C as labels: Ticked
- Customize tab:
- Chart style > Background color: None
- Chart style > Chart border color: None
- Pie chart > Border colour: None
- Chart and axis titles > Title text: <Empty>
- Legend > Position: None
Finally, resize your new chart and drag it into the space at the bottom of the daily chores list. With a little re-sizing, you should be able to make it fit nicely into the bottom square of the list and position the percentage completion figure inside the doughnut hole. Like this:
Now, don’t worry, you don’t have to do ALL that again! Once you’ve completed one daily task list, you can simply copy and paste it to create the others.
Select cells B2:F16, ‘Edit > Copy‘, click cell H2 and ‘Edit > Paste‘. Then click your doughnut chart, select ‘Edit > Copy‘, click cell H10 and click ‘Edit > Paste‘. Finally, click your pasted doughnut chart and adjust the data ranges to H2:I10, so it works from the new daily list (as this doesn’t happen automatically).
Change the heading to Tuesday and you’re done.
Rinse and repeat for all the other days of the week, and also for the Other Jobs list. You should then have 8 nice looking task lists.
Calculating weekly progress
Our weekly progress bar uses a similar setup to the daily one above. Hopefully, after making the daily progress bar and task list you can understand how to format the weekly bar in the template. I won’t repeat all the steps, but the template has it’s weekly progress bar in cells B34:X36.
The horizontal progress bar in that section requires 3 pieces of data to work. These are again hidden (with white text) in cells H34, H35 and H36.
Cell H34 is the total of all completed jobs in all lists, calculated by simply adding up all the hidden completed job totals from each individual list:
=SUM(B10,H10,N10,T10,B26,H26,N26,T26)
I did the same to get the total of all jobs remaining in cell H35:
=SUM(B11,H11,N11,T11,B27,H27,N27,T27)
And finally, the total number of all jobs is in H36:
=SUM(B16,H16,N16,T16,B32,H32,N32,T32)
The percentage of overall weekly progress is in merged cells W34:X36, is formatted as custom number format 0% and calculated by dividing completed jobs by total jobs:
=H34/H36
Using sheets themes
To get fonts to change by changing your Google Sheets theme is as simple as making sure that you never change from the ‘Default (Arial)’ font in your sheet. You can change font size, bold etc, just leave the selected font as the default.
To get the colors of your daily task list headers and charts to change with the theme, you just need to ensure that any color you select for a chart, or a background, is picked from the theme palette. Google Sheets shows the current theme palette at the bottom of the color picker dialog.
Using a theme means you’re limited to 6 accent colors, but the advantage is that if you change the theme color, everywhere you’ve used it will also update. You can see this in action by opening the theme toolbar using ‘Format > Theme‘ and selecting a different theme.
Improvements you could add
If the lists are too short, and you need more rows, you can simply expand them by inserting rows. Right click below any of the job rows and select ‘insert 1 row above’. Sheets will take care of updating the formulas and the formatting for you, it should just … work!
With what you’ve learned from working through this article, you ought to find it pretty easy to add more task lists to this sheet by following the copy and paste procedure above. You could change this to work on a monthly basis by creating one list for each month of the year in a new tab, and create a yearly progress bar.
The same techniques would apply!
The finished article
If you want to look at our finished sheet, here’s the link: Weekly Cleaning Jobs. This template would work for any sort of job progress, not just cleaning. Your access to that finished version will be ‘Viewer Only’.
If you want to make a copy for yourself (which you are welcome to), either click this link to: make a copy of our cleaning jobs template, or open the link above and select ‘File > Make a copy’.
