Excel Terms and Functions

Question 1

How do you toggle relative and absolute references?

Answer 1

F4

Question 2

Define VLOOKUP

Answer 2

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]}

Question 3

Define HLOOKUP

Answer 3

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

Question 4

Define INDEX

Answer 4

=INDEX(array, row_num, [col_num])

Question 5

Define MATCH

Answer 5

=MATCH(lookup_value, lookup_array, [match_type})

Question 6

What is a substitute for VLOOKUP/HLOOKUP?

Answer 6

=INDEX(array, MATCH(), MATCH())

Question 7

Define IF

Answer 7

=IF(logical_test, [value_if_true], [value_if_false])

Question 8

Define AND

Answer 8

AND(criteria1, criteria2, etc)

*All criteria must be met

Question 9

Define OR

Answer 9

OR(criteria1, criteria2, etc)

*At least one criteria must be met

Question 10

Define NOT

Answer 10

NOT(criteria1, criteria2, etc)

*None of the criteria may be met

Question 11

Define SUMIF

Answer 11

=SUMIF(range, criteria, [sum_range])

Question 12

Define COUNTIF

Answer 12

=COUNTIF(range, criteria)

Question 13

Define AVERAGEIF

Answer 13

=AVERAGEIF(range, criteria, [average_range])

Question 14

Define COUNTIFS

Answer 14

=COUNTIFS(criteria_range1, criteria1,…)

Question 15

Define SUMIFS

Answer 15

=SUMIFS(sum_range, criteria_range1, criteria1,…)

Question 16

Define AVERAGEIFS

Answer 16

=AVERAGEIFS(average_range, criteria_range1, criteria1,…

Question 17

Define UPPER

Answer 17

=UPPER(text)

Question 18

Define LOWER

Answer 18

=LOWER(text)

Question 19

Define PROPER

Answer 19

=PROPER(text)

Question 20

Define CONCATENATE/CONCAT

Answer 20

=CONCATENATE(text1, . . .)

Question 21

Define LEFT

Answer 21

=LEFT(text, [num_chars])

Question 22

Define RIGHT

Answer 22

=RIGHT(text, [num_chars])

Question 23

Define MID

Answer 23

=MID(text, start_num, num_chars)

Question 24

Define FIND

Answer 24

=FIND(find_text, within_text, [start_num])

Question 25

Define SUBSTITUTE

Answer 25

=SUBSTITUTE(text, old_text, new_text, [instance_num])

Question 26

Define TODAY

Answer 26

=TODAY()

*Gives today’s date

Question 27

Define NOW

Answer 27

=NOW()

*Gives the current time and date

Question 28

Define DAYS

Answer 28

=DAYS(end_date, start_date)

*Gives the days, or rather nights, elapsed

Question 29

How do you get the actual number of days from the DAYS function?

Answer 29

Add 1 to the =DAYS() function

Question 30

Define MONTH

Answer 30

=MONTH(serial_number)

*This will extract the month from a full date

Question 31

Define YEAR

Answer 31

=YEAR(serial_number)

*This will extract the year from a full date

Question 32

Define YEARFRAC

Answer 32

=YEARFRAC(start_date, end_date, [basis]}

*Expresses the difference of something in years, can be used with the TODAY function

Question 33

Under what ribbon tab do you find “Freeze Panes”?

Answer 33

View

Question 34

How do you resort or reorganize rows of data?

Answer 34

Use the “Sort & Filter” function on the Home ribbon

Question 35

How do you turn column headers into filter drop-down lists?

Answer 35

Control+Shift+L

Question 36

What is Excel’s order of operations?

Answer 36

“PEMDAS”

Parentheses, Exponents, Multiplication, Division, Addition, and Subtraction

Question 37

How do you give a customized filter?

Answer 37

Use the “Custom Sort” function on the Home ribbon

Question 38

Define OFFSET

Answer 38

=OFFSET(reference, rows, cols, [height], [width])

*Remember to start one row ahead of where MATCH will use as a table

Question 39

Define ABS

Answer 39

=ABS(number)

*Gives the absolute value of a number

Question 40

Define ROUND

Answer 40

=ROUND(number, num_digits)

*Takes the number you want to round, and the number of digits to round to.

Question 41

Define ROUNDUP

Answer 41

=ROUNDUP(number, num_digits)

*Will always round up

Question 42

Define ROUNDDOWN

Answer 42

=ROUNDDOWN(number, num_digits)

*Will always round down

Question 43

Define RANDBETWEEN

Answer 43

=RANDBETWEEN(bottom, top)

Question 44

Define RAND

Answer 44

=RAND()

*Returns a real decimal number between 0 and 1

Question 45

Define SUMPRODUCT

Answer 45

=SUMPRODUCT(array1, array2, array3,….)

*SUMPRODUCT multiplies a set of ranges together and returns the sum of their products.

Question 46

Define IFERROR

Answer 46

=IFERROR(value, value_if_error)

*A given value and the value to display if the value is in error

Question 47

Define PV

Answer 47

=PV(rate, npernumber of periods, pmt payment made each period, negative number, [fv], [type])

Question 48

Define FV

Answer 48

=FV(rate, nper, pmt, [pv], [type])

*Future value of a loan

Question 49

Define RATE

Answer 49

=RATE(nper, pmt, pv, [fv], [type], [guess])

*Gives the interest rate per period.

Question 50

Define PMT

Answer 50

=PMT(rate, nper, pv, [fv}, [type])

*Gives the payment per period of a loan.

Question 51

Define NPER

Answer 51

=NPER(rate, pmt, pv, [fv], [type]}

*Gives the number of periods for a loan.

Question 52

What is the goal of charting?

Answer 52

It is to design and organize data in a visually compelling way so that your audience can easily process complex information.

Question 53

Best use of column or bar chart?

Answer 53

When you want to compare several categories at a single point in time.

Question 54

Best use of a line chart?

Answer 54

When you want to track some change in the value of a category or categories over time.

Question 55

What are steps 1-3 of breaking down a Data Analytics problem?

Answer 55

1. Ask clarifying questions - What don’t you understand?
2. Ask yourself questions - What do you want to know about the problem?
3. Familiarize yourself with the raw data

Question 56

What are steps 4-5 of breaking down a Data Analytics problem?

Answer 56

4. Test the quality of the raw data

5. Begin building out calculations