WorksheetsExcel Functions and Formulas
Total questions: 25
Worksheet time: 20mins
What is the default reference type in Excel?
Absolute reference
Relative reference
Mixed reference
In Excel, how can you create an absolute reference?
By using the $ symbol before the column and row
By using the % symbol before the column and row
By using the & symbol before the column and row
By using the # symbol before the column and row
Fill in the blank: A relative reference corresponds to a cell location in a workbook and changes when users move the formula to another _______.
cell
sheet
workbook
file
The purpose of using different cell reference types in a workbook is to:
allow dynamic updating of formulas when copied
restrict changes to specific cells
enhance the visual appearance of the workbook
improve the speed of calculations
Which is an example of a structured reference formula?
=SUM(A1:A10)
=AVERAGE(A1:A10)
Label the following statement as true or false: Blank cells and zeros do not count against an average.
True
False
Which function in Excel counts only the blank cells within a range of data?
COUNT
COUNTA
COUNTBLANK
SUM
What is the purpose of the COUNT function in Excel?
Use a count function to count the number of grades greater than 70 in cells B4:M4. What function should you use?
COUNTIF
COUNTA
COUNTBLANK
COUNTIFS
Use a count function in cell U4 to count the number of grades in cells B4:M4. What function should you use?
COUNT
SUM
AVERAGE
MAX
Use a count function in cell V4 to count the missing scores for cells B4:M4. What function should you use?
COUNTIF
COUNTA
COUNT
SUM
Use an IF function in cells W4:W18 that tests for missing scores. Ensure a cell displays YES if the student has missing scores.
=IF(W4:W18="", "YES", "NO")
=IF(ISNUMBER(W4:W18), "YES", "NO")
=IF(W4:W18>0, "YES", "NO")
By default, what appears in a cell that meets the FALSE condition of an IF function?
Use a function in cell G4 to generate a list of unique values from cells A4:A18. What function should be used?
UNIQUE
FILTER
SORT
INDEX
What is the purpose of the LEFT function in Excel?
Which function extracts a specified number of characters from the middle of a text string in Excel?
MID
LEFT
RIGHT
TRIM
The purpose of using the CONCAT function in Excel is:
To add numbers in a range of cells
To combine text from multiple cells into one cell
To find the average of a set of numbers
To sort data in ascending order
What is the difference between CONCAT and TEXTJOIN functions in Excel?
CONCAT combines text from multiple ranges without a delimiter, while TEXTJOIN combines text from multiple ranges with a delimiter.
CONCAT combines text from multiple ranges with a delimiter, while TEXTJOIN combines text from multiple ranges without a delimiter.
What delimiter should be used when combining text from columns A and B with a dot between names?
Comma
Semicolon
Space
Dot
Label the following statement as true or false: Chart types cannot be changed once you have created them.
True
False
What key is used when selecting non-contiguous (not connecting or sharing a border) ranges of data?
The Ctrl key is used when selecting non-contiguous ranges of data.
The Shift key is used when selecting non-contiguous ranges of data.
The Alt key is used when selecting non-contiguous ranges of data.
The Tab key is used when selecting non-contiguous ranges of data.
The purpose of adding a data series to a chart is to:
visualize trends over time
add decorative elements
increase file size
make the chart colorful
Why doesn't the Switch Row/Column feature work well on a pie chart?
The feature is not compatible with pie charts.
Pie charts do not have rows and columns.
Switching rows and columns does not affect pie charts.
Pie charts require specific data formats.
Which of the following cell references will not change when a formula is copied to another cell?
a) A1
b) $A$1
c) A$1
d) $A1
It is always best to use the exact value instead of the cell reference when using formulas.
TRUE
FALSE
