Microsoft Excel Questions and Answers 2 — Questions and Answers

Question 1: What is the difference between a workbook and a worksheet in Excel?

A workbook is the entire file containing one or more worksheets; a worksheet is a single tab within that file (Correct answer)
A workbook is for data entry and a worksheet is for charts
They are the same thing with different names
A worksheet can contain multiple workbooks

Correct answer: A workbook is the entire file containing one or more worksheets; a worksheet is a single tab within that file

A workbook is the Excel file itself (the .xlsx or .xlsm file), while a worksheet is an individual sheet (tab) within that workbook. One workbook can contain many worksheets.

Understanding the workbook/worksheet distinction is fundamental to Excel. A workbook is the file you open and save — it has a filename and extension (.xlsx, .xlsm, etc.). A worksheet (or sheet) is one of the tabs at the bottom of the Excel window. Each workbook can contain multiple worksheets, limited only by available memory. You navigate between worksheets using the tab bar at the bottom. Worksheets can be renamed, reordered, color-coded, hidden, and referenced in formulas using the syntax SheetName!CellReference.

Question 2: How do you create a named range in Excel?

Select the cells, then type a name in the Name Box to the left of the formula bar (Correct answer)
Right-click the cells and select Name Range
Press Ctrl+N while cells are selected
Go to Insert tab and click Named Range

Correct answer: Select the cells, then type a name in the Name Box to the left of the formula bar

The fastest way to create a named range is to select the cells, click in the Name Box (which shows the cell reference), type your desired name, and press Enter.

The Name Box is located to the left of the formula bar and normally shows the active cell address (e.g., A1). To create a named range: select the cells, click the Name Box, type a name (must start with a letter or underscore, no spaces), and press Enter. You can also use Formulas > Define Name for more options including scope (workbook or specific worksheet) and comments. Named ranges make formulas more readable (=SUM(MonthlySales) vs =SUM(B2:B13)), simplify navigation (typing a name in the Name Box jumps to that range), and are used in Data Validation dropdown lists.

Question 3: What is the purpose of the Format Painter tool in Excel?

To copy formatting from one cell and apply it to other cells (Correct answer)
To change the color of the worksheet background
To create custom number formats
To format text as a painting or image

Correct answer: To copy formatting from one cell and apply it to other cells

Format Painter copies all formatting (font, color, borders, number format, etc.) from a source cell and applies it to destination cells with a single click or drag.

Format Painter (Home tab, paintbrush icon) copies formatting from a selected cell and applies it elsewhere. Single-click the button to apply formatting once; double-click to lock it on for multiple applications (press Escape to deactivate). It copies font settings, fill color, borders, number formatting, alignment, text wrapping, and conditional formatting. This is much faster than manually recreating complex formatting. A lesser-known feature: it also works with shapes and chart elements. The keyboard shortcut alternative is: copy the source cell (Ctrl+C), select the target, then use Ctrl+Shift+V (Paste Special) and select Formats.

Question 4: Which Excel feature helps you identify errors in formulas by showing which cells are referenced?

Trace Precedents and Trace Dependents (Correct answer)
Error Checking
Formula AutoComplete
Smart Lookup

Correct 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.

Formula auditing tools on the Formulas tab include Trace Precedents (shows which cells feed into the selected formula with blue arrows), Trace Dependents (shows which formulas use the selected cell), Remove Arrows, Error Checking, and Evaluate Formula. Blue arrows indicate direct references on the same sheet; red arrows indicate error values; dashed arrows with worksheet icons indicate cross-sheet references. The Evaluate Formula dialog steps through complex formulas one operation at a time. These tools are essential for debugging formulas in complex workbooks with many interdependencies.

Question 5: What is the difference between relative and absolute cell references in Excel?

Relative references change when copied to another cell; absolute references stay fixed (Correct answer)
Relative references are slower; absolute references calculate faster
Relative references work only on the current sheet; absolute references work across sheets
There is no practical difference between them

Correct answer: Relative references change when copied to another cell; absolute references stay fixed

Relative references like A1 adjust based on the formula's new position when copied. Absolute references like $A$1 remain fixed regardless of where the formula is copied.

Cell references in Excel come in three types: Relative (A1) — both column and row adjust when the formula is copied. If =A1 in cell B1 is copied to B2, it becomes =A2. Absolute ($A$1) — both column and row are locked with $ signs and never change when copied. Mixed ($A1 or A$1) — one dimension is locked while the other adjusts. Press F4 while editing a reference to cycle through all four types: A1 → $A$1 → A$1 → $A1 → A1. Understanding reference types is crucial for building formulas that work correctly when copied across rows and columns.

Question 6: How can you quickly insert the current date into a cell in Excel?

Press Ctrl+; (semicolon) (Correct answer)
Press Ctrl+D
Type =DATE()
Press F5

Correct answer: Press Ctrl+; (semicolon)

Ctrl+; inserts the current date as a static value that does not update. Ctrl+Shift+; inserts the current time.

Ctrl+; (Ctrl+semicolon) inserts today's date as a static value — it will never change once entered. This is different from =TODAY() which is a volatile function that updates to the current date every time the workbook recalculates. Ctrl+Shift+; inserts the current time as a static value. You can combine both in one cell: press Ctrl+;, type a space, then press Ctrl+Shift+; to get a static date and time stamp. For an auto-updating timestamp, use =NOW() for date+time or =TODAY() for date only.

Microsoft Excel Questions and Answers 2

What is the difference between a workbook and a worksheet in Excel?