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.
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 Vlookup() 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: AdventureWorks_Sales_2024_pivot_table_ready completed
AdventureWorks_Product_Subcategories
AdventureWorks_Product_Categories
Overview of Vlookup() function.
You can find an overview tutorial for Vlookup here
There is also an information sheet 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 vlookup() function.
Starting files
Completed file
AdventureWorks_Sales_2024_with_region
Section 2 – Adding the gender/occupation/marital status
Starting file
Open the file called AdventureWorks_Sales_2024_with_region
Completed file
AdventureWorks_Sales_2024_with_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
Section 4 – Linking the product file with category and sub-category names
Starting files
AdventureWorks_Product_Subcategories updated with Category name
Completed file
AdventureWorks_Products updated with category and sub-category
Section 5 – Updating the sales with category and sub-category names
Starting files
AdventureWorks_Sales_2024_with_occupation gender and marital status
Completed file
AdventureWorks_Sales_2024 with category and sub-category
Section 6 – Updating the sales with product sales prices
Starting files
AdventureWorks_Sales_2024 with category and sub-category
Completed file
Adventure_Works_ready_for_pivot_tables
Pivot Tables Overview
Section 7a – Pivot Tables – total sales by region
Starting files
Adventure_Works_ready_for_pivot_tables
Completed file –
check the Sales by Region and Month tab
AdventureWorks_Sales_2024_pivot_table_ready completed
Section 7b – Pivot Tables – sales analysed by occupation/gender/marital status
Starting file
Adventure_Works_ready_for_pivot_tables
Completed file
check the Sales by occup Gender status tab
AdventureWorks_Sales_2024_pivot_table_ready completed
Section 7c – Pivot Tables – sales by category
Starting file
Adventure_Works_ready_for_pivot_tables
Completed file –
check the Sales by Category tab
AdventureWorks_Sales_2024_pivot_table_ready completed
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.