Skip to content

Excel Challenge 9 – Data Visualization

Welcome to Excel Challenge number nine!

In this article, I present four questions to test your data visualization skills within Microsoft Excel. In other words, I’ll see how good you are working with charts and graphs.

You can click the link below to download the spreadsheet and test yourself before reading through the article. In fact, I highly recommend it!

Excel Challenge 9 – File Download

Good luck, and give it your best effort!

Question 1

For question one, your task is to create a chart comparing the sales performance of each sales agent.

Question 1 – Solution

A bar chart is often the best solution for comparing numerical values. So that is what you will be using to solve question one.

To create a bar chart, select all of the data. Then go to Insert Bar or Column Chart > 2D Clustered Column.

You now have a bar chart comparing the sales performance of each sales agent.

Question 2

For question two, your goal is to create a chart to display the change in monthly revenue over the course of one year.

Question 2 – Solution

Question two requires you to depict the change in numerical values over time. A line chart is often the best tool for showing change over time.

To create a line chart, select all of the data. Then go to Insert Line or Area Chart > 2D Line.

You now have a line chart showing the change in revenue over the year.

Question 3

For question three, you must create a chart to depict the correlation between study hours and average exam scores.

Question 3 – Solution

When you want to show the correlation between two values, it is best to use a scatter plot.

So to create a scatter plot, select all of the data. Then go to Insert Scatter or Bubble Chart > Scatter.

Change the title to Avg. Exam Score vs Study Hours.

It’s also a good idea to add axis labels to avoid confusion.

Click on the chart, click the plus icon, and check the box next to Axis Titles.

You can then click on each axis label to edit the text. Change the vertical axis label to Exam Score. Change the horizontal axis label to Study Hours.

You now have a chart showing the correlation between these two metrics.

Question 4

For question four, your task is to create a chart showing the distribution of units sold. You also want to display the distribution in groups of 10.

Question 4 – Solution

A histogram is the best chart you can use to show the distribution of values in a dataset.

To create a histogram, select all of the data. Then go to Insert Statistic Chart > Histogram.

Double-click on the chart title and give it a name like Distribution of Units Sold.

Next, you want to chunk the data into groups of ten. In other words, you want to make the bin size ten.

To change the bin size, right-click on the horizontal axis and select Format Axis.

In the panel on the right, click on the data bars icon, select bin width, and change the value to 10.

You can then close the panel on the right. You can also expand the chart.

You now have a chart showing the distribution of units sold.

How Did You Do?

So how did you do? Did you find working with charts and graphs to be easy or hard? What was the hardest part?

Leave me a comment and let me know how you did. I’d love to get your feedback!

Also, if you missed the first few Excel Challenges, you can find them listed below.

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