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
to43
using a formula to calculate totals, averages, minimum and maximum values.
Perform Data Validation
In column
B
, for rows4
to39
, 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.