Excel Formulas and Data Analysis Course: PivotTables and charts for analysis

1. Question 1 sur 17

   How should data be set up before you make a PivotTable from it?

   • AA blank row between months to separate them
   • BA subtotal row after each group of records
   • COne header row, no blank rows or columns
   • DMerged header cells over each group of columns
   Voir la réponse

   Réponse: 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.

2. Question 2 sur 17

   You have selected a cell in your data. Which command starts a new PivotTable?

   • AData > Subtotal
   • BFormulas > Insert Function
   • CInsert > PivotTable
   • DHome > Format as Table
   Voir la réponse

   Réponse: C. Insert > PivotTable

   Select the data, then choose Insert > PivotTable and pick where to put it.

3. Question 3 sur 17

   In the PivotTable Field List, which area shows a field's items as labels down the left side?

   • AColumns
   • BFilters
   • CRows
   • DValues
   Voir la réponse

   Réponse: C. Rows

   Rows area fields appear as Row Labels on the left. Columns area fields appear across the top.

4. Question 4 sur 17

   Where does a field in the Filters area appear?

   • AAs summarized numbers in the body of the table
   • BOn a separate worksheet next to the PivotTable
   • CAs a report filter above the PivotTable
   • DAs row labels down the left side of the table
   Voir la réponse

   Réponse: 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.

5. Question 5 sur 17

   You add a column of numbers, with no blanks or text, to the Values area. How does the PivotTable summarize it by default?

   • AAverage
   • BSum
   • CCount
   • DMax
   Voir la réponse

   Réponse: B. Sum

   Fields in the Values area are shown as a Sum by default when Excel reads them as numbers.

6. Question 6 sur 17

   A Units column has a few blank cells. You add it to the Values area. Which summary does Excel use by default?

   • ACount
   • BSum
   • CAverage
   • DMin
   Voir la réponse

   Réponse: A. Count

   Count is the default for value fields that have nonnumeric values or blanks. Change it with Summarize Values By > Sum.

7. Question 7 sur 17

   You fixed a wrong amount in the source data, but the PivotTable still shows the old total. What should you do?

   • ARefresh it (right-click > Refresh)
   • BSort the source data from smallest to largest
   • CType the right total over the PivotTable cell
   • DPress Ctrl+Z to undo the change to the source
   Voir la réponse

   Réponse: 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.

8. Question 8 sur 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	Région	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

   • A3
   • B2950
   • C7540
   • D1500
   Voir la réponse

   Réponse: B. 2950

   The PivotTable adds every South amount: 850 + 1,500 + 600 = 2,950.

9. Question 9 sur 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	Région	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

   • A6
   • B28
   • C10
   • D2
   Voir la réponse

   Réponse: C. 10

   North Alarms rows have 6 units (Ana) and 4 units (Dev), so the cell shows 10.

10. Question 10 sur 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	Région	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

    • A43.9%
    • B33.3%
    • C50.0%
    • D56.1%
    Voir la réponse

    Réponse: A. 43.9%

    North's total is 3,310 and the grand total is 7,540. 3,310 / 7,540 = 43.9%.

11. Question 11 sur 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	Région	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

    • A3
    • B2950
    • C1
    • DBen
    Voir la réponse

    Réponse: A. 3

    Rep holds text, so Excel uses Count. There are 3 South rows.

12. Question 12 sur 17

    What is a slicer?

    • AA tool that splits one cell into two columns
    • BButtons you click to filter a table or PivotTable
    • CA small chart that fits inside a single cell
    • DA rule that deletes repeated rows from a list
    Voir la réponse

    Réponse: 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.

13. Question 13 sur 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?

    • AA combo chart with orders on a secondary axis
    • BA 3-D clustered column chart for both series
    • CA pie chart with one slice for each month
    • DDelete the revenue series from the chart
    Voir la réponse

    Réponse: 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.

14. Question 14 sur 17

    What is a sparkline?

    • AA chart that refreshes itself every second
    • BA red line that marks errors in formulas
    • CA tiny chart inside one cell
    • DA thick border style used under totals
    Voir la réponse

    Réponse: C. A tiny chart inside one cell

    A sparkline is a tiny chart in a cell. The types are Line, Column and Win/Loss.

15. Question 15 sur 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?

    • ASparkline
    • BStacked column chart
    • CPie chart
    • DScatter (XY) chart
    Voir la réponse

    Réponse: D. Scatter (XY) chart

    Scatter charts plot pairs of numbers (x and y) as single points, which helps you compare many data points.

16. Question 16 sur 17

    Which chart shows how values are spread out by grouping them into ranges called bins?

    • AHistogram
    • BCombo chart
    • CDoughnut chart
    • DLine chart
    Voir la réponse

    Réponse: A. Histogram

    A histogram shows the distribution of data grouped into frequency bins, for example how many invoices fall in each $100 range.

17. Question 17 sur 17

    You added a trendline to a sales chart. Where do you set it to project the trend into future periods?

    • APage Layout > Print Titles
    • BData > Sort > Add Level
    • CFormat Trendline pane > Forward
    • DData > Data Validation > Settings
    Voir la réponse

    Réponse: C. Format Trendline pane > Forward

    In the Format Trendline pane, a value in Forward (or Backward) projects the trendline beyond your data.

Clavier : 1–4 pour répondre, Entrée pour la question suivante.