Office Applications 2 - Unit 2: Creating Microsoft® Excel® Spreadsheets
What displays in a formatted cell when a number entered is too long to be displayed in the cell?
#######
What symbol is used in absolute and mixed cell references?
$
Which one of the following is a correct version of an absolute cell reference?
$A$1
Which one of the following is a mixed cell reference?
$C1
Which one of the following is a correct version of a mixed cell reference?
$C14
To convert a number to text, which symbol do you type before it?
'
Which of the following symbols is used to convert a number to text?
'
Which symbol do you type before a number to convert it to text?
'
When ______ is typed in a cell, it will be left-aligned.
'01254
When _____ is typed in a cell, it will be left-aligned.
'02222
What symbol is used in Excel formulas to calculate multiplication?
*
The PMT function is calculated by default as a negative number. What symbol should be typed after the = in the PMT function to make the answer a positive number?
-
What symbol should be typed after the = in the PMT function to convert the number to a positive number?
-
The actual value of 7 percent is _______.
.07
The actual value of 20 percent is ________.
.2
The actual value for 50% is _____.
.5
In Excel formulas, what symbol is used to calculate division?
/
What symbol is used in Excel formulas to calculate division?
/
When numbers are formatted in percent format, they are multiplied by _______ for display purposes.
100
In the PMT function, if calculating monthly payments on a loan, the APR needs to be divided by _____.
12
What is the answer to the formula 8+6/2+5?
16
What is the answer to the formula 12+6/2+10?
25
If the number 30 is in a cell, what number will be displayed after you format the cell in percent format?
3000%
A sheet tab name can contain up to _________ characters.
31
The maximum number of characters that a cell can contain is _____.
32,767
When _____ is typed in a cell, it will be right-aligned.
700.22
When ______ is typed in a cell, it will be right-aligned.
902.22
Which of the following comparison operators represents not equal to?
<>
Formulas begin with a _________.
=
Formulas begin with the ________ symbol.
=
In Excel, formulas should begin with the _____ symbol.
=
When creating formulas in the Excel program, it's important to start the formula with the _______.
=
Which of the following functions is not listed on the AutoSum drop-down menu?
ABS
Which of the following is not one of the commonly used functions listed?
ADDITION
_____ cell references do not change when copied.
Absolute
________ cell references do not change when copied.
Absolute
_________ cell references used in formulas do not change when the formula is copied.
Absolute
Match the following.
Absolute cell references: Cell references that contain the dollar sign symbol ($)in formulas and do not change when copied. Mixed cell reference: Cell references in a formula where either just the column letter is absolute or just the row number is absolute.
Match the following.
Alignment formats: Determine where data in cells will be aligned. Borders: Lines that can be formatted to display on any edge of a cell. Cell styles: Predefined formats used to quickly format cells. Conditional formatting: Formatting that is applied when cell data meets certain criteria. Custom borders: Borders with formats not found on the Border tool.
Match the following.
Alphabetical text: Data made up of letters of the alphabet and symbols. Alphanumerical text: Data made up of the letters of the alphabet, symbols, and numbers. Clipboard: A holding place for cut or copied text. Custom Lists: A feature that allows you to create your own AutoFill lists for data that you frequently enter in worksheets. Data entry: The act of entering data into cells in an Excel worksheet. Format bar: A shortcut bar that contains tools for commonly used formats. Importing data: The act of adding data to an Excel worksheet from another software program or file.
Match the following.
Alphabetical text: Data made up of letters of the alphabet and symbols. Alphanumerical text: Data made up of the letters of the alphabet, symbols, and numbers. Data: Information you type in cells. Date/time: Date and time data have numeric values. Formulas: Used in the Excel application to create equations and usually contain cell addresses. Functions: Preset calculations used for complex formulas in which you fill in the blanks with arguments. Hyperlinks: Links to Web pages or email addresses. Noncalculated numbers as text: Numbers that you won't calculate.
The keyboard shortcut to force a new line break in a cell is _______.
Alt+Enter
Match the following.
Argument: Information the Excel program uses in the calculation for the function. AutoCalculate: Feature that allows you to quickly perform a calculation without actually typing a formula in a cell. AutoSum tool: Used to quickly create a SUM function to add a range of numbers. Functions: Preset calculations used for complex formulas in which you fill in the blanks with arguments.
Match the following.
Arithmetic operators: Used to perform mathematical calculations. Comparison operators: Used to compare two values and return (display) the value of either TRUE or FALSE. Formulas: Used in the Excel application to create calculations and usually contain cell addresses. Operators: Symbols used in formulas to indicate the types of calculations to be performed. Order of operations: Order in which calculations are performed in a formula.
Match the following.
Ascending: Sorting in alphabetical order from A to Z or from the smallest number to the largest number. Custom Sort: Used to sort data by more than one column at a time. Descending: Sorting in reverse alphabetical order from Z to A or from the largest number to the smallest number. Filter command: Used to search data for specified criteria and display only the rows of data that match the criteria. Sort A to Z tool: Used to sort text data in ascending alphabetical order. Sort Largest to Smallest tool: Used to sort numerical data in descending order from the highest number to the lowest number. Sort command: Used to arrange data in a certain order; two terms to know when sorting data are "ascending" and "descending". Sort Smallest to Largest tool: Used to sort numerical data in ascending order from the lowest number to the highest number. Sort Z to A tool: Used to sort text data in descending reverse alphabetical order.
_______ is the feature that allows you to quickly advance cell data while filling a range of cells.
AutoFill
Match the following.
AutoFill: Allows you to quickly advance cell data while filling a cell or range of cells. Custom Lists: A feature that allows you to create your own AutoFill lists for data that you frequently enter in worksheets. Numerical series: A series of numbers that advance by a set amount. Smart tag: Tool that appears that allows you to choose options without using the tools on the ribbon.
Click in an empty cell at the bottom of a column of numbers, and click on _____ to quickly create a SUM function.
AutoSum
The SUM function is the most commonly used function, so it has its own tool called the _____ tool.
AutoSum
The ________ tool is used to quickly create a SUM function.
AutoSum
What is the name of the tool that contains the commonly used functions in the Excel program?
AutoSum
Use the ________ predefined formats to quickly format cells.
Cell Styles
_____ are used in formulas instead of actual numbers.
Cell references
_______ are pre-defined formats used to quickly format cells.
Cell styles
Match the following.
Center Across Selection: Command used to center data across selected cells without merging the cells. Clear tool: Tool used to clear the contents, formats, comments, and/or hyperlinks from cells. Delete Cells: Command used to delete the selected cells. Delete Sheet Columns: Command used to delete columns. Delete Sheet Rows: Command used to delete rows.
The ______ tool in the Editing group on the Home tab allows you to clear all options from a cell.
Clear
If you have added several formats to cells in a worksheet and decide you want to remove all the formats from the cells, but leave the data in the cells, use the _____ command.
Clear Formats
If you want to leave the content in selected cells but remove all of the formatting from the cells, use the _______ command.
Clear Formats
You can remove the formats only of cells using the Clear tool options of ________.
Clear Formats
Match the following.
Clipboard: A holding place for cut or copied text. Fill handle: Black box in the bottom right corner of a cell or a range of cells. Fill tool: Takes the data from the first cell in a selected range and copies it to all of the other cells in the selection. Filling: The act of copying a cell or range of cells to adjacent cells (cells right next to the cell or range being copied). Range: A group of cells that are next to each other (adjacent) and form a rectangular shape. Selecting: The act of choosing cells.
If you want to copy data from one cell to another cell that is several rows down in a worksheet, which method should you use?
Copy and Paste
Press and hold _______ while dragging a sheet tab with the mouse to copy the worksheet.
Ctrl
Use the _______ key on your keyboard to select multiple ranges of cells.
Ctrl
What key on your keyboard do you press and hold when dragging a cell to copy it?
Ctrl
What key on your keyboard do you use to select multiple ranges of cells?
Ctrl
The _______ on the keyboard is used to copy a worksheet while dragging the sheet tab with the mouse.
Ctrl key
The keyboard shortcut to hide a row is _______.
Ctrl+9
The keyboard shortcut to display formulas in worksheets is _________.
Ctrl+`
Select all that apply. When you want to select more than one worksheet at a time, what two keys on your keyboard could you use?
Ctrl, shift
What is the AutoFill option to create your own AutoFill list called?
Custom List
To sort by last name and then by first name, use the ________ command.
Custom Sort...
To sort by the Department column and then by the Sales column at the same time, use the _____ command.
Custom Sort...
Use the __________ command to sort by more than one column at a time.
Custom Sort...
Match the following.
Custom fill colors: Colors and pattern formats that are not found on the Fill Colors tool. Fill color: Color displayed inside a cell. Font formats: Formats you can apply to the data in cells. Format bar: A shortcut bar that contains tools for commonly used formats. Number formats: Formats that can be applied to numbers.
What tab on the ribbon displays the Filter command?
Data
Match the following.
Data entry: The act of entering data into cells in an Excel worksheet. Get External Data group: Lists tools used to import data into an Excel worksheet. Importing data: The act of adding data to an Excel worksheet from another software program or file. Text Import Wizard: Used to walk you through importing data into an Excel worksheet. Wrap Text tool: Used to wrap text in a cell and make the row taller.
To name a range, right-click on selected cells and choose ________.
Define Name
Pressing the _____ key on your keyboard will delete the contents of selected cells all at once.
Delete
Which key on your keyboard is used to delete the contents of selected cells all at once?
Delete
_______ and type on a sheet tab to rename it.
Double-click
What keyboard shortcut is used to make a cell reference absolute in a formula?
F4
What is the correct answer to the formula 10=12?
FALSE
After deleting a worksheet, you can undo the deletion with the Undo tool.
False
Borders and fill colors can be removed from a cell by deleting the cell contents.
False
If you apply borders or fill color to cells, removing the borders or fill color can be done by selecting the cells and pressing the Delete key on your keyboard.
False
In a formula, addition is calculated before multiplication.
False
The COUNT function counts all cells that have any type of character in them in a range.
False
Fill the background of a cell with color using the _______ tool.
Fill Color
Which one is not a date AutoFill option?
Fill Semiannually
The ___________ command is used to search and display data that meets specified criteria.
Filter
To search through data and display results that match certain criteria, use the ________ command.
Filter
The _______ dialog box displays formatting tabs for Font, Number, and Alignment.
Format Cells
To find formatting commands that are not displayed on the Home tab, open the _______ dialog box.
Format Cells
When opening the dialog box for the Font, Alignment, or Number groups on the Home tab, the _____ dialog box opens.
Format Cells
The Function Wizard tool can be found to the left of the ______.
Formula Bar
The ______ shows what is truly in a cell.
Formula Bar
To see the formula in a cell, look at the _________.
Formula Bar
Where do you look on the Excel screen to see the formula in a cell?
Formula Bar
Where do you look on the Excel screen to see the formula that has been typed in a cell?
Formula Bar
Where do you look on the Excel screen to see what is truly in a cell?
Formula Bar
_______ are used in the Excel application to create calculations.
Formulas
Use the _____ to search for functions to use for calculations.
Function Wizard
Match the following.
Function Wizard: Command used to help Excel users search for functions and understand the arguments needed for the function. Function Wizard dialog box: Dialog box used to help users find a function and fill in the arguments for a function. IF function: Logical function that is used to check for a condition and return a value if the condition is true and a different value if the condition is false. PMT function: Commonly used financial function to calculate the payment amount on a loan.
Use the ________ command to prevent certain columns from being displayed when printing a worksheet.
Hide
Select all that apply. Which two tabs on the ribbon contain the Sort commands?
Home, data
Match the following.
Insert Cells: Command used to insert blank cells. Insert Sheet Columns: Command used to insert blank columns. Insert Sheet Rows: Command used to insert blank rows. Merge Across: Command used to merge the selected cells into one cell for each row selected. Merge and Center: Command used to merge selected cells into one cell and center the data. Merge Cells: Command used to merge selected cells into one cell
The fastest method to insert a new worksheet is to click on the ______.
Insert Worksheet tab
To insert new worksheets using the quickest method, click on the _______.
Insert Worksheet tab
Use the _______ command to combine the selected cells and center them.
Merge and Center
Match the following.
Move or Copy: Command used to move or copy worksheets. Rename: Command used to rename sheet tabs. Tab Color: Command used to apply color to sheet tabs. Worksheet templates: Predesigned worksheet with content and formatting.
One use for named ranges is to be able to navigate to them quickly. Use the _________ to navigate to a named range.
Name Box
Use the ________ to navigate to a named range.
Name Box
Match the following.
Name Manager: Notes that can be attached to cells to add additional information that is not printed on the worksheet. Named range: Used to give a range of cells a name. Range: A group of cells that are next to each other (adjacent) and form a rectangular shape. Relative cell references: Cell references that are relative to the location of the cell where the formula is entered and that change when copied. Show Formulas: Command used to display all of the formulas directly in a worksheet.
The ________ command is used to filter numbers.
Number Filters
Which one is not a comparison option when filtering numbers?
Over
In the formula =C5*$B$3, C5 is what type of cell reference?
Relative
________ cell references change when copied.
Relative
To use the shortcut for inserting a row, ______ on the row number and choose Insert.
Right-click
_____ on selected cells will initiate the shortcut for inserting or deleting cells.
Right-clicking
The ______ command on the Fill tool is used to manually fill a series of numbers.
Series
To manually fill a series of numbers, use the ______ command on the Fill tool.
Series
Select all that apply. To select more than one worksheet at the same time, which of the following keys on your keyboard can you use?
Shift, Ctrl
Select all that apply. Which of the following keys on your keyboard can you use to select more than one worksheet at the same time?
Shift, Ctrl
The ________ tool on the Formulas tab is used to display formulas directly in a worksheet.
Show Formulas
To display formulas in a worksheet, use the _________ tool.
Show Formulas
To display more AutoCalculate calculations, right-click on the _______.
Status Bar
What is the correct answer to the formula 10<>12?
TRUE
Select all that apply. What two values do comparison operators return?
TRUE, FALSE
Select all that apply. When entering formulas using comparison operators, what two values could be returned?
TRUE, FALSE
Use the _______ command to color-code worksheet tabs.
Tab Color
Match the following.
Tab key: Used to move across the columns. Enter key: Used to move down to the next row.
The _______ is automatically started to help import the data.
Text Import Wizard
When importing data using the Get External Data tools on the Data tab, what wizard is automatically started to help import the data?
Text Import Wizard
By default, the PMT function displays a negative value.
True
Do not type commas and dollar signs when entering numbers in cells.
True
In a formula, multiplication is calculated before addition.
True
When entering a PMT function, if you don't put a minus sign after the equals sign, the number will calculate as a negative number.
True
When entering numbers in cells, type only the number and no symbols except for a preceding hyphen (-) if the number is negative.
True
_______ are predesigned worksheets with content for formatting.
Worksheet templates
To calculate exponentiation in Excel formulas, what symbol is used?
^
What symbol is used in Excel formulas to calculate exponentiation?
^
The formula =C5*$B$3, $B$3 is what type of cell reference?
absolute
In the order of operations, what is calculated after multiplication and division?
addition and subtraction
To move a cell by clicking and dragging, where do you point on the cell to begin?
an edge
Where do you point in a cell to move the cell?
an edge
APR stands for ________.
annual percentage rate
In the function =sum(B3:B20), B3:B20 is called the _____.
argument
The information the Excel program uses in functions is called a(an) ______.
argument
Use the Function Wizard dialog box to enter the _______ of a function.
arguments
The AutoSum tool contains a(an) ________ to display more calculation options.
arrow
When dragging a sheet tab to move it, the location of the _______ determines where the sheet tab will be moved.
arrow
When dragging a sheet tab to move it, when you let go of the mouse, the sheet tab will be moved to the location of the _______.
arrow
To sort data in alphabetical order from A to Z is called __________ order.
ascending
To sort data in numerical order from smallest to largest is called _____ order.
ascending
What does the mouse pointer look like when filling a cell?
black cross
When you point the mouse to the fill handle, what does the mouse pointer look like?
black cross
When designing data for sorting, there should be no _____ rows or columns in the data.
blank
When designing data for sorting, there should be no _________ columns in the data.
blank
When designing data for sorting, there should be no _________ rows in the data.
blank
When setting up data in a worksheet to be sorted or filtered, it's important to leave no _________ rows or columns in the data.
blank
The lines that can be formatted to display on the edges of cells are called _______.
borders
To display lines on any edge of a cell to make it stand out, use the _____ format.
borders
Instead of using numbers in formulas, use _________.
cell references
When creating formulas in Excel, use the __________ of the values you want to calculate.
cell references
The relative cell references are automatically ________ when formulas are copied.
changed
When formulas are copied, the relative cell references are automatically _________.
changed
Which one of the following is not a preset AutoFill option?
colors
Data bars are a form of ________.
conditional formatting
The Data Bars feature is a type of _____.
conditional formatting
Use______ to format cells when they meet a certain criteria.
conditional formatting
If you delete selected cells using the Delete key on your keyboard, only the _______ of the cells is removed.
content
The COUNTIF function counts the cell if the cell contents match the _____.
criteria
Create a/an _______ to create your own AutoFill option.
custom list
Information typed in cells is known as _______.
data
To sort data in reverse alphabetical order from Z to A is called ________ order.
descending
To sort data in reverse numerical order from largest to smallest is called _____ order.
descending
In the PMT function, if calculating monthly payments on a loan, the APR needs to be ________ 12.
divided by
Renaming a sheet tab is as simple as _____ on the sheet tab.
double-clicking
Moving a worksheet to a new location in the workbook is easy. Simply click on the worksheet tab and _______.
drag
If the cell to the right of the cell you are typing in is _________, the data can "bleed" over that cell.
empty
If you type more data than can be displayed in a cell and the cell to the right of it is _________, the data can bleed over the cell.
empty
The first step to auto fill a series of numbers is to _______.
enter the first two numbers in separate cells
What is the shortcut key to make a cell reference in a formula absolute?
f4
The third argument in the IF function indicates what the function should display if the logical test is _______.
false
______ is the color displayed inside a cell.
fill color
The PMT function is a ________ function.
financial
The Microsoft® Excel® application has several preset AutoFill options, but ______ is not one of the presets.
formats
What element of the Excel screen is used to see what is truly in a cell?
formula bar
In the Microsoft® Excel® application, calculations are called ______________.
formulas
Numbers are calculated in Excel using _____ and functions.
formulas
A(an) ______ is a built-in preset calculation.
function
SUM is a built-in preset calculation called a(an) _____.
function
SUM is an example of a built-in _____.
function
Numbers are calculated in Excel using formulas and ______.
functions
What two letters does the Function Wizard tool contain?
fx
To prevent certain columns from being displayed when printing a worksheet, _______ the columns.
hide
Select all that apply. When deleting cells, you need to decide whether the remaining cells will be shifted _______ or shifted _______.
left, up
The IF statement is a(n) ________ function.
logical
What type of function is the IF function?
logical
Which one of the following is a correct version of a relative cell reference?
m1
The Center Across Selection centers data across selected cells without _______ the cells.
merging
Which one of the following is not a preset AutoFill option?
multiples
In the PMT function, if calculating monthly payments on a loan, and the term is in years, the number of years should be _____ 12.
multiplied by
What argument does the PI function use?
no argument is needed
Follow the _________ when creating formulas.
order of operations
Formulas should follow the ________.
order of operations
The _____ is a basic math principle that should be followed when writing formulas in the Excel program.
order of operations
When numbers are changed in cells that are involved in formulas, the formulas are automatically _________.
recalculated
In the formula =F10*$B$3, F10 is what type of cell reference?
relative
When copying formulas, _____ cell references change.
relative
Select all that apply. When inserting cells, you need to decide whether the remaining cells will be shifted ________ or shifted ________.
right, down
To insert a row using the shortcut, _______ on the row number and choose Insert.
right-click
_______ with the mouse on selected cells to initiate the shortcut for inserting or deleting cells.
right-click
The shortcut to insert or delete cells starts with _______ the mouse on the selected cells.
right-clicking
If a number entered in an unformatted cell is too long to be displayed in the cell, what will be displayed?
scientific notation
What displays in an unformatted cell when a number entered is too long to be displayed in the cell?
scientific notation
The ______ is used to change the date AutoFill option.
smart tag
Be careful with merging cells because they can cause problems with commands such as _______ in worksheets.
sorting
Merging cells can cause problems with commands such as _______ in worksheets.
sorting
The AutoCalculate feature displays calculations on the _______.
status bar
When cells are selected in a worksheet, the AutoCalculate feature displays calculations on the _____.
status bar
What type of file is a CSV file?
text
When entering numbers that start with a zero, apply the _______ number format.
text
When entering zip codes that start with a zero, apply the ___________ number format before entering the numbers.
text
Point to _______ of a cell to fill the cell to the right or down.
the bottom right corner
Where do you point in a cell to fill cell data?
the bottom right corner
Point to _______ of a cell to select it.
the center
Where do you click on a cell to select the cell?
the center
Where do you point in a cell to select the cell?
the center
The IF function contains three arguments.
true
The second argument in the IF function indicates what the function should display if the logical test is _______.
true
When formatting numbers with the Accounting Number Format tool, the numbers are formatted with ______.
two decimal places
When formatting numbers with the Comma Number Format tool, the numbers are formatted with _______.
two decimal places
Fill ______ skips the Saturday and Sunday dates in a date fill.
weekdays
To skip the Saturday and Sunday dates in a date AutoFill, use the ______ option.
weekdays
The move mouse pointer looks like a _______.
white arrow
What does the mouse pointer look like when moving a cell?
white arrow
The copy mouse pointer looks like a _______.
white arrow with a small plus sign
What does the mouse pointer look like when copying a cell with the drag method?
white arrow with a small plus sign
What does the mouse pointer look like when selecting a cell?
white plus sign
If ######## or 12.0E+3 displays in a cell, what should you do so the cell will display the actual number?
widen the column
Select all that apply. What two things can you do to display the entire contents of a cell?
widen the column, set Wrap Text formatting
You can copy a worksheet as a new worksheet in the same workbook or you copy it to another _______.
workbook
Text has a value of ______.
zero
The value of text is ______.
zero
