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.
Zacznij od lekcji Excel Formulas and Data Analysis Course: Lessons: ok. 240 min czytania. Każde pytanie testowe jest omówione w lekcjach.
Pytanie 1 z 17
How should data be set up before you make a PivotTable from it? Pokaż odpowiedź Odpowiedź: 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.
Pytanie 2 z 17
You have selected a cell in your data. Which command starts a new PivotTable? Pokaż odpowiedź Odpowiedź: C. Insert > PivotTable
Select the data, then choose Insert > PivotTable and pick where to put it.
Pytanie 3 z 17
In the PivotTable Field List, which area shows a field's items as labels down the left side? Pokaż odpowiedź Odpowiedź: C. Rows
Rows area fields appear as Row Labels on the left. Columns area fields appear across the top.
Pytanie 4 z 17
Where does a field in the Filters area appear? Pokaż odpowiedź Odpowiedź: 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.
Pytanie 5 z 17
You add a column of numbers, with no blanks or text, to the Values area. How does the PivotTable summarize it by default? Pokaż odpowiedź Odpowiedź: B. Sum
Fields in the Values area are shown as a Sum by default when Excel reads them as numbers.
Pytanie 6 z 17
A Units column has a few blank cells. You add it to the Values area. Which summary does Excel use by default? Pokaż odpowiedź Odpowiedź: A. Count
Count is the default for value fields that have nonnumeric values or blanks. Change it with Summarize Values By > Sum.
Pytanie 7 z 17
You fixed a wrong amount in the source data, but the PivotTable still shows the old total. What should you do? Pokaż odpowiedź Odpowiedź: 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.
Pytanie 8 z 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 Data 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
Pokaż odpowiedź Odpowiedź: B. 2950
The PivotTable adds every South amount: 850 + 1,500 + 600 = 2,950.
Pytanie 9 z 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 Data 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
Pokaż odpowiedź Odpowiedź: C. 10
North Alarms rows have 6 units (Ana) and 4 units (Dev), so the cell shows 10.
Pytanie 10 z 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 Data 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
Pokaż odpowiedź Odpowiedź: A. 43.9%
North's total is 3,310 and the grand total is 7,540. 3,310 / 7,540 = 43.9%.
Pytanie 11 z 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 Data 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
Pokaż odpowiedź Odpowiedź: A. 3
Rep holds text, so Excel uses Count. There are 3 South rows.
Pytanie 12 z 17
What is a slicer? Pokaż odpowiedź Odpowiedź: 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.
Pytanie 13 z 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? Pokaż odpowiedź Odpowiedź: 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.
Pytanie 14 z 17
What is a sparkline? Pokaż odpowiedź Odpowiedź: C. A tiny chart inside one cell
A sparkline is a tiny chart in a cell. The types are Line, Column and Win/Loss.
Pytanie 15 z 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? Pokaż odpowiedź Odpowiedź: D. Scatter (XY) chart
Scatter charts plot pairs of numbers (x and y) as single points, which helps you compare many data points.
Pytanie 16 z 17
Which chart shows how values are spread out by grouping them into ranges called bins? Pokaż odpowiedź Odpowiedź: A. Histogram
A histogram shows the distribution of data grouped into frequency bins, for example how many invoices fall in each $100 range.
Pytanie 17 z 17
You added a trendline to a sales chart. Where do you set it to project the trend into future periods? Pokaż odpowiedź Odpowiedź: C. Format Trendline pane > Forward
In the Format Trendline pane, a value in Forward (or Backward) projects the trendline beyond your data.
Klawiatura: 1–4, aby odpowiedzieć, Enter – następne pytanie.