Formulas and Commands

Question 1

=ABS

Answer 1

Absolute function. Returns positive numbers only.

Question 2

=AND(logical argument 1, logical arg 2,…)

=OR(logical argument 1, logical arg 2,…)

Answer 2

If statement logic

Question 3

=CHOOSE(index number, value 1, …)

Answer 3

Select values based upon coding. Substitute for VLOOKUP and/or INDEX, MATCH. Lookup without using an external array / reference.

Question 4

=CONCATENATE(text 1,text2,…)

Answer 4

Combine data in 2 (or more) different cells; can use ampersand (=A1&B1) to do the same thing

Question 5

=COLUMNS(reference range)

Answer 5

Count the number of columns in an array or reference

Question 6

=RATE(periods,payment,-PV,FV,type)

Answer 6

CAGR / growth over a period of time based upon a number of periods

Question 7

=COUNT(value1, value2,…)

Answer 7

Count the number of items in an area

Question 8

=HLOOKUP(lookup,array,row index,range lookup)

=VLOOKUP(lookup,array,col. index,range lookup)

Answer 8

Reference inputs from an array to lookup values in a data set, either by row or by column

Question 9

=IF(logical test,value if true,value if false)

Answer 9

Condition-based arguments/logic

Question 10

=IFERROR(value,value_if_error)

Answer 10

Returns a value you specify if a formula results in an error; otherwise, returns the result of the formula

Question 11

=INDIRECT(ref_text,[a1])

Answer 11

Convert a text value into a valid cell reference. Can be used to create dynamic references (e.g., =INDIRECT(B6&“!A1”)) to reference another page (B6 = text value, which a spreadsheet; !A1 = cell in that spreadsheet)

Question 12

=INDEX(reference,row_num,column_num,area_num)

Answer 12

Provides a value or reference at the intersection of a particular row (1st) and column (2nd) in a range

Question 13

=MATCH(lookup value,lookup array,match type)

Answer 13

Provides the sequence position of text or a reference in an array (e.g., the 4th item in an array would return 4)

Question 14

=INDEX(reference,MATCH(__),MATCH(__))

Answer 14

Returns a specific value at an exact column and row intersection
INDEX = output, MATCH 1 = Row, MATCH 2 = Column

Question 15

=NPV(rate,value 1,…)

Answer 15

Provides the NPV for a DCF analysis

Question 16

=OFFSET(reference,rows,columns,height,width)

Answer 16

Returns a value offset from a specified reference point (good for dynamic values in an array, such as quarterly sales and you want the sum for, say, Q3)

Question 17

=SYD(cost,salvage,life,period)

Answer 17

Modeling depreciation on an item

Question 18

=TEXT(value,”format of text”)

Answer 18

Convert number to text in a specific format (e.g., convert text to dates, dates to yyyymm, etc.)

Question 19

Custom Number Syntax sequence (4 sections)

Answer 19

4 sections of code, separated by semicolons, in this order: POSITIVE; NEGATIVE; ZERO; TEXT

Question 20

Custom Number Syntax

Answer 20

Digit placeholder that represents optional digits and does not display extra zeros.

0 Digit placeholder that displays insignificant zeros.
? Digit placeholder that leaves a space for insignificant zeros but doesn’t display them.
@ Text placeholder
. Decimal point
, Thousands separator. A comma that follows a digit placeholder scales the number by a thousand.
\ Displays the character that follows it.
“ “ Display any text enclosed in double quotes.
% Multiplies the numbers entered in a cell by 100 and displays the percentage sign.
/ Represents decimal numbers as fractions.
E Scientific notation format
_ Skips the width of the next character. It’s commonly used in combination with parentheses to add left and right indents, _( and _) respectively.
* (asterisk) Repeats the character that follows it until the width of the cell is filled. It’s often used in combination with the space character to change alignment.
[] Create conditional formats.

Question 21

=COUNT(value1,…)

Answer 21

Counts the number of cells in a range that have NUMBERS in them

Question 22

=COUNTA(value1,…)

Answer 22

Counts the number of non-empty cells in a range that have NUMBERS or CHARACTERS in them

Question 23

=LEN(text)

Answer 23

Counts the number of characters in a cell, to include spaces

Question 24

=TRIM(text)

Answer 24

Gets rid of any space in a cell, except for single spaces between words

Question 25

=RIGHT(text,number of characters)
=LEFT( )
=MID(text, start number, number of characters)

Answer 25

Returns the specified number of characters within a string (relative to a position: right, left, mid)

Question 26

=SUMIF(range,criteria,sum_range)
=COUNTIF(range,criteria,sum_range)
=AVERAGEIF(range,criteria,sum_range)

Answer 26

Performs the respective function IF criteria is met

Question 27

=SUMIFS
=COUNTIFS
=AVERAGEIFS

Answer 27

Performs the respective function IF multiple criteria are met

Question 28

Calculate

Answer 28

F9

Question 29

Create chart (using table data)

Answer 29

Alt + F1

Question 30

Create Table

Answer 30

Ctrl + T

Question 31

Find & Replace

Answer 31

Ctrl + H

Question 32

Go to

Answer 32

Ctrl + G (or F5)

Question 33

Group

Answer 33

Alt + Shift + right arrow

Question 34

Ungroup

Answer 34

Alt + Shift + left arrow

Question 35

Hide column

Answer 35

Ctrl + 0

Question 36

Unhide column

Answer 36

Ctrl + Shift + 0

Question 37

Hide row

Answer 37

Ctrl + 9

Question 38

Unhide row

Answer 38

Ctrl + Shift + 9

Question 39

Insert hyperlink

Answer 39

Ctrl + K

Question 40

Move around data

Answer 40

Ctrl + arrow key

Question 41

Move around selected data

Answer 41

Ctrl + .

Question 42

Refresh

Answer 42

F5

Question 43

Strike through

Answer 43

Ctrl + 5