Microsoft Excel Basic and Advance Questions and Answers 2 — Questions and Answers
Question 1: Which Excel feature allows you to apply consistent formatting across multiple cells based on their values?
- Conditional Formatting (Correct answer)
- Format Painter
- Cell Styles
- Data Validation
Correct answer: Conditional Formatting
Conditional Formatting automatically applies formatting rules based on cell values, such as highlighting cells above a certain threshold.
Conditional Formatting is one of Excel's most powerful visual analysis tools. It allows users to set rules that automatically change cell appearance (color, icons, data bars) based on their values. For example, you can highlight all sales figures below target in red. Unlike Format Painter which copies static formatting, Conditional Formatting is dynamic and updates as values change.
Question 2: What is the maximum number of rows in an Excel worksheet (Excel 2007 and later)?
- 1,048,576 (Correct answer)
- 65,536
- 16,384
- 256
Correct answer: 1,048,576
Starting with Excel 2007, worksheets support up to 1,048,576 rows (2^20), a significant increase from the 65,536 row limit in earlier versions.
Excel 2007 introduced the .xlsx format which expanded the grid to 1,048,576 rows by 16,384 columns (2^20 by 2^14). The older .xls format supported only 65,536 rows by 256 columns. This expansion was necessary to handle growing data needs. Understanding these limits is essential when working with large datasets to know when you might need alternative tools like Power Query or a database.
Question 3: Which keyboard shortcut opens the Find and Replace dialog in Excel?
- Ctrl+H (Correct answer)
- Ctrl+F
- Ctrl+G
- Ctrl+R
Correct answer: Ctrl+H
Ctrl+H opens the Find and Replace dialog directly on the Replace tab, while Ctrl+F opens it on the Find tab.
Ctrl+H opens the Find and Replace dialog with the Replace tab active, allowing you to search for specific text or values and replace them with alternatives. Ctrl+F opens the same dialog but on the Find tab. This is one of the most frequently used shortcuts for data cleanup tasks like fixing typos, standardizing entries, or updating outdated values across large spreadsheets.
Question 4: What does the $ symbol do when used in a cell reference like $A$1?
- Makes the reference absolute so it does not change when copied (Correct answer)
- Converts the value to currency format
- Locks the cell from editing
- References a cell on another sheet
Correct answer: Makes the reference absolute so it does not change when copied
The $ symbol creates an absolute reference, preventing the column and/or row from adjusting when the formula is copied to other cells.
In Excel, the $ symbol before a column letter or row number creates an absolute reference. $A$1 locks both column A and row 1. Mixed references are also possible: $A1 locks only the column, while A$1 locks only the row. Without the $ symbol, references are relative and automatically adjust when formulas are copied. This is fundamental for building formulas that reference fixed lookup tables or constants while allowing other parts to adjust.
Question 5: Which Excel tool allows you to see the impact of changing input values on formula results?
- What-If Analysis (Data Tables) (Correct answer)
- Solver
- Formula Auditing
- Scenario Manager
Correct answer: What-If Analysis (Data Tables)
What-If Analysis with Data Tables lets you substitute different input values into formulas to see how results change, making it ideal for sensitivity analysis.
What-If Analysis Data Tables allow you to calculate multiple results by substituting a range of values into one or two variables in a formula. A one-variable data table shows results for different values of one input, while a two-variable table varies two inputs simultaneously. This is distinct from Scenario Manager (which saves named sets of values) and Solver (which finds optimal values). Data Tables are particularly useful in financial modeling for sensitivity analysis.
Question 6: What happens when you press Ctrl+Shift+L in Excel?
- Toggles AutoFilter on the selected data range (Correct answer)
- Applies a hyperlink to the cell
- Locks the selected cells
- Opens the Format Cells dialog
Correct answer: Toggles AutoFilter on the selected data range
Ctrl+Shift+L toggles the AutoFilter dropdowns on or off for the selected data range, enabling quick data filtering.
Ctrl+Shift+L is the keyboard shortcut to toggle Excel's AutoFilter feature. When activated, it adds dropdown arrows to each column header in your data range. These dropdowns allow you to filter data by specific values, text, dates, or custom criteria. Pressing the shortcut again removes the filter dropdowns. This is one of the most commonly used features for data analysis and is equivalent to going to Data tab and clicking the Filter button.
Microsoft Excel Basic and Advance Questions and Answers 2
Which Excel feature allows you to apply consistent formatting across multiple cells based on their values?