Excel Sample Exam 1

Format Painter

Copies only the formatting of one cell to other cells

Paste Special

Pastes only the part of the copied cells you choose, such as values, formulas, or a transposed layout.

Freeze Panels

Keeps header rows and/or columns visible while you scroll.

Group/Ungroup

Adds or removes a collapsible +/- outline so you can hide and show groups of rows or columns

Data Validation

Restricts what can be entered in a cell, for example with a dropdown list.

Conditional Formatting

Automatically formats cells when a rule or formula is TRUE.

Hide/Unhide

Hides rows or columns from view (the data and formulas still work) and shows them again.

Wrap Text

Shows long text on several lines inside one cell.

Clear All

Removes Everything.

Clear Contents

Removes values and formulas but keeps formatting.

Clear Formats

Removes formatting but keeps values.

Go to Special

Selects all cells of one type at once, such as blanks, constants, formulas, or visible cell only.

Name Manager

Creates, edits, and deletes names for ranges, formulas, and LAMBDA functions.

Quick Access Toolbar

A customizable toolbar at the top of the window for the commands you use most.

Name Box

Shows the address or name of the selected cell; type an address or name in it to jump there or to name a range.

Convert the selected range into an Excel Table

Cmd+T

Move one cell right/accept an AutoComplete suggestion

Tab

Edit the active cell

Control+U

Jump to the edge of the current block of data

Cmd+Arrow

Extend the selection by one cell

Shift+Arrow

Extend the selection to the edge of the data

Cmd+Shift+Arrow

Fill Down (copy the top cell down the selection)

Cmd+D

Fill Right (copy the leftmost cell across the selection)

Cmd+R

Toggle between showing values and showing formulas

Control+

While editing a formula, cycle $A$1 → A$1 → $A1 → A1

Cmd+T

Paste values only (match destination formatting)

Cmd+Shift+V

Copy

Cmd+C

Cut

Cmd+X

Paste

Cmd+V

Selected the current data block (twice = whole sheet)

Cmd+A

Bold

Cmd+B

Italic

Cmd+I

Underline

Cmd+U

Redo(after an Undo)

Cmd+Shift+Z (or Cmd+Y)

Undo

Cmd+Z

Insert a line break inside a cell

Control+Option+Return

Insert = SUM() for the range above or to the left

Cmd+Shift+T

Recalculate all formulas

Fn+F9 (or CMD+=)

Flash Fill (reads the pattern from the example you typed in the column and fills the rest of the column to match)

Cmd+E

#NAME?

Excel does not recognize a name in the formula: a misspelled function, an undefined range name, or text without quotation marks (=SUMM(A1:A3))

#NUM!

The formula uses or produces a number Excel cannot calculate (=SQRT(-4))

#VALUE!

The formula uses the wrong type of value, usually a text where a number is expected (=A1×2 where A1 contains the text “BEA”)

Relative Reference

A1: both the column and the row change when the formula is copied

Absolute Reference

$A$1: nothing changes

Mixed References

$A1 locks the column, A$1 locks the row, A $ locks whatever comes right after it

Benefits of Converting a range into an Excel Table

• Formulas that refer to the table automatically include new rows added to it

• Formulas can use column names, such as EMP[AnnualSalary], instead of cell addresses

• Filter buttons are added to the header row automatically

EMP[AnnualSalary]

the whole AnnualSalary column of table EMP

[@Price]

the Price in the current row of tblProduct

tblProduct[#Headers]

the header row of tblProduct

tblProduct[#All]

all of tblProduct, including headers

B2#

How one would refer to all of the results of a dynamic array formula in another formula

Dynamic Array

Only the top left cell holds the formula, the other cells show its results

#SPILL!

When =Sequence(5) is entered into A1, but A3 already contains the text “x”, Value A1 shows

Array constant creates a single ROW contains 1,2,3

={1,2,3}

Array constant creates a single column containing 1,2,3

={1;2;3}

=DATE(2026,Sequence(12),15)

Formula that lists the 15th of every month in 2026.

DDDD

Monday (or day of the week)

DDD

Mon (abbreviation for day of the week)

DD

31 (number day for that day of the week)

0 vs #

0 always shows a digit, padding with zeros; # shows only significant digits

Custom Format ;;;

Hides every value. The value is still in the cell and still works in formulas (ex. can use in conditional formatting to only show colors)

RAND()

One random decimal, 0≤x≥1

RANDBETWEEN(bottom, top)

one random whole number from bottom to top

RANDARRAY(rows, cols, min, max, whole_number)

an array (spill) of random numbers; decimals, or whole numbers when whole_number = TRUE

In math, true and false

True = 1, False = 0