Deck 15: Creating an Advanced Workbook

Question 1

Conditional formatting is dynamic-if a cell's value changes, the cell's format also changes as needed. ​

Answer 1

True

Question 2

While using Flash Fill, make sure that:

A)you enter the desired pattern manually in at least five cells.
B)the worksheet is in Print Preview.
C)the data pattern includes at least one blank cell.
D)you enter the data pattern in the column right next to the related data.

Answer 2

D

Question 3

While printing a complete workbook, Excel also prints the hidden rows and columns in the workbook. ​

Answer 3

False

Question 4

The order of operations that Microsoft Excel follows is division, multiplication, exponentiation, and addition. ​

Question 5

Damien, an accountant, needs to calculate the following: 8^12*(124+1344+176)/100. He uses an Excel worksheet for the calculation. Which of the following functions will Excel perform first?

A)Exponentiation
B)Addition
C)Multiplication
D)Division

Question 6

For Flash Fill to suggest content, you need to: ​

A)enter at least one value and start typing the next value.
B)apply the concatenate formula.
C)enter text starting with the same letter in each cell.
D)assign sequential numbers to text entered in each cell.

Question 7

Lidya, a Mathematics student, solves the formula "=5^6(306/44)*(122/55) - 1000" manually. She uses an Excel sheet to cross-check the answer. The function that Excel will perform last is _____.

A)subtraction
B)multiplication
C)division
D)exponentiation

Question 8

Flash Fill is a function in Microsoft Excel used to enter formulas with multiple calculations. ​

Question 9

If a formula contains several functions, Excel starts with the outermost function and then moves inward. ​

Question 10

In Microsoft Excel, the error value message #NUM! appears when the wrong type of argument is used in a function or formula. ​

Question 11

In a new worksheet, the page margins are set to 0.3 inches on the left and right and 0.5 inches on the top and bottom with 0.6-inch margins around the page header and footer. ​

Question 12

Steve enters the equation "=2*(16 − 4)/10+23" in a cell in Microsoft Excel. The operation within parenthesis indicates that:

A)it needs to be performed first.
B)it needs to be performed second.
C)it needs to be performed third.
D)it needs to be performed last.

Question 13

A text argument can be included in an IF function by enclosing it in quotation marks. ​

Question 14

The IF function is an example of a text function in Microsoft Excel. ​

Question 15

Flash Fill enters text based on _____.

A)the order of numbers assigned to the text in each cell
B)the concatenate formula
C)patterns it finds in the data
D)conditional formatting assigned by the user

Question 16

In Microsoft Excel, the error value message #NAME? appears when a value is not available to a function or formula. ​

Question 17

​ The header and footer entered in an Excel sheet are static and do not get updated automatically when the worksheet"s name is changed.

Question 18

The Conditional Formatting button is available on the Home tab in Microsoft Excel. ​

Question 19

Which of the following is the order of operations to calculate formulas with multiple functions in Excel?

A)Exponentiation, multiplication, division, and addition
B)Addition, division, multiplication, and exponentiation
C)Division, exponentiation, addition, and subtraction
D)Exponentiation, subtraction, division, and multiplication

Question 20

In Microsoft Excel, you can use multiple IF functions in the same formula to test for several conditions. ​

Question 21

If a row is hidden in an Excel sheet, _____. ​

A)it does not get printed on paper
B)it affects the formulas in the workbook
C)a single border appears between the rows
D)the row numbers remain consecutive

Question 22

Sarah, who has been an apparel retailer for ten years, wants to analyze and compare the monthly profits for the ten years. She fills details such as the year, month, and profit in three columns on an Excel sheet. She wants to highlight the months with a profit of more than $100. In this scenario, Sarah should use the _____ function to highlight the relevant cells at once. ​

A)conditional formatting
B)tatistical
C)Flash Fill
D)Text to Columns

Question 23

The Quick Analysis button appears when: ​

A)you click the Format as Table button.
B)you select the first cell of the data range.
C)you click the Conditional Formatting button.
D)you select a range of data.

Question 24

In the accompanying image of Microsoft Excel 2016, box A points to the _____.

A)Conditional Formatting button
B)Logical button
C)Error Checking button
D)Format button

Question 25

In the accompanying image of Microsoft Excel 2016, box A points to an _____.

A)error message
B)error value
C)error checking button
D)error indicator

Question 26

The accompanying image illustrates the _____ dialog box in the File tab of Microsoft Excel 2016. ​ ​

A)Excel Options
B)Excel Formulas
C)Excel Rules
D)Excel Tools

Question 27

Which of the following statements is true of the IF function in Excel?

A)It can include text within quotations.
B)It is a Text function.
C)It is a Statistical function.
D)It can return three values.

Question 28

Victor, a population analyst, has the following data on an Excel sheet: month-wise population of a city in 2014 in column A and month-wise population of the same city in 2015 in columnB)He wants to calculate the change in population in 2015 compared to 2014 and check if the change is more or less than 2 million. Victor uses multiple functions, including the IF function, within a single formula for this analysis. In this scenario, Victor uses a _____.

A)loop function
B)nested function
C)Web function
D)Compatibility function

Question 29

Identify a true statement about conditional formatting.

A)It cannot be used for a cell containing text.
B)When a cell's value changes, the formatting does not change automatically.
C)It cannot change the color of the font in a cell.
D)The format applied to a cell depends on the value or content of the cell.

Question 30

In the accompanying image of Microsoft Excel 2016, box A points to an:

A)error message indicating that a cell reference used in a formula or function is no longer valid.
B)error message indicating that Excel does not recognize text in the formula.
C)error message indicating that a value is not available to a function or formula.
D)error message indicating that the wrong data type is used in the formula.

Question 31

Roger, an accountant at SellDom Inc., has entered the company's expenditure, assets, profits, and debt details on an Excel sheet. Since the data are valuable, he needs to retain and save the sheet. If Roger wants to print only the details of the company's expenditure and profits from the Excel sheet, he should: ​

A)delete the assets and debts columns.
B)hide the assets and debts columns.
C)use Text to Columns for the assets and debts columns.
D)use Flash Fill for the assets and debts columns.

Question 32

In Microsoft Excel 2016, the formula '=IF(G5=L5,"Yes", "No")' is a _____ function.

A)text
B)logical
C)lookup & reference
D)math & trig

Question 33

The Error Checking button appears on an Excel sheet when:

A)the cursor is pointed on the green triangle.
B)the cell containing the error is the active cell.
C)the cursor is placed on the adjacent cell and the Ctrl key is pressed.
D)the cell containing the error is deselected.

Question 34

The error value " #N/A" indicates that:

A)the wrong type of argument is used in a formula.
B)the formula or function contains a number divided by 0.
C)a worksheet does not recognize text in a formula or function.
D)a value is not available to a function or formula.

Question 35

Which of the following is an example of conditional formatting? ​

A)Highlighting cells based on their values
B)Merging the cells that have the same value
C)Adding a dropdown to the heading with the values in a list
D)Separating the text in a cell to different columns

Question 36

Conditional formatting can be used to:

A)calculate nested formulas.
B)fill text based on patterns in the data.
C)clear all the rules in a workbook.
D)process all the logical functions.

Question 37

In the accompanying nested function, which of the following functions will be executed first? =IF(D4>((SUM(E4:E10))/7)*100,"True") ​

A)IF
B)Addition
C)Division
D)Multiplication

Question 38

Y ou can tell rows or columns are hidden in an Excel sheet when :

A)all the data points are clearly visible.
B)content is suggested based on patterns in the data.
C)the numbers or letters are no longer consecutive.
D)a single border demarcation appears between them.

Question 39

Which of the following indicates that an error has occurred in an Excel calculation? ​

A)An error value beginning with a dollar sign ($) appears in the cell.
B)A small green triangle appears in the upper-left corner of the cell.
C)Excel returns the calculation result as zero.
D)Excel displays the message " " An error has occurred. " "

Question 40

The accompanying image is of a _____ dialog box in Microsoft Excel 2016.

A)Workbook Formatting Rule
B)Conditional Formatting Rules Manager
C)File Formatting Rule
D)Cell Formatting Rules Manager

Question 41

Case 15-2 Zelda, the Human Resource (HR) manager at ProfDot Technologies, creates a database on an Excel sheet of the names of potential candidates for a job. In addition, she enters the work experience, current salary, and expected salary against each name.
Zelda uses conditional formatting to make the following changes: change the background color of the cells with current salary of $3000 to $4000 to yellow, change the font color, generate data bars on each cell, and highlight the cells containing the names with blue color. In this scenario, which of the following changes will be applied first?

A)Generating data bars
B)Changing the color of the font
C)Changing the background color of the cells to yellow
D)Highlighting the background of the cells with blue color

Question 42

Case 15-1 Frank, a business analyst at Pro-White Technologies, deals with vast amount of data and analysis. Since Frank uses Excel worksheets for most of his work, he uses several advanced functions of Excel that eases and speeds up his work.
Using an Excel worksheet, Frank creates a list of the cities where his company's business could possibly thrive. He also includes the names of the states the cities belong to and the average revenue of the residents in the sheet. Frank wants to collate the whole data in a single column (for example, Boston, Massachusetts - $75667). In this scenario, Frank should use _____ as the data he needs to collate follows a pattern. ​

A)Data Validation
B)Text to Columns
C)Group Fill
D)Flash Fill

Question 43

John, a researcher, collects a large amount of data to perform statistical analysis and saves them using Excel. He wants to print the data and bind the pages to submit as a thesis. In this scenario, John has to _____ to accommodate the page binding . ​

A)increase the print area
B)increase the font size
C)widen the margins
D)narrow the margins

Question 44

Steve, a sales manager, maintains a worksheet for sales details, which includes his target, the number of sales achieved, the clients, etc. in the past two years. If Steve wants to print these details for just the last quarter, Steve should use the _____. ​

A)Print Selection pane
B)Print Preview pane
C)Print Area button
D)Print Titles button

Question 45

An _________ is a small green triangle that appears in the upper-left corner of a cell with a possible error.

A)error marker
B)error indicator
C)error detector
D)error signature

Question 46

In the accompanying image of Microsoft Excel 2016, box A points to the _____.

A)manual page breaks
B)error indicators
C)error markers
D)automatic page breaks

Question 47

Case 15-1 Frank, a business analyst at Pro-White Technologies, deals with vast amount of data and analysis. Since Frank uses Excel worksheets for most of his work, he uses several advanced functions of Excel that eases and speeds up his work.
Frank fills eight columns and over a thousand rows of text in an Excel worksheet. He wants to collate the text in three of the columns in a single column. Frank enters the first two cells manually and then uses the Flash Fill function. Flash Fill suggests a pattern that is not the pattern Frank requires. To reject the suggested pattern, Frank should press the _____ key.

A)Alt
B)Ctrl
C)Esc
D)Shift

Question 48

Case 15-1 Frank, a business analyst at Pro-White Technologies, deals with vast amount of data and analysis. Since Frank uses Excel worksheets for most of his work, he uses several advanced functions of Excel that eases and speeds up his work.
Frank has a set of data in four columns B, C, D, and E, which he wants to collate in a single columnA)He uses Flash Fill to collate the data. However, after filling the column using Flash Fill, Frank changes the data in cell C5. Which of the following is the most likely result of this change?

A)Cell A5 automatically updates with the changed data.
B)Content generated by Flash Fill remains unchanged.
C)The text in cell A5 disappears.
D)The text in A4 copies to A5.

Question 49

The accompanying image of Microsoft Excel 2016 shows the _____ dialog box that provides several print options. ​ ​

A)Create Table
B)Print Preview
C)Page Setup
D)Data Table

Question 50

In a Microsoft Excel worksheet, you can add a header and footer by clicking the _____. ​

A)Page Layout button
B)Page Break button
C)Review tab
D)Format tab

Question 51

Which of the following statements is true of hiding rows and columns in an Excel sheet? ​

A)You need to use the command Show Cells to unhide them.
B)It does not affect the other formulas in the workbook.
C)You can apply Flash Fill to hidden columns.
D)The order of the columns or rows remains consecutive.

Question 52

Garner, a management student, conducts a survey on the purchasing power of people in a small town and records the information on an Excel sheet. When he tries to print the data, print preview displays the last two rows on a separate sheet. To print the entire content on a single sheet, Garner should: ​

A)change the settings to Wide Margins.
B)zoom in on the active sheet.
C)use automatic page break.
D)scale the printout by reducing the font size.

Question 53

In Microsoft Excel, the Page Break Preview button is available on the _____.

A)title bar
B)status bar
C)Review tab
D)Data tab

Question 54

Hannah, a financial analyst, creates a balance sheet for one of her clients. When she prints the balance sheet, the content appears split at awkward places on the sheet. Hannah decides to reprint the sheet. In this scenario, Hannah should use the _____ to split the Excel sheet into logical segments. ​

A)Quick Analysis tool
B)manual page break
C)automatic page break
D)Header & Footer tool

Question 55

Case 15-2 Zelda, the Human Resource (HR) manager at ProfDot Technologies, creates a database on an Excel sheet of the names of potential candidates for a job. In addition, she enters the work experience, current salary, and expected salary against each name.
Zelda previously highlighted the cells with the names of candidate who have over two years of work experience using conditional formatting. If Zelda wants to remove the condition from the worksheet, she should use the _____ option.

A)Wrap Text
B)Edit Rules
C)Clear Rules
D)Clear Contents

Question 56

​ In the accompanying image of Microsoft Excel 2016, box A points to the _____.

A)manual page break
B)automatic page break
C)worksheet border
D)error indicator

Question 57

Which of the following is true of printing the data in an Excel sheet? ​

A)It prints the formulas present in the formula bar.
B)It prints as much of the content that fits on a single page.
C)It does not print data that occupies more than three cells.
D)It does not print the header and footer of the Excel sheet.

Question 58

A _________ is a value in a formula that does not change.

A)constant
B)derivative
C)standard
D)function

Question 59

Case 15-2 Zelda, the Human Resource (HR) manager at ProfDot Technologies, creates a database on an Excel sheet of the names of potential candidates for a job. In addition, she enters the work experience, current salary, and expected salary against each name.
Zelda wants to change the background color of the cell with the names of candidates whose expected salary is in the range of $6000 to $7000 per month. In this scenario, Zelda should use the _____ option in conditional formatting.

A)Color Scales
B)Top/Bottom Rules
C)Highlight Cells Rules
D)Icon Sets

Question 60

In Microsoft Excel 2016, the Hide & Unhide option can be accessed by clicking the _____ on the Home tab.

A)Insert button
B)Clear button
C)Format button
D)Edit button

Question 61

The region of an active Microsoft Excel sheet that is sent to the printer is known as the _________.

A)print space
B)print preview
C)print area
D)print domain

Question 62

In Microsoft Excel, _________ is a page break that Excel inserts when no more content will fit on the page.

A)manual page break
B)automatic page break
C)portable page break
D)final page break

Question 63

Explain with examples the different ways in which the IF function can be used. ​

Question 64

A row or a column in an Excel worksheet can be hidden by using the _________ button on the Home tab.

A)Format
B)Edit
C)Alignment
D)Connection

Question 65

The _________ is a logical function that returns one value if a statement is true and returns a different value if that statement is false.

A)IF function
B)DELTA function
C)CELL function
D)N function

Question 66

To access conditional formatting on Microsoft Excel, click the Conditional Formatting button on the _________ tab.

A)Insert
B)Review
C)File
D)Home

Question 67

Explain the uses of conditional formatting. ​

Question 68

An _________ is a message that appears in a cell indicating that some part of a formula is preventing Excel from returning a calculated value.

A)error indicator
B)error marker
C)error value
D)error message

Question 69

In the context of logical functions, a(n) _________ operator is a symbol that indicates the relationship between two values.

A)comparison
B)arithmetic
C)reference
D)text concatenation

Question 70

In Microsoft Excel, the Print Titles button is found on the _________ tab.

A)Design
B)Review
C)View
D)Page Layout

Question 71

Explain how manual page breaks is a better option over automatic page breaks. ​

Question 72

A(n) _________ is a function that works with statements that are either true or false.

A)statistical function
B)logical function
C)text function
D)information function

Question 73

In Microsoft Excel, _________ is a page break that you insert to specify where a page break should occur.

A)manual page break
B)automatic page break
C)portable page break
D)final page break

Question 74

In the context of logical functions, the _________ symbol in the logical test argument is a comparison operator.

A)&
B)/
C):
D)=