Excel Expert Cert Prep

You used the AutoFill Handle to fill the series in a column, but Excel just copied the number all the way down. What probably went wrong?

After filling the series down, you may have forgotten to check and make sure Fill Series was selected in the Auto Fill Options menu.

Suppose you want to change the colors currently used in your PivotChart. Which approach will work?

Chart Tools > Design > Chart Layouts > Change Colors Page Layout > Themes > Themes Page Layout > Themes > Colors

You need to remove duplicate data in a column. After selecting the data, the best way to accomplish this is to go to _____.

Data > Data Tools > Remove Duplicates

When you want to group and summarize data you create an outline of the data using _____.

Data > Outline > Group

How would you edit the conditional formatting rule formula "=A3=$A$1" for the range A3:A50 so the entire row was formatted for any cells in the range that meet the criteria?

Edit the rule using Conditional Formatting > Conditional Formatting Rules Manager > Applies to field, select the entire range to include all columns and rows, click Apply.

T/F: To enable all macros in a workbook, go to File > Excel Options > Trust Center > File Block Settings > Enable all Macros.

False

T/F: To only display a comment for a specific cell, click in the cell that contains the comment and go to Review > Comments > Show All Comments.

False

T/F: You cannot use COUNTIFS() if you only have one set of criteria. You can only use COUNTIF().

False

T/F: You would use a combo chart to display two different data types on a chart.

False

T/F: To find the smallest number in a list of Items Sold, you would use the NOTMAX() function.

False, MINIFS()

T/F: Sunburst charts are very similar to pie charts since several data series can be displayed in the sunburst chart.

False, Sunburst charts are very different from pie charts because a pie chart only displays one data series, while a Sunburst chart can display multiple data series.

T/F: The WORKDAY() function returns a number from 1 to 5 that indicates which day of the workweek the date falls on.

False, The WORKDAY() function returns the serial number of the date before or after a specified number of workdays.

T/F: You would use AND() in a nested COUNTIF() function if you wanted to make sure the data met one set of criteria AND was included in the count total.

False, You would use AND() in a nested function with COUNTIF() to make sure that two sets of criteria were met before the data was included in the count total.

How do you enable iterative calculations in a worksheet?

Go to File > Options > Formulas > Calculation options > Enable iterative calculation

How can you add Spanish (Costa Rica) to your list of editing languages so you can make sure that your spelling and grammar are correct in any documents that contain Spanish?

Go to File > Options > Language > Office authoring languages and proofing> Add a language, and select Spanish (Costa Rica) from the drop-down list. Click Add

Which LOOKUP function would you use if you want Excel to search for the match by row?

HLOOKUP

Which function will search through a column of data and return the row number of the specified value?

MATCH

Which date syntax will display the full month, a space, and the four-digit year?

MMMM YYYY

You created a map chart to display the cities and states in which sales were completed for 2019. When you view the map, the cities are not being displayed. What do you think went wrong?

Map charts are not able to display cities--only states and countries.

Which function would you use to calculate a car payment amount if you knew the PV (present value), interest rate, and term?

PMT

What do the "whiskers" in a Box and Whiskers chart display?

The "whiskers" are lines extending vertically from the boxes indicating variability outside the upper and lower quartiles.

You created a PivotTable and want to add Slicers so you can filter the data. You know that the Slicers command is under PivotTable Tools > Analyze > Filter > Insert Slicer but you don't see the PivotTable Tools tab on the ribbon. Why can't you see the tab?

The PivotTable Tools tab is contextual, so you only see the tab when the PivotTable is selected.

You created a conditional formatting rule that will highlight the cell if any of the values in the range A3:A50 meets or exceeds the New Client Goal of 3 in cell A1, using this Rule: "=$A$3 > =$A$1". However, the formatting was changed for the entire range whether the criteria was met or not. What probably went wrong?

The conditional formatting rule should be: =A3 > =$A$1.

What happens when you use Query to reference data in another workbook?

The data is linked between the source file and the destination file.

You would use the Formula Auditing tool _____ to see which cells rely on the value in the selected cell.

Trace Dependents

T/F: The SWITCH() function compares the cell value against a list of values and returns a result based on the first matching value. If no value is found, an optional value may be returned.

True

T/F: You can apply a data validation rule to cells that contain data to see if the data meets the validation rule by selecting the cells and going to Data > Data Tools > Data Validation > Circle Invalid Data.

True

When would you use a nested IF function?

Use a nested IF function to first determine IF the cell value meets the True/False criteria and, IF it does (True), perform the next calculation. IF it does not (False), either do nothing or perform a different calculation.

Which application in Excel is used to copy macros between workbooks?

Visual Basic (VBA)

Which statement regarding workbook versions is incorrect?

You cannot specify where Excel saves the AutoRecover files.

You tried to protect a range of cells in a worksheet with a password, but users can still update the range without entering a password. What probably went wrong?

You forgot to enter a Range Password before clicking OK to close the New Range dialog box

Why is the Scenario Manager useful?

You use the Scenario Manager to create different groups of values or scenarios that you can switch between to see different outcomes.

You have data on five different worksheets, one for each branch of the company, that shows sales for 2019. To summarize the data from all five worksheets into a corporate worksheet, you would use _____.

the Consolidate feature

Why would you use the AVERAGEIFS function?

to find the average of all cells that meet multiple sets of criteria in more than one range