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.
Beginnen Sie mit den Lektionen Excel Formulas and Data Analysis Course: Lessons: etwa 240 Minuten Lesezeit. Jede Testfrage wird in den Lektionen behandelt.
Frage 1 von 17
How should data be set up before you make a PivotTable from it? Antwort anzeigen Antwort: 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.
Frage 2 von 17
You have selected a cell in your data. Which command starts a new PivotTable? Antwort anzeigen Antwort: C. Insert > PivotTable
Select the data, then choose Insert > PivotTable and pick where to put it.
Frage 3 von 17
In the PivotTable Field List, which area shows a field's items as labels down the left side? Antwort anzeigen Antwort: C. Rows
Rows area fields appear as Row Labels on the left. Columns area fields appear across the top.
Frage 4 von 17
Where does a field in the Filters area appear? Antwort anzeigen Antwort: 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.
Frage 5 von 17
You add a column of numbers, with no blanks or text, to the Values area. How does the PivotTable summarize it by default? Antwort anzeigen Antwort: B. Sum
Fields in the Values area are shown as a Sum by default when Excel reads them as numbers.
Frage 6 von 17
A Units column has a few blank cells. You add it to the Values area. Which summary does Excel use by default? Antwort anzeigen Antwort: A. Count
Count is the default for value fields that have nonnumeric values or blanks. Change it with Summarize Values By > Sum.
Frage 7 von 17
You fixed a wrong amount in the source data, but the PivotTable still shows the old total. What should you do? Antwort anzeigen Antwort: 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.
Frage 8 von 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 Datum 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
Antwort anzeigen Antwort: B. 2950
The PivotTable adds every South amount: 850 + 1,500 + 600 = 2,950.
Frage 9 von 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 Datum 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
Antwort anzeigen Antwort: C. 10
North Alarms rows have 6 units (Ana) and 4 units (Dev), so the cell shows 10.
Frage 10 von 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 Datum 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
Antwort anzeigen Antwort: A. 43.9%
North's total is 3,310 and the grand total is 7,540. 3,310 / 7,540 = 43.9%.
Frage 11 von 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 Datum 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
Antwort anzeigen Antwort: A. 3
Rep holds text, so Excel uses Count. There are 3 South rows.
Frage 12 von 17
What is a slicer? Antwort anzeigen Antwort: 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.
Frage 13 von 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? Antwort anzeigen Antwort: 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.
Frage 14 von 17
What is a sparkline? Antwort anzeigen Antwort: C. A tiny chart inside one cell
A sparkline is a tiny chart in a cell. The types are Line, Column and Win/Loss.
Frage 15 von 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? Antwort anzeigen Antwort: D. Scatter (XY) chart
Scatter charts plot pairs of numbers (x and y) as single points, which helps you compare many data points.
Frage 16 von 17
Which chart shows how values are spread out by grouping them into ranges called bins? Antwort anzeigen Antwort: A. Histogram
A histogram shows the distribution of data grouped into frequency bins, for example how many invoices fall in each $100 range.
Frage 17 von 17
You added a trendline to a sales chart. Where do you set it to project the trend into future periods? Antwort anzeigen Antwort: C. Format Trendline pane > Forward
In the Format Trendline pane, a value in Forward (or Backward) projects the trendline beyond your data.
Tastatur: 1–4 zum Antworten, Eingabetaste für die nächste Frage.