Flash Fill |
Enters text based on patterns it finds in the data |
COUNT |
Function will not include any cell in the range containing a non-numeric value in the final tallu |
Equals sign |
An excel formula always begins with this |
Range |
Group of cells in a rectangular block |
Formula bar |
Bar under ribbon used to enter formula. (May appear as a figure question) |
Cell References |
Sometimes color coded to reference a specific place where a formula/input is entered. (May appear as a figure question) |
Keyboard shortcuts |
Help you work faster and more efficiently because you can keep your hands on the keyboard. |
Parentheses |
Used to change the order of operation and enclose part of a formula |
Exponentation |
Takes place over multiplication |
Portrait |
Orientation where page is taller than wide. (Default) |
Scale |
Used to modify width or hight so that all of the columns or all of the rows fit on a single page. |
Excel uses this order of operation: |
PEMDAS |
SUM formula |
=SUM(CellA:CellB) |
mm/dd/yyyy |
Date format |
Ctrl+S |
Quick save, updates workbook to reflect latest content |
Home |
Used to go to column A of the current row |
Ctrl+Home |
Used to make A1 the active cell |
Ctrl + Page Up or Ctrl +Page Down |
Used to move to the previous or next sheet |
Colon |
Used to separate cell references |
(T/F) When text wraps within a cell, the column width increases so that all of the text within the cell is displayed. |
False - It's the cell width |
Because Excel stores dates and times as _____, you can apply different formats without affecting the date and time value. |
Numbers |
In Excel, dates are _____-aligned in the cell by default, regardless of date format. |
Right |
Merge Across |
merges each of the rows in the selected range across the columns in the range. |
In Excel, to merge cells A1, B1, C1, D1, and E1, _____ is the correct reference for the merged cell. |
A1 |
When using the Format Painter, double-click the Format Painter button to paste the same format multiple times and click the Format Painter again to _____. |
Turn it off |
Print area |
Prints data only in selected area and/or cells |
Manual page break |
Used to set page breaks in areas of worksheet that are not awkward |
To set a page break in Excel, select the _____. |
First cell below the row you want to enter |
print title |
Information that prints on each page, like a company name or logo |
(T/F) Calculated values too large to fit into the cell are displayed in scientific notation |
True |
In the following formula: =IF(A1="YES", "DONE", "RESTART"), what happens if A1= "NO"? |
The formula returns the text to restart |
Max Function |
Returns the maximum value in the range |
Absolute references are marked with a ____. |
$ |
When you copy a formula that contains an absolute reference to a new location, the reference ____. |
Does not change |
Relative reference |
Used to have different formulas refer to the same cell |
Optional arguments |
Provide more control over returned values |
If an optional argument is not included, Excel assumes a(n) ____ value for it. |
Default |
COUNT Function |
Counts how many cells in a range (Value 1:Value 2) contain numbers |
In "SUM(number1 )", number 1, number 2, and number 3 may be numbers or ____. |
Cell references |
In the formula =INT(AVERAGE(A1:A100)), which would occur first? ____ |
The average of the values in the range A1:A100 is calculated. |
In the formula =IF(A1=B1, C1, C2), the result will be C2 if ____. |
A does not = B |
Nested Function |
A function inside a function |
Arguments |
Numbers, text, or cell references used to return values |
(T/F) When a formula includes a cell reference, Excel interprets that cell reference as being located relative to the position of the current cell. |
True |
(T/F) The WORKDAY function displays the date of the weekday a specific number of weekdays past a starting date. |
True |
(T/F) A lookup table organizes numbers or text into categories. |
True |
You want to take out a loan for $130,000. The annual interest on the loan is |
=PMT(5%/12, 15*12, 130000) |
NPER |
Function used to determine how many payment periods are required to payback a loan |
To resize an embedded chart, ____. |
select the chart and drag the corner sizing handle of the selection box |
Data labels |
provide descriptive text for the individual data markers, such as pie slices |
Dual axis |
Used on charts with vastly different values |
Legend |
Identifies data markers associated with each data series |
Interest |
Amount added to the principal loan by the lender |
FV function |
Future value |
Principal |
Base amount being lent out |
PMT(rate, nper, pv ) In the figure above, nper stands for the ____. |
total number of payment periods |
PMT(rate, nper, pv ) In the figure above, pv stands for the ____. |
present value of the loan |
PMT(rate, nper, pv ) In the figure above, fv and type are ____. |
optional arguments |
Clustered column chart |
displays the data series in separate columns side-by-side so that you can compare the relative heights of the columns in the three series. |
Watermarks |
"washed out" markings that are placed vaguely behind all contents of the sheet so they do not obscure any information |
Move chart |
dialog box that provides options for moving charts between worksheets and chart sheets. |
(T/F)A color bar is a conditional format that adds a horizontal bar to the background of a cell containing a numeric value. |
False |
Alt2 key combo |
Used to snap the chart to upper left and/or lower right corner of a cell |
When you have more than one sort field, you should use the Sort ____ to specify the sort criteria. |
Dialog box |
To add a sort field in the Sort dialog box, click the ____ button. |
Add level |
After you filter a column, the ____ Filter command becomes available so you can remove the filter and redisplay all the records. |
Clear |
To calculate subtotal for a table, the first step is to use the ____ button on the TABLE TOOLS DESIGN tab. |
Convert to Range |
Pivot table layouts |
Four squares labeled FILTERS< COLUMNS, ROWS, and Values. |
By default, the PivotTable report uses the ____ function for numbers in the Values area. |
SUM |
You cannot change data directly in the PivotTable. Instead, you must edit the Excel table, and then ____, or update, the PivotTable to reflect the updated data. |
refresh |
You are a student who is new to the more advanced features of Excel. You have a lot of familiarity with ranges from an earlier Excel class, but you know that there is an alternative that will give you access to additional features that you do not have with a cell range. What is that option? |
Convert the range to a table. |
To create a table, click the ____ tab on the Ribbon and then, in the Tables group, click the Table button. |
INSERT |
A table name cannot include ____. |
Spaces |
(True/False) If a column consists of day or month labels, you can sort them in their correct chronological order using one of the predefined custom lists |
True |
(True/False)You can drag one field to the FILTERS area of the PivotTable Fields pane to |
False - You have to change the original excel table then update the pivot table by refreshing it. |
Worksheet group |
a collection of two or more selected worksheets |
When you reference cells and ranges in other worksheets, if the worksheet name contains spaces, you must enclose the sheet name in ____. |
single quotation marks |
Which of the following formulas is correct, given a sheet named Sales Data? |
C |
The general form of an external reference is ____, where Path is the path of the workbook file on the computer or network, Workbook Name is the filename of the workbook, and Sheet Range and Cell Range are worksheets and cells in the workbook, respectively. |
WorksheetName!CellRange |
You can create a(n) ____ reference by deleting the $ from the row reference in the cell reference $B$6. |
Mixed |
If you receive a destination workbook but the source files are not included, click the ____ Link button in the Edit Links dialog box to replace the external references with the existing values. |
Break |
If the destination file is closed when you make a change in the ____ file, you can choose whether to update the link to display the current values when you open the destination file. |
source |
All template files have a(n) ____ extension. |
.xltx |
The ____ caption indicates a worksheet group. |
|
To ungroup worksheets, you can click a sheet ____ of a sheet not in the group. |
tab |
To remove a worksheet group, you can right-click the sheet tab of a sheet in the group and then click ____ on the shortcut menu. |
Ungroup sheets |
When you reference a cell or range in a different worksheet, the ____ separates the sheet reference from the cell reference. |
! |
(True/False)Once you group a collection of worksheets, any changes you make to one worksheet are applied to all sheets in the group |
True |
(True/False)Using multiple worksheets with identical layouts enables you to use 3-D references to quickly summarize the data in another worksheet. |
True |
Defined name |
Must not contain spaces or dashes |
One way to ensure that correct data is entered into a cell or range is to use the Excel data _____ feature. |
validation |
You use the _____ tab in the Data Validation dialog box to enter the validation rules for the active cell. |
Settings |
_____ messages appear as ScreenTips next to the active cell. |
Input |
Usually, you will want to protect a worksheet but leave some cells _____. |
Unlocked |
Protecting the _____ prohibits users from renaming, deleting, hiding, or inserting worksheets. |
Structure |
In VBA, macros are called _____ procedures. |
Sub |
To save a workbook with macros, the default Excel Workbook format needs to change to a macro-enabled workbook, which has the _____ file extension. |
xlsm |
The _____ dialog box displays all of the names in the workbook. |
Name manager |
Any blanks or parentheses in a row or column label will be changed to _____ in defined names. |
underscore characters |
(True/False)The Visual Basic Editor is a separate application that works with Excel and all of the Office products to edit and manage VBA code. |
True |
(True/False)One way to run a macro is to assign it to a(n) button that is placed directly on the worksheet. |
True |
(True/False)Running a macro means Excel performs a portion of the steps that were recorded. |
False - it performs all steps recorded. |
(True/False)Each macro must have a unique name that begins with a letter. |
True |