Module 1-4

Question 1

How would the number 00060010 appear in a cell?

Answer 1

60010

Question 2

How do you auto fit a column to match its longest cell entry?

Answer 2

Either double click the right border of the cell or click format button in cells group and click AutoFit column width

Question 3

Is a conditional format a format that changes based on the value entered in the cell?

Answer 3

Yes

Question 4

What is the difference between a sans serif and serif fonts?

Answer 4

Sans serif fonts are without extra decorative bits at the end, unlike serif fonts

Question 5

Is the general format the way numbers are displayed without interfering?

Answer 5

Yes

Question 6

State the 4 types of conditional formatting?

Answer 6

Colour scales, highlighting, data bars, and icon sets

Question 7

How many standard colours are there?

Answer 7

10

Question 8

How do you display the IF function if Q3 is greater than Q4 and want to text “OK, otherwise display “RETRY”?

Answer 8

=IF(Q3>Q4), “OK”, “RETRY”)

Question 9

Write a formula to perform exact match look up with the lookup value from cell G5 using a vertical lookup table located in the range A1:F50 and return the value from third column of the table?

Answer 9

= VLOOKUP(G5, A1:F50, 3, FALSE)

Question 10

Does excel ignore blank cells?

Answer 10

Yes

Question 11

In excel if number is rounded to the nearest integer then is the number that has to be displayed in the function 0?

Answer 11

Yes

Question 12

Would you use AVERAGE function to calculate the arithmetic mean of a price list?

Answer 12

Yes

Question 13

What function out of IF, ROUND, VLOOKUP, and AVERAGE is a logical function?

Answer 13

IF. A logical function is used to test whether a function is true or false

Question 14

Do sparklines replace cell contents?

Answer 14

Yes

Question 15

Does a trend line extend from the horizontal/vertical axis to the plot area to make it easier to identify value s in the chart?

Answer 15

No

Question 16

To drag the chart to the cell you want, is it necessary to hold down the CTRL key?

Answer 16

No

Question 17

Can formulas have nested functions?

Answer 17

Yes

Question 18

To select the range of non-adjacent cells do you need to select the cells of the first range then hold the CTRL key and select non-adjacent ones one by one?

Answer 18

Yes

Question 19

To select all cells in worksheet do you need to click inverted triangle in top left corner?

Answer 19

Yes

Question 20

Does the result of a formula always also show in the formula bar?

Answer 20

Yes

Question 21

What is the difference between COUNT and COUNTIF functions?

Answer 21

COUNT is a simple function that counts the amount of cells that have data in a range and COUNTIF is where conditions can be put on so only certain data points are selected

Question 22

Where are the currency signs located for both the currency and accounting format and how are are zeroes and negatives displayed in accounting format?

Answer 22

Currency format: Left of the first value recorded. Accounting format: Most left edge of the cell near the border and zeroes are recorded as dashes whereas negatives are in parentheses

Question 23

How many themes are built into excel?

Answer 23

30

Question 24

What does comma style mean?

Answer 24

Visualising numbers over 1,000 with commas

Question 25

To fix #DIV/0 issue should the IFERROR function be used to see if the denominator is 0 or no value, so these can be the result?

Answer 25

Yes

Question 26

For the #N/A issue does a range need to be selected that you want to make like another, then is test to columns, delimited, tab, and general column selected?

Answer 26

Yes

Question 27

To replace #NAME? what needs to happen?

Answer 27

Press Ctrl + H to open find and replace box, then type the function in find what box and leave replace with box blank then click replace all

Question 28

To fix #NULL! do references need to be picked so ranges intersect?

Answer 28

Yes

Question 29

What does #NUM! mean?

Answer 29

Fix the formula

Question 30

To fix #REF! does Ctrl + F (find function) need to be selected then the replace tab?

Answer 30

Yes then type the function in find field and leave replace empty, then press replace all

Question 31

For #VALUE! do you need to narrow range and then go to evaluate formula and click evaluate til see error? If something is in quotation marks does this mean text?

Answer 31

Yes. Yes.

Question 32

What do the functions that end with A mean? What does MODE.MULT and MODE.SNGL mean?

Answer 32

Returns the … including numbers, text, and logical values. Returns a vertical array of most frequently occurring, or repetitive values in an array of data. Returns most common value of a data set

Question 33

For a VLOOKUP does FALSE=Exact and Approximate=True? Is it always exact unless told otherwise due to approximate could give unexpected value?

Answer 33

Yes. Yes.

Question 34

How are data markers added?

Answer 34

Right click on the line you want to add them to and select format data series. Click button with paint can icon then marker button

Question 35

Is flash fill more flexible than autofill? Is the shortcut Ctrl + E?

Answer 35

Yes. Yes.

Question 36

What is the difference between relative, absolute and mixed referencing?

Answer 36

Relative: referenced cells move down as formula moves down. Absolute: $ signs for fixed reference. Mixed: want either row or column to be fix but not both