Master the Fundamentals of Excel

Well done, you’ve almost reached the end of the course! Now you're going to practice what you’ve learned in an Excel file.

Over to You!

Scenario

You're working in a company that has two departments (Sales and Service) and operates in three regions: Europe, America and Asia. You have the monthly pre-tax sales figures for each department and each month for 2016 and 2017.

These figures are available here.

Instructions

Use Formulas

• Complete column  F  using a formula and the fill handle. You want to calculate the difference between the sales figures for 2017 and 2016.

• Complete rows  40  to  43  using a formula to calculate totals, averages, minimum and maximum values.

Perform Data Validation

• In column  B  , for rows  4  to  39  , add data validation to allow only two data entries: Sales or Service. 

Create a Pivot Table

• In a new tab that you’ll name Pivot Table, create a pivot table with:

  • in rows: the month.

  • in columns: the functions.

  • in values: the sales figures for 2017.

  • a filter on geographical area.

Create a Chart

• Using the data in your pivot table, create a chart that seems appropriate. Add a chart title.

Check Your Work

So, did you make it to the end? 😊 Let’s have a look! You can check your work with our answer key.

Download the Course Summary Sheet