What we will learn
- Create flexible data aggregations using pivot tables
- Represent data visually using pivot charts
- Filter data using slicers in multiple pivot tables
Data Souce
Lab 1 – table
- Convert the data into an Excel table
- You can add total row, filter the data, and the total will reflect the total only for the filtered data. Let’s try this. Add a total row for the table, and use the Sum aggregation to show the total of the Revenue column and then filter the data only for United States.
Lab 2 – table
- Add a “Month” column. Insert a new column to the left of the Customer ID column, and use formula to derive the month of sales from the Date column.
HINT: Use the Text() function and look for a format code in the examples that would work on a date field. - Add an “Age Group” column. Remember to clear any filters you previously applied. Insert a new column to the left of the Customer Gender, and use formula to derive the age group from the Customer Age column. Let’s group the customers based on the following criteria:
- let’s add a “Frame Size” column. Insert a new column to the left of the Order Quantity, and use a combination of the IF() and RIGHT() functions to derive the frame size of a bicycle from the last two characters of the Product column, when the Product Category is Bikes. Otherwise, leave it blank.
HINT: Use the IF() function to test for Product Category=”Bikes” and if it does, use the RIGHT() function to extract the last two characters of the Product column. - let’s add a “Profit” column. Insert a new column to the right of the Revenue, and use formula to derive the Profit from both the Revenue and Cost columns. Show the total for the Profit column. Use the Sum aggregation in the total row of the table, for the Profit column.
HINT: Profit is Revenue minus Cost.
Lab 3 – Pivot table
- Insert a new pivot table based on the SalesData to a new sheet. Arrange the layout so that the pivot table displays the Product Category and Sub Category in the Rows, Year in the Columns, and Revenue(Sum of) as the Values.
- Insert another pivot table to the same sheet, next to the existing pivot table. Arrange the layout so that the pivot table displays the Country and State in the Rows, Year in the Columns, and Revenue (Sum of) as the Values. Sort the pivot table by Sum of Revenue so that the Country and State with the highest revenue is displayed first.
- Let’s add another pivot table. This time arrange the layout so that the pivot table displays the Frame Size in the Rows and Revenue (Sum of) as the Values. Hide the rows that do not have a Frame size (blank Frame size), then sort the pivot table by Sum of Revenue so that the Frame size with the highest revenue is displayed first.
- Add another pivot table with Age Group as the Rows and Revenue (Sum of) as the Values. You will learn how to custom sort the Age Group in the next module. But for now, sort the pivot table by Sum of Revenue so that the Age Group with the highest revenue is displayed first.
Lab 4 – Pivot Chart
- Add a pivot chart for the pivot table that shows yearly sales (revenue) by Country (the pivot table you created for question 2 in Lab 3A). Select a Line chart to display the yearly trend. Make sure that the Years are located in the X axis, the Revenue in the Y axis, and the Countries as categories.
Hint: You might find the Switch Row/Column in Select Data Source window useful. In addition, try Collapsing the Country fields in order to hide the States fields.
Lab 5 – Slicer
- Create a slicer for the pivot chart and table.