242 Exam Multiple Choice
Calculates the arithmetic mean, or average, of values in a range.
AVERAGE function
Indicates a cell's specific location; the cell reference does not change when you copy the formula.
Absolute cell reference
The position of data between the cell margins.
Alignment
An input, such as a cell reference or value, needed to complete a function.
Argument
Enables you to copy the contents of a cell or cell range or to continue a sequence by dragging the fill handle over an adjacent cell or range of cells.
Auto Fill
Label that describes either the category axis or the value axis.
Axis Title
Chart type that compares categories of data horizontally.
Bar Chart
Tallies the number of cells in a range that contain values.
COUNT function
Provides descriptive labels for the data points plotted in a chart.
Category axis
The intersection of a column and row.
Cell
A container for the entire chart and all of its elements.
Chart Area
Label that describes the entire chart.
Chart title
Chart that groups columns side by side to compare data points among categories
Clustered Column Chart
The horizontal measurement of a column.
Column width
Chart that contains two chart types, such as column and line, to depict two types of data, such as individual data points and percentages.
Combo Chart
A set of rules that applies specific formatting to highlight or emphasize cells that meet specifications
Conditional Formating
A conditional format that displays horizontal gradient or solid fill indicating the cell's relative value compared to other selected cells.
Data Bar
An identifier that shows the exact value of a data point in a chart.
Data Label
Numeric value that describes a single value on a chart.
Data Point
Group of related data points that display in row(s) or column(s) in a worksheet.
Data Series
The background color appearing behind data in a cell.
Fill Color
A small green square at the bottom-right corner of a cell.
Fill Handle
The process of specifying conditions to display only those records that meet those conditions.
Filtering
A combination of cell references, operators, values, and/or functions used to perform a calculation.
Formula
Displays the content (text, value, date, or formula) in the active cell.
Formula Bar
Horizontal or vertical line that extends from the horizontal or vertical axis through the plot area.
Gridline
Evaluates a condition and returns one value if the condition is true and a different value if the condition is false.
IF function
A range of cells containing values for variables used in formulas.
Input Area
Key that identifies the color, gradient, picture, texture, or pattern fill assigned to each data series in a chart.
Legend
Chart type that shows trends over time in which the value axis indicates quantities and the horizontal axis indicates time.
Line Chart
An expression that evaluates to true or false.
Logical test
A range that contains data for the basis of the lookup and data to be retrieved.
Lookup table
Identifies the highest value in a range.
MAX function
Identifies the midpoint value in a set of values.
MEDIAN function
Displays the lowest value in a range.
MIN function
Contains both an absolute and a relative cell reference in a formula; the absolute part does not change but the relative part does when you copy the formula.
Mixed cell refernce
Displays the current date and time.
NOW function
Identifies the address of the current cell.
Name Box
Rules that control the sequence in which Excel performs arithmetic operations.
Order of Operations
A range of cells containing results based on manipulating the variables.
Output area
Calculates the periodic payment for a loan with a fixed interest rate and fixed term.
PMT function
Chart that shows each data point in proportion to the whole data series.
Pie Chart
Section of a chart that contains graphical representation of the values in a data series
Plot Area
A rectangular group of cells.
Range
A group of related fields representing one entity, such as data for one person, place, event, or concept.
Record
Indicates a cell's location from the cell containing the formula; the cell reference changes when the formula is copied.
Relative cell reference
The vertical measurement of a row
Row height
Calculates the total of values contained in one or more cells.
SUM function
Displays the name of a worksheet within a workbook.
Sheet Tab
A circle that enables you to adjust the height or width of a selected chart.
Sizing Handle
The process of listing records or text in a specific sequence, such as alphabetically by last name
Sorting
Miniature chart contained in a single cell.
Sparkline
A set of rules that governs the structure and components for properly entering a function.
Syntax
Displays the current date
TODAY function
Window of options to format and customize chart elements.
Task pane
Includes letters, numbers, symbols, and spaces.
Text
Looks up a value in a vertical lookup table and returns a related result from the lookup table.
VLOOKUP function
A number that represents a quantity or an amount.
Value
Displays incremental numbers to identify approximate values, such as dollars or units, of data points in a chart.
Value Axis
A file containing related worksheets
Workbook
A spreadsheet that contains formulas, functions, values, text, and visual aids.
Worksheet
Formatting that enables a label to appear on multiple lines within the current cell.
Wrap Text
Chart type that shows the relationship between two variables.
X Y (scatter) chart
Which of the following characters are wildcards in Excel? (Check all that apply.) (2 points) a. * b. # c. $ d. ?
a. * d. ?
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? (1 point) a. =$B5 b. =B3 c. =$B3 d. =$C5
a. =$B5
What is a fast way to apply several formats at one time? (1 point) a. Apply a cell style. b. Use Auto Fill. c. Click each one individually. d. Use Copy and Paste options.
a. Apply a cell style.
What is the keyboard shortcut to create an absolute reference? (1 point) a. F4 b. F2 c. Alt d. F3
a. F4
Which of the following is not an alignment option? (1 point) a. Fill Color b. Increase Indent c. Wrap Text d. Merge & Center
a. Fill Color
When you start to type =AV, what feature displays a list of functions and defined names? (1 point) a. Formula AutoComplete b. Function ScreenTip c. Function Arguments dialog box d. Insert Function dialog box
a. Formula AutoComplete
If cell E15 contains the formula =$C$5*J$15, what type of cell reference is the J$15 in the formula? (1 point) a. Mixed reference b. Relative reference c. Absolute reference d. Syntax
a. Mixed reference
Which type of chart is the least appropriate for depicting yearly rainfall totals for five cities for four years? (1 point) a. Pie chart b. Line chart c. Bar chart d. Column chart
a. Pie chart
Which of the following is not true about the VLOOKUP function? (1 point) a. The lookup table must be in descending order. b. The lookup table must be in ascending order. c. The default match type is approximate. d. The match type must be false when completing an exact match.
a. The lookup table must be in descending order.
Which of the following is not an aggregate function that can be applied in a total row? (1 point) a. VLOOKUP b. COUNT c. AVERAGE d. MAX
a. VLOOKUP
You just copied a range of data containing formulas. However, you want to preserve the formula results and the original number and text formatting in the pasted range. Which paste option would you select? (1 point) a. Values & Source Formatting b. Values & Number Formatting c. Keep Source Formatting d. Formulas
a. Values & Source Formatting
What function would most efficiently accomplish the same thing as =(B5+C5+D5+E5+F5)/5? (1 point) a. =SUM(B5:F5)/5 b. =AVERAGE(B5:F5) c. =COUNT(B5:F5) d. =MEDIAN(B5:F5)
b. =AVERAGE(B5:F5)
Which of the following is an unqualified structured reference? (1 point) a. =Sales[Purchase_Price]-Sales[Down_Payment] b. =[Purchase_Price]-[Down_Payment] c. =Purchase_Price-Down_Payment d. =[Sales]Purchase_Price-[Sales]Down_Payment
b. =[Purchase_Price]-[Down_Payment]
Look at the stacked bar chart in Figure 3.35. Which of the following is a category on the category axis? (1 point) a. 700 b. CIS Managers c. Job Titles d. Thousands
b. CIS Managers
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? (1 point) a. Click Paste in the Clipboard group on the Home tab. b. Click the Paste arrow in the Clipboard group and select Values & Source Formatting. c. Display the Paste Special dialog box and select Formulas & Number Formatting. d. Click the Paste arrow in the Clipboard group and select Formulas.
b. 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? (1 point) a.Commas to separate thousands b. Dollar sign immediately on the left side of the value c. Two decimal places d. Zero values displayed as hyphens
b. Dollar sign immediately on the left side of the value
Which step is not part of planning a worksheet design? (1 point) a. Decide what input values are needed. b. Enter labels, values, and formulas. c. Decide what outputs are needed to achieve the purpose. d. State the purpose of the worksheet.
b. Enter labels, values, and formulas.
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? (1 point) a. Select a smaller range as the print area. b. Increase the left and right margins. c. Decrease the Scale value. d. Decrease column widths if possible.
b. Increase the left and right margins.
Which statement is not a recommended guideline for designing and creating an Excel table? (1 point) a. Avoid naming two fields with the same name. b. Leave one blank row between records in the table. c. Include field names on the first row of the table. d. Ensure that no blank columns separate data columns within the table.
b. Leave one blank row between records in the table.
After you create a line type sparkline, what option should you select to display dots for each data point? (1 point) a. Negative Point b. Markers c. High Point d. Sparkline Color
b. Markers
What should you do to ensure that records in a table are unique? (1 point) a. Find the duplicate records and change some of the data to be different. b. Use the Remove Duplicates command. c. Look at each row yourself and manually delete duplicate records. d. Do nothing; a logical reason probably exists to keep identical records.
b. Use the Remove Duplicates command.
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 task? (1 point) a. Set a print area. b. Adjust page breaks. c. Change the print page order. d. Print titles.
c. Change the print page order.
What do you click to remove a data series from a chart so that you can focus on other data series? (1 point) a. Chart Styles b. Chart Series c. Chart Filters d. Chart Elements
c. Chart Filters
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? (1 point) a. Change the chart type to a clustered column chart. b. Click Legend in the Labels group on the Layout tab and select Show Legend at Bottom. c. Click Switch Row/Column in the Data group on the Design tab. d. Click Layout 2 in the Chart Layouts group on the Design tab and apply a different chart style.
c. Click Switch Row/Column in the Data group on the Design tab.
If you want to show exact values for a data series in a bar chart, which chart element should you display? (1 point) a. Value axis title b. Chart title c. Data labels d. Legend
c. Data labels
Which of the following does not display automatically when you create a clustered column chart? (1 point) a. Legend b. Chart title placeholder c. Data labels d. Gridlines
c. Data labels
What should you do if you see pound signs (###) instead of values or results of formulas? (1 point) a. Increase the zoom percentage. b. Adjust the row height. c. Increase the column width. d. Delete the column.
c. Increase the column width.
The function =PMT(C5,C7,-C3) is stored in cell C15. What must be stored in cell C5? (1 point) a. Loan amount b. APR c. Periodic interest rate d. Number of payment periods
c. Periodic interest rate
How can you display formulas within the cells instead of the cell results? (1 point) a.Press Ctrl+G. b. Press Ctrl+C. c. Press Ctrl+`. d. Click Cell References on the Home tab.
c. Press Ctrl+`
The value axis currently shows increments such as 50,000 and 100,000. What option would you select to display the values in increments of 50 and 100? (1 point) a. More Primary Vertical Axis Title Options b. Show Axis in Millions c. Show Axis in Thousands d. Show Right to Left Axis
c. Show Axis in Thousands
If you would like to set a conditional formatting rule based on the function =AND(G6="Finance", H7<7000), which formatting rule type is needed? (1 point) a. Format only values that are above or below average b. Format all cells based on their values c. Use a formula to determine which cells to format d. Format only cells that contain
c. Use a formula to determine which cells to format
Which of the following is not an argument of the IF function? (1 point) a. value_if_false b. value_if_true c. lookup_value d. logical_test
c. lookup_value
You want to create a single chart that shows the proportion of yearly sales for five divisions for each year for five years. Which type of chart can accommodate your needs? (1 point) a. Pie chart b. Surface chart c. Clustered bar chart d. 100% stacked column chart
d. 100% stacked column chart
Which of the following functions should be used to insert the current date and time in a cell? (1 point) a. =CURRENT() b. =TODAY() c. =DATE d. =NOW()
d. =NOW()
Which of the following is not a type of sparkline? (1 point) a. Line b. Win-Loss c. Column d. Bar
d. Bar
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? (1 point) a. New Window command and cascade the windows b. Hide Rows command c. Split Rows command d. Freeze Panes command
d. Freeze Panes command
Which Conditional Formatting rule is best suited to apply formatting to the top five values in a range of values? (1 point) a. Above Average b. Greater Than c. Between d. Top 10 Items
d. Top 10 Items
