Mixed Deck — All Microsoft Excel Topics Flashcards
100 cards from real Microsoft Excel practice questions. Tap to flip, then mark Knew It or Still Learning — missed cards come back until you master them.
Read the first 20 Mixed Deck — All Microsoft Excel Topics flashcards as text
You want to show only the five best-selling products on a PivotChart. Which filter should you apply to the Product field?
Answer: Value Filter > Top 10 set to 5 items
Top 10 value filters can be adjusted to any number of items by sum of a value field.
Which Excel feature allows you to automatically fill a series of dates, numbers, or text patterns?
Answer: AutoFill
AutoFill uses the fill handle (small square at the bottom-right of a selection) to extend a series automatically.
Which function would you use to find the position of a value in a list?
Answer: MATCH
MATCH returns the relative position (row or column number) of a value within a range.
Which function replaces all occurrences of a substring within a text string?
Answer: SUBSTITUTE
SUBSTITUTE replaces all (or a specific) occurrences of a given text with new text within a string.
What does the SUMIFS function do differently from SUMIF?
Answer: SUMIFS allows multiple criteria while SUMIF allows only one
SUMIFS can apply multiple criteria across different columns simultaneously, while SUMIF is limited to a single condition.
Let's say that cell A1 shows the value "12000.7789". How should this number be rounded to the nearest integer using the following formula?
Answer: =ROUND(A1,0)
The `ROUND` function in Excel is used to round a number to a specified number of decimal places. To round a number to the nearest integer, you specify `0` as the number of decimal places. This instructs Excel to round the value in cell A1 to the closest whole number.
What is the purpose of Custom Views?
Answer: Saving different display, filter, print settings to switch between
Custom Views save combinations of display, filter, print settings for quick switching.
What is the maximum number of rows supported in a single Excel 2016 worksheet?
Answer: 1,048,576
Excel 2007 and later supports 1,048,576 rows (2^20) per worksheet. The 65,536-row limit applied to Excel 2003 and earlier (.xls format).
Which Excel feature automatically fills a column by detecting a pattern from examples you type, such as extracting first names?
Answer: Flash Fill
Flash Fill recognizes patterns and completes the column with Ctrl+E.
Which spreadsheet application was Excel primarily designed to compete against?
Answer: Lotus 1-2-3
Lotus 1-2-3 dominated the spreadsheet market in the 1980s, and Microsoft developed Excel specifically to surpass it in features and usability.
What does VLOOKUP's fourth argument (range_lookup) control?
Answer: Exact match (FALSE) or approximate match (TRUE)
FALSE requires exact match; TRUE allows approximate match from sorted data.
How does the Watch Window help with large spreadsheets?
Answer: Displays selected cells' values in a floating window visible while navigating
Watch Window shows cell values and formulas in a persistent panel across sheets.
What does the SWITCH function do?
Answer: Evaluates an expression against a list of values and returns the first match
SWITCH compares an expression against value-result pairs, returning the result for the first match.
How do you create a named range that automatically expands?
Answer: Use OFFSET and COUNTA in the name definition
Combining OFFSET with COUNTA creates a dynamic named range that grows with your data.
What happens when you double-click the fill handle (small square at the bottom-right corner of a selected cell)?
Answer: Excel auto-fills the formula or pattern down to match the adjacent column's data range
Double-clicking the fill handle automatically copies the cell's formula or pattern down to the last row that has data in the adjacent column.
Which Excel feature helps you identify errors in formulas by showing which cells are referenced?
Answer: Trace Precedents and Trace Dependents
Trace Precedents shows arrows pointing from cells that feed into a formula, while Trace Dependents shows arrows pointing to cells that use the selected cell's value.
After creating an Excel table from a range of data, you want to provide users with a set of visible, clickable buttons to filter the 'Region' and 'Product Category' columns without using the drop-down arrows in the headers. Which feature should you insert?
Answer: Slicer
Slicers are interactive controls that provide buttons for filtering data in tables, PivotTables, and PivotCharts. They can be inserted from the 'Table Design' tab and offer a user-friendly way to see and change the current filter state.
What does the 'What-If Analysis' group in Excel's Data tab include?
Answer: Scenario Manager, Goal Seek, and Data Table
The What-If Analysis group contains Scenario Manager, Goal Seek, and Data Table.
How do you freeze the top row in Excel so it remains visible while scrolling down?
Answer: View tab → Freeze Panes → Freeze Top Row
On the View tab, clicking Freeze Panes and then Freeze Top Row locks row 1 in place during vertical scrolling.
New records were added to the source table of a PivotChart, but the chart has not changed. What should you do?
Answer: Click Refresh on the PivotChart Analyze tab
PivotCharts do not update until the PivotTable cache is refreshed.
Written and edited by Nathan Beja, founder of Practice Test Geeks, with AI assistance. Exam facts are checked against the official sources linked below. Editorial policy
Sources & references
Exam facts on this page (fees, format, dates, passing scores) are checked against these official sources; the date is our last check. Editorial policy
- store.certiport.com — Fees, retake fee · verified Nov 2026
- certiport.pearsonvue.com — Renewal, score validity, score release, exam format · verified Nov 2026
- learn.microsoft.com — Test delivery, time limit, eligibility · verified Nov 2026