In this article, there will be six practice tasks. To solve the problems, you need to be familiar with inserting pivot tables, using slicers, utilizing calculated fields, inserting pivot pie charts, removing the grand total, editing the pivot table, hiding the gridlines from the table, and grouping by month.
Download Practice Workbook
Download the Excel file by submitting your Email Address:
Problem Overview
There are seven columns and 714 rows in the sample dataset. It represents sales data of a company from 2019 to 2022. The “Amount” column refers to the sales amount for the given date.
You’ll have six exercises related to the pivot table test for an interview. The exercises are provided in the “Problem” sheet, and the solutions in the “Solution” sheet.
Exercise 1 – Create a Pivot Table
- Use the provided data to create a pivot table that displays the Zone-wise sales amount.
Exercise 2 – Insert a Pivot Chart
- Create a pie chart with the quarterly sales data.
Exercise 3 – Group Pivot Table by Month
- Use the Date and Amount columns to insert a pivot table. Group the amount by the months.
Exercise 4 -Edit Pivot Table
- Insert a pivot table using the Client and Amount columns.
- Change the column headers to “Client”, and “Sales Amount”.
- Remove the Grand Total row.
Exercise 5 – Using the Calculated Fields Feature
- Create a pivot table using the Region and Amount columns.
- Use the calculated field feature to display the tax amount for each region.
- The tax is 5% for all the regions.
Exercise 6 – Using a Slicer
- Insert a pivot table using the Client, Region, Year, and Amount columns.
- Use three Slicers (for Client, Region, and Year).
The animated image below shows the application of the slicers.
The following image shows the solution to the first two exercises.
Download Practice Workbook
You can download the Excel file by submitting your Email Address:
