Module 3 Practice

If the VLOOKUP function cannot find a match in the lookup table, what does it return?

#N/A error value

Your worksheet contains a price in cell A5, and many formulas refer to that price. How would you refer to that price in the formulas?

$A$5

Which of the following formulas adds 25 and 10, and then multiplies the result by 50?

(25+10)*50

Sam wants to count the number of cells between B1 and B20 that contain numbers in them. Which of the following formula should he use to do so?

=COUNT(B1:B20)

A student is given the task of counting the number of nonblank cells in the range of cells B1 to B20. Which of the following formulas should he use to do so?

=COUNTA(B1:B20)

What is the correct formula to insert the date January 12, 1998 into a cell?

=DATE(1998,1,12)

Ian needs to extract the day of the month from the date entered as a serial number in cell A1. Which formula can he use to do this?

=DAY(A1)

The score of a student is inserted in cell B2. The passing score for the subject is 60. Which of the following functions should you insert in cell C2 to check whether the student has passed or failed?

=IF(B2>=60, "Pass", "Fail")

Which formula can you use to extract the month number from the date entered in cell F5 as July 8, 2016?

=MONTH(DATE(2016,7,0))

Cell A5 contains the number 24.7835. In another cell, Ariane wants to enter the number from cell A5 but rounded to two decimal places. Which of these formulas can she use to do so?

=ROUND(A5,2)

When you paste formula cells using the Paste button in the Clipboard group, which of the following is true?

A button appears near the selected cells with options for pasting formulas and values.

Which of the following functions would you use to calculate the arithmetic mean of a price list?

AVERAGE

When you double-click the right border of a column heading, which of the following occurs?

AutoFit resizes the column to the widest cell entry.

To select a single worksheet cell so you can work with it, which of the following would you do?

Click the cell.

While working in Page Layout view, how can you enter your name so it appears in the lower-right corner of the printout?

Click the footer section on the right and type your name.

To help you easily identify sheets in a workbook, you can add _____ to the sheet tab.

Color

Jacquan wants to use a cell style similar to the default Title style, but with a larger font size and different font color. What can he do?

Create a custom cell style.

Which type of conditional formatting rule would you create to add a horizontal bar to a cell background, where the length of the bar reflects the value in the cell?

Data Bar

When you copy a formula from cell A10 to cell B10, what happens to the cell references in the formula?

Excel changes the cell references in the copied formula to reflect the new location of the formula.

A Line sparkline displays a green block for increasing values and a red block for declining values.

False

A good practice is to move the most important worksheets to the end of the workbook.

False

A(n) trendline is a line that extends from the chart's horizontal or vertical axis into the plot area, making it easier to identify the values in the chart.

False

AutoComplete can extend any data pattern involving dates, times, numbers, and text.

False

Cell A4 contains the time 10:27 pm. To round that time to the nearest 30-minute interval, use the formula =MROUND(A4,0:30).

False

Changing a worksheet window to Normal view changes the contents of the worksheet.

False

The Comma Style format adds a comma and a dollar sign to a cell value.

False

To add a worksheet to a workbook, you would use the New formula tab below the worksheet.

False

To create a formula that provides a statistical analysis of data, include a function from the Text category.

False

To display the contents of a worksheet but not how it will look when printed, use Page Layout view.

False

To find the largest value in a cell range, use the MIN function.

False

To insert only the current date in cell B1, use the NOW function in cell B1.

False

You can insert a formula by selecting a range of values, clicking the Quick Analysis button, and then clicking Tables.

False

You should move a chart to a chart sheet when you want the source data to appear with the chart.

False

You have selected a cell with a formula. Which of the following can you use to copy that formula to an adjacent cell?

Fill handle

Maddie entered customer first names in column A and last names in column B. What feature can she use to enter the full names in column C?

Flash fill

To change the color of cell content, you use the ________ color list arrow.

Font

Myra does not want a worksheet of sensitive data to be available to other users, but she does want to use its data in formulas in other worksheets. What can she do?

Hide the worksheet.

To format a cell range so that values between 100 and 500 appear in red, which of the following can you use?

Highlight Cells Rules

Nathan wants a formula to return "Yes" if the value in cell A1 is less than the value in cell B1, and to return "No" otherwise. Which of the following functions should he use?

IF

In a complex formula, how does Excel determine which calculation to perform first?

It follows the order of operations.

In column D of a worksheet, Cal lists the number of hours each employee works. What function should he use to find the employee who worked the most hours?

MAX

To have Excel enter the lowest price from a range of prices, which of the following would you use?

MIN function

To combine multiple cells into one combined cell, which of the following do you use?

Merge and Center button

Steffie needs a wider left margin in her worksheet. Which of the following can she do to change the margin?

On the Margins tab of the Page Setup dialog box, change the number in the Left box.

Bree added data labels to a pie chart, where they appear on each slice. She wants the data labels to appear outside of the pie chart but close to each slice. Which Label Position option should she select for the data labels?

Outside End

Your worksheet appears with a reduced view of each page and blue dividers where new pages begin. What view are you in?

Page Break Preview

To which of the following chart types can you not add axis titles?

Pie chart

You can create conditional cell formats using either the Home tab or the _____.

Quick Analysis tool

After inserting a new worksheet, what should you do to identify the purpose of the worksheet?

Rename the worksheet.

Which of the following options can you set to make sure a worksheet will print on one page?

Scaling

Kathy wants to add the numbers entered in a range of selected cells. To do this, which tab will she click on the menu that appears after clicking on Totals in the Quick Analysis option?

Sum

Which of the following formulas totals the cells B6, B7, B8, B9, and B10 most efficiently?

The formula =SUM(B6:B10)

Which of the following is true about deleting a worksheet row?

To delete a row, you can select the row, and then click the Delete button in the Cells group.

A combination chart combines two chart types and can plot data on primary and secondary axes.

True

A student types the formula '= YEAR( TODAY())-2005' to calculate the age of a person born in 2005. This formula will use the TODAY function as an argument for the YEAR function to obtain the current year, and then subtract 2005 returning the person's age.

True

A theme is a predefined coordinated set of colors, fonts, graphical effects, and other formats.

True

Alicia created a chart that shows months in rows and prices in columns. She can switch the rows and columns to show prices in rows and months in columns.

True

Column sparklines show a general trend without a legend, title, or gridlines.

True

Dean wants to insert two blank rows above row 5. To do so, he can click the row 5 heading, and then click the Insert button in the Cells group two times.

True

In a chart, a data table displays the data series in rows and the category values in columns.

True

Jose inserts the formula =DATE(2021, 2, 15) in cell A15. When he presses ENTER, 2/15/2021 will appear in the cell.

True

To compare data from different categories where the values are indicated by the height of the data series, select a Stock chart on the Recommended Charts tab in the Insert Chart dialog box.

True

To move a worksheet, you can drag its sheet tab to the left or right.

True

To paste only a copied cell's formats, use the Paste Special command.

True

To print a worksheet, you begin by going to Backstage view.

True

Use Goal Seek to specify an output value and work backward to find the input value needed to reach that goal.

True

When you are using a workbook and want to open another one, you can display Backstage view and then click Open.

True

When you click a cell to insert a cell reference in a formula, you must type a(n) number to lock the cell reference in the formula

True

When you insert a worksheet row, a row is inserted above the cell pointer and the sheet contents move downward.

True

When you use the Windows Start menu to start Excel, it opens in Backstage view.

True

You can edit cell contents in the formula bar or in the cell itself.

True

You can set a scaling option to resize a worksheet to fit within a single page.

True

You use conditional formatting to format a cell based on its value.

True

Arlo wants to use Goal Seek to answer a what-if question in a worksheet. Which button should he click on the Data tab to access the Goal Seek command?

What-if Analysis

Which type of sparklines display a green block for increased values and a red block for declining values?

Win/Loss

Which of the following is true about moving cell contents?

You can move cells using the drag-and-drop feature.

In the formula =SUM(A6:A9), which of the following best describes A6:A9?

argument

A(n) _____ is descriptive text that appears next to a chart's horizontal or vertical axis.

axis title

You can use the Find and Replace dialog box to find and replace _____.

cell formatting

Which of the following indicates the column and row where a cell is located?

cell reference

o format a range so that all values greater than $500 appear in red, which of the following can you use?

conditional formatting

If you see a cells with blue fills of varying lengths representing the values, the cells most likely have ________ applied to them.

data bars

A _______ refers to a collection of characters with a similar, specific design.

font

Which of the following settings can you change when you create a custom theme?

fonts

A _____ is a named operation that replaces the action of an arithmetic expression.

function

Which of the following is a built-in formula that helps you perform complex calculations?

function

Your worksheet contains confidential information in column C; to prevent others who use your worksheet from seeing the data, you can _____ column C.

hide

What does the third argument (3) refer to in the following formula: VLOOKUP(10005, A1:C6, 3, FALSE)?

number of the column containing the return value

You double-click the Format Painter button when you want to _____.

paste the same format multiple times

To rename a worksheet, you change the text on the _____.

sheet tab

A(n) _____ chart organizes hierarchies of data in a series of concentric rings and is appropriate for showing groups within categories of information.

sunburst

To paste a data range so that column data appears in rows and row data appears in columns, you can _____ the data using the Paste list arrow.

transpose

A _____ chart displays each category of data in a rectangle and smaller rectangles within them for subcategories.

treemap

Which type of chart would show increases in the value of a stock fund in green, decreases in gray, and the initial and final values in red?

waterfall

To move cell contents one space to the right, you can use the Increase Space button.

False

To paste a copied cell range, you only need to specify the upper right of the range where you want to paste it.

False

To preserve an exact cell address in a copied formula, you can use a relative cell reference.

False

To retrieve a return value from a vertical lookup table, you use the VTABLE function.

False

Use a sunburst chart to track the addition and subtraction of values in a sum.

False

When you align a range, you combine the cells into one cell.

False

When you copy cell contents, the data is removed from its original location and placed on the Clipboard.

False

When you print in portrait orientation, the page is wider than it is tall.

False

When you select a cell range, the cells must be adjacent to each other.

False

If you discover an error immediately after you have confirmed a cell entry, what of the following would you use next?

The Undo button on the Quick Access toolbar

Which of the following elements can you add to a chart to display a line representing the general direction in a data series?

Trendline

A constant is a term in an equation whose value does not change.

True

Niki wants to insert a Line sparkline in Excel. To do this, she can click on the cell she wants to insert a Sparkline, then click Insert tab, select Line Sparkline from the Sparklines group and then OK.

True

The easiest way to insert the SUM function is to use the AutoSum button on the Home tab.

True

To change a chart title, you click the chart title, type the new title, and then press ENTER.

True

A _____ chart tracks the addition and subtraction of values within a sum.

waterfall

To format text so that it automatically appears in multiple lines within a cell, you can transpose it.

False

To increase the magnification of a worksheet from 100% to 120%, you use the Magnification slider.

False

Jason wants to average the sales in the range C3:C50, and then round the result to the nearest integer. Which of the following formulas should he use?

=ROUND(AVERAGE(C3:C50),0)

In the formula =B6*$B$2, which of the following describes $B$2?

Absolute cell reference

Louis has entered a column of expense values. What feature can he use to insert the total expenses at the end of the column?

AutoSum button

Which of the following inserts a function into a cell?

AutoSum button

How can you distinguish between a manually added page break and an automatic page break in a worksheet?

Automatic page breaks appear as dashed lines while manual page breaks appear as solid lines.

To select nonadjacent cells or ranges on a worksheet, you can press and hold _____ while selecting each one.

CTRL

Which of the following keys do you press to copy cells while dragging them?

CTRL

What can you do to make data series easier to distinguish from one another?

Change the color of the data series.

A predesigned combination of formats, such as font size and color, is called a cell default.

False

Decreasing a cell's indent moves its contents to the right one space.

False

If you specify that you want worksheet gridlines to appear on the screen, they will automatically appear on the printout as well.

False

To align the upper-left corner of a chart with the upper-left corner of a cell, press and hold CTRL as you drag the chart.

False

You've copied a cell containing formula to the rows below it, and the results in the copied cells are all zeros. To find the problem, what should you check for in your original formula?

If it needs an absolute cell reference.

To preserve the original version of a workbook so you can make changes to a copy of it, which of the following would you do?

Open the workbook, make changes, and then save it using a different name.

Eleanor wants to repeat the first row of the worksheet on each printed page of a four-page worksheet. Which of the following options should she use in the Page Setup dialog box?

Print titles

Which of the following dialog boxes do you use to remove a data series from a chart?

Select Data Source dialog box

What must you do before removing a page break?

Select a cell below or to the right of the page break.

To save a workbook for the first time, you use the Save As dialog box.

True