In this section, we will be working through the Excel task that Spreadsheet Sheila was assigned. It is broken into a number of sections. Please don’t be overwhelmed. The whole task is broken down into smaller tasks and you will see as you go through that there is a lot of repetition.
In this tutorial we will be using the Xlookup() function. This is a a newer function in Excel , but you will see there is a lot of overlap.
Overview
In the book, Spreadsheet Sheila was given a number of files to work with and her boss told her what needed to be done. That’s what we will be starting with. At the end of each section, we will have a completed version and that is what we will bring to the next step. Each video takes you step by step what you need to do. You don’t have to do them all at once. Be kind to yourself. Just do ONE section at a time
The main tools we need are the Xlookup() function and pivot tables.
Overview of the files and what we need to do.
These are the six files that Kim gave Laura to work on. Download them and save them in a location on your computer. I would suggest Documents.
Note: If you want to go straight to the completed version here it is
Adventure_Works_Sales_2024_pivot_table_ready xlookup region and month occup etc category
Here is a list of the files you will be using with links to them.
AdventureWorks_Product_Subcategories
AdventureWorks_Product_Categories
Overview of Xlookup() function.
You can find an overview tutorial for Xlookup here
Preparing the data
Section One – Adding the Region name
In this tutorial, we will be looking at how to update the Region name in our Sales file with the Xlookup() function.
Starting files
Completed file
AdventureWorks_Sales_2024_with_region_xlookup
Section 2 – Adding the gender/occupation/marital status
Starting files
You will be using the files
AdventureWorks_Sales_2024_with_region_xlookup
Completed file
AdventureWorks_Sales_2024_with_region_occupation gender and marital status
Section 3 – Adding the category name
We are doing this because we need to pull in the Category name later on into our Sales table so we need to create this interim step.
Starting files
AdventureWorks_Product_Subcategories
AdventureWorks_Product_Categories
Completed file
AdventureWorks_Product_Subcategories updated with category name xlookup
Section 4 – Linking the product file with category and sub-category names
Starting files
AdventureWorks_Product_Subcategories updated with category name xlookup
Completed file
Adventure works products updated with category and sub category xlookup
Section 5 – Updating the sales with category and sub-category names
Starting files
AdventureWorks_Sales_2024_with_region_occupation gender and marital status
Adventure works products updated with category and sub category xlookup
Completed file
AdventureWorks_Sales_2024_with_region_occupation gender marital status category subcategory xlookup
Section 6 – Updating the sales with product sales prices
Starting files
AdventureWorks_Sales_2024_with_region_occupation gender marital status category subcategory xlookup
Adventure works products updated with category and sub category xlookup
Completed file
Adventure_Works_Sales_2024_pivot_table_ready xlookup
Pivot Tables Overview
Section 7a – Pivot Tables – total sales by region
Starting files (from previous session
Adventure_Works_Sales_2024_pivot_table_ready xlookup
Completed file –
Adventure_Works_Sales_2024_pivot_table_ready xlookup region and month
Section 7b – Pivot Tables – sales analysed by occupation/gender/marital status
Starting file – from previous session – change the name
Adventure_Works_Sales_2024_pivot_table_ready xlookup region and month
Completed file
check the Sales by occup Gender status tab
Adventure_Works_Sales_2024_pivot_table_ready xlookup region and month occup gender status
Section 7c – Pivot Tables – sales by category
Starting file
from previous session
Adventure_Works_Sales_2024_pivot_table_ready xlookup region and month occup gender status
Completed file –
check the Sales by Category tab
Adventure_Works_Sales_2024_pivot_table_ready xlookup region and month occup etc category
You did it!
Congratulations on working your way through this. Obviously your scenario will be different but this is very typical of the sort of work that is done in Excel.