IDEK excel certification??

Which one is an absolute cell reference in Excel?

$B$2

Column A contains the email addresses. Which tool can separate the email list without using a formula to fill the first names to column B and the last names in column C?

Flash Fill

In the worksheet below, what will be the result in A1 after cells A1 and B1 are merged?

Student ID

Which is NOT a valid Excel formula?

=B3+5(A1+A2)

Which of the following yields a different result compared to the others?

=AVERAGE(B1, B4)

Which range is a single row of cells across multiple columns?

A11:F11

Which formula refers to the cell A1 on worksheet Sheet2 in the same workbook?

=Sheet2!A1

In the worksheet below, what’s the name of each element?

1 Format Painter, 2 Name box, 3 Formula bar, 4 Select all

There are 3 different cell references: relative, mixed, and absolute cell reference. Which is the correct keyboard shortcut to switch the cell reference types?

F4 (Windows) or Command + T (Mac)

Monthly inquiries of 2021 are entered in cell B4 to B15, as shown below. To get year-to-date running total inquiries, which formula should you enter in C4 and autofill through C15?

=SUM($B$4:B4)

When you save a workbook to a Comma-separated values (CSV) format, what are saved?

Cell values on the current worksheet

Which of the following issues are NOT checked when we use Inspect Document?

Named ranges and misspelled words

How to use Format Painter with non adjacent cells?

Double-click the Format Painter

To calculate the Total Salary for each person, hours are multiplied by the hourly wage Hours, which of the following options allows us to enter to formula once in C4 and then drag down to C7 using the Fill Handle?

=B4*$B$1

Cell C2 contains the formula =A2+B1. If you drag the formula to C3 using the Fill Handle, what is the result in cell C3?

2

To assist you in checking formulas, you can use the Trace Precedents and Trace Dependents commands to graphically display and trace the relationships between these cells and formulas with tracer arrows, as shown in this figure. Which one is NOT correct?

Column B is a precedent of the formula in cell D8

What happens if you use the AutoSum button in cell E10?

AutoSum will return =SUM(E1:E9)

What is the best solution for the ### issue shown below?

Double click the right boundary of column header A

Which paste option is used in the image below?

Paste special > Transpose

What’s the best way to repeat the column headings when printing a multiple-page document?

Use Print Titles

which one of the following options can NOT extract the last four characters of column A?

=EXTRACT()

Which formula will return the current time only?

=NOW() - TODAY()

Which function in cell B2 can extract the City from column A to column B as shown in the figure below?

=RIGHT(A2,LEN(A2)-3)

Which function can return the third largest value within a column?

LARGE

Which formula should you use in cell B2 in order to get the result Nancy Smith?

=Proper(A2)

To run the number 23.4328 upward to 23.44, which function should you use?

ROUNDUP

The value of cell A1 is 30. Cell A2 contains the formula =IF(A1<50,100-A1,0), what is the result of cell A2?

70

Given the image below, the formula =ISTEXT(A4) will return the result as:

TRUE

What is the result of the formula =IF(MEDIAN(4,5,5,7,8,9)>MODE(4,5,5,7,8,9), “MEDIAN”, “MODE”)?

MEDIAN

You are determining Average Price by dividing Total by Qty. Which Excel function would you use to avoid the #DIV/0! error in cell D3?

IFERROR

Which formula will calculate the average sales of Emilee and product lip gloss?

=AVERAGEIFS(F:F,D:D, “lip gloss”, B:B. “Emilee”)

Which formula returns the result 78?

=CHOOSE(A3,B2,B3,B4)

what is the result of the formula =5&7?

57

Which formula is used to return how many cells in range from B2 to B10 that the value is greater than 90?

=COUNTIF(B2:B10, “>90”)

The formula =IF(IF(TRUE,1,0),TRUE,FALSE) will return the result as:

TRUE

To return 1 when B2 is “Saturday” or “Sunday”, which formula from below should you use?

=IF(OR(B2="Sunday”, B2=”Saturday”),1,0)

Given the image below which function returns the result CT?

=XLOOKUP(F2,A2:A6,C2:C6)

Why would you use VLOOKUP with the last input parameter set to TRUE?

to require an approximate match

Given the image below, the formula =OFFSET(B3,3,2) will return the result as:

40

Given the image below, to calculate how many cells begin with A, which of the following formulas should you use?

=COUNTIF(A2:A11, “A*”)