Part 6: Excel Pivot Tables & Data Analysis Quiz (20 MCQ)

Correct answer: C. To summarize and analyze large data by grouping and calculating results

★ Key Takeaway: PivotTables turn long rows of data into clear summaries you can explore.

Explanation: A PivotTable lets you quickly answer questions like "Total sales by region" or "Orders by month" without writing complex formulas. You drag fields into Rows, Columns, Values, and Filters to build reports in seconds.

Why other options are incorrect:

• A. To correct spelling mistakes in text automatically – Spell checking is a separate Excel feature and is not related to PivotTables.
• B. To convert a workbook into a PDF file – PDF export is done through Save As or Export, not PivotTables.
• D. To lock cells so others cannot edit them – Cell locking is handled with Protect Sheet/Workbook, not PivotTables.

Tip: PivotTables are one of the fastest ways to look professional in Excel. If you want guided practice with real datasets, a 1-on-1 tutor can help you master PivotTables quickly. Explore 1-on-1 Excel tutoring on Fiverr →

Correct answer: A. Rows

★ Key Takeaway: The Rows area defines the categories you see down the left side.

Explanation: If you drag a field like Region or Product into Rows, Excel lists each unique value as row labels. This is how PivotTables group your data into readable categories.

Why other options are incorrect:

• B. Values – Values is where calculations go, like Sum of Sales or Count of Orders.
• C. Columns – Columns places categories across the top, not down the left side.
• D. Filters – Filters are used to limit what the PivotTable shows, not to create row labels.

Correct answer: B. Values

★ Key Takeaway: Values is where PivotTables do math.

Explanation: Putting Sales in Values creates a calculation like Sum of Sales (or Count/Average depending on settings). This is how PivotTables produce totals and summaries.

Why other options are incorrect:

• A. Rows – Rows creates categories like Region or Product; it does not perform totals by itself.
• C. Filters – Filters restrict what data is included, but they do not calculate totals.
• D. Slicers – Slicers are visual filter buttons; they are not the calculation area.

Correct answer: D. Refresh the PivotTable

★ Key Takeaway: PivotTables do not update automatically; refresh pulls in changes.

Explanation: PivotTables summarize a snapshot of your data. When the source changes (new rows, edits), you usually need to Refresh so Excel rebuilds the PivotTable results.

Why other options are incorrect:

• A. Rename the worksheet – Renaming does not update PivotTable calculations.
• B. Change the font size of the PivotTable – Formatting does not recalculate or reload the data.
• C. Turn on gridlines – Gridlines only affect display, not PivotTable data.

Correct answer: C. An Excel Table (formatted table)

★ Key Takeaway: Excel Tables make PivotTable source ranges more reliable.

Explanation: When your source is an Excel Table, it automatically grows as you add rows. That makes PivotTables easier to maintain because the data range stays correct.

Why other options are incorrect:

• A. A chart sheet – A chart sheet shows visuals; it is not a data structure for PivotTable input.
• B. A merged-cell layout – Merged cells often break clean tabular structure and can cause PivotTable issues.
• D. A PivotChart – A PivotChart is a visualization of a PivotTable, not the source data structure.

Correct answer: B. The Sales field contains text or blank values, so Excel cannot sum it reliably

★ Key Takeaway: PivotTables count when values are not truly numeric.

Explanation: If numbers are stored as text (or mixed with blanks/text), Excel may default to Count. Clean the column so Sales is numeric, then set Value Field Settings to Sum.

Why other options are incorrect:

• A. The PivotTable is on a protected sheet – Protection affects editing, not whether the field sums or counts.
• C. The workbook is saved as CSV – CSV format is unrelated to PivotTable calculation choice inside Excel.
• D. The chart title is missing – Chart titles do not affect PivotTable calculations.

Correct answer: A. Drill-down (Show Details) by double-clicking a value cell

★ Key Takeaway: Drill-down shows the exact records behind a PivotTable total.

Explanation: When you double-click a PivotTable number, Excel creates a new sheet listing the source rows that produced that summary. This is extremely useful for auditing and checking mistakes.

Why other options are incorrect:

• B. Freeze Panes – Freeze Panes keeps headers visible while scrolling; it does not show underlying PivotTable records.
• C. Conditional Formatting – Conditional Formatting changes appearance based on rules; it does not expand details.
• D. Name Manager – Name Manager handles named ranges; it is not a PivotTable detail feature.

Correct answer: C. Columns

★ Key Takeaway: Columns builds headings across the top of the PivotTable.

Explanation: Adding a time field to Columns creates a left-to-right layout, such as months across the top. This is common for comparing performance by period.

Why other options are incorrect:

• A. Rows – Rows lists items vertically; it will not place headings across the top.
• B. Filters – Filters lets you choose which data to include, not create top headings.
• D. Values – Values contains calculations like Sum or Count; it does not create headings.

Correct answer: D. Slicer

★ Key Takeaway: Slicers make PivotTable filtering visual and easy.

Explanation: A slicer adds clickable buttons (like North, South, East, West) that filter PivotTables instantly. It is commonly used in dashboards because users can filter without opening dropdown menus.

Why other options are incorrect:

• A. Spell Check – Spell Check reviews text spelling; it does not filter PivotTables.
• B. Track Changes – Track Changes is a collaboration feature and is not used for PivotTable filtering.
• C. Page Break Preview – Page Break Preview is for printing layout, not interactive filtering.

Tip: If PivotTables and slicers feel confusing at first, practicing with a real sales dataset makes everything click much faster. Learn Excel faster with a 1-on-1 tutor on Fiverr →

Correct answer: B. It removes it from the row labels and turns it into a dropdown filter for the whole report

★ Key Takeaway: Filters control which data is included without showing it as labels.

Explanation: Putting a field in Filters means it does not appear as Row/Column labels, but you can choose a value (like one Region) to filter the entire PivotTable output.

Why other options are incorrect:

• A. It converts the field into a formula – PivotTable field placement does not turn fields into formulas.
• C. It deletes the field from the source data – PivotTables never delete your original data.
• D. It automatically creates a chart – Charts are optional and must be inserted separately as PivotCharts.

Correct answer: C. Show Values As

★ Key Takeaway: Show Values As changes how PivotTable numbers are displayed, like percentages.

Explanation: Instead of showing raw totals, Show Values As can display % of Grand Total, % of Row Total, running totals, and more. This is useful for understanding share and contribution.

Why other options are incorrect:

• A. Data Validation – Data Validation controls allowed input, not PivotTable value display.
• B. Remove Duplicates – Remove Duplicates cleans source data; it does not format PivotTable results.
• D. Flash Fill – Flash Fill helps fill patterns in data entry, not PivotTable calculations.

Correct answer: A. Group

★ Key Takeaway: Grouping turns detailed dates into readable time periods.

Explanation: If your PivotTable uses a Date field, you can Group it to summarize by Month or Year. This is a key analysis technique for time-based reporting.

Why other options are incorrect:

• B. Wrap Text – Wrap Text changes how text appears in cells; it does not group PivotTable dates.
• C. Text to Columns – Text to Columns splits text; it is not a PivotTable grouping tool.
• D. Goal Seek – Goal Seek is a what-if analysis tool and does not group PivotTable fields.

Correct answer: C. Value Field Settings

★ Key Takeaway: Value Field Settings controls whether PivotTable values are Sum, Count, Average, and more.

Explanation: Right-click the value in the PivotTable and choose Value Field Settings to change the summary calculation. This is how you switch between Sum, Average, Count, Max, Min, and other calculations.

Why other options are incorrect:

• A. Insert > Shapes – Shapes are visual objects and do not affect PivotTable calculations.
• B. Review > Comments – Comments are for notes and collaboration, not PivotTable math.
• D. Name Manager – Name Manager handles named ranges; it does not change PivotTable value calculations.

Correct answer: D. PivotChart

★ Key Takeaway: PivotCharts visualize PivotTable results and update when filters change.

Explanation: A PivotChart is linked to a PivotTable, so when you filter or change fields, the chart updates automatically. This is perfect for interactive reporting and dashboards.

Why other options are incorrect:

• A. Sparkline – Sparklines are tiny in-cell charts and are not linked like PivotCharts.
• B. Data Form – Data Form is for entering records and is unrelated to PivotTable charting.
• C. Conditional Formatting – Conditional Formatting changes cell appearance; it does not create a linked chart.

Correct answer: B. Calculated Field

★ Key Takeaway: Calculated Fields add new calculations to a PivotTable using existing fields.

Explanation: A Calculated Field lets you create a new measure like Profit = Sales - Cost within the PivotTable. This is useful when your dataset does not already include the calculation as a column.

Why other options are incorrect:

• A. Freeze Panes – Freeze Panes only affects scrolling and visibility, not PivotTable calculations.
• C. Remove Duplicates – Remove Duplicates cleans data; it does not create calculated PivotTable measures.
• D. Page Layout – Page Layout is for printing and page setup, not PivotTable math.

Correct answer: A. Tabular Form

★ Key Takeaway: Tabular Form makes PivotTables look more like clean tables.

Explanation: Tabular Form places each row field in its own column, which is easier to read and often easier to export or use in further analysis. Many professional reports prefer Tabular Form for clarity.

Why other options are incorrect:

• B. Hide All – This is not a standard PivotTable report layout option for readability.
• C. Page Break Preview – Page Break Preview is for printing layout, not PivotTable structure.
• D. Full Screen Reading View – This is not a PivotTable layout feature.

Correct answer: C. Refresh data when opening the file

★ Key Takeaway: Automatic refresh helps keep PivotTables current without manual steps.

Explanation: In PivotTable Options, you can enable Refresh data when opening the file. This is helpful for reports that rely on updated data, such as weekly sales files or monthly expense logs.

Why other options are incorrect:

• A. Enable AutoSave for the workbook – AutoSave only controls whether file changes are saved automatically; it does not refresh PivotTable data when the file is opened.
• B. Wrap Text – Wrap Text affects cell display and does not update PivotTable results.
• D. Protect Workbook – Protection controls editing; it does not refresh PivotTables automatically.

Correct answer: B. It hides the overall totals row/column without changing the underlying calculations

★ Key Takeaway: Grand Totals are display choices, not data changes.

Explanation: Turning off Grand Totals removes the visual total row/column, which can make reports cleaner. The PivotTable still calculates the values for each group normally.

Why other options are incorrect:

• A. It deletes the original source data – PivotTable settings never delete the original data.
• C. It converts the PivotTable into a normal range automatically – PivotTables do not convert automatically; you must copy/paste values if desired.
• D. It forces the PivotTable to count values instead of sum – Grand Totals have nothing to do with whether values are summed or counted.

Correct answer: D. Sort by the Values field in descending order

★ Key Takeaway: PivotTables can be sorted by the calculated values, not just labels.

Explanation: Right-click a row label and choose Sort, then pick descending by Sum of Sales (or your values field). This is common in reports where you want top performers first.

Why other options are incorrect:

• A. Sort the worksheet tabs alphabetically – Sorting sheet tabs does not change the PivotTable order.
• B. Turn on spell check for the PivotTable – Spell check does not sort numeric results.
• C. Rename the PivotTable – Renaming does not change the sorting of the data.

Correct answer: A. What is the total sales by region and by month?

★ Key Takeaway: PivotTables are built for fast summaries across categories and time.

Explanation: PivotTables can group data by region and month and then calculate totals instantly. This is exactly the kind of business reporting PivotTables are designed for.

Why other options are incorrect:

• B. What is the exact color code of a cell background? – PivotTables summarize data; they do not extract color codes.
• C. How do I change Excel into dark mode on my computer? – This is an interface setting, not a data analysis question.
• D. How do I draw a shape and connect it with arrows? – Shapes are drawing tools and are unrelated to PivotTable analysis.