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

1. Pergunta 1 de 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
   Mostrar resposta

   Resposta: 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. Pergunta 2 de 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
   Mostrar resposta

   Resposta: C. Insert > PivotTable

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

3. Pergunta 3 de 17

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

   • AColumns
   • BFilters
   • CRows
   • DValues
   Mostrar resposta

   Resposta: C. Rows

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

4. Pergunta 4 de 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
   Mostrar resposta

   Resposta: 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. Pergunta 5 de 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
   Mostrar resposta

   Resposta: B. Sum

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

6. Pergunta 6 de 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
   Mostrar resposta

   Resposta: A. Count

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

7. Pergunta 7 de 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
   Mostrar resposta

   Resposta: 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. Pergunta 8 de 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

   • A3
   • B2950
   • C7540
   • D1500
   Mostrar resposta

   Resposta: B. 2950

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

9. Pergunta 9 de 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

   • A6
   • B28
   • C10
   • D2
   Mostrar resposta

   Resposta: C. 10

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

10. Pergunta 10 de 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

    • A43.9%
    • B33.3%
    • C50.0%
    • D56.1%
    Mostrar resposta

    Resposta: A. 43.9%

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

11. Pergunta 11 de 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

    • A3
    • B2950
    • C1
    • DBen
    Mostrar resposta

    Resposta: A. 3

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

12. Pergunta 12 de 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
    Mostrar resposta

    Resposta: 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. Pergunta 13 de 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
    Mostrar resposta

    Resposta: 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. Pergunta 14 de 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
    Mostrar resposta

    Resposta: 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. Pergunta 15 de 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
    Mostrar resposta

    Resposta: D. Scatter (XY) chart

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

16. Pergunta 16 de 17

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

    • AHistogram
    • BCombo chart
    • CDoughnut chart
    • DLine chart
    Mostrar resposta

    Resposta: A. Histogram

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

17. Pergunta 17 de 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
    Mostrar resposta

    Resposta: C. Format Trendline pane > Forward

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

Teclado: 1–4 para responder, Enter para a pergunta seguinte.