Com Exam 1
The last possible column in a worksheet
XFD
What steps should you take to ensure that column labels display on each printed page of a large dataset?
You can select the Rows to repeat at top option in the Print titles section on the Sheet tab in the Page Setup dialog box.
What type of data do you enter in a Function Arguments dialog box, and what are four things the dialog box tells you?
You enter the required and optional arguments for a particular function. The dialog box tells you the values stored in the respective arguments, the function results, a description of the active argument, a link to get help on the function, and a description of the function itself.
Why would you use the Paste Special options in Excel?
You might want the pasted data to be in a different format than the default paste. For example, you might want to paste formulas or values only.
In the task pane, what does a diagonal black triangle next to a chart category indicate? a)All the category's options are displayed b)None of the category's options are displayed c)There are no options for this category d)Every option for this category have already been chosen
a
Which of the following characteristics is not applicable to the Accounting Number Format? a)Dollar sign immediately on the left side of the value b)Commas to separate thousands c)Two decimal places d)Zero values displayed as hyphens
a
Which of the following is an unqualified structured reference? a)=[Purchase_Price] - [Down_Payment] b)=Sales[Purchase_Price] - Sales[Down_Payment] c)=Purchase_Price - Down_Payment d)=[Sales]Purchase_Price - [Sales] Down_Payment
a
Which of the following characters are wildcards in Excel? (Check all that apply.) a)* b)# c)? d)$
a, c
If you wanted to reference a cell in a formula and you did not want that cell to change when copying and pasting you would use what kind of reference?
absolute
The position of data between the cell margins.
alignment
Similar to a line chart
area
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
How can you display formulas within the cells instead of the cell results? a)Press Ctrl + G b) Press Ctrl + ` c)Click Cell References on the Home tab d)Press Ctrl + C
b
What chart type is preferable when the category names are long? a)Column b)Bar c)Pie
b
What is a fast way to apply several formats at one time? a)Click each one individually. b)Apply a cell style. c) Use Auto Fill. d)Use Copy and Paste options.
b
What keyboard shortcut will automatically update any formulas using =TODAY() or =NOW()? a) F8 b) F9 C)F10 d)F11
b
Which of the following is not a type of sparkline? a)Line b)Bar c)Column d)Win-Loss
b
Which of the following is not true about the VLOOKUP function? a)The lookup table must be in ascending order. b)The lookup table must be in descending order. c)The default match type is approximate. d)The match type must be false when completing an exact match.
b
Lowest value in a series
breakpoint
By how many levels will Excel allow you to sort? a)16 b)32 c)64 d)128
c
If cell E15 contains the formula =$C$5*J$15, what type of cell reference is the J$15 in the formula? a)Relative reference b)Absolute reference c)Mixed reference d)Syntax
c
What is the default data table style? a)Normal b)Medium 1 c)Medium 2 d)None
c
What is the keyboard shortcut to create an absolute reference? a) F2 b)F3 c)F4 d)Alt
c
Where does Excel automatically display statistics like count, average, and sum when a range of values is selected? a)Formula bar b)Name box c)Status bar d)Statistics bar
c
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? a)Formulas b)Keep Source Formatting c)Values & Source Formatting d)Values & Number Formatting
c
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? a)Click Paste in the Clipboard group on Home tab. b)Click the Paste arrow in the Clipboard group and select Formulas. c)Click the Paste arrow in the Clipboard group and select Values & Source Formatting. d)Display the Paste Special dialog box and select e) Formulas & Number Formatting.
c
The intersection of a column and a row.
cell
What can you not do from the Select Data Source dialog box?
change chart type
Contains no spreadsheet data
chart sheet
What punctuation is used to separate the two cell references in a range?
colon(:)
A conditional format that displays a particular color based on the relative value of the cell contents to the other selected cells.
color scale
Which chart type displays the data vertically?
column
Column that contains the return values
column index number
The horizontal measurement of a column.
column width
Which chart type is best when you want to chart two unrelated chart types?
combo chart
If you want to show exact values for a data series in a bar chart, which chart element should you display? a)Chart title b)Legend c)Value axis title d)Data labels
d
Including what, distorts the effectiveness of charting data points? a)Too many series b)Too many data points c)Too much variance in the values of the data d)Aggregates
d
Which of the following is not an aggregate function that can be applied in a total row? a)MAX b)AVERAGE c)COUNT d)VLOOKUP
d
Which of the following is not an argument of the IF function? a)value_if_true b)value_if_false c)logical_test d)lookup_value
d
A conditional formatting that displays a horizontal gradient or solid fill indicating the cell's relative value compared to other selected cells.
data bar
A precise value in a chart
data point
Group of related data points that display in row(s) or column(s) in a worksheet.
data series
The smallest data element contained in a table, such as first name, last name, address, and phone number.
field
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 box
Contains the table name
fully qualified structured reference
Similar to a column chart
histogram
Moves active cell to column A in that row
home
Symbol or signs that classify data into three, four, or five categories, based on the values in a range.
icon set
A range of cells containing values for variables used in formulas.
input area
Where does the primary axis display on a Combo chart?
left side
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.
look up table
You Answered 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
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
The vertical measurement of a row.
row height
Also called pointing.
semi-selection
You Answered Displays the name of a worksheet within a workbook.
sheet tab
What does Excel use to indicate optional arguments in a function?
square brackets [ ]
Displays information about a command or operation as it is being performed
status bar
Subtypes include: High-Low-Close, Open-High-Low-Close, Volume-High-Low-Close, and Volume-Open-High-Low-Close
stock chart
A tag or use of a table element, such as a field label, as a reference in a formula.
structured reference
Uses a field name as the reference in a table
structured reference
Uses two dimensions and a continuous curve
surface chart
A set of rules that governs the structure and components for properly entering a function.
syntax
Includes letters, numbers, symbols, and spaces.
text
How is text aligned by default?
to the left
Which Paste option would you choose if you wanted to flip the rows and columns?
transpose
Field headings without row references
unqualified reference
You Answered A number that represents a quantity or an amount.
value
A file containing related worksheets.
workbook
A spreadsheet that contains formulas, functions, values, text, and visual aids.
worksheet
What should you not select when selecting data for a chart?
worksheet titles
Formatting that enables a label to appear on multiple lines within the current cell.
wrap text
What does it mean when you see ######## in a cell?
your column is not wide enough
In the formula, =1+(2-3)+5/6-6^2, what will Excel evaluate first?
(2-3)
Which step is not part of planning a worksheet design? -Decide what input values are needed. -State the purpose of the worksheet. -Decide what outputs are needed to achieve the purpose. -enter labels values and formulas
-enter labels values and formulas
How many variations of the stock chart does Excel have?
4
In the formula, =1+(2-3)+5/6-6^2, what will Excel evaluate second?
6^2
Which of the following would not give the same result for "=(5+32+75+8)/4"?
=AVERAGEIF(5+32+75+8, 4)
IF A7=21, which of the following =IF statements would produce the result of "Much Greater"?
=IF(A7<14, "Greater", IF(A7>20, "Much Greater", "Not Greater than 20"))
What can you not do within the worksheet tabs shortcut menu? A Format the worksheet B Add color to the tab C Insert a worksheet D delete a worksheet
A
Which of the following would not produce the correct monthly payment on a loan of $13,000 which will be paid off in 5 years with an annual interest rate of 6%? A =PMT(6%, 60, -13000) B =PMT(.5%, 60, -13000) C =PMT(.5%, 5*12, -13000) D =PMT(6%/12, 60, -13000)
A
What is a nested function, and why would you create one?
A nested function is where one function is used as an argument within another function. You use nested functions to create more complex functions that provide sophisticated calculations that otherwise would be very difficult.
Indicates a cell's specific location; the cell reference does not change when you copy the formula.
Absolute cell reference
Which of the following is not a way to widen a column? A Drag the line between the column headings to the desired size. B In the Page Layout tab, Scale to Fit group, select Automatic in the Width drop-down box. C Double-click the line between the column headings.
B
What would not be a typical item to have in a header or footer? A The spreadsheet designer's name B The filename or file path C The file size D The page number
C
If you wanted to count the number of entries in a list that contained numbers, text, and blank cells, but ignore the blank cells, which function would you use?
COUNTA
Includes text, but not blank cells.
COUNTA
List at least four types of appropriate labels that describe chart elements. What types of things can you do to customize these labels?
Chart title, horizontal axis title, vertical axis label, data label, legend, etc. You can change the font, font size, font color, etc.
Why would you convert a range of data into an Excel table?
Column headings remain onscreen without having to use Freeze Panes. Table styles easily format table rows and columns with complementary fill colors. Calculated columns where the formulas copy down the columns automatically are available to create and edit. Calculated total row enables the user to implement a variety of summary functions. Structured references can be used instead of cell references in formulas. Table data can export to a SharePoint list.
A set of rules that applies specific formatting to highlight or emphasize cells that meet specifications.
Conditional formatting
How is conditional formatting is similar to an IF function?
Conditional formatting is similar to the IF function in that they both perform a task conditionally—based on the truth of the condition. If a condition is true, an IF function provides a particular result. If a condition is met, the conditional formatting rule applies formatting.
Which of the following is not a chart element? A Chart area B Plot area C Legend D Data area
D
How is data bar conditional formatting helpful when reviewing a column of data?
Data bar conditional formatting displays a horizontal gradient or solid fill indicating the relative value of each cell's value to those in the applied range. The higher the value compared to other cells, the wider the data bar.
When would it be useful to display formulas instead of formula results in a worksheet?
Displaying formulas is helpful to check formulas. You can see more formulas at one time instead of clicking each cell containing a formula and then looking at the Formula Bar. You can also print cell formulas if you find it is easier to proofread formulas on a printout.
If a cell containing =B$14 is copy and pasted three columns over and two rows down, what would the resultant cell reference become?
E$14
List at least five ways you can filter numbers.
Exact number(s) by clicking check box(es), Equals, Does Not Equal, Greater Than, Greater Than Or Equal To, Less Than, Less Than or Equal To, Between, Top 10, Above Average, Below Average
As long as you pick the correct chart you can mix numeric value systems, such as dollars and number of units sold. True False
False
The COUNTA function only counts text entries. It does not count calculable values or blank cells. True False
False
To look up an exact match, what should you enter in the range_lookup argument in VLOOKUP function?
False
You can manually adjust a page break by pointing to the page break line and waiting for the four-headed arrow to appear, then drag the page break to your desired location. True False
False
What should you do if there are columns you need, but do not want to display?
Hide the columns you don't want to display and unhide them when you're finished
Why should you not include aggregates, such as totals or average, along with individual data points in a chart?
Including aggregates diminishes the effectiveness of comparing individual data points. Including aggregate data is an invalid data point. Totals should not be included with non-aggregate data points.
If a cell contains =$N$21 and two new columns are inserted in front of column N and three rows are inserted above row 21, what happens to the =$N$21?
It becomes =$P$24
List at least four guidelines for planning a table in Excel.
Keep field names on the top row. Keep field names relatively short, descriptive, and unique. Format the field names so that they stand out from the data. Enter data for each record on a row below the field names. Do not leave blank rows between records or between the field names and the first record. Delete any blank columns between fields in the dataset.
In the task pane, what does a diagonal white triangle next to a chart category indicate?
None of the category's options are displayed.
Indicates a cell's location from the cell containing the formula; the cell reference changes when the formula is copied.
Relative cell reference
What are some of the fill options you can apply to a chart area or a plot area?
Solid, gradient, picture, and texture
What is the purpose of sorting data in a table?
Sorting helps you arrange data in different sequences so that you can display the data in different perspectives for analysis.
A structure that organizes data in a series of records (rows), with each record made up of a number of fields (columns).
Table
Formats any cell that has content that matches the first content in the first selected cell
Text contains
What three types of content can you enter into a cell? Give an example (different from those in the book) for each type.
Text: Microsoft Excel Value: 1234.56 Date: January 31, 2018
What are the main differences between Accounting Number Format and Currency format? Which format has its own command on the Ribbon?
The $ is at the left side of the cell for Accounting Number Format and immediately to the left of the value in Currency format. Negative values display in parentheses in Accounting Number Format and are preceded by a minus sign or a parentheses or in red in Currency. The right side of the value is at the right cell margin for Currency format, and is offset a little from the right cell margin for Accounting Number Format. The Accounting Number Format has its own command on the Ribbon.
Describe the three arguments for an IF function.
The logical_test is an expression that evaluates to true or false. The value_if_true argument contains the result if the logical_test is true. The value_if_false argument contains the result if the logical_test is false.
How should you structure a vertical lookup table if you need to look up values in a range?
The lookup values need to be in the first column of the lookup table, and if the last optional argument for range_lookup is TRUE (or omitted), then the first column of the lookup table should be sorted in ascending order.
What are six options you can control after selecting a table style?
The six options include header row, total row, first column, last column, banded rows, and banded columns.
Why should you use a cell references instead of typing values in formulas?
The values could change. If you enter values in a formula, you would have to edit the formulas, which could introduce other errors. You should enter values in other cells and then use those cell references in the formulas. That way, when you change individual cell values, the results of the formulas will automatically update.
After a table has been named, that name can be used in functions instead of cell references. True False
True
The default calculation using the PMT function will produce a negative number. True False
True
You cannot enter data or formulas on a chart sheet. True False
True
Which axis in a chart displays incremental numbers for the data points?
Value
In a bar chart, which axis displays the categories?
Vertical
What is the benefit of using a mixed reference?
When applicable, mixed references can be used in place of absolute references as a method of shortening the length of the formula.