Data Analysis

You want to create a single chart that shows each of five divisions' proportion of yearly sales for each year for five years. Which type of chart can accommodate your needs? Clustered bar chart 100% stacked column chart Surface chart Pie chart

100% stacked column chart

Given the formula =B1*B2+B3/B4^2 where B1 contains 3, B2 contains 4, B3 contains 32, and B4 contains 4, what is the result? 121 14 76 9216

14

Given that cells A1, A2, and A3 contain values 2, 3, and 10, respectively, and B6, C6, and D6 contain values 10, 20, and 30, respectively, what value will be returned by the function =IF(B6>A3,C6A1,D6A2)? 60 40 90 10

90

A formula containing the entry =$B3 is copied to a cell one column to the right and two rows down. How will the entry appear in its new location? =$B3 =$C5 =B3 =$B5

=$B5

What function would most appropriately accomplish the same thing as =(B5+C5+D5+E5+F5)/5? =AVERAGE(B5:F5) =SUM(B5:F5)/5 =COUNT(B5:F5) =MEDIAN(B5:F5)

=AVERAGE(B5:F5)

Which of the following is not an appropriate use of the SUM function? =SUM(F1:G10) =SUM(D15-C15) =SUM(A8:A15,D8:D15) =SUM(B3:B45)

=SUM(D15-C15)

Cell B10 contains a date, such as 1/1/2016. Which formula will determine how many days are between that date and the current date, given that the cell containing the formula is formatted with Number Format? =TODAY() =TODAY()+NOW() =TODAY()-B10 =CURRENT()-B10

=TODAY()-B10

What indicates that the closing price was higher than the opening price in a stock chart? A rectangle with a black fill color A positive data label above the data series for a particular day A double vertical line between the price markers A rectangle with a white fill color

A rectangle with a white fill color

Which of the following is a nonadjacent range? A1:A10, D1:D10 L15:L65 A1:Z99 C15:D30

A1:A10, D1:D10

Which check box in the Table Style Options group enables you to apply different formatting to the records in a table? Header Row Total Row Banded Columns Banded Rows

Banded Rows

Which date filter option enables you to specify criteria for selecting a range of dates, such as between 3/15/2016 and 7/15/2016? Before Equals Between All Dates in the Period

Between

You have a list of all the employees in your organization. The list contains employee name, office, title, and salary. You want to list all employees in each office branch. The branches should be listed alphabetically, with the employee earning the highest salary listed first in each office. Which is true of your sort order? Salary is the primary sort and should be from highest to lowest. Branch office is the primary sort and should be in A to Z order. Salary is the primary sort and should be from lowest to highest. Branch office is the primary sort and should be in Z to A order.

Branch office is the primary sort and should be in A to Z order.

You have a large dataset that will print on several pages. You want to ensure that related records print on the same page with column and row labels visible and that confidential information is not printed. You should apply all of the following page setup options except which one to accomplish this? Print titles. Set a print area. Change the print page order. Adjust page breaks.

Change the print page order.

Which icon does not display to the right of a selected chart? Chart Quick Analysis Chart Elements Chart Styles Chart Filters

Chart Quick Analysis

If cell D15 contains the formula =$C$5*D$15, what is the D15 in the formula? Relative reference Circular reference Absolute reference Range name

Circular reference

Currently, a column chart shows values on the value axis, years on the category axis, and state names in the legend. What should you do if you want to organize data with the states on the category axis and the years shown in the legend? Click Layout 2 in the Chart Layouts group on the Design tab and apply a different chart style. Click Legend in the Labels group on the Layout tab and select Show Legend at Bottom. Click Switch Row/Column in the Data group on the Design tab. Change the chart type to a clustered column chart.

Click Switch Row/Column in the Data group on the Design tab.

You selected and copied worksheet data containing formulas. However, you want the pasted copy to contain the current formula results rather than formulas. What do you do? Display the Paste Special dialog box and select Formulas and number formats. Click the Paste arrow in the Clipboard group and select Formulas. Click the Paste arrow in the Clipboard group and select Values & Source Formatting. Click Paste in the Clipboard group on the Home tab.

Click the Paste arrow in the Clipboard group and select Values & Source Formatting.

Which of the following characteristics is not applicable to the Accounting Number Format? Zero values displayed as hyphens Dollar sign immediately on the left side of the value Commas to separate thousands Two decimal places

Dollar sign immediately on the left side of the value

What type of conditional formatting displays horizontal colors in which the width of the bar indicates relative size compared to other values in the selected range? Data Bars Color Scales Icon Sets Sparklines

Data Bars

If you want to show exact values for a data series in a bar chart, which chart element should you display? Data labels Value axis title Legend Chart title

Data labels

Which tab contains commands to apply a predefined chart layout that controls which elements are included, where, and their color scheme? Design Layout Page Layout Format

Design

When you select the ______ rule type, the New Formatting Rule dialog box does not show the Format button. Use a formula to determine which cells to format Format only cells that contain Format all cells based on their values Format only unique or duplicate values

Format all cells based on their values

When you start =AV, what displays a list of functions and defined names? Function Arguments dialog box Function ScreenTip Insert Function dialog box Formula AutoComplete

Formula AutoComplete

You are working with a large worksheet. Your row headings are in column A. Which command(s) should be used to see the row headings and the distant information in columns X, Y, and Z? Hide Rows command Freeze Panes command New Window command and cascade the windows Split Rows command

Freeze Panes command

Which of the following is not an acceptable range name? Goal for 2016 Test_Weight FICA Target_2015

Goal for 2016

What should you do if you see a column of pound signs (###) instead of values or results of formulas? Delete the column. Adjust the row height. Increase the zoom percentage. Increase the column width.

Increase the column width

Assume that the data on a worksheet consume a whole printed page and a couple of columns on a second page. You can do all of the following except what to force the data to print all on one page? Increase the left and right margins. Decrease column widths if possible. Decrease the Scale value. Select a smaller range as the print area.

Increase the left and right margins

Which statement is not a recommended guideline for designing and creating an Excel table? Include field names on the first row of the table. Ensure no blank columns separate data columns within the table. Leave one blank row between records in the table. Avoid naming two fields with the same name

Leave one blank row between records in the table.

If you want to balance a title over several columns, what do you do? Click Center to center the title horizontally over several columns. Enter the data in the cell that is about midway across the spreadsheet. Use the Increase Indent command until the title looks balanced. Merge and center the data over all columns.

Merge and center the data over all columns.

You want to display a total row that identifies the oldest date in a field in your table. What function do you select from the list? Min Count Sum Max

Min

What Excel interface item displays the address of the current cell? Status bar Formula Bar Quick Access Toolbar Name Box

Name Box

The function =PMT(C5,C7,-C3) is stored in cell C15. What must be stored in cell C5? Periodic interest rate Loan amount Number of payment periods APR

Periodic interest rate

Which type of chart is the least appropriate for depicting yearly rainfall totals for five cities for four years? Pie chart Line chart Column chart Bar chart

Pie Chart

Given the function =VLOOKUP(C6,$D$12:$F$18,3), the entries in: The third column of the lookup table must be text only. Range D12:D18 are in ascending order. Range D12:D18 are in descending order. Range D12:D18 contain multiple values in each cell.

Range D12:D18 contain multiple values in each cell.

What is the typical sequence for creating a chart? Click the cell to contain the chart, select the chart type, and then select the data source. Select the chart type, select the data source, and then size and position the chart. Select the data source, select the chart type, and then size and position the chart. Select the data source, size the chart, select the chart type, and then position the chart.

Select the data source, select the chart type, and then size and position the chart.

The value axis currently shows increments such as 50,000 and 100,000. What do you select to display increments of 50 and 100? Show Right to Left Axis Show Axis in Millions More Primary Vertical Axis Title Options Show Axis in Thousands

Show Axis in Thousands

Which of the following applies to a sparkline? Chart title Legend Multiple data series Single-cell chart

Single-cell chart

What is the first step in planning an effective worksheet? Identify the input and output areas. State the purpose of the worksheet. Decide how to format the worksheet data. Enter labels, values, and formulas.

State the purpose of the worksheet.

Why would you press Ctrl+` in Excel? To display the print options To enable the AutoComplete feature To undo a mistake you made To display cell formulas

To display cell formulas

You suspect a table has several identical records. What should you do? Find the duplicate records and change some of the data to be different. Use the Remove Duplicates command. Do nothing; a logical reason probably exists to keep identical records. Look at each row yourself and manually delete duplicate records.

Use the Remove Duplicates command.