WorksheetsMicrosoft Excel Quiz - Modules 1-7
Total questions: 177
Worksheet time: 2hrs 31mins
In the formula =SUM(A6:A9), which of the following best describes A6:A9?
argument
function
labels
active cells
Which of the following views shows how a worksheet will appear when printed?
Page Layout
Page Break Preview
Normal
Reading
If you discover an error immediately after you have confirmed a cell entry, which of the following could you do to reverse the error? Select all the options that apply.
Click the Undo button on the Quick Access toolbar.
Click the Cancel button on the Formula bar.
Press CTRL+Z.
Press CTRL+Y.
Which of the following keys do you press to copy selected cells while you drag and drop the selected cells to their new location?
ALT
SHIFT
TAB
CTRL
Which of the following formulas adds 25 and 10, and then multiplies the result by 50?
=25+10*50
=25+10x50
=50x25+10
=50*(25+10)
Which of the following tabs in the ribbon provides immediate access to worksheet print options such as changing orientation and scaling to fit?
Preview
Home
Page Layout
View
Which of the following options can you set to make sure the active worksheet will print on one page?
Zoom percentage
Scaling
Print selection
Gridlines
Which of the following is the temporary storage area that holds selections you copy or cut?
Clipboard
Backstage
Name box
Worksheet
To create a new, blank workbook, which of the following can you use? Select all the options that apply.
Click copy in Backstage view
Press CTRL+N
Click New in Backstage view
Press CTRL+W
When you double-click the right border of a column heading, which of the following occurs?
Excel hides the column to the left of the double-clicked border.
AutoFit resizes the column to the default width of 8.43 characters.
Excel adds a column to the right of the double-clicked border.
AutoFit resizes the column to accommodate the widest cell entry.
The default font size for worksheets is _____ points.
10
11
12
14
Which of the following is true about adding cell borders?
You cannot apply borders to all worksheet cells.
A cell border underlines the cell text, not the entire cell.
You can add a border to the left, top, right, or bottom edge of a cell or range.
Borders are always single or double black lines.
Your worksheet contains confidential information in column C; to prevent others who use your worksheet from seeing the data, you can _____ column C.
delete
undo
edit
hide
You receive a worksheet in which the rows are numbered 1, 2, 3, 5, 6. This means that row 4 is _____.
deleted
hidden
cut
conditionally formatted
Sam wants to count the number of cells between B1 and B20 that contain numbers in them. Which of the following formula should he use to do so?
=COUNT(B1:B20)
=COUNTFIF(B1:B20)
=COUNTNUM(B1:B20)
=COUNTNUMERIC(B1:B20)
Abdul needs to count the number of nonblank cells in the range of cells B1 to B20. Which of the following formulas should he use to do so?
=COUNT(B1:B20)
=COUNTFIF(B1:B20)
=COUNTA(B1:B20)
=DCOUNTA(B1:B20)
Which of the following statements is true about COUNT functions?
The COUNT function returns the number of calls in a range that are not blank.
The COUNT function returns the number of calls in a range that contain any data at all.
Using the COUNT function is useful for computing the average of a cell range.
The COUNT function returns the number of cells in a range that contain numeric data.
Ayanda entered customer first names in column A and last names in column B. In cell C2, they entered the corresponding full name by typing the first name from cell A2, pressing SPACEBAR, and then typing the last name from cell B2. What feature can they use next to enter the remaining full names in column C?
Business Intelligence
Flash Fill
Quick Analysis
What-if analysis
Which of the following indicates the column and row where a cell is located?
cell reference
active cell
column heading
formula bar
You can view the cell reference of the active cell in the _____ in the area above the worksheet grid.
formula bar
Name box
worksheet title
status bar
Your worksheet appears with a reduced view of each page and blue dividers where new pages begin. What view are you in?
Normal view
Page Layout view
Page Break Preview
Print screen in Backstage view
In a complex formula, how does Excel determine which calculation to perform first?
It calculates the leftmost formulas first.
It calculates operations outside parentheses first.
It follows the order of operations.
It calculates functions first.
Zaida stored the formula =SUM(E12:G12) into cell A12. She then copied and pasted the formula to the cell 3 rows directly below A12. Which of the following depicts the change to the cell references in the cell that contains the pasted contents?
=SUM(E12:G12)
=SUM(E15:G15)
=SUM(H12:J12)
=SUM(H15:J15)
Min has selected a cell range and then pasted the most recently copied formula into the range by using the Paste button in the Clipboard group. Which of the following applies to this situation?
A button appeared below the selected cells, providing options for pasting formulas and values.
They must adjust the cell references in the pasted formulas in the selected range.
The Auto Fill Options button appeared, providing options for pasting in the selected range.
They can preview the pasted formulas by pointing to each cell in the selected range.
Which of the following is not a way to move cell contents?
Click the Cut button, right-click the selection, then click the Paste button.
Press CTRL+X, right-click the selection, then press CTRL+V.
Press and hold CTRL while dragging selected cells to a new location.
Drag-and-drop selected cells to a new location.
Bohdan wants to cut the range A1:A5 and paste it to C1:C5. Which of the following statements is true? Select all the options that apply.
Before he pastes it, he needs to select C1:C5.
Before he pastes it, he only needs to select cell C1.
After he pastes it, the information is deleted from the Clipboard.
After he pastes it, the information is deleted from the original location.
Which of the following is not true about setting column width?
Column widths are expressed as the number of characters a column can contain.
The default column width is 8.43 standard-sized characters.
You can change the width of only one column at a time.
You can use the Format button in the Cells group to set an exact column width.
Which of the following is true about deleting a worksheet row?
After you delete a row, the rows below it shift down one row.
Deleting a row and clearing a row have the same results.
Deleting a row removes both the data and the selected cells from the worksheet.
Deleting a row removes the data but preserves the worksheet structure.
When you insert a column in a worksheet, what happens to formulas you already entered?
Excel updates the cell references in their fomulas to reflect the moved cell locations
Excel displays an error in the moved cells that contain formulas.
You need to manually update the cell references in the formulas of the moved cells.
Excel does not change the formulas in the moved cells.
After you delete a worksheet column, Excel removes the data from its cells and _____.
the cells to its right shift left to fill the vacated space
the cells below it shift up to fill the vacated space
preserves the worksheet structure
the cells to its left shift right to fill the vacated space
Semra wanted to rename a worksheet, so she double-clicked the sheet _____, typed a new name, and then pressed ENTER.
columns
rows
header
tab
To select nonadjacent cells or ranges on a worksheet, you can press and hold _____ while selecting each one.
ALT
SHIFT
ESC
CTRL
Which of the following type of data can you wrap within a cell?
text
numbers
dates
times
To preserve the original version of a workbook so you can make changes to a copy of it, which of the following would you do after opening the workbook?
Copy the cells below the last line of data, and then make changes.
Change the first worksheet, and save it using the same name.
Save it using the same name, and then make changes.
Save it using a different name, and then make changes.
Where do you rename a workbook and adjust its save location?
Save As dialog box
Home tab
Name box
Active cell
Which of the following is true about deleting a worksheet? Select all the options that apply.
You can right-click a sheet tab and then click Delete on the shortcut menu.
Deleting a worksheet deletes any text and data it contains.
You can click the Delete button in the Worksheet group on the Insert tab.
You cannot delete a worksheet from a workbook.
Which of the following helps you move different parts of a worksheet into view when the worksheet is too large to fit on the screen at once?
sheet tabs
arguments
scroll bars
operators
Which of the following is a fast and convenient way to enter a commonly-used function into a selected cell or range?
function injector
AutoSum button
argument
formula prefix (=)
The Percent style formats numbers as percentages with _____ decimal places by default.
one
two
three
zero
To format a range so that all values greater than $500 appear in red, which of the following can you use?
conditional formatting
cell formatting
cell styles
Quick Access toolbar
To change conditional formatting that applies a red fill color to one that applies a green fill color, which of the following can you do?
Delete the conditional formatting rule.
Edit the conditional formatting rule.
Format the range in the Font dialog box.
Format the range as a table.
Steffie needs a wider left margin in her worksheet. Which of the following can she do to change the margin?
On the Margins tab of the Page Setup dialog box, change the number in the Left box.
On the Page Layout tab, set the Width and Height to Automatic and the Scale to 100%.
On the Page tab of the Page Setup dialog box, change the Orientation setting.
On the Margins tab of the Page Setup dialog box, click the Preset button.
You can use the Find and Replace dialog box to find and replace _____.
cell formatting
cell references
font styles
worksheet names
Jacquan wants to use a cell style similar to the default Title style, but with a larger font size and different font color. What can he do?
Apply a different theme
Create a custom cell style
Transpose the Title style
Apply conditional formats
To apply a cell style, you would use the Cell Styles command on the _____ tab.
Layout
Insert
View
Home
To combine multiple cells into one combined cell and center the contents, which of the following do you use?
Column Width command
Center button
Merge and Center button
Increase Indent button
How can you distinguish between a manually added page break and an automatic page break in a worksheet?
Automatic page breaks appear as dashed lines while manual page breaks appear as solid lines
Automatic page breaks appear as curved lines while manual page breaks appear as jagged lines
Automatic page breaks appear as dashed lines while manual page breaks appear as wavy lines
Automatic page breaks appear as zigzag lines while manual page breaks appear as solid lines
What must you do before removing a page break?
Click the heading of the column containing the page break
Select a cell below or to the right of the page break
Click the heading of the row containing the page break
Set the print area
Marisol wants the date on her spreadsheet to include the weekday name, month name, day, and year. Which of the following formats should she use?
Date Time
Text
Short Date
Long Date
To copy a cell's formatting to another cell, which of the following can you use?
Format Cells dialog box
Format Painter
Quick Analysis Tool
Format as Table
You double-click the Format Painter button when you want to _____.
copy a cell's data to another cell
paste the same format multiple times
paste conditional formatting only
clear a cell's formatting
Eleanor wants to repeat the first row of the worksheet on each printed page of a four-page worksheet. Which of the following options should she use in the Page Setup dialog box?
Print titles
Print area
Gridlines
Row and column headings
Which of the following conditional formatting options can you use to highlight the top 10 values in a range of cells?
Icon Sets
Color Scales
Data Bars
Highlight Cells Rules
To format a cell range so that values between 100 and 500 appear in red, which of the following can you use?
icon sets
themes
Top/Bottom Rules
Highlight Cells Rules
Igor wants to set 1-inch margins on the left and right and 0.5-inch margins on the top and bottom of a printed page. Which of the following dialog boxes should he use to change the margins?
Footer dialog box
Page Setup dialog box
Format Cells dialog box
Workbook Setup dialog box
You can edit a worksheet footer in _____.
Page Layout view
Normal view
Page Break Preview
Print Preview
How can you enter your name so it appears in the lower-right corner of each page of the printout?
Click the header section on the right and type your name.
Click a cell in the lower-right part of the worksheet and type your name.
Open the Footer dialog box and type your name in the Right section box.
Switch to Print Preview and type your name in the lower-right corner of the preview.
To paste a data range so that column data appears in rows and row data appears in columns, you can _____ the data using the Paste list arrow.
reverse
transpose
fill
merge
Alba wants to use a watermark image as the background of her worksheet. She goes to the Page Setup group on the Page Layout tab. What option should she click to begin choosing the image file to use as a watermark?
Breaks
Margins
Orientation
Background
If you want to print only part of a worksheet, you can set a _____.
print area
print title
page break
scaling option
Nathan wants a formula to return "Yes" if the value in cell A1 is less than the value in cell B1, and to return "No" otherwise. Which of the following functions should he use?
IFERROR
VLOOKUP
IF
RAND
The score of a student is inserted in cell B2. The passing score for the subject is 60. Which of the following functions should you insert in cell C2 to check whether the student has passed or failed?
=IF(B2>=60, "Pass", "Fail")
=IF(B2 < 60, "Pass", "Fail")
=IF(B2 = 60, "Pass", "Fail")
=IFERROR(IF(B2 >= 60, "Pass", "Fail"))
Jason wants to average the sales in the range C3:C50, and then round the result to the nearest integer. Which of the following formulas should he use?
=ROUND(AVERAGE(C3:C50),0)
=AVERAGE(C3:C50), ROUND, 0))
=ROUND(C3:C50)
AVERAGE(ROUND(C3:C50),0)
You've copied a cell containing formula to the rows below it, and the results in the copied cells are all zeros. To find the problem, what should you check for in your original formula?
If it needs an absolute cell reference.
If it needs a relative cell reference.
If it needs landscape orientation.
If it needs a function.
Arlo wants to use Goal Seek to answer a what-if question in a worksheet. Which button should he click on the Data tab to access the Goal Seek command?
What-if Analysis
Data Validation
Consolidate
Relationships
You have selected a cell with a formula. Which of the following can you use to copy that formula to an adjacent cell?
mode indicator
Page Break Preview
scroll bar
Fill handle
Which of the following functions would you use to calculate the arithmetic mean of a price list?
MAX
COUNT
SUM
AVERAGE
Which of the following are true about entering a function using the Insert Function dialog box? Select all the options that apply.
You open the dialog box by typing "function" in the formula bar.
You can use the search tool in the Insert Function dialog box to find a function.
You can select a category to explore functions by type.
You can use the Insert Function dialog box to insert a function based on nearby formulas.
Cell A5 contains the number 24.7835. In another cell, Ariane wants to enter the number from cell A5 but rounded to two decimal places. Which of these formulas can she use to do so?
=ROUND(A5,2)
=MROUND(A5,2)
=ROUND(A5,2,0)
=MROUND(A5,0.2)
What is the correct formula to insert the date January 12, 1998 into a cell?
=DATE(1998,1,12)
=DATE(1998,12,1)
=DATE(12-1-1998)
=DATE(1/12/1998)
Ian needs to extract the day of the month from the date entered as a serial number in cell A1. Which formula can he use to do this?
=DAY(A1)
=DAY("A1")
=DATE(DAY(A1))
=DATE("DAY(A1)")
Which formula can you use to extract the month number from the date entered in cell F5 as July 8, 2016?
=MONTH(F5)
=MONTH("F5")
=MONTH(DATE(2016,7,0))
=MONTH(DAY(2016,7,0))
What does the third argument (3) refer to in the following formula: VLOOKUP(10005, A1:C6, 3, FALSE)?
number of the column containing the return value
location of the lookup table
cell with the lookup value
whether to return a value only when an exact match is found
Which of the following lets you extend any data pattern involving dates, times, numbers, or text?
AutoSum list
AutoFill Options button
Paste Options button
Quick Analysis
The IF function can use which of the following comparison operators? Select all the options that apply.
= (equal)
> (greater than)
* (multiplication)
<> (not equal)
Which of the following functions does Excel provide for rounding values? Select all the options that apply.
ROUNDIF
ROUNDDOWN
ROUNDUP
ROUND
Which of the following functions generate random values? Select all the options that apply.
RAND
RANDDOWN
RANDUP
RANDBETWEEN
Which Excel features can you use to extend a formula into a range with AutoFill? Select all the options that apply.
Fill handle
Fill button in the Editing group
Quick Analysis tool
AutoSum button in the Editing group
If the VLOOKUP function cannot find a match in the lookup table, what does it return?
"Match not found" message
#N/A error value
FALSE
#NAME error value
In column D of a worksheet, Cal lists the number of hours each employee works. What function should he use to find the employee who worked the most hours?
MIN
MEDIAN
INT
MAX
Which of the following is a type of logical function?
AVERAGE
ROUND
IF
VLOOKUP
To which of the following chart types can you not add axis titles?
Pie chart
Line chart
Area chart
Column chart
Which of the following chart types show how two numeric data series are related to each other?
Pie
Bar
Line
Scatter
Which of the following dialog boxes do you use to remove a data series from a chart?
Chart Elements dialog box
Format Data Source dialog box
Select Data Source dialog box
Insert Chart dialog box
If you see cells with blue fills of varying lengths representing the values, the cells most likely have ________ applied to them.
themes
icon sets
data bars
cell styles
Which of the following elements can you add to a chart to display a line representing the general direction in a data series?
Axes
Legend
Gridlines
Trendline
Which of the following changes can you apply to a chart legend? Select all the options that apply.
Position
Data Series colors
Fill color
Number formatting
Which of the following can you change in a combination chart? Select all the options that apply.
The bounds of the secondary axis
The major tick marks on the primary axis
The minor tick marks on all axes
The orientation of the axis titles
Which of the following statements describe a histogram chart? Select all the options that apply.
It is a column chart showing the distribution of values from a single data series.
Use it to display distribution of scores on an exam.
Data values are allocated to bins.
It includes a secondary line chart.
Which of the following options can you set when you create or edit a data bar conditional formatting rule? Select all the options that apply.
value used for the longest data bar
fill color of the data bars
how to display negative values
font color of the text
Annemarie lists 12 months of product sales data in the range A3:M7. The products are listed in the range A3:A7 and the monthly sales data in the range B3:M7. She wants to display a simple chart at the end of each row in column N to track the monthly sales for each product. What can she insert in the range N3:N7?
Line chart
Line sparklines
Scatter chart
Data bars
You can add data markers to sparklines to highlight which of the following values? Select all the options that apply.
low values
high values
ending values
negative values
Bree added data labels to a pie chart, where they appear on each slice. She wants the data labels to appear outside of the pie chart but close to each slice. Which Label Position option should she select for the data labels?
Center
Inside End
Outside End
Overlay
A _____ chart tracks the addition and subtraction of values within a sum.
waterfall
histogram
scatter
combo
Which type of sparklines can most clearly display the daily fluctuations of the selling price of goods or commodities?
Line
Win/Loss
Column
Combo
Which type of conditional formatting rule would you create to add a horizontal bar to a cell background, where the length of the bar reflects the value in the cell?
Top Values
Icon Sets
Color Scales
A label that appears as a text bubble attached to a data marker is called which of the following?
data callout
histogram
data bar
sparkline
Amir wants an exploded pie chart. Which of the following should he do?
Click the Explode Pie button
Drag the pie slice(s) away from the pie.
Right-click on the slice(s) of pie and select Explode Piece.
There is no way he can explode the pie chart.
To create a workbook containing text, formulas, macros, and formatting that you use repeatedly, you create a _____.
master
model
template
standard
Ricki has an Analysis workbook containing many worksheets of sales data that she wants to use in a new workbook. What is the easiest way for her to use the sales data in the new workbook?
Copy the cells containing sales data to the new workbook.
Insert hyperlinks from the Analysis workbook to the new workbook.
Copy the worksheets from the Analysis workbook to the new workbook.
Hide the worksheets in the Analysis workbook.
Joe wants to format several worksheets at the same time. What is the easiest way for him to perform this task?
Create a custom view of the worksheet
Group the worksheets
View the worksheets side by side
Link the worksheets
Text or an image you click to open a webpage or file is which of the following?
3-D reference
Hyperlink
ScreenTip
Template
Which of the following techniques can you use to remove hyperlink from a cell?
Click the Clear button on the Home tab and then click Remove Hyperlink
Click the Delete button on the Home tab and then click Remove Hyperlink
Right-click the hyperlink and then click Restore on the shortcut menu
Right-click the hyperlink and then click Remove Hyperlink on th shortcut menu
Calista wants to provide additional information about a hyperlink she is creating. Which of the following can she do?
Add a description
Add a ScreenTip
Change the Link to location
Add a HyperTip
How do you select a cell containing a hyperlink without activating the link?
Double-click the cell
Right-click the cell
Click the cell and then click Do Not Activate
Click the cell and then click Edit Hyperlink
A workbook template has which of the following file extensions?
.xltx
.xls
.xlsx
.xlst
What types of resources can you access using a hyperlink in Excel? Select all the options that apply.
Email address
Current cell
Worksheet in the workbook
Webpage
What information can you provide to create a link to an email address? Select all the options that apply.
Text to display
Email address
Text of the email message
Subject of the email message
Which of the following tasks can you perform using the Edit Links dialog box? Select all the options that apply.
Update each link.
Change a link's data source.
Change the formula containing the external reference.
Break a link.
When you select a range of values, you can create defined names based on the text in which of the following locations within the range? Select all the options that apply.
Top row
Left column
Same column
Specified row
Which of the following are external references? Select all the options that apply.
[Sales.xlsx]January!A14
'[Annual Sales.xlsx]January'!A14
January!A14
[Sales.xlsx]Jan:March!A14
In the Expenses workbook, Cassie defined the range D10:G10 with the name RentExpenses. Which of the following formulas can replace the formula =SUM(Expenses!D10:G10)?
=SUM([Expenses]!Rent)
=SUM(RentExpenses)
=SUM([RentExpenses], D10:G10)
=SUM(D10:G10)
You can use the Name Manager dialog box to view and manage named ranges, including the _____, which indicates where the named range is recognized.
scope
external reference
filter
extent
Which of the following is an acceptable name for a range in Excel?
Net-Income
Profit!
_TotalExpenses
Average
Which of the following is a simpler way to write the following formula: June!C5+July!C5+Aug!C5?
[June-Aug!]C5
June:Aug+C5
June:Aug!C5
(June:Aug), C5
After beginning a formula, what can you do instead of typing the syntax of a 3-D reference?
Click a sheet tab, click a cell range, and then press ENTER.
Copy a sheet tab and then press CTRL+V to paste it in the formula.
Use the fill handle to select the external range.
Click the Enter button on the formula bar, click a sheet tab, and then click a cell range.
When you use the Arrange All button to arrange more than one workbook window, which of the following layout options can you use? Select all the options that apply.
Tiled
Cascade
Scrolling
Vertical
If you click Existing File or Web Page in the Insert Hyperlink dialog box, select a workbook, click the Bookmark button, enter a cell reference, and then click OK, what are you linking to?
A hidden location in another workbook
A cell in another workbook
Another workbook file
A worksheet in another workbook
When you insert a defined name into a formula, Excel treats the defined name as a(n) _____.
relative cell reference
mixed cell reference
static cell reference
absolute cell reference
What happens when you click the sheet tab of a worksheet not included in a worksheet group?
You add the worksheet to the group.
You create a new group of the first and last worksheets.
You ungroup the worksheets.
The worksheet grouping does not change.
How does Excel indicate that worksheets are grouped? Select all the options that apply.
The word "Group" appears in large letters in the background of each worksheet.
The word "Group" is added to the title bar.
The sheet tab names are bold.
The word "Group" is added to the sheet tab names.
What can you do to a worksheet group to change each worksheet within the group? Select all the options that apply.
a. Enter formulas and data.
b. Change row heights and column widths.
c. Apply conditional formats.
d. Set view options.
When you arrange windows in a Vertical or Side by Side layout, you can more easily compare the two worksheets by using synchronized _____.
a. grouping
b. updating
c. inking
scrolling
Before Barry inserts new formulas in the range A5:F5, he wants to clear the cell contents. How can he do so?
a. Drag the fill handle from cell A5 to cell F5.
b. Select the range and then press DELETE.
c. Select the range and then click the Clear Formats button.
d. Select the range and then click Clear on the AutoFill Options menu.
To use defined names in existing formulas, click the Define Name arrow, and then click ________.
a. Get Defined Names
b. Apply Names
c. Use Defined Names
d. Named Ranges
Which of the following buttons on the Home tab can you use to insert a row in a table?
Format as Table
Insert & Merge
Insert
Add Row
Josh wants to insert a column in an Excel table to add the values from two other columns. Which of the following methods can he use to insert a table column?
Click a column header and then click the Insert button on the Home tab.
Right-click a column header, click Format Cells on the shortcut menu, and then click Add New Column.
Click a column header, click the filter arrow, and then click Insert.
Double-click a column header and then click Insert on the shortcut menu.
A range of data Excel treats as a single object that can be managed independently from other data in the workbook is a(n) __________.
Outline
Summary
Table
Dashboard
Kylie created a table with a Skill Number column that contains values from 400 to 500. What type of filter can she use to select records with skill numbers less than 450?
Text filter
Top Values filter
Number filter
Form filter
Shelley creates a table containing the marks of Language Arts students in her class with these columns: Names of Students and Marks. She now wants to see the names of students who scored exactly 60 marks. What will Shelley do after selecting the column header arrow for the column with heading Marks?
Check the box beside Select All and the number 60 and click OK.
Uncheck (Select All), select the box beside the number 60 and click OK.
Select Number Filters > Between > (Enter value 0 in dialog box on top) > (Enter value 60 in dialog box below it) > OK.
Select Number Filters > Less Than or Equal To > (Enter value 0 in dialog box on top) > (Enter value 60 in dialog box below it) > OK.
Adele wants to filter a Customers table to show customers in Denver or customers with invoices over $2500. What type of filter should she use?
Number AutoFilter
Advanced filter
Text with wildcard filter
Date AutoFilter
How can you hide the filter buttons in a table?
Click the Filter Button check box in the Table Styles Options group.
Clear the filters from the table.
Right-click a filter button and then click Remove.
Click a filter button and then click Hide.
An Excel table contains columns named Salary and Commission. Which of the following formulas uses a structural reference to sum the values in the Salary through Commission fields?
=SUM(Salary:Commission)
=SUM(Salary[Commission])
=SUM([Salary]:[Commission])
=SUM("Salary":"Commission")
Which of the following boxes will you check after clicking on a table and then selecting Table Tools > Design, to add a row to the table that will display summary statistics of the different table columns?
Total Row
Header Row
Footer Row
Banded Rows
Tracy created a table where the odd and even-numbered columns are formatted differently. She wants to remove this formatting. What setting should she change?
Banded columns
Calculated columns
Odd/Even columns
First column
Which of the following can help you quickly format a cell range with labels in the left column and top row, and totals in the bottom row?
table style
cell style
conditional formatting
data validation
Michelle finished a 5 kilometer run in 180th position. The organizers shared an Excel spreadsheet with names of all the participants and the time they took to complete the race. The top 15 finishers are listed in rows 2 to 16. Michelle wants to compare her time against theirs. How can she do so?
Select row 17 and click View tab > Window group > Split.
Select rows 2 to 16 and click View tab > Window group > Split.
Select rows 2 to 16 and click View tab > Window group > New Window > Split.
Select row 16 and click View tab > Window group > Split > Arrange All.
Helen wants to resize slicer buttons to exact dimensions. What can she use to do so?
Slicer Settings button in the Slicer group
Height and Width boxes in the Buttons group
Slicer Styles gallery
Slicer Size box in the Slicer group
Leigh-Ann used a slicer to filter a table on the Department field. Which of the following buttons should she click on the slicer to redisplay data for all the departments?
Multi-Select button
Clear Filter button
Slicer Settings button
Slicer Styles button
Will has a table that includes a field with four product types. What kind of filter would be best to create for the table?
Date filter
Custom number filter
Slicer
Advanced filter
How can you remove the split bars from a worksheet?
Click the View tab, and then click the Split button in the Windows group.
Click the Page Layout tab, and then click the Bring Forward button in the Arrange group.
Click the View tab, and then click the Hide button in the Windows group.
Click the Page layout tab, and then click the Remove Breaks button in the Page Setup group.
Which of the following do Excel tables provide that data ranges do not? Select all the options that apply.
Built-in sorting and filtering tools
What-if analysis tools
Styles that format different parts of the table
Automatic subtotal rows
Which of the following can you apply to an Excel table? Select all the options that apply.
Header row
Total column
Banded rows
Table name
Which of the following are ways to format the appearance of a slicer? Select all the options that apply.
Number of columns
Slicer size
Button size
Slicer style
Which of the following are ways Excel provides to identify duplicate records in a table? Select all the options that apply.
Duplicate Values Wizard
Remove Duplicates tool
Circle Duplicates tool
Highlight Duplicate Values conditional formatting
Which of the following parts of a worksheet can you freeze? Select all the options that apply.
top row
first column
middle column
specified pane
In the formula =[SalesPrice]*.05, what do you call [SalesPrice]?
3-D reference
external reference
structural reference
absolute reference
To use the SUBTOTAL function to calculate the sum of filtered table records, set the Function_Num argument to _____.
a. 1 for AVERAGE
b. 9 for SUM
c. 2 for COUNT
d. 10 for TOTAL
Which of the following summary statistics can you display in a total row in an Excel table? Select all the options that apply.
a. lookup
b. average
c. minimum
d. sum
After splitting a worksheet window into four panes, how can you display a single pane? Select all the options that apply.
a. Freeze the top row
b. Click the Split button on the View tab
c. Double-click the split bar
d. Click the Arrange All button on the View tab
Which of the following are options you can select when using the Find and Replace dialog box to find text? Select all the options that apply.
a. Type of criteria
b. Look in
c. Match case
d. Within
Gwen wants to locate cells that contain the date 11/14/21. Which of the following tools should she use?
a. Locate & Change
b. Find & Select
c. Fill
d. Filter
If you want to sort data first by department, and then by last name, the last name field is the _____ sort field.
a. primary
b. limited
c. secondary
d. minor
Dmitri created a table of employee data and wants to list the records in order by most recent hire date. Which of the following should he do?
a. Sort the records in descending order by hire date
b. Sort the records in ascending order by hire date
c. Sort the records first by name and then by hire date
d. Filter the records by hire date
Stacey filtered a table on the Product Type field and now wants to filter on the Price field instead. What should she do next?
a. Click a filter button and then click Price
b. Clear the existing filter from the table
c. Use the Number filter
d. Sort by the Price field
To explicitly indicate that a range contains fields and records, you create a(n) _____.
a. outline
b. table
c. advanced filter
d. chart
Which of the following is an unacceptable name for an Excel table?
a. Employee Tbl
b. _SalesTable
c. (Customers)
d. ProductTable
Antonio wants to calculate the sum of values in the Order Amt column in a table. What should he do?
a. Add a total row and then choose Sum in the Order Amt column.
b. Add a caclculated field to the table that uses the SUM function.
c. Use the SUBTOTAL function at the bottom of the Order Amt column.
d. Add a total column and then choose Total to calculate all tot
Edwin wants to insert a PivotChart to summarize sales data. On which of the following should he base the new PivotChart?
an existing PivotTable
another PivotChart
a column or bar chart on another worksheet
a slicer on the same worksheet
Which of the following groups and summarizes data in a concise format of rows and columns?
Area chart
PivotTable
Slicer
Filter
To which of the following locations can you move a PivotChart?
New workbook
Worksheet in the current workbook
Worksheet in a different workbook
You cannot move a PivotChart
Which of these is the default layout for a newly created Pivot table?
Compact Form
Outline Form
Tabular Form
Chart Form
Which of the following is the default name assigned to the first PivotTable in a workbook?
Table1
New PivotTable
PivotTable1
Pivot1
Which of the following is the default function Excel uses to summarize non-numeric data in a PivotTable?
SUM
COUNT
MIN
VAR
The scores of a student in two subjects are inserted in cells B2 and C2. The passing score for each subject is 60. Which of the following formulas returns TRUE if at least one score is greater than or equal to 60, or else it returns FALSE?
=IF(B2>=60, C2>=60)
=OR(B2>=60, C2>=60)
=AND(B2>=60, C2>=60)
=NOT(OR(B2>=60, C2>=60))
The value in cell B2 cell is 65. In cell C2, the value is 75. Which of the following formulas returns FALSE if any of the conditions are false for the values in cells B2 and C2?
=AND(B2>=60, C2>=60)
=OR(B2>=60, C2>=60)
=IF(B2>=60, C2>=60)
=NOT(AND(B2>=60, C2>=60))
Which of the following functions would you use to find the column location of specified text?
MATCH
INDEX
VLOOKUP
HLOOKUP
If a worksheet arranges lookup values in rows rather than columns, which function is best for retrieving data from the lookup table?
VLOOKUP
HLOOKUP
INDEX
MATCH
The _____ function returns the value from a range of data at the intersection of the specified row and column indexes.
INDEX
MATCH
LOOKUP
ARRAY
How can you drill down a PivotTable to display detailed data?
Double-click a cell in the PivotTable.
Right-click a cell in the PivotTable, and then click Details.
Add a field to the Drill area of the PivotTable.
Click the minus button next to a field in the PivotTable.
Which of the following formatting options can you apply to PivotCharts? Select all the options that apply.
Quick Layout
chart style
show/hide field buttons
value number format
What must you do to hide the field buttons of a PivotTable?
Click Field Buttons on the PivotChart Analyze tab.
Right-click the field buttons and select Delete.
Remove any slicers.
Remove the fields from the PivotTable.
Which of the following functions do you often use with the INDEX function to find data from a specified row and column of data?
MATCH
OR
HLOOKUP
RANGE
Which of the following are functions that calculate statistics only on those cells that match a logical condition? Select all the options that apply.
IF
COUNTIF
SUMIF
AVERAGEIF
To apply a slicer to multiple PivotTables, which of the following should you do?
Copy the slicer and connect them individually to each PivotTables.
Click the Report Connections on the Slicer tab and check the appropriate PivotTables.
Right-click the slicer and select Connect to All PivotTables.
A slicer can only be connected to one PivotTable.
Roberta added a calculated field to the Values area of a PivotTable, where it appears as "Sum of ORDERS." Where can she change the text displayed for this field?
Number Format dialog box
Calculated Field Format dialog box
Value Field Settings dialog box
PivotTable Options dialog box
Kevin formatted the values in a PivotTable by selecting the values and then clicking the Accounting Number Format button on the Home tab. When he changed the layout of the PivotTable, he lost the number formatting. Which dialog box should he use instead to ensure the Accounting Number Format persists?
Format Cells dialog box
Value Field Settings dialog box
PivotTable Format dialog box
Value Properties dialog box
