This is a general skills course with a PassEveryTest Certificate of Completion. It is not an accredited qualification or a vendor, employer or government certification (such as Microsoft Office Specialist), and it is not affiliated with or endorsed by Microsoft. When you work with real data, follow your employer's policies and applicable law.
Start with the lessons Excel Formulas and Data Analysis Course: Lessons: about 240 minutes of reading. Every test question is covered in the lessons.
Question 1 of 17
How should data be set up before you make a PivotTable from it? Show answer Answer: C. One header row, no blank rows or columns
Microsoft says PivotTable data should be in a tabular format with a single header row and no blank rows or columns. An Excel table is ideal.
Question 2 of 17
You have selected a cell in your data. Which command starts a new PivotTable? Show answer Answer: C. Insert > PivotTable
Select the data, then choose Insert > PivotTable and pick where to put it.
Question 3 of 17
In the PivotTable Field List, which area shows a field's items as labels down the left side? Show answer Answer: C. Rows
Rows area fields appear as Row Labels on the left. Columns area fields appear across the top.
Question 4 of 17
Where does a field in the Filters area appear? Show answer Answer: C. As a report filter above the PivotTable
Filters area fields are shown as top-level report filters above the PivotTable, so you can show one region or rep at a time.
Question 5 of 17
You add a column of numbers, with no blanks or text, to the Values area. How does the PivotTable summarize it by default? Show answer Answer: B. Sum
Fields in the Values area are shown as a Sum by default when Excel reads them as numbers.
Question 6 of 17
A Units column has a few blank cells. You add it to the Values area. Which summary does Excel use by default? Show answer Answer: A. Count
Count is the default for value fields that have nonnumeric values or blanks. Change it with Summarize Values By > Sum.
Question 7 of 17
You fixed a wrong amount in the source data, but the PivotTable still shows the old total. What should you do? Show answer Answer: A. Refresh it (right-click > Refresh)
Refresh (right-click > Refresh, or Alt+F5) makes the PivotTable read the source data again. Refresh All updates every PivotTable in the workbook.
Question 8 of 17
You make a PivotTable from the sales sheet below with Region in Rows and Sum of Amount in Values. What does the South row show? A B C D E F 1 Date Rep Region Product Units Amount 2 2026-03-02 Ana North Cameras 4 1200 3 2026-03-02 Ben South Alarms 10 850 4 2026-03-03 Ana North Alarms 6 510 5 2026-03-04 Chloé East Cameras 2 600 6 2026-03-05 Ben South Cameras 5 1500 7 2026-03-06 Dev North Sensors 12 360 8 2026-03-09 Ana North Cameras 3 900 9 2026-03-10 Chloé East Alarms 8 680 10 2026-03-11 Dev North Alarms 4 340 11 2026-03-12 Ben South Sensors 20 600
Show answer Answer: B. 2950
The PivotTable adds every South amount: 850 + 1,500 + 600 = 2,950.
Question 9 of 17
A PivotTable from the sales sheet below has Product in Rows, Region in Columns and Sum of Units in Values. What number is shown for Alarms in the North column? A B C D E F 1 Date Rep Region Product Units Amount 2 2026-03-02 Ana North Cameras 4 1200 3 2026-03-02 Ben South Alarms 10 850 4 2026-03-03 Ana North Alarms 6 510 5 2026-03-04 Chloé East Cameras 2 600 6 2026-03-05 Ben South Cameras 5 1500 7 2026-03-06 Dev North Sensors 12 360 8 2026-03-09 Ana North Cameras 3 900 9 2026-03-10 Chloé East Alarms 8 680 10 2026-03-11 Dev North Alarms 4 340 11 2026-03-12 Ben South Sensors 20 600
Show answer Answer: C. 10
North Alarms rows have 6 units (Ana) and 4 units (Dev), so the cell shows 10.
Question 10 of 17
A PivotTable from the sales sheet below has Region in Rows and Sum of Amount shown as % of Grand Total. What does the North row show (to 1 decimal place)? A B C D E F 1 Date Rep Region Product Units Amount 2 2026-03-02 Ana North Cameras 4 1200 3 2026-03-02 Ben South Alarms 10 850 4 2026-03-03 Ana North Alarms 6 510 5 2026-03-04 Chloé East Cameras 2 600 6 2026-03-05 Ben South Cameras 5 1500 7 2026-03-06 Dev North Sensors 12 360 8 2026-03-09 Ana North Cameras 3 900 9 2026-03-10 Chloé East Alarms 8 680 10 2026-03-11 Dev North Alarms 4 340 11 2026-03-12 Ben South Sensors 20 600
Show answer Answer: A. 43.9%
North's total is 3,310 and the grand total is 7,540. 3,310 / 7,540 = 43.9%.
Question 11 of 17
From the sales sheet below, you put Region in Rows and drag the Rep field (text) into Values. What does the South row show? A B C D E F 1 Date Rep Region Product Units Amount 2 2026-03-02 Ana North Cameras 4 1200 3 2026-03-02 Ben South Alarms 10 850 4 2026-03-03 Ana North Alarms 6 510 5 2026-03-04 Chloé East Cameras 2 600 6 2026-03-05 Ben South Cameras 5 1500 7 2026-03-06 Dev North Sensors 12 360 8 2026-03-09 Ana North Cameras 3 900 9 2026-03-10 Chloé East Alarms 8 680 10 2026-03-11 Dev North Alarms 4 340 11 2026-03-12 Ben South Sensors 20 600
Show answer Answer: A. 3
Rep holds text, so Excel uses Count. There are 3 South rows.
Question 12 of 17
What is a slicer? Show answer Answer: B. Buttons you click to filter a table or PivotTable
Slicers give clickable buttons for filtering and show which items are currently selected. Hold Ctrl to select more than one item.
Question 13 of 17
Your chart compares monthly revenue (about $50,000) with the number of orders (about 120). The orders line looks flat. What does Microsoft suggest? Show answer Answer: A. A combo chart with orders on a secondary axis
A secondary axis helps when values vary widely between series or you mix data types, such as price and volume.
Question 14 of 17
What is a sparkline? Show answer Answer: C. A tiny chart inside one cell
A sparkline is a tiny chart in a cell. The types are Line, Column and Win/Loss.
Question 15 of 17
You want to see whether staff with more training hours also have higher customer scores. Each person has one value for each. Which chart fits best? Show answer Answer: D. Scatter (XY) chart
Scatter charts plot pairs of numbers (x and y) as single points, which helps you compare many data points.
Question 16 of 17
Which chart shows how values are spread out by grouping them into ranges called bins? Show answer Answer: A. Histogram
A histogram shows the distribution of data grouped into frequency bins, for example how many invoices fall in each $100 range.
Question 17 of 17
You added a trendline to a sales chart. Where do you set it to project the trend into future periods? Show answer Answer: C. Format Trendline pane > Forward
In the Format Trendline pane, a value in Forward (or Backward) projects the trendline beyond your data.
Keyboard: 1–4 to answer, Enter for the next question.