Excel module 1, 2, 3
When should you use relative cell references?
When you want to preserve the relationship to the formula location.
Why is it necessary to use care when inserting worksheet cells?
It may disturb row or column alignment, which can affect worksheet accuracy.
Rohan wants to create a column containing dates and want to display days beside each date in the next column. He can use the formula '=CHOOSE(WEEKDAY(B5),"Mon", "Tue", "Wed", "Thur", "Fri", "Sat")'.
True
The formula =ROUNDUP(G5,0) can be used to round up 59.425, entered in cell G5, to 0.
True
In the formula =B6*$B$2, which of the following describes $B$2?
Absolute cell reference
Which of the following is true about moving cell contents?
You can move cells using the drag-and-drop feature.
Which formula can you use to extract the month from the date in cell F5: 2016 July 8th?
=MONTH(DATE(2016,7,0))
Which of the following functions would you use to calculate the arithmetic mean of a price list?
AVERAGE
To return cell contents to its default font and style, you can use the _____ command.
Clear Formats
Which of the following is the temporary Windows storage area that holds selections you copy or cut?
Clipboard
To apply a cell style, you would use the Cell Styles command on the _____ tab.
Home
How many actions does the Undo button let you reverse?
100
On which tab and button are document properties located for editing?
File tab, Info button
Which of the following are ways to align cell contents in relation to cell edges? Select all the options that apply.
Left Center
To have Excel enter the lowest price from a range of prices, which of the following would you use?
MIN function
To view a worksheet without headers, margins, and rulers, use Page Layout view.
Normal
The Clipboard task pane shows the contents of the _____.
Office Clipboard
Which worksheet view shows headers, footers, and rulers?
Page Layout view
Where do you rename a workbook and adjust its save location?
Save As dialog box
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
To choose a standard theme color, click on the Insert tab, click Colors, then pick the color set you want.
False
To enter a typed number into a cell, you can press Tab or Backspace.
False
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
Which of the following should be selected as X in the series of clicks to save a custom theme: X > Themes group > Themes > Custom > Document theme you want to use?
Page Layout
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.
Igor wants to set margins for a worksheet he has created per his choice. Which of these will he select as X and Y in the series of clicks to do so: Page Layout tab > Page Setup > X > Margins drop-down list > Y > Specify choice for Top, Header, Left, Right, Bottom and Footer text boxes > OK?
X-Margins, Y-Custom Margins
To paste copied cell contents with values only, not including formatting, which of the following can you use?
Paste list arrow Paste option icon Paste 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.
Transpose
A student is asked to find the difference between two values of time entered in cells A1 and B1, where A1 is the greater value and B1 the lesser value. In order to do so, he will type the formula =A1-B1 in an empty cell.
True
Which of the following lets you edit the contents of a cell?
Double-click the cell or click in the formula bar.
To move a worksheet, you can drag its sheet tab to the left or right.
True
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
Which formula can be applied for the given data in a cell A2: 01/01/2017 to get the serial number of the date as text?
=DATEVALUE(''01/01/2017'')
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(...)
A worksheet named Income (2) is most likely which of the following?
A copy of another worksheet named Income
What feature inserts a function that totals the cells above or to the left of the selected cell?
Absolute reference
Which of the following lets you fill a cell with only specific elements of a copied cell?
Auto Fill Options button
When you double-click a cell's right column border, which of the following occurs?
AutoFit resizes the column to the widest cell entry
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)
To help you easily identify sheets in a workbook, you can add _____ to the sheet tab.
Color
Which of the following would you use to create a duplicate of the selected cell when the CTRL key is pressed?
Copy pointer
A predesigned combination of formats, such as font size and color, is called a cell default.
False
A student inserts the formula =TIMEVALUE("10:30 AM") in a cell. On pressing ENTER, it will return the value 10:30.
False
Changing a worksheet window to Normal view changes the contents of the worksheet.
False
Decreasing a cell's indent moves its contents to the right one space.
False
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.
False
Stuart inserts the formula '=YEAR ('23-Aug-2012)' into cell B12. When he presses ENTER, 23/08/2012 will appear in B12.
False
The formula =ROUNDDOWN(G5,0) can be used to round the number 46.734, entered in cell G5, to the nearest whole number.
False
To add a worksheet to a workbook, you would use the New formula tab below the worksheet.
False
To change the background color of a cell, you use the Background color button.
False
To combine multiple cells into one and center its content, you change the cells' style.
False
To create your own theme color, click on Page Layout, then Colors, then Customize Colors, then click the button next to the theme color you want to change, and finally pick a color under Theme Colors.
False
To find the largest value in a cell range, use the MIN function.
False
To paste a copied cell range, you only need to specify the upper right of the range where you want to paste it.
False
To preserve an exact cell address in a copied formula, you can use a relative cell reference.
False
To round time 10:30 pm inserted in cell A4 to the nearest 30-minute interval, the formula: =MROUND(A4,0:30) can be used.
False
To copy a cell's formatting to another cell, which of the following can you use?
Format painter
Where can you see a brief description of a selected function?
Insert Function dialog box
In a complex formula, how does Excel determine which calculation to perform first?
It follows the order of operations.
To combine multiple cells into one combined cell, which of the following do you use?
Merge and Center button
Musa wants to choose a standard color theme for an Excel worksheet he is working on. He clicks on the Page Layout tab and then clicks Colors. What should he do next to complete the procedure?
Pick the color set he wants.
You can create conditional cell formats using either the Home tab or the _____.
Quick Analysis tool
Which of the following functions inserts the total of a range?
SUM
Which of these will 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
Which of these will 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.
To change the name of a worksheet, you rename the _____.
Sheet tab
Which of the following appears in Page Layout view?
Text box containing the header Margins Page number indicator
After you delete a worksheet column, _____.
The columns to its right shift left.
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
A student types the formula '= YEAR( TODAY())-2005' to calculate the age of a person born in 2005. This formula will use the TODAY function as an argument for the YEAR function to obtain the current year, and then subtract 2005 returning the person's age.
True
Jose inserts the formula '=DATE('2000, 2, 2)' in cell AI5. Upon pressing ENTER, 2/2/2000 will appear in AI5.
True
Kamala enters a date as the start date in cell E2 and a list of holidays in cells F2:F5. Now she wants to add 30 workdays to the start date. She can do this using the following formula: = WORKDAY (E2, 30, F2:F5).
True
To extract the seconds from the time entered in cell B1 as 12:00:52 PM, type =SECOND(B1) in cell C1 and press ENTER.
True
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.
True
To insert information that appears at the bottom of every worksheet page, you can use a command on the Insert tab.
True
To insert the current date in cell B1, type CURRENT DATE in cell A1 and NOW() in cell B1 and press ENTER.
True
To open a workbook after you start Excel, you can use the Open Other Workbooks command on the Navigation bar.
True
You can edit cell contents in the Formula bar or in the cell itself.
True
You can repeatedly paste an item from the Office Clipboard as many times as you like.
True
You can start Excel using the Start button on the Windows taskbar or a shortcut on your desktop.
True
Clicking the Percentage style in the Number Format list applies _____ decimal places by default.
Two
Betty wants to choose the list to create a custom number format. To do so, what should she choose after the following series of clicks? Home tab < Number group < Number Format list < More Number Formats < Format Cells dialog box < Category < Custom
Type
Arlo wants to use goal seek to answer "what if" questions in a file. To do this, he clicks the cell in the spreadsheet that he wants to change, then selects the "X" button from the Data tab and selects Goal seek... from the drop down menu. What is X in the procedure?
What-if Analysis
Which of the following 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.
Which formula can you use to extract the month from the date in cell F5: 2016 July 8th?
Which formula can you use to extract the month from the date in cell F5: 2016 July 8th?
Which of the following is true about entering a function using the Insert Function dialog box?
You can select a function you've recently used. You don't need to type an equal sign to begin the formula. You can search for a function that meets your needs.
In which of the following boxes under Define target value will you type a numerical value while creating a key performance indicator (KPI)?
Absolute value
To align currency symbols and decimal points in a column of numbers, you can apply the ____ number format.
Accounting
Which of the following can you use to insert a formula using a function?
AutoSum button on the Ribbon Insert Function dialog box Copy and paste a cell containing a function AutoSum list arrow on the Ribbon
Which of the following would you use to print only one page of a multipage worksheet?
Backstage view
When you enter a value in a worksheet cell, it is automatically left-aligned.
False
You can use the I-beam pointer to copy cell contents into adjacent cells.
False
To print more than one copy of your worksheet, which tab would you go to?
File
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
You want to change all worksheet occurrences of "Radio" to "TV"; to do this, you can use the _____ feature.
Find and Replace
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
The Comma Style format adds a comma and a dollar sign to a cell value.
False
To print worksheet contents across the length of a page, you can use portrait orientation.
False
To see how copied contents will look with both values and number formatting, you would use Paste Numbers.
False
When you change cell contents to italics, you are changing the cell's alignment.
False
Luis wants to unhide and view all rows and columns in a worksheet. Which of the following series of clicks/entries/selections can he use to create a custom view of the worksheet?
Worksheet > View tab > Workbook Views group > Custom Views > Add > Name box (Type a name ) > Include in view (Tick check box- Hidden rows, columns and filter settings ) > OK
Helga wants to reset page breaks in a worksheet to display only automatic page breaks. Which of these 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
Which of the following is true about deleting a worksheet? Select all the options that apply.
You can use the Delete list arrow in the Cells group on the Home tab. You can right-click a sheet tab and click Delete.
Which of the following is true about pasting copied text? Select all the options that apply.
You can view the copied text on the Office Clipboard.! You can use the Paste button in the Clipboard group to paste the copied text.
In formulas, calculations in square brackets are calculated first.
False
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.
True
A jewelry designer creates an excel sheet to list different items he sold on the previous day. Cells A1, B1, C1, and D1 contain headings—Order ID, Unit price, and Quantity, respectively. Cells A2 to A6 list the order ID for items sold—10001,10002,10003,10004, and 10005, respectively. Cells B2 to B6 list their respective unit price—$15, $18.5, $12, $9.5, and $6. Cells C2 to C6 list their respective quantities—20, 32, 16, 15, and 10. If he enters the formula in cell D1 as: =VLOOKUP (10005, A1: H3, 3, FALSE), what will the formula return?
10
To enter your name in the lower right corner of a worksheet page, you can _____.
Select the right footer text box and type your name.
What is the correct formula to insert the date January 12, 1998 into a cell?
=DATE(1998,1,12)
Ian needs to extract the day from the date 5/31/2017 entered in cell A1. Which formula can he use to do this?
=DAY(A1)
Which of the following formulas can you use to extract the minute from the time in cell G5: 10:00:56?
=MINUTE(G5)
To apply formats to data when it meets criteria you specify, you can use conditional formatting.
True
Kathy wants to add the numbers entered in a range of selected cells. To do this, which tab will she click on the menu that appears after clicking on Totals in the Quick Analysis option?
Sum
