Business in Excel
If the VLOOKUP function cannot find a match in the lookup table, what does it return? "Match not found" message #N/A error value FALSE #NAME error value
#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? A5 $A$5 $A5 A$5
$A$5
The IF function can use which of the following comparison operators? Select all the options that apply. = (equal) > (greater than) * (multiplication) <> (not equal)
= (equal) > (greater than) * (multiplication) <> (not equal)
What is the correct formula to insert the date January 12, 1998 into a cell? =DATE(1998,1,12) =DATE(1998,12,1) =DATE(12-1-1998) =DATE(1/12/1998)
=DATE(1998,1,12)
Which formula can you use to extract the month number from the date entered in cell F5 as July 8, 2016? =MONTH(F5) =MONTH("F5") =MONTH(DATE(2016,7,0)) =MONTH(DAY(2016,7,0))
=MONTH(DATE(2016,7,0))
Which of the following formulas most efficiently adds the values from cells B6, B7, B8, B9, and B10? =TOTAL(B6+B7+B8+B9+B10) =TOTAL(B6-B10) =SUM(B6:B10) =SUM(B6-B10)
=SUM(B6:B10)
Which of the following functions would you use to calculate the arithmetic mean of a price list? MAX COUNT SUM AVERAGE
AVERAGE
To use defined names in existing formulas, click the Define Name arrow, and then click _____. Get Defined Names Apply Names Use Defined Names Named Ranges
Apply Names
Which of the following lets you extend any data pattern involving dates, times, numbers, or text? AutoSum list AutoFill Options button Paste Options button Quick Analysis
AutoFill Options button
When you double-click the right border of a column heading, which of the following occurs? Excel hides the column to the left of the double-clicked border. AutoFit resizes the column to the default width of 8.43 characters. Excel adds a column to the right of the double-clicked border. AutoFit resizes the column to accommodate the widest cell entry.
AutoFit resizes the column to accommodate the widest cell entry.
Which of the following keys do you press to copy selected cells while you drag and drop the selected cells to their new location? ALT SHIFT TAB CTRL
CTRL
After beginning a formula, what can you do instead of typing the syntax of a 3-D reference? Click a sheet tab, click a cell range, and then press ENTER. Copy a sheet tab and then press CTRL+V to paste it in the formula. Use the fill handle to select the external range. Click the Enter button on the formula bar, click a sheet tab, and then click a cell range.
Click a sheet tab, click a cell range, and then press ENTER.
When Valerie used AutoFill to copy a formula, Excel copied the format and removed shading from some cells. What can she do to prevent the formatting change? Click the AutoFill Options button and then click Fill Without Formatting. Click the AutoFill Options button and then click Fill Formatting Only. Click the Paste Options button and then click Undo. Click the Quick Analysis button and then click Fill Without Formatting.
Click the AutoFill Options button and then click Fill Without Formatting.
To select a single worksheet cell so you can work with it, which of the following would you do? Click the cell. Move the cell pointer over the cell. Click the status bar. Click the Name box.
Click the cell.
Which of the following methods will save an existing workbook to your OneDrive account? Select all the options that apply. Click the file name in the title bar and then click Upload. Use the Save As dialog box to select a OneDrive folder as the file location. Click the File tab, then select Save As to select a OneDrive folder as the file location. Click the File tab, then select Save to select a OneDrive folder as the file location.
Click the file name in the title bar and then click Upload. Use the Save As dialog box to select a OneDrive folder as the file location.
Ricki has an Analysis workbook containing many worksheets of sales data that she wants to use in a new workbook. What is the easiest way for her to use the sales data in the new workbook? Copy the cells containing sales data to the new workbook. Insert hyperlinks from the Analysis workbook to the new workbook. Copy the worksheets from the Analysis workbook to the new workbook. Hide the worksheets in the Analysis workbook.
Copy the worksheets from the Analysis workbook to the new workbook.
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? Apply a different theme. Create a custom cell style. Transpose the Title style. Apply conditional formats.
Create a custom cell style.
Amir wants an exploded pie chart. Which of the following should he do? Click the Explode Pie button Drag the pie slice(s) away from the pie. Right-click on the slice(s) of pie and select Explode Piece. There is no way he can explode the pie chart.
Drag the pie slice(s) away from the pie.
What types of resources can you access using a hyperlink in Excel? Select all the options that apply. Email address Current cell Worksheet in the workbook Webpage
Email address Worksheet in the workbook
What can you do to a worksheet group to change each worksheet within the group? Select all the options that apply. Enter formulas and data. Change row heights and column widths. Apply conditional formats. Set view options.
Enter formulas and data. Change row heights and column widths. Apply conditional formats. Set view options.
Decreasing a cell's indent moves its contents to the right one space. True False
False
In formulas, calculations in square brackets are calculated first. True False
False
To change the background color of a cell, you use the Background color button. True False
False
To paste a copied cell range, you only need to specify the upper right cell of the range where you want to paste it. True False
False
Use a sunburst chart to track the addition and subtraction of values in a sum. True False
False
When you change cell contents to italics, you are changing the cell's alignment. True False
False
When you click a cell to insert a cell reference in a formula, you must type a number to lock the cell reference in the formula. True False
False
Which Excel features can you use to extend a formula into a range with AutoFill? Select all the options that apply. Fill handle Fill button in the Editing group Quick Analysis tool AutoSum button in the Editing group
Fill handle Fill button in the Editing group Quick Analysis tool AutoSum button in the Editing group
To format a cell range so that values between 100 and 500 appear in red, which of the following can you use? icon sets themes Top/Bottom Rules Highlight Cells Rules
Highlight Cells Rules
Which of the following conditional formatting options can you use to highlight the top 10 values in a range of cells? Icon Sets Color Scales Data Bars Highlight Cells Rules
Highlight Cells Rules
Which of the following lets you apply bold formatting to a selected cell? Select all the options that apply. Home tab Format Cells dialog box Insert tab Mini toolbar
Home tab Format Cells dialog box Insert tab Mini toolbar
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. If it needs a relative cell reference. If it needs landscape orientation. If it needs a function.
If it needs an absolute cell reference.
Which of the following Excel tools displays the syntax for a selected function? Formula bar Insert Function dialog box Insert dialog box Function Library
Insert Function dialog box
When you create a custom combination chart, which of the following chart types can you use for the primary and secondary axis? Select all the options that apply. Line Clustered Column Treemap Waterfall
Line Clustered Column
Annemarie lists 12 months of product sales data in the range A3:M7. The products are listed in the range A3:A7 and the monthly sales data in the range B3:M7. She wants to display a simple chart at the end of each row in column N to track the monthly sales for each product. What can she insert in the range N3:N7? Line chart Line sparklines Scatter chart Data bars
Line sparklines
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? MIN MEDIAN INT MAX
MAX
To combine multiple cells into one combined cell and center the contents, which of the following do you use? Column Width command Center button Merge and Center button Increase Indent button
Merge and Center button
Evelyn wants to work on different parts of a workbook at the same time by displaying worksheets in separate windows. Which option on the View tab should she select? Arrange All New Window Zoom Page Break Preview
New Window
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. On the Page Layout tab, set the Width and Height to Automatic and the Scale to 100%. On the Page tab of the Page Setup dialog box, change the Orientation setting. On the Margins tab of the Page Setup dialog box, click the Preset button.
On the Margins tab of the Page Setup dialog box, change the number in the Left box.
To which of the following chart types can you not add axis titles? Pie chart Line chart Area chart Column chart
Pie chart
Which of the following changes can you apply to a chart legend? Select all the options that apply. Position Data Series colors Fill color Number formatting
Position Data Series colors Fill color Number formatting
Which of the following methods copies an existing worksheet within the same workbook? Drag its sheet tab to a new location. Press and hold CTRL and drag its sheet tab to a new location. Press and hold SHIFT and drag its sheet tab to a new location. Press and hold ALT and drag its sheet tab to a new location.
Press and hold CTRL and drag its sheet tab to a new location.
Which of the following functions generate random values? Select all the options that apply. RAND RANDDOWN RANDUP RANDBETWEEN
RAND RANDDOWN RANDUP RANDBETWEEN
Which of the following functions does Excel provide for rounding values? Select all the options that apply. ROUNDIF ROUND ROUNDDOWN ROUNDUP
ROUNDIF ROUND ROUNDDOWN ROUNDUP
Which of the following statements is true about COUNT functions? The COUNT function returns the number of calls in a range that are not blank. The COUNT function returns the number of calls in a range that contain any data at all. Using the COUNT function is useful for computing the average of a cell range. The COUNT function returns the number of cells in a range that contain numeric data.
The COUNT function returns the number of cells in a range that contain numeric data.
Which of the following can you change in a combination chart? Select all the options that apply. The bounds of the secondary axis The major tick marks on the primary axis The minor tick marks on all axes The orientation of the axis titles
The bounds of the secondary axis The major tick marks on the primary axis The orientation of the axis titles
How does Excel indicate that worksheets are grouped? Select all the options that apply. The word "Group" appears in large letters in the background of each worksheet. The word "Group" is added to the title bar. The sheet tab names are bold. The word "Group" is added to the sheet tab names.
The word "Group" appears in large letters in the background of each worksheet. The word "Group" is added to the title bar. The sheet tab names are bold. The word "Group" is added to the sheet tab names.
When you use the Arrange All button to arrange more than one workbook window, which of the following layout options can you use? Select all the options that apply. Tiled Cascade Scrolling Vertical
Tiled Cascade Scrolling Vertical
Kamala enters a date as the start date in cell E2 and a list of holidays in cells F2:F5. Now she wants to add 30 workdays to the start date. She can do this using the following formula: = WORKDAY (E2, 30, F2:F5). True False
True
You can insert a formula by selecting a range of values, clicking the Quick Analysis button, and then clicking Tables. True False
True
Which of the following tasks can you perform using the Edit Links dialog box? Select all the options that apply. Update each link. Change a link's data source. Change the formula containing the external reference. Break a link.
Update each link. Change a link's data source. Change the formula containing the external reference. Break a link.
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 Data Validation Consolidate Relationships
What-if Analysis
Which of the following are true about entering a function using the Insert Function dialog box? Select all the options that apply. You open the dialog box by typing "function" in the formula bar. You can use the search tool in the Insert Function dialog box to find a function. You can select a category to explore functions by type. You can use the Insert Function dialog box to insert a function based on nearby formulas.
You open the dialog box by typing "function" in the formula bar. You can use the search tool in the Insert Function dialog box to find a function. You can select a category to explore functions by type. You can use the Insert Function dialog box to insert a function based on nearby formulas.
What happens when you click the sheet tab of a worksheet not included in a worksheet group? You add the worksheet to the group. You create a new group of the first and last worksheets. You ungroup the worksheets. The worksheet grouping does not change.
You ungroup the worksheets.
Which of the following are external references? Select all the options that apply. [Sales.xlsx]January!A14 '[Annual Sales.xlsx]January'!A14 January!A14 [Sales.xlsx]Jan:March!A14
[Sales.xlsx]January!A14 '[Annual Sales.xlsx]January'!A14 January!A14 [Sales.xlsx]Jan:March!A14
You need to add another product to your inventory worksheet, which shows product names in row A. Which of the following should you add? a row a column a button conditional formatting
a column
When you underline cell content, you are using _____. a font style an orientation setting an indent setting a centering style
a font style
When you insert a defined name into a formula, Excel treats the defined name as a(n) _____. relative cell reference mixed cell reference static cell reference absolute cell reference
absolute cell reference
Which of the following is the temporary storage area that holds selections you copy or cut? Clipboard Backstage Name box Worksheet
clipboard
If you see cells with blue fills of varying lengths representing the values, the cells most likely have ________ applied to them. themes icon sets data bars cell styles
data bars
A Line sparkline displays a green block for increasing values and a red block for declining values. True False
false
A named range, also called a descriptive range, uses descriptive names instead of cell or range references. True False
false
A 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. True False
false
To add a worksheet to a workbook, you would use the New sheet tab below the worksheet. True False
false
To find the largest value in a cell range, use the MIN function. True False
false
When Micah opens a workbook containing external references, a dialog box appears indicating the workbook contains links to external sources. Micah trusts the linked data, so he can click the Trust button to update the data. True False
false
A _______ refers to a collection of characters with a similar, specific design. symbol font point keyword
font
To change the color of cell content, you use the ________ color list arrow. Cell Text Pattern Font
font
Which of the following settings can you change when you create a custom theme? column widths print titles sort orders fonts
fonts
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? Move the worksheet to the end of the workbook. Display the worksheet in Privacy view. Hide the worksheet. Rename the worksheet.
hide the worksheet
Ttext or an image you click to open a webpage or file is which of the following? 3-D reference Hyperlink ScreenTip Template
hyperlink
Which of the following are ways to align cell contents in relation to cell edges? Select all the options that apply. left center bold underline
left center bold underline
You can add data markers to sparklines to highlight which of the following values? Select all the options that apply. low values high values ending values negative values
low values high values ending values negative values
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 location of the lookup table cell with the lookup value whether to return a value only when an exact match is found
number of the column containing the return value
You double-click the Format Painter button when you want to _____. copy a cell's data to another cell paste the same format multiple times paste conditional formatting only clear a cell's formatting
paste the same format multiple times
If you want to print only part of a worksheet, you can set a _____. print area print title page break scaling option
print area
Which of the following chart types show how two numeric data series are related to each other? Pie Bar Line Scatter
scatter
You can use the Name Manager dialog box to view and manage named ranges, including the _____, which indicates where the named range is recognized. scope external reference filter extent
scope
Which of the following helps you move different parts of a worksheet into view when the worksheet is too large to fit on the screen at once? sheet tabs arguments scroll bars operators
scroll bars
When you arrange windows in a Vertical or Side by Side layout, you can more easily compare the two worksheets by using synchronized _____ . grouping updating inking scrolling
scrolling
Which of the following type of data can you wrap within a cell? text numbers dates times
text
A combination chart combines two chart types and can plot data on primary and secondary axes. True False
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 False
true
Before inserting sparklines into cells, make sure the cells are blank because sparklines replace cell contents. True False
true
Column and line sparklines show a general trend without a legend, title, or gridlines. True False
true
Currency is an example of a number format. True False
true
Lucinda wants to apply conditional formats to three worksheets that have the same structure. She can apply the conditional formats to the three worksheets at the same time by grouping the worksheets. True False
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 False
true
Prasad 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 False
true
To change a chart title, you click the chart title, type the new title, and then press ENTER. True False
true
To preview and print a workbook, click the File tab to display Backstage view. True False
true
Using Excel formula to find the average of the numbers 15, 20, 25, 23 and 32, you would enter =AVERAGE(15, 20, 25, 23, 32) in a cell. True False
true
When you insert a worksheet row, a row is inserted above the cell pointer and the sheet contents move downward. True False
true
When you use the Windows Start menu to start Excel, it opens in Backstage view. True False
true
Workbook styles are predesigned workbooks that contain formulas and design elements. True False
true
You can use the DELETE key to clear cell contents. True False
true
You use conditional formatting to format a cell based on its value. True False
true
Which of the following options can you set when you create or edit a data bar conditional formatting rule? Select all the options that apply. value used for the longest data bar fill color of the data bars how to display negative values font color of the text
value used for the longest data bar fill color of the data bars how to display negative values font color of the text
A _____ chart tracks the addition and subtraction of values within a sum. waterfall histogram scatter combo
waterfall
The Percent style formats numbers as percentages with _____ decimal places by default. one two three zero
zero
