Skip to content

Email Newsletter Challenge 4

This week’s challenge will test your ability to perform a little bit of data analysis. You will actually have three tasks in total.

You can download the spreadsheet challenge using the links below.

The Challenge

This week, your tasks are:

  1. Calculate the total sales for each region
  2. Sort the regions in descending order by total sales
  3. Create a chart to show the comparison of total sales by region

Just take it one step at a time, and you can do it!

The Solution

For task one, the solution is the same for both Excel and Google Sheets. You will use the SUMIF function to calculate the total sales for each region.

Select cell G4, and enter the following formula.

=SUMIF($B$4:$B$18,F4,$D$4:$D$18)

This will give you the total sales for the North region. You make the first and third arguments absolute references because you want these to stay the same when you copy this formula down in the next step.

Speaking of the next step, use the fill handle to copy the formula down for the remaining regions.

At this point, things get a little different for Excel and Google Sheets.

EXCEL

The next step is to sort the regions in descending order by total sales.

Select any one of the total sales numbers you just calculated.

Then go to Home > Sort & Filter > Sort Largest to Smallest.

The data is now sorted. The next step is to create a chart to show the comparison of total sales by region. A bar chart is the perfect tool for this.

To create a bar chart, select the data in F3:G7.

Go to Insert > Bar or Column Chart > 2D Bar.

Change the title to Total Sales by Region by selecting and editing it.

You can then customize the chart further if you’d like to.

GOOGLE SHEETS

The next step is to sort the regions in descending order by total sales.

Select the data in F4:G7.

Right-click > View More Cell Actions > Sort Range.

Sort by Column G, and select Z to A. Click Sort.

The data is now sorted. The next step is to create a chart to show the comparison of total sales by region. A bar chart is the perfect tool for this.

To create a bar chart, select the data in F3:G7.

Go to Insert > Chart. Then in the panel on the right, change the chart type to Bar Chart.

Click on Customize. And under Chart & Axis Titles, change the chart title to Total Sales by Region.

You can then customize the chart further if you’d like to, and close the chart editor panel when you are finished.

And that’s it. You have now completed your fourth weekly spreadsheet challenge!

I hope you had fun and learned something new.

Also, if you want to learn more about everything that was covered in this challenge, I highly encourage you to sign up for the Excel Launchpad course. This is the best course you can take for nailing the fundamentals of Excel and getting this amazing skill onto your resumé.

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