Excel Expert Modules 1-7
When you reference another workbook in a formula, what symbol is after the source worksheet name within the fromula?
!
To return an exact match by using the MATCH function, what value would you use for the match_type?
0
How many series of data can you use in a pie chart?
1
How many conditions can you have within an AND function?
255
Which of the following is the correct syntax for the WORKDAY function?
=WORKDAY(start_date, days, [holidays]
When a cell has a precedent in another workbook, what is attached to the arrow?
A black grid
When Excel detects a possible error in a cell, which of the following indicators will the cell display?
A green triangle in the corner
What is a cell that affects the value of other cells known as?
A precedent
What are the main options available in the Conditional Formatting Rules Manager dialog box? A. New Rule B. Delete Rule C. Edit Rule D. Reverse Rule
A. New Rule B. Delete Rule D. Reverse Rule
Instead of creating a nested IF function, what single function can you use if you have multiple criteria to meet?
AND
Which of the following best describes what the macro recorder captures?
Almost all the actions you perform
Which character indicates a comment in a macro?
Apostrophe
Which of the following features is Flash Fill a part of?
AutoFill
By default, how are formulas set to update?
Automatically
Which of the following are selections you can make in the Subtotal dialog box? A. Group by B. At each change in C. Use function D. Add subtotal
B. At each change in C. Use function D. Add subtotal
Which of the following options can you use to set calculation options? A. Select the Format tab, and then select the Formula Auditing group B. Select the Format tab, and then select the Calculation group C. Select File, Select Options, and then select General D. Select File, select Options, and then select Formulas
B. Select the Format tab, and then select the Calculation group D. Select File, select Options, and then select Formulas
Which of the following functions can you replace with Flash Fill? UPPER LEFT VLOOKUP CONCAT
CONCAT UPPER LEFT
Which of the following commands does not belong to the Formula Auditing group?
Calculation Options
Which type of chart can you use when using the Forecast Sheet command?
Column and Line
When a chart combines two or more types of charts, what is it known as?
Combo
What is another name for the condition used in conditional formatting?
Criteria
What is the keyboard shortcut to enter the current time into a cell?
Ctrl + Semicolon
Which of the following keyboard shortcuts can you use to help locate errors in a worksheet?
Ctrl+G and F5
What do you use to add new words to Office apps?
Custom dictionary
Which tab includes commands related to macros?
Developer
On which group on the Home tab can you find Flash Fill?
Editing
To step into a formula, what command would you use?
Error checking
When referencing a named range in another workbook, what keyboard shortcut can you use to open the Paste Name dialog box?
F3
In a VLOOKUP or a HLOOKUP function, what should you NOT specify in the fourth segment of the formula if you want an approximate match return?
False
You can use the Fill command in the Editing group on the Home tab to:
Fill cells with a series of values.
Which of the following categories does the NPER function belong to?
Financial
Which of the following describes what you can do with Goal Seek?
Find the correct input for the value you want
Which of the following commands can you use to help predict trends?
Forecast Sheet
On which tab will you find Watch Window?
Formulas
To change the color of the error indicator in a cell, which tab would you select in the Excel Options dialog box?
Formulas
To display progressively smaller steps, which of the following charts would you use?
Funnel
Which of the following Excel Options tabs can you use to change the default font type and font size?
General
Which of the following statements are true?
Goal Seek can replace one value at a time
You can use formula auditing tools to:
Identify why a formula is not working as expected.
By default, where are macros saved?
In the current workbook
To which of the following categories do the IF, AND, OR, and NOT functions belong?
Logical
Which of the following categories does the IFS function belong to?
Logical
If the result of a formula should be 12/31/2020 and it displays as 44196. Which of the following formats will fix it?
Long Date
What is the additional axis on a dual-axis chart also known as?
Secondary vertical (value) axis
The available tabs for Data Validation settings are a. Error Alert b. Settings c. Format d. Input Message
Settings Error Alert Input Message
Which of the following categories does the AVERAGEIF function belong to?
Statistical
What are the styles of error messages available for data validation? a. Stop b. Warning c. Caution d. Information
Stop Warning Information
If a cell has a ##### error code, what does it indicate
The column is not wide enough to display the cell contents
In a Linear fill series, what does the Step value refer to?
The difference between each number in the series.
In a Growth fill series, what does the Step value refer to?
The value by which to multiply each value in the series.
What does the V in VLOOKUP mean
Vertical
Macros consist of code in which programming language?
Visual Basic for Applications
If you want to calculate the next three working days from a specific date, which of the following functions would you use?
WORKDAY
Which of the following command categories is Goal Seek part of?
What-If Analysis
When you reference another workbook in a formula, what type of brackets enclose the source workbook within the formula?
[ ]
Where can you enable macros when you open a macro-enabled workbook? a. A command in the Backstage view b. On the Home tab c. On the message bar d. On the Review tab
a. A command in the Backstage view c. On the message bar
Select the requirements for data grouping a. A label in the first row b. A total in the last row c. Similar facts in each column d. No blank rows or columns in the selected range
a. A label in the first row c. Similar facts in each column d. No blank rows or columns in the selected range
By using the Data Validation command, you can provide worksheet users with Select here to enter text. a. A warning message about their cell entry. b. A list of values to choose from for the cell. c. A message about the input they need to enter in the cell. d. choice of formulas
a. A warning message about their cell entry. c. A message about the input they need to enter in the cell.
Which of the following tabs are available in Excel Options? a. Advanced b. Data c. Ease of Access d. Review
a. Advanced b. Data c. Ease of Access
Select the functions you can apply as subtotals. a. Average b. Sum c. Concatenate d. Count
a. Average b. Sum d. Count
Which of the following keyboard shortcuts can you use to navigate to a precedent or dependent cell? a. Ctrl+[ b. Ctrl+( c. Ctrl+] d. Ctrl+)
a. Ctrl+[ c. Ctrl+]
Which of the following belongs to the what-if analysis tools? a. Data Tables b. Scenario Manager c. Goal Seek d. Data Validation
a. Data Tables b. Scenario Manager c. Goal Seek
Which of the following can you configure from the Record Macro dialog box? a. Description b. Macro name c. Security settings d. Shortcut key
a. Description b. Macro name d. Shortcut key
What options are available for different languages a. Display b. Translation c. Editing d. Help
a. Display c. Editing d. Help
Which of the following commands are available in the Scenario Manager dialog box? a. Merge b. Summary c. Add d. Show All
a. Merge b. Summary c. Add
To represent Sunday as 1 in the WEEKDAY function, which return type can you use from the following? a. Omit it b. 1 c. 11 d. 17
a. Omit it b. 1 d. 17
Which of the following charts would be useful for displaying frequencies? a. Pareto b. Sunburst c. Waterfall d. Histogram
a. Pareto d. Histogram
To summarize data, which of the following functions can you use with the Consolidate feature? a. Sum b. Average c. Product d. Dividend
a. Sum b. Average c. Product
There are many Lookup & Reference functions in Excel 2019, not all of which are mentioned in this lesson. Which of the following functions do not belong to the Lookup & Reference function category? a. Switch b. Hlookup c. Replace d. Lookup
a. Switch c. Replace
What two options can you use to add new words to the custom dictionary? a. The spell checker b. References c. The Backstage view d. Dictation
a. The spell checker c. The Backstage view
What formulas can you use to change text case? a. UPPER b. MIDDLE c. LOWER d. PROPER
a. UPPER c. LOWER d. PROPER
Which of the following are types of charts? a. Waterfall b. Win/loss c. Box-and-Whisker d. 3D map
a. Waterfall c. Box-and-Whisker
Which of the following statements is correct? a. You can add watches from other workbooks while you are working in another workbook, but only if the other workbooks are open. b. You can add watches from other workbooks while you are working in another workbook, even if the other workbooks are closed. c. You can track a watch from another workbook if you have added the watch from the workbook you are currently working in. d. You can track a watch from another workbook if you have added the watch from the workbook you are currently working in, but only if the other workbook is open.
a. You can add watches from other workbooks while you are working in another workbook, but only if the other workbooks are open. d. You can track a watch from another workbook if you have added the watch from the workbook you are currently working in, but only if the other workbook is open.
If you use only one section in a number format, Excel applies it to
all numbers
Pre-built conditional formatting rules include: a. Sparklines b. Icon sets c. Data bars d. Color scales
b. Icon sets c. Data bars d. Color scales
Which of the following statements is true? a.You can hide the Formula Bar in the current workbook only. b. You can hide the Formula Bar for all workbooks only. c. You can hide ScreenTips from everywhere in Excel, except when entering functions manually. d. You can hide ScreenTips from everywhere in Excel.
b. You can hide the Formula Bar for all workbooks only. d. You can hide ScreenTips from everywhere in Excel.
Which of the following commands can help fix errors in a worksheet? a. Watch Window b. Remove Arrows c. Error Checking d. Evaluate Formula
c. Error Checking d. Evaluate Formula
Excel applies conditional formatting rules in order of
description
What can you use to change text case?
formulas
What is the maximum number of sections a number format can have?
four
What must you use to separate sections of a number format?
semicolon
How many levels can you have in an outline?
six
Use the Consolidate feature to consolidate data from multiple
worksheets
Where can you find the Record Macro command?
On the Developer tab
What is PMT an abbreviation of?
Payment
To set the dictionary language to Spanish (Mexico), which tab would you use within Excel Options?
Proofing
Which of the following must you complete when creating an NPER function? a. Rate b. Pmt c. Pv d. Fv
Rate Pmt Pv
The HLOOKUP function belongs to which category of functions
Lookup & Reference
To which category of functions does MATCH belong?
Lookup & Reference
Which of the following functions does not exist in Excel 2019
MAXIF
Which of the following converts actions that you perform into VBA code?
Macro Recorder
Which of the following do you use to automate repetitive tasks?
Macros
Which of the following categories does the SUMIFS function belong to?
Math & Trig
Which of the following is a container that organizes macros in a workbook?
Modules
The WEEKDAY function has several return types that you can use to represent the day of the week as a number. If you enter 2 as the return type, the result you get is 1. What day of the week does 1 refer to?
Monday
Which of the following functions will keep a formula that refers to dates current?
NOW
If the content of A2 is 100 and the contents of B2 is 200, what result would you get using the formula =IF(AND(A2>50,B2>200),"Excellent","Needs improvement")
Needs Improvement
You can use an IFS function instead of which of the following functions
Nested IF
What is the dialog box in which you select a rule type for a new rule?
New Formatting Rule
What does NPER stand for?
Number of periods
