Unit 4 Excel Exam

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 are functions you can use to calculate measures of central tendency? Select all the options that apply.

-AVERAGE -MEDIAN

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 -Mini toolbar

Which of the following statements describe a histogram chart? Select all the options that apply.

-It is a column chart showing the distribution of values from a single data series. -Use it to display distribution of scores on an exam., -Data values are allocated to bins.

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

To paste copied cell contents with values only, not including formatting, which of the following can you use? Select all the options that apply .

-Paste Special -Paste Options button

Which of the following are true about entering a function using the Insert Function dialog box? Select all the options that apply.

-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.

The IF function can use which of the following comparison operators? Select all the options that apply.

= (equal) > (greater than) <> (not equal)

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")

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)

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)

When the title slide is the current slide, what happens when you click the New Slide button?

A new slide with the Title and Content layout is added after the title slide.

When a word is marked as misspelled, how does it look different from correctly spelled words?

A wavy, red line appears underneath it.

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

AVERAGE

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

Absolute cell reference

Which of the following describe ways you can align a paragraph in a text box or placeholder? Select all the options that apply.

All of them

You can add data marks to sparklines to highlight which of the following values? Select all the options that apply.

All the above

Which of the following lets you extend any data pattern involving dates, times, numbers, or text?

AutoFill Options 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.

If you type the wrong character, which key can you press to remove the incorrect character?

BACKSPACE

Alba wants to use a watermark image as the background of her worksheet. She goes to the Page Setup group on the Page Layout tab. What option should she click to begin choosing the image file to use as a watermark?

Background

If a word is marked as misspelled, which of the following changes the selected word to the correct spelling?

Change

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

Change the color of the data series.

If you want to change the direction of a transition, which of the following should you do?

Change the direction using the Effect Options button.

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.

If selected text is bold, how do you change it so that it is no longer bold?

Click the Bold button on the Home tab on the ribbon.

How do you change the layout of a slide?

Click the Layout button on the Home tab, and then click the layout you want to use.

If you want to rotate a selected shape by a specific number of degrees, which of the following is the best option?

Click the Rotate button on the Drawing Tools Format tab, and then select one of the Rotate commands.

Which of the following actions would change the format of selected text or a selected text box?

Click the Underline button on the Home tab.

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.

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

Which tab on the ribbon contains the themes?

Design

Which of the following do you change if you want to change the speed of a transition?

Duration

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

False

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

False

A treemap chart is a type of waterfall chart.

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

Because of their compact size, you cannot apply a style to a sparkline.

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

Clicking the Increase Decimal button in the Number group would change the displayed cell value 14.54 to 14.5.

False

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

False

If you add a footer to all of the slides including the title slide, you can add slide numbers to all of the slides except the title slide.

False

If you click the Slide Show button on the status bar, the presentation will appear in Slide Show view with Slide 1 displayed no matter which slide is currently selected.

False

If you don't like the speed of a transition that you applied, you cannot change it.

False

If you want to paste values and number formatting only, click the Paste Numbers button that appears below the pasted cell.

False

In Slide Show view, you can see the next slide, speaker notes, and a timer, while your audience sees the current slide fill the screen on another monitor.

False

It's impossible to resize a shape to exact dimensions.

False

One way you can rotate a shape is to drag its adjustment handle.

False

Stuart inserts the formula '=YEAR ('23-Aug-2012)' into cell B12. When he presses ENTER, 23/08/2012 will appear in B12.

False

Text that you delete by pressing DELETE or BACKSPACE is placed on the Clipboard.

False

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

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

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

False

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

False

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

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

To save a copy of an open presentation with a new name, you use the Copy As command.

False

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

False

When you change cell contents to italics, you are changing the cell's alignment.

False

You can format an object even when it is not selected.

False

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

False

You cannot change a shape to a different shape.

False

You cannot change the levels of items in a list in a content placeholder.

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

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

If you want a specific date to appear every time you open a presentation rather than updating to the current date, which of the following options do you need to select when you add the date to the slides?

Fixed

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

Font

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

Font

Which of the following contains text you type and that appears on all the slides in the presentation?

Footer

To copy a cell's formatting to another cell, which of the following can you use?

Format Painter

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

Which of the following conditional formatting options can you use to highlight the top 10 values in a range of cells?

Highlight Cells Rules

To apply a cell style, you would use the Cell Styles command on the _____ tab.

Home

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

Which of the following is a type of logical function?

IF

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.

Which of the following can you do to make a chart title stand out on a chart?

Increase the font size.

Which of the following Excel tools displays the syntax for a selected function?

Insert Function dialog box

Which formatting command changes text so it is slanted?

Italic

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 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?

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

Which view do you choose if you want to add a picture to a slide?

Normal

Which view is the best view to use if you want to add a picture to a slide?

Normal

If you want to add a note to a slide to remind you to mention a fact that is related to the slide, where do you place the note?

Notes pane

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.

If you want to change the color of the border around a shape, which of the following do you change?

Outline

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

You can edit a worksheet footer in _____.

Page Layout view

Igor wants to set 1-inch margins on the left and right and 0.5-inch margins on the top and bottom of a printed page. Which of the following dialog boxes should he use to change the margins?

Page Setup dialog box

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

Pie chart

Which of the following changes can you apply to a chart legend? Select all the options that apply.

Position Fill color

When you have a second monitor (such as a projector) attached to your computer, which view provides you with additional tools, such as a timer, to help you as you give your presentation?

Presenter view

Which of the following methods causes the next slide to be displayed during a slide show? Select all the options that apply. (Slideshow will immediately advance to the next slide.)

Press SPACEBAR. AND Click the left mouse button.

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

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

Quick Analysis tool

Which of the following functions generate random values? Select all the options that apply.

RAND RANDBETWEEN

Which of the following functions does Excel provide for rounding values? Select all the options that apply.

ROUNDUP ROUNDDOWN ROUND

If you want to change all occurrences of "Radio" to "TV" in a worksheet, you can use the _____ command .

Replace

To select more than one object at a time on a slide, which key do you press while you click to select the objects?

SHIFT

Which of the following chart types show how two numeric data series are related to each other?

Scatter

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.

If you want to change the fill color of a shape, what must you do first?

Select the shape.

If you want to format one word in a text box as bold, what do you need to do before you click the Bold button on the Home tab?

Select the word.

Which button on the Drawing Tools Format tab would you click to change the color, weight, or style of the border of a shape?

Shape Outline

What are the small squares that appear in the corners and in the middle of the sides of a selected object's border called?

Sizing handles

Which of the following views does not let you change the order of slides in a presentation?

Slide Show

Which view displays each slide so it fills the entire screen?

Slide Show

If you want to see many slide thumbnails on the screen at the same time, which view should you use?

Slide Sorter

Which view do you choose if you want to see a thumbnail of each slide in a presentation arranged in a grid?

Slide Sorter

To make your presentation more interesting, you should consider converting lists to which of the following?

SmartArt graphic

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

SmartArt consists of the graphic and which of the following?

Text pane

Which of the following can you fill a shape with? Select all options that apply.

Texture, Solid color, picture

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 orientation of the axis titles

If you have already named and saved your presentation at least once, what happens when you click the Save button on the Quick Access Toolbar?

The changes you made since the last time you saved the file are saved.

If a slide contains a content placeholder, what happens to the content placeholder if you insert a photo using the Pictures button on the Insert tab?

The content placeholder is replaced by the picture.

If you use the Pictures button in a content placeholder to insert a picture on a slide, and the picture that you insert is smaller than the content placeholder, what happens after you insert it?

The picture replaces the content placeholder, but the picture does not change size to match the size of the content placeholder.

Which slide usually does not require the slide number, date, and footer text on it?

Title slide

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 combination chart combines two chart types and can plot data on primary and secondary axes.

True

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

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

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

Before inserting sparklines into cells, make sure the cells are blank because sparklines replace cell contents.

True

Binay wants to create an X-Y scatter plot. To do so, he should select the data he wants to plot in the scatter chart, then click the Insert tab, and then click Insert Scatter (X, Y) or Bubble Chart.

True

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

True

If a word is marked as misspelled, you can right-click it to open a menu of suggested spellings, and then click the correct spelling on the menu.

True

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

True

In order to enter text in a shape, you must select it first.

True

In order to format all the text in a text box at the same time, you must select the text box first.

True

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

Most transitions have an effect that you can modify, such as the direction of the transition.

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

One way to insert a picture on a slide is to click the Pictures button in a content placeholder.

True

One way to make a list on a slide more interesting is to convert the list to a SmartArt graphic.

True

Rohan can display the current date in a cell using the TODAY() function.

True

Text entered in the Footer box on the Slide tab in the Header and Footer text box can appear anywhere on the slides, not just at the bottom of the slides.

True

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

True

To change several format attributes of a shape at the same time, you can apply a shape style.

True

To change the color of a shape, you change the shape fill.

True

To display another slide in Normal view, you can click its thumbnail in the pane on the left.

True

To insert information that appears at the bottom of every worksheet page, you can use a command on the Insert tab.

True

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

True

To reverse the last change you made in a presentation file, you click the Undo button on the Quick Access Toolbar.

True

To rotate cell contents, you use the Orientation button in the Alignment group on the Home tab.

True

When a presentation includes slide numbers on all of the slides, it is often appropriate to prevent the slide number from appearing on the title slide.

True

You can add data markers to sparklines to indicate the high and low points in the chart.

True

You can change the theme after you create a presentation.

True

You can choose a layout before you insert a slide.

True

You must select the text or the text box before you change the font.

True

If you want to change the thickness of a shape outline, which of the following do you need to change?

Weight

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

Win/Loss

When you underline cell content, you are using _____.

a font style

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

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

data bars

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

fonts

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

What unit is text size measure in?

points

If you want to print only part of a worksheet, you can set a _____.

print area

After you enter text in a placeholder, what does the placeholder become?

text box

To move a slide in Normal view, what do you drag in the pane on the left?

the slide thumbnail

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 tracks the addition and subtraction of values within a sum.

waterfall

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