Excel Challenge 28: Create a Column Chart With Conditional Formatting

Do you consider yourself to be a TOTAL BOSS at creating engaging, functional charts? If so, it’s time to put your skills to the test. 

Everything you need to participate in this Excel challenge can be found on this page. To take part:

• First, watch the challenge video and read the instructions below the video.
• Review the previously published video(s) and article(s) on which the challenge is based.
• Download the Excel worksheet you will use to complete the challenge tasks.
• Put yourself to the test!

Want to chat about your approach and process with other Excel-heads? Join our Slack channel to share your insights and questions with like-minded learners.

The challenge🧠

Here’s the scenario to be solved from the download file:

Every month the Sales Team logs their actual sales in an Excel spreadsheet. The Sales Team Manager would like to visualize how sales are performing against the monthly sales target of $2000. 

Tasks

It’s our job to create a clustered column chart with the following features:

1. The actual sales should be represented by columns, but the sales target value should be represented as a line. 
2. The chart should be conditional, i.e., the columns should change color depending on if they have met the sales target or not. 
3. The chart should update when the sales target value is changed in cell C2. 

The challenge is to create a chart that meets the above criteria. 

Would you like to test your knowledge further? Try to set the chart up so the columns in the data table can be hidden, but the chart data remains visible. 

How quickly can you complete this challenge? Fifteen minutes? Ten? Less?

Clues 🕵️

If you’re just getting started with Excel, check out the following resources to learn how to create and customize Excel charts.

Read: GoSkills Ultimate Guide to Excel Charts

If you’re already proficient with Excel, maybe you can tell us a creative way to solve this problem in our Slack channel.

Have fun!

The solution 🎉

We hope you enjoyed taking part in this challenge! Here is our expert's solution.

Liked it? 🤗

Stay tuned to the GoSkills Excel Resource hub for more Excel challenges, and check out our range of expert-led Excel courses for all skill levels to further sharpen your skills.

If you enjoyed this challenge, you might be ready to jump into our Microsft Excel Dashboards course, taught by award-winning Microsoft MVP Ken Puls. Try for free.