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

1. السؤال 1 من 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
   أظهر الإجابة

   الإجابة: 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. السؤال 2 من 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
   أظهر الإجابة

   الإجابة: C. Insert > PivotTable

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

3. السؤال 3 من 17

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

   • AColumns
   • BFilters
   • CRows
   • DValues
   أظهر الإجابة

   الإجابة: C. Rows

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

4. السؤال 4 من 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
   أظهر الإجابة

   الإجابة: 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. السؤال 5 من 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
   أظهر الإجابة

   الإجابة: B. Sum

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

6. السؤال 6 من 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
   أظهر الإجابة

   الإجابة: A. Count

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

7. السؤال 7 من 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
   أظهر الإجابة

   الإجابة: 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. السؤال 8 من 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	التاريخ	Rep	المنطقة	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
   أظهر الإجابة

   الإجابة: B. 2950

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

9. السؤال 9 من 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	التاريخ	Rep	المنطقة	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
   أظهر الإجابة

   الإجابة: C. 10

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

10. السؤال 10 من 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	التاريخ	Rep	المنطقة	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%
    أظهر الإجابة

    الإجابة: A. 43.9%

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

11. السؤال 11 من 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	التاريخ	Rep	المنطقة	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
    أظهر الإجابة

    الإجابة: A. 3

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

12. السؤال 12 من 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
    أظهر الإجابة

    الإجابة: 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. السؤال 13 من 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
    أظهر الإجابة

    الإجابة: 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. السؤال 14 من 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
    أظهر الإجابة

    الإجابة: 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. السؤال 15 من 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
    أظهر الإجابة

    الإجابة: D. Scatter (XY) chart

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

16. السؤال 16 من 17

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

    • AHistogram
    • BCombo chart
    • CDoughnut chart
    • DLine chart
    أظهر الإجابة

    الإجابة: A. Histogram

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

17. السؤال 17 من 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
    أظهر الإجابة

    الإجابة: C. Format Trendline pane > Forward

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

لوحة المفاتيح: 1–4 للإجابة، وEnter للسؤال التالي.