PF Lesson 3 Microsoft Excel
Which is an example of an absolute cell reference?
$B$2 An absolute cell reference requires dollar signs in front of both the column letter and row number.
What is the result of the formula =5+2*3+1?
12
Write a formula that uses a function to determine the total value of cells C4 through C17.
=SUM(C4:C17)
The _______ feature attempts to see an existing pattern in the data entered and then apply that pattern to filling in additional cells.
Autofill
What's another name for cell reference?
Cell name
Press the _______ key to move to the next cell in the row.
Tab
What does a worksheet Total Row display?
The Total Row displays the results of column-specific calculations and provides you with access to some of Excel's built-in table summary function capabilities.
What do conditional formats do?
They change cell appearance based on conditions you specify.
You've created a spreadsheet for your daily appointments, but you accidently left out the Thursday column. Which is the fastest way to correct the worksheet?
To add a missing column, you should insert one between existing ones.
Which function converts a text string to uppercase or capitalization for all the text in a string?
UPPER
relative cell reference
When the formula is copied to another cell, the cell references will be adjusted relative to the formula's new location.
If a cell displaying 6 contains the formula =D3*2, what is displayed when the value in cell D3 is changed to 10?
When you change the value in cell D3 to 10, the display in the cell changes to 20.
What's the difference between duplicating a cell and moving one?
When you duplicate a cell, you copy its contents to a new cell. When you move a cell, its contents are removed from the current location and moved to another.
Why is it a good idea to apply numeric formats before you enter data?
When you enter data into a cell that has already been formatted, you can check your numbers against what you're expecting for better accuracy.
If you want to copy the formula in cell J3 to cells J4 and J5, which method is fastest?
You can drag the Fill Handle to quickly copy a formula to adjacent cells.
Which chart should you use to show the percentage of each ingredient used on a whole pizza?
You should use a pie chart to show what percentage of each ingredient is used on a whole pizza.
The _______ cell is where typed data appears.
active
A filter is used to
an advanced form of search and allows multiple criteria for performing a search.
One way to move a cell to another location is with the
cut and paste commands
chronological order
data arranged by date or time
argument
data in a function
Area Chart
demonstrates total value across the trend
descending order
high to low
You have a spreadsheet with data in rows 4 through 10 in columns B, C, E, and F. How would you select the data to create a chart?
hold down the Ctrl key while you drag to select the additional cell ranges.
Data in chronological order is sorted
in order from lowest to highest
Ascending Order
low to high
When several cells are selected together, they're referred to as a cell _______.
range
Which is the first step to applying cell formats?
select the cell to be formatted
When you type data into a spreadsheet, it appears in
the active cell
How can the Quick Analysis button be used to format cells?
to apply conditional formatting to a range
Press the _______ arrow key to select the cell above the current active cell.
up
Which of the following statements is true of a pie chart?
uses slices that are sized as a percentage of a whole.
What two Sort functions are available in Excel to sort cell ranges?
Quick Sort or Custom Sort
How will the cell reference $G$14 be treated when you copy the formula to a cell in the next row?
The cell reference won't change because the dollar signs indicate an absolute cell reference.
Explain how to change the size of a chart.
by selecting it and dragging a handle.
A chart legend
corresponds to the category labels in the first column of data.
Labels are used in a worksheet to
describe data in a row or column
What's a cell? How is it identified?
A cell is the intersection of a row and column in a worksheet. You identify it by a row number and column letter.
Which of the following statements is true of a function?
A function uses one or more arguments to make a calculation and return a single value.
You created a budget in Excel. You would like to quickly see how each budget category compares to the others to make up the total. What is the best way to accomplish this?
A pie chart is a graphical representation of data where the entire circle equals the whole and the individual sections are graphically represented as sections showing their proportion of the whole.
A relative cell reference
A relative cell reference changes based on the location of column and row it has been copied to.
What's a relative reference?
A relative reference is a cell or a range reference that will change when a formula is moved or copied from one cell to another.
What's the difference between a workbook and a worksheet?
A workbook is an Excel file; a worksheet is one sheet of the workbook.
How can you add a new row to a table after you've created it?
Add a new row to a table by selecting the last cell and then pressing Tab.
In Excel, adding ________ to shapes, pictures, charts, or other graphics helps people with visual impairments understand pictures and other graphical content through a screen reader.
Alternative text (alt text)
If a cell displaying #DIV/0! contains the formula =C2/D9, what must be the value of cell D9?
Cell D9 must contain a 0.
What are the steps for displaying table rows that have grades lower than a C in a column titled Grade?
Click the arrow in the Grade header and then click Number Filters and then Less Than. In the displayed dialog box, type C for the criteria and click OK.
You want dollar values less than $0 to appear in red. Which is the best formatting to achieve this?
Conditional Formatting
List two uses for conditional formatting.
Conditional formatting can be used to make you aware of data that are outside the typical range for a set of numbers. You can also use conditional formatting to reveal numbers that are duplicates in a column.
mixed cell reference
Either the column or row does not change when copied
What function can you use to copy and paste formatting (font type, color, size) from one cell or to another cell range?
Format Painter
A/An _______ is a link within Excel that, when clicked, performs a particular action, like starting an email or navigating to a webpage.
Hyperlink
The default file format for Excel workbook files is the XLSX format. What's another file format that can be used for Excel files?
A table that automatically analyzes and summarizes your data is called a/an
PivotTable
What function is used to show returns of the first character or characters in a text string based on the number of characters specified?
RIGHT function
Why would you add alternate text to a chart?
So people who use screen readers will be provided with a description of pictures or graphical content that's used.
A colleague gives you a spreadsheet containing the personnel records of all department employees. Employees are added to a row at the bottom of the spreadsheet as they're hired. As a result, you have to scan the entire spreadsheet to find the information for employees. What is the best way to resolve this problem?
The Sort feature in Excel allows you to change the view of your data, rearranging it into a specified order. For example, text can be sorted alphabetically or reverse alphabetically, and numbers can be sorted in ascending or descending numeric order.
What does it mean when you see ##### in a cell?
The cell isn't wide enough to display the numeric data it contains.
What are two formatting options to consider with long labels?
Wrap Text and Orientation
Is it possible to have a chart on a separate worksheet? Explain.
Yes, it's possible to have a chart on a separate worksheet by using the Move Chart command on the Chart Tools Design tab.
Conditional cell formatting is used to
apply a color or other format based on the value in the cell
B10 is an example of a _______ name.
cell
To add a data series to a chart from the Chart Tools contextual tab, do which of the following steps?
Design Tab > Data Group > Select Data
You entered an Excel function to perform a mathematical calculation on several columns of numbers in your spreadsheet. The cell where the function resides displays #####. What is the reason for this?
The column isn't wide enough to display the information in the cell. OR There's a negative date or time in the cell.
If you want to show a warming trend over 20 years, which chart type is best?
You can use a line chart to show changes over time.
Name at least three actions that can be done with the Quick Analysis button.
You can use the Quick Analysis button to format data, create a chart, add a total, create a PivotTable, and add Sparklines.
To sort data in a table, you must
You click the arrow button in a table to display a menu with Sort commands.
List two reasons you might add an image to a worksheet.
You might add an image to a worksheet to include your company logo or to provide a picture of the product related to the data.
List three examples of when you would sort data.
You might sort data to arrange names in alphabetical order, to arrange times in chronological order, or to arrange test grades in order from highest to lowest.
Give an example of when you would use a column chart.
You might use a column chart to represent quarterly sales data for different years so that you can see how figures from a previous year compare to current figures.
Which formula can you use to determine how many cells in a range have values?
You use the COUNT function to determine how many cells in a range have values.
Name one way to widen a column.
You widen a column by dragging a boundary.
In addition to data, your worksheet should include _______ to identify the purpose of the worksheet, columns, and rows.
labels
The IF function is used to
makes a comparison and then returns a result based on that comparison.
On the Excel Ribbon, select the Data tab in the Sort & Filter group, and then press the Sort button to conduct a/an _______ sort.
multiple column sort
The Tab key moves the active cell to the
next cell in the row