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

1. Pregunta 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
   Ver respuesta

   Respuesta: 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. Pregunta 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
   Ver respuesta

   Respuesta: C. Insert > PivotTable

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

3. Pregunta 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
   Ver respuesta

   Respuesta: C. Rows

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

4. Pregunta 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
   Ver respuesta

   Respuesta: 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. Pregunta 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
   Ver respuesta

   Respuesta: B. Sum

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

6. Pregunta 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
   Ver respuesta

   Respuesta: A. Count

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

7. Pregunta 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
   Ver respuesta

   Respuesta: 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. Pregunta 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	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

   • A3
   • B2950
   • C7540
   • D1500
   Ver respuesta

   Respuesta: B. 2950

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

9. Pregunta 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	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

   • A6
   • B28
   • C10
   • D2
   Ver respuesta

   Respuesta: C. 10

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

10. Pregunta 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	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

    • A43.9%
    • B33.3%
    • C50.0%
    • D56.1%
    Ver respuesta

    Respuesta: A. 43.9%

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

11. Pregunta 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	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

    • A3
    • B2950
    • C1
    • DBen
    Ver respuesta

    Respuesta: A. 3

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

12. Pregunta 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
    Ver respuesta

    Respuesta: 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. Pregunta 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
    Ver respuesta

    Respuesta: 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. Pregunta 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
    Ver respuesta

    Respuesta: 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. Pregunta 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
    Ver respuesta

    Respuesta: D. Scatter (XY) chart

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

16. Pregunta 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
    Ver respuesta

    Respuesta: A. Histogram

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

17. Pregunta 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
    Ver respuesta

    Respuesta: 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, Intro para la siguiente pregunta.