Skip to content

Email Newsletter Challenge 23

This week will test your ability to add some flare to your spreadsheets!

Are you ready? Think you’re up to the task? Download the spreadsheet challenge using the links below.

Challenge

This week, your task is to create a progress bar in the merged cell above the data to show how many tasks have been marked as “Done.”

Solution – Google Sheets

You will have to use the SPARKLINE function to solve this problem in Google Sheets. Select the merged cell, and enter the following:

=SPARKLINE( COUNTIF( B6:B11, "Done") / COUNTA( B6:B11 ), { "charttype" , "bar" ; "max" , 1 } )

Once you’ve entered the proper formula, the progress bar will appear. The formula itself uses the COUNTIF function to count the cells containing “Done” and divides this value by the number of tasks in total (determined by the COUNTA function).

This result is then passed to the SPARKLINE function which creates the progress bar. And that’s it for your solution in Google Sheets.

Solution – Excel

To solve this problem in Excel, start by selecting the merged cell and enter the following formula.

=COUNTIF( B6:B11, "Done" ) / COUNTA( B6:B11 )

This formula uses the COUNTIF function and COUNTA function to calculate the percentage of tasks that have been marked as done.

Once you enter the formula above, you then need to apply conditional formatting to create the progress bar. Select the merged cell, and go to Home > Conditional Formatting > Data Bars > More Rules.

Check the box next to show bar only.

Set Minimum to Number and 0. Set Maximum to Number and 1.

Then click OK. And just like that, you have a progress bar for your task list in Excel! If you still have questions, just leave me a comment below. I’m here to help! 😊

I hope you had fun, felt challenged and learned something new. And if you’re not signed up for my weekly email newsletter, you can sign up right here!

This Post Has 0 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top