Comprehensive Spreadsheets Exam (mods 1-4)
Your worksheet contains confidential information in column C; to prevent others who use your worksheet from seeing the data, you can _____ column C.
hide
What is a way to align cell contents in relation to cell edges?
left
What is true about entering a function using the Insert Function dialog box?
1. You can search for a function that meets your needs. 2. You don't need to type an equal sign to begin the formula. 3. You can select a function you've recently used.
To paste copied cell contents with values only, not including formatting, which of the following can you use? Correct!
1. Paste Preview 2. Paste option icon 3. Paste list arrow
To create a new, blank workbook, what can you use?
1. The New command in Backstage view. 2. The New command on the File tab.
Bart needs to apply a sparkline style to a sparkline chart. To do so, he can click on the following: Sparkline chart > Design > Style (choose a style).
F
Changing a worksheet window to Normal view changes the contents of the worksheet.
F
Clicking the Increase Decimal button in the Number group would change the displayed cell value 14.54 to 14.5.
F
Decreasing a cell's indent moves its contents to the right one space.
F
Henri wants to convert a workbook to PDF so that he can print it in high quality using a commercial printer. He can do so by clicking on Page Layout > Save As > (Enter name in ) File name box > Save As type list > PDF > Minimum Size (publishing online) > Options > OK > Save.
F
If you specify that you want worksheet gridlines to appear on the screen, they will automatically appear on the printout as well.
F
In formulas, calculations in square brackets are calculated first.
F
Jim frequently prints out the same section of a worksheet. To do so, he selects the cells he wants to define as print area, then clicks Print Area and then clicks Set Print Area on the Page Layout tab in the Scale to Fit group.
F
Penina wants to insert markers into a sparkline chart. To do so, she can click on the following: Sparkline chart > Design > Line > Markers check box.
F
The Comma Style format adds a comma and a dollar sign to a cell value.
F
To change the background color of a cell, you use the Background color button.
F
To check your workbook for features that assist people with disabilities, you can use the Ability Checker.
F
To combine multiple cells into one and center its content, you change the cells' style.
F
To enter a typed number into a cell, you can press Tab or Backspace.
F
To find the largest value in a cell range, use the MIN function.
F
To preserve an exact cell address in a copied formula, you can use a relative cell reference.
F
To print worksheet contents across the length of a page, you can use portrait orientation.
F
To see how copied contents will look with both values and number formatting, you would use Paste Numbers.
F
To share a protected workbook, place it on a network location, then click Review > Share Workbook > Editing tab > (Select) Allow changes by more than one user > Advanced tab > (Select) options for tracking and updating changes > OK > File > Save As > Share.
F
To view a worksheet without headers, margins, and rulers, use Page Layout view.
F
When you change cell contents to italics, you are changing the cell's alignment.
F
When you copy and paste a formula with absolute cell references, Excel substitutes new references to reflect the new formula location.
F
When you copy cell contents, the data is removed from its original location and placed on the Clipboard.
F
When you enter a value in a worksheet cell, it is automatically left-aligned.
F
When you run the Spell Checker in a workbook, Excel automatically checks the spelling in all worksheets at once.
F
You can use the I-beam pointer to copy cell contents into adjacent cells.
F
Which of the following options should one choose to prompt Excel to calculate all open workbooks manually?
F9
To print more than one copy of your worksheet, which tab would you go to?
File
What tab lets you set worksheet print options?
File
What tabs lets you see exactly how your worksheet looks before printing?
File
On which tab and button are document properties located for editing?
File tab, Info Button
You have selected a cell with a formula. Which of the following can you use to copy that formula to an adjacent cell?
Fill handle
To enter a range of text based on examples that are already in your worksheet, which of the following can you use?
Flash fill
To change the color of cell content, you use the ________ color list arrow.
Font
What should be chosen as X in the given series of clicks to calculate formulas automatically except for data tables: File < Options < X < Automatic?
Formulas
What should be selected as X in the given series of clicks to set formulas for manual calculation: File < Options < X < Manual?
Formulas
What would you choose as X in the given series of clicks to calculate formulas automatically: File < Options < X < Automatic?
Formulas
Which of the following is a built-in formula that helps you perform complex calculations?
Function
To format a cell range so that values between 100 and 500 appear in red, which of the following can you use?
Highlight Cells Rules
To apply a cell style, you would use the Cell Styles command on the _____ tab.
Home
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.
Where can you see a brief description of a selected function?
Insert Function dialog box
Which of the following lets you search for a function or select one from a category?
Insert Function dialog box
What would you select as X in the given series of clicks to insert a pie chart: Insert > Charts group > X >?
Insert Pie or Doughnut Chart
In a complex formula, how does Excel determine which calculation to perform first?
It follows the order of operations
Why is it necessary to use care when inserting worksheet cells?
It may disturb row or column alignment, which can affect worksheet accuracy.
To have Excel enter the lowest price from a range of prices, which of the following would you use?
MIN function
To combine multiple cells into one combined cell, which of the following do you use?
Merge & Center button
To print your worksheet at its actual size, which of the following would you select?
No scaling
What would you select as X in the following series of clicks/entries to change the number format of a chart axis: (Double-click) Value Axis labels > Format Axis > Axis Options > X > Select choice in Category box?
Number
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?
Open the workbook, make changes, and then save it using a different name.
Your worksheet appears with a reduced view of each page and blue dividers where new pages begin. What view are you in?
Page Break Preview
To print your worksheet on a piece of paper larger than 8-1/2 x 11", which Excel tab would you use?
Page Layout
To see a preview of how a pasted value will look, which of the following would you use?
Paste List arrow
To which of the following chart types can you not add axis titles?
Pie chart
Where can you see a preview of how your worksheet will look when printed, including headers?
Print tab in Backstage view
In the formula =A8*$A$1, which of the following describes A8?
Relative cell reference
Nisha wants to freeze all rows above row 4 in a worksheet she is working on to keep them visible while scrolling through the rest of the worksheet. What series of clicks will help her do so?
Row 4 > View tab > Windows group > Freeze Panes > Freeze Panes
Which of the following functions inserts the total of a range?
SUM
Where do you rename a workbook and adjust its save location?
Save As dialog box
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
What would you do in step X in the series of clicks/ selections to delete a vertical page break: View tab > Workbook View > Page Break Preview > X > Page layout tab > Page Setup > Breaks > Remove Page Break?
Select the column to the right of the page break you want to delete.
Myra wants to hide a worksheet to remove it from view. Which of these series of clicks will help her do so?
Sheet tab > Home tab > Cells group > Format > Visibility > Hide & Unhide > Hide Sheet
What would you select as X in the following series of clicks to change the chart style: Chart > Chart Style > X > Any Chart Style?
Style
A student wants to insert a column sparkline in Excel. To do this, she can click on the cell she wants to insert a Sparkline, then click Insert tab, select the Sparkline type of choice from the Sparklines group and then select OK.
T
Arrow keys can be used to move any chart element.
T
Binay wants to create an X-Y scatter plot. To do so, he should select the data he wants to plot in the scatter chart, then click the Insert tab, and then click Insert Scatter (X, Y) or Bubble Chart.
T
Currency is an example of a number format.
T
Dean wants to insert five blank rows above a row. To do so, he can select the heading of the row above which he wants to insert the additional rows, hold Ctrl key, click the selected rows, and then click Insert on the pop-up menu.
T
Ina wants to protect a worksheet with a password to allow changes by other users in only selected elements. To do so, she can first unlock editable cells, then click Review tab > Protect Sheet > Allow all users of this worksheet to list > Select the elements you want people to be able to change > OK.
T
Jack wants to insert a Win/Loss sparkline in Excel. To do this, he can click on the cell he wants to insert a Sparkline in, then click Apps tab, select Win/Loss Sparkline from the Sparklines group and then click OK.
T
Jose inserts the formula '=DATE('2000, 2, 2)' in cell AI5. Upon pressing ENTER, 2/2/2000 will appear in AI5.
T
Kai wants to hide a worksheet with a password to prevent other users from viewing it. To do so, he can Click Review > Protect Workbook > Enter a password in the Password box > OK. He can then retype the password to confirm it, and then click OK.
T
Niki wants to insert a Line sparkline in Excel. To do this, she can click on the cell she wants to insert a Sparkline, then click Insert tab, select Line Sparkline from the Sparklines group and then OK.
T
One can create a one-variable data table in Excel to test a series of values for a single input cell and see the influence of these values on the result of a related formula.
T
The easiest way to insert the SUM function is to use the AutoSum button on the Home tab.
T
To apply formats to data when it meets criteria you specify, you can use conditional formatting.
T
To customize the status bar, right click on the status bar to open the Customize Status Bar menu and uncheck the items that you don't want to show.
T
To edit placeholder text in a chart element, select the cell whose placeholder text you want to edit, on Ribbon, click Data > Data Validation > Data Validation > Input Message tab and edit the text in the box.
T
To enter data in a cell using examples already in your workbook, you can use the flash fill feature.
T
To fit all worksheet content on one page, you can set page scaling in Backstage view.
T
To format the cell value 44.54 as 44.540, you can use the IncreaseDecimal button.
T
To insert an equation, click on the Object in the Text group on the Insert tab, then click Microsoft Equation 3.0 in the Object type box, and then click OK.
T
To insert the current date in cell B1, type CURRENT DATE in cell A1 and NOW() in cell B1 and press ENTER.
T
To lock certain cells in a worksheet to protect them, select the cells, then click Home tab > Alignment group > Format Cells popup window > Protection tab > (select) Locked check box > OK to close the popup window. Then click on Review tab > Changes group > Protect Sheet.
T
To make your workbooks usable by people with disabilities, you can add alternative text to worksheet images.
T
To paste only a copied cell's formats, use the Paste Special command.
T
To print a worksheet, you begin by going to Backstage view.
T
To remove conditional formatting from a selected range, you can select the rule in the Conditional Formatting Rules Manager dialog box, then click the Delete Rule button.
T
To show formulas instead of values, click on the Formulas tab in the ribbon, then in the Formula Auditing group, click on the Show Formulas option.
T
To unprotect a workbook, open the Excel file, then click on File > Info > Protect Workbook > Encrypt with Password > OK.
T
To unprotect a workbook, open the Excel file, then click on Review > Unprotected Sheet, then enter the password for the unprotected sheet and click OK.
T
Ursula wants to freeze the header row so that it remains visible while she scrolls down the worksheet. To do so, she can click the View tab and then click Freeze Top Row.
T
When you copy and paste a formula to a new location, the formula's relative references do not change.
T
While creating a two-variable data table in Excel, you need to enter two ranges of possible input values, one in a row and another in a column.
T
You can apply date formats to cells by using the Date category in the Format Cells dialog box.
T
You can edit cell contents in the Formula bar or in the cell itself.
T
You can start Excel using the Start button on the Windows taskbar or a shortcut on your desktop.
T
You can use the DELETE key to clear cell contents
T
Which of the following is true when you copy and paste formulas using the fill handle?
The AutoFill Options button lets you fill cells with specific elements of the copied cell.
What is true about COUNT functions?
The COUNT function returns the number of calls in a range that contain numeric da
When you paste formula cells using the Paste button in the Clipboard group, which of the following is true?
The Paste Options button then lets you paste specific elements of the copied cells
If you discover an error immediately after you have confirmed a cell entry, what would you use next?
The Undo button on the Quick Access toolbar
What formulas totals the cells B6, B7, B8, B9, and B10 most efficiently?
The formula =SUM(B6:B10)
For which of the following would you use the Paste list arrow on the Ribbon when pasting a copied cell?
To paste the value's number formatting only.
Which of the following will you select as X in the series of clicks to highlight the top 10 items in a range of cells: (Select) Range of cells > Home tab > Styles group > Conditional formatting > X > Top 10 items > Change value and highlight option in dialog box if required > OK?
Top/Bottom Rules
How can you remove data labels from a chart?
Uncheck the box beside Data Labels in Chart Elements
Boris wants to remove a workbook window from his workplace. What series of clicks can he use to hide it?+B12:D44
View tab > Window group > Hide
Carol wants to unhide a workbook window she had hidden earlier. Which of these series of clicks can she press to do so?
View tab > Window group > Unhide
Sala wants to unfreeze the first column of his worksheet. What series of clicks will help him do so?
View tab > Windows group > Freeze Panes > Unfreeze Panes
What is true when you delete a cell?
When you delete cells using the Delete list arrow, you can choose which way to move adjacent cells.
When should you use relative cell references?
When you want to preserve the relationship to the formula location
A _______ refers to a collection of characters with a similar, specific design.
font
You receive a worksheet in which the rows are numbered 1, 2, 3, 5,6. This means that row 4 is _____.
hidden
To align currency symbols and decimal points in a column of numbers, you can apply the ____ number format.
Accounting
A predesigned combination of formats, such as font size and color, is called a cell default.
F
You have cut the range A1:A5, and want to paste it to C1:C5. What is true?
1. Before you paste it, you only need to select cell C1 2. After you paste it, the information is deleted from the original location.
What lets you apply bold formatting to a selected cell?
1. Home tab 2. Format Cells dialog box 3. Mini toolbar
What can you use to insert a formula using a function?
1. Insert Function dialog box 2. AutoSum button on the Ribbon 3. AutoSum list arrow on the Ribbon 4. Copy and paste a cell containing a function
Your worksheet contains a price in cell A5, and many formulas refer to that price. How would you refer to that price in the formulas?
$A$5
What is true about deleting a worksheet? Select all the options that apply.
1. You can use the Delete list arrow in the Cells group on the Home tab. 2. You can right-click a sheet tab and click Delete.
How many actions does the Undo button let you reverse?
100
The default worksheet text is _____-point Calibri.
11
Clicking the Percentage style in the Number Format list applies _____ decimal places by default.
2
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)
A student is given the task of counting the number of nonblank cells in the range of cells B1 to B20. Which of the following formulas should he use to do so?
=COUNTA(B1:B20)
The score of five students in a subject are inserted in row B from B2 to B6 cells and the grades for the scores are: A: 90 to 100, B: 80 to 89, C: 70 to 79, D: 60 to 69, and F: 0 to 59. Which of the following functions can you insert in the D2 cell so that it returns the grade for each score in row B?
=IF(B2<60,"F",IF(B2<70,"D",IF(B2<80,"C",IF(B2<90,"B","A"))))
The score of students in a subject is inserted in B2 cell and the passing score for the subject is 60. Which of these functions will you insert in C2 cell to check if the student has passed or failed?
=IF(B2>=60, "Pass", "Fail")
A student wants to round the number 24.7835 entered in cell A2 to two decimal places. Which of these formulas can she use to do so?
=ROUND(A5,2)
Nathan wants TRUE to be elicited if either of the statements are true, and FALSE if both are true. Which of the following formula can he use to ensure that?
=XOR(...)
Which of the following is true about adding cell borders?
A cell border extends the width of the cell
Which of the following functions would you use to calculate the arithmetic mean of a price list?
AVERAGE
In the formula =B6*$B$2, which of the following describes $B$2?
Absolute cell reference
What would you select as X in the following series of clicks to change the chart layout for lines or bars that you want to change: Chart > Chart Tools > Layout tab > X > Layout option for any lines or bars that you want to add or change?
Analysis
When you double-click a cell's right column border, which of the following occurs?
AutoFit resizes the column to the widest cell entry.
What feature inserts a function that totals the cells above or to the left of the selected cell?
AutoSum
Which of the following inserts a function into a cell?
AutoSum 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.
Which of the following would you use to print only one page of a multipage worksheet?
Backstage view
When you cut or copy a cell, it is cut or copied to what?
Both the Windows and Office Clipboard
Amanda needs to manually calculate an open worksheet. Which of the following options should she click on in the Formulas tab in the Calculation group?
Calculate Now
Which of the following will you select as X in the following series of clicks to lay the title over a chart: Chart Title box > Plus (+) sign > Arrow next to Chart title > X?
Centered Overlay
To accept a suggested spelling for that instance only, which of the following would you click?
Change
The spell checker flags your company's name as a misspelling. Which of the following will stop the spell checker from flagging later occurrences in the document?
Change All
Your worksheet is too wide to fit on one portrait page. What is the best way to fix this problem?
Change the page orientation to landscape
To return cell contents to its default font and style, you can use the _____ command.
Clear Formats
Steffie wants to change the margins of a worksheet to Normal to align it better on a printed page. To do this, what can she do after selecting the worksheet for this purpose?
Click Margins in the Page Setup group on the Page Layout tab and then click on Normal.
Eleanor wants a title row to be printed at the top of every sheet when she prints her data in an MS Excel file. What will she do in step X in the following series of clicks/tasks to do so: Page Layout tab > Print Titles > Page Setup Dialog box > X > OK?
Click on the select button in Rows to repeat at top area and select a title row.
How can you remove Split panes in a worksheet you are working on?
Click the View tab in the Ribbon, then click the Split button in the Window group.
To select a single worksheet cell so you can work with it, which of the following would you do?
Click the cell
Which of the following is the temporary Windows storage area that holds selections you copy or cut?
Clipboard
To help you easily identify sheets in a workbook, you can add _____ to the sheet tab.
Color
What should you select as X in the following series of clicks to change the chart type to a clustered column chart: Chart > Chart Tools > Design tab > Type group > Change Chart Type > All Charts > X > Clustered Column > OK?
Column
Which of the following would you use to create a duplicate of the selected cell when the CTRL key is pressed?
Copy pointer
Bertie wants to show data labels inside a text bubble shape. Which option can she click on after clicking on the arrow next to Data labels under Chart Elements?
Data Callout
What lets you edit the contents of a cell?
Double-click the cell or click in the formula bar.
To change a range's conditional formatting from data bars to icon sets, which of the following can you do?
Edit the conditional formatting rule
Which of the following is true about inserting cells in a worksheet?
Excel automatically adjusts cell references to reflect new formula locations
Helga wants to reset page breaks in a worksheet to display only automatic page breaks. What can she select as X and Y in the series of clicks to do so: Worksheet > View tab > Workbook Views group > X > Page Layout tab > Page Setup group > Y > Reset All Page Breaks?
X- Page Break Preview, Y- Breaks
What is true of the Excel spell checker?
You can change all instances of a flagged word to a selected suggestion.
Which of the following is true about moving cell contents?
You can move cells using the drag-and-drop feature
When you underline cell content, you are using _____.
a font style
In the formula =SUM(A6:A9), which of the following best describes A6?
argument
To format a range so that all values greater than $500 appear in red, which of the following can you use?
conditional formatting
Which of the following is not a way to move cell contents?
the fill handle in the lower-right corner of an active cell or range