Excel Essentials

What is the first step you need to perform before creating a pivot table in Excel?

• Clean your data by ensuring no blanks among the rows and columns, and that the data header is clearly labelled at the top

• Create an Excel table to act as a point of reference for the pivot table

• Directly populate your "PivotTable Fields List" with the relevant fields

Match the following categories of the pivot table "Areas" with their respective descriptions.

(Drag each item on the left column to its matching definition.)

• filters

• Allows you to display the fields as columns in the pivot table

• columns

• Allows you to display the fields as rows in the pivot table

• rows

• Allows you to selectively display data on the pivot table for selected components

• values

• Allows you to summarise the data field in the pivot table (i.e., Sum, Count, Average, etc)

If you would like to remove the "Sub Category" data field from "Rows" in the pivot table as per the screenshot below, what should you do?

• Delete the "Sub Category" column from the data source and refresh the pivot table

• Drag "Sub Category" out of the "Rows" areas pane

• You have to recreate a new pivot table as you are unable to remove items after placing them in the pivot table

Please use the Practice Workbook (Worksheet D) to answer this question.

Refer to the data set provided. If you want to display the net sales of each Salesperson and filter by date, which data fields should you place in the pivot table area pane to achieve similar results as the screenshot below?

• Filters: Months

  Rows: Salesperson

  Values: Sum of Net Sales

• Filters: Months

  Rows: Salesperson

  Columns: Net Sales

• Filters: Salesperson

  Rows: Net Sales

  Columns: Months

Please use the Practice Workbook (Worksheet D) to answer this question.

Which pivot table feature allows you to display only "Product 1" from the pivot table data set?

• Sorting

• Filtering

• Remove duplicates

• Nesting

Please use the Practice Workbook (Worksheet D) to answer this question.

What are the net sales of Salesperson Freddy for Product 4?

• $34,673

• $28,202

• $26,507

• $27,020

Please use the Practice Workbook (Worksheet D) to answer this question.

Rank the regions from the highest number of clients to the lowest number of clients!

• South - North - East - West

• North - South - West - East

• East - North - South - West

• West - North - East - South

Please use the Practice Workbook (Worksheet D) to answer this question.

Suppose the manager of the sales team needs to propose the salesperson with the highest net sales at the end of 2021. Based on the current data set, who will they select?

• Pablo

• Freddy

• Julia

• Maria