MIS 112 Problem Sets

dataLookup_eg8.xlsx Please see the attached workbook. What would you get in E4 if you typed in "South-Africa" in cell E3? A. an error because western cape is not a region in South Africa B. "an error because the formatting of the word "South-Africa" is different than the one found in the table C. the value "Western Cape" D. the value "South Africa"

"an error because the formatting of the word "South-Africa" is different than the one found in the table

Examine the attached file: PS1_formulas.xlsx What is total widget sales for the West Region for the year? A. $13,282 B. $5,129 C. $4,015 D. None of the above

$5,129

What is the correct order of steps to be followed when you use a query wizard? A. 1. choosing the columns, 2. sort order, 3. filtering data, and 4. finish B. 1. filtering data, 2. choosing the columns, 3. sort order, and 4. finish C. 1. choosing the columns, 2. filtering data, 3. sort order, and 4. finish D. None of the options provided is correct

1. choosing the columns, 2. filtering data, 3. sort order, and 4. finish

Please refer to the figure. Within the "Number" tab, how many categories are available for displaying numbers? A. 12 B. 8 C. 10 D. 14

12

With a single IF() function, how many actions can you ask Excel to carry out? A. 1 (i.e., an action if the criteria is true) B. 2 (i.e., an action if the criteria is any one of the true, or an action if the criteria is false) C. 3 (i.e., an action if the criteria is any one of the true, false, unknown) D. As many as you want, this is why IF() functions are so difficult to understand

2 (i.e., an action if the criteria is true, or an an action if the criteria is false)

In Excel, the maximum length of a Web query URL is _______ characters. A. 124 B. 512 C. 48 D. 255

255

What is the maximum number of characters a worksheet name can contain? A. 31 B. 34 C. 28 D. 32

31

dataLOOKUP_Ex1.xlsx Please see the attached workbook. What tax rate value is returned when you perform a VLOOKUP with an entered income of $45,500? A. 15% B. 28% C. 31% D. 36%

31%

What will be the result of this formula =IF(A1<100000,A1*5%,A1*7.5%) , if the cell A1 has a value of 90000? A. 4500 B. 500000 C. 4750 D. 6750

4500

Please refer to the figure. How many worksheets are contained in the workbook shown? A. 1 B. 5 C. 3 D. A1

5

What would you type in the Find and Replace dialog box (Home (tab) -> Editing (command group) -> Find & Select) to find all four-digit entries that begin with 5 and end with 2? A. 5?2 B. 5??2 C. 52? D. *2

5??2

formulas_mixed_cell_references.xlsx Examine the attached Excel file. When you copy the formula in cell "C3" to "F7", the cell in "F7" contains which of the following formulas? A. =$B$7*$F$2 B. =B$7*$F2 C. =B7*F2 D. =$B7*F$2

=$B7*F$2

Which of the following options, when used as a formula in the Custom Data Validation criteria for cell range A1:A10, enables the user to enter a value in A1:A10 only if it's greater than or equal to the value in A1? A. =A1>=$A$1 B. =A1>=A1 C. =A2<=$A$1 D. =A2>=$A1

=A1>=$A$1

In the attached Excel workbook PS1_Comments_Calculation.xlsx , which of the following formulas can be used to calculate Sophie's total score in both the exams? A. =B4+C4 B. =B3+C3 C. =B4*C4 D. ={B4+C4}

=B4+C4

Examine the attached file: PS1_formulas.xlsx To calculate the percentage of the total widget sales made by the top 5 employees, which of the following formulae would you enter in cell "C22" (shaded purple) A. C20/F8 B. =SUM(C15:C19) C. =C20/F8 D. =PERCENTSALES(F2)

=C20/F8

Advanced_aggregation.xlsx Use the attached spreadsheet to answer the following question. Which formula can be used to find: How many songs were sung by singers whose last names contain exactly six letters? A. =COUNTIF(B2:B23,"6*?") B. =COUNTIF(B2:B23, "{6?}") C. =COUNTIF(B2:B23,"??") D. =COUNTIF(B2:B23,"??????")

=COUNTIF(B2:B23,"??????")

Advanced_aggregation.xlsx Use the attached spreadsheet to answer the following question. Which formula can be used to find: How many songs were longer than the average length of all songs on the list? A. =COUNTIF(D2:D23,"<"&AVERAGE(D2:D23)) B. =COUNTIF(D2:D23,">"&AVG(D2:D23)) C. =COUNTIF(D2:D23,">"&AVERAGE(D2:D23)) D. =COUNTIF(D2:D23,">"AVERAGE(D2:D23))

=COUNTIF(D2:D23,">"&AVERAGE(D2:D23))

Examine the image in the question. In "C3," we have the supplier address "Tucson, AZ 85721" appearing in a single cell (the formula combines data from cells "E3," "F3," "G3"). Which of the following formulas will achieve this result? A. =E3 + "," + F3 + G3 B. =E3 &", " &F3 &" " &G3 C. =City, State/Province, Zip/Postal Code D. =SUM(E3:G3)+COUNT(C3)

=E3 &", " &F3 &" " &G3

What formula displays the value of B5 stored in a file named "example.xslx" in a different directory called "TEST" in the "C:\" drive? A. =HYPERLINK("C:\TEST\example.xslx"B5) B. =HYPERLINK("C:\TEST\example",B5) C. =HYPERLINK("C:\TEST\example.xslx",B5) D. =HYPERLINK("C:\example.xslx",B5)

=HYPERLINK("C:\TEST\example.xslx",B5)

To create a hyperlink to a specific location in a Microsoft Word document, you must use a bookmark to define the location you want to jump to in the document. Which of the following is the right option to create a hyperlink to the bookmark 'QrtlyProfits' in the document 'Annual Report.doc' located at http://example.microsoft.com? The link text to be displayed is "Quarterly Profit Report". A. =HYPERLINK("[Example.xlsx]E2","Quarterly Profit Report") B. =HYPERLINK("[http://example.microsoft.com/Annual Report.docx]QrtlyProfits","Quarterly Profit Report") C. =HYPERLINK("[Annual Report.docx]QrtlyProfits","Quarterly Profit Report") D. =HYPERLINK("QrtlyProfits","Quarterly Profit Report")

=HYPERLINK("[http://example.microsoft.com/Annual Report.docx]QrtlyProfits","Quarterly Profit Report")

dataLookup_eg7.xlsx Please see the attached workbook. If the 'amount' for the given 'date' is to be retrieved from the given range of data (I4:K7), which of the following formulas should be used? A. =INDEX(I4:K7,MATCH(B2,I4:I7,0),3) B. =INDEX(I4:K7,3) C. =MATCH(B2,I4:I7,0) D .=MATCH(I4:I7,0)

=INDEX(I4:K7,MATCH(B2,I4:I7,0),3)

Please examine the attached image. Which of the following formulas will calculate the total number of stores across the four states? A. =SUM(B2:B5) B. =COUNT(B2:B5) C. =NUMBER(A1:B5) D. =COUNT(B1)

=SUM(B2:B5)

Which of the following options, when used as a formula in the Custom Data Validation criteria for cell A1, will ensure that the cell entry is a date, and that thedate is a Sunday? Hint: Date format: MM/DD/YYYY A. =WEEKDAY(A1)=1 B. =WEEKDAY(A1)=SUNDAY C. =ISSUNDAY(A1)=1 D. =ISWEEKDAY(A1)=SUNDAY

=WEEKDAY(A1)=1

Examine the image in the question. Which of the following is true for the new name being defined? A. A name is being applied to a. range B4:E4. This name "UA", can be used in formulas anywhere in the current workbook. B. A new name is being created for use specifically in the current worksheet (Sheet1), based on the given range (B4:E4) C. The UA range is being moved from cells B5:E5 to cells B4:E4 D. Three ranges, "UA", "ASU" and "NAU" are being defined as new names in the workbook

A name is being applied to a range B4:E4. This name "UA", can be used in formulas anywhere in the current workbook.

Based on the class lecture, which of the following is true about table renaming? A. table renaming is not recommended in Excel as it will cause formulas to stop functioning properly. B. renaming a table is done automatically when a table is extended (e.g., when you add a new row of data) C. a table can be renamed to better reflect its contents (e.g., PARTS or PRODUCTS) D. Excel tables are named based on the worksheet it is located in, e.g., a table in "Sheet1" will be named "Sheet1Table1" Or "Sheet1Table2"

A table can be renamed to better reflect its contents (e.g., PARTS or PRODUCTS)

Which of the following statements is true? A. A workbook can have at most 3 worksheets. B. A worksheet can have many workbooks. C. A workbook can have many worksheets. D. A worksheet can have at most 3 workbooks.

A workbook can have many worksheets.

Please refer to the figure. In an Excel worksheet, what column name comes directly after Column Z? A. AA B. AZ C. ZA D. ZZ

AA

In context of the MATCH() function, which of the following is FALSE? A. The syntax of match is: MATCH (lookup_value,lookup_array, row_offset) B. MATCH returns the cell contents (e.g., a product name) when it detects two cells with duplicate contents C. A #VAL error value is returned if the MATCH function is unsuccessful in finding a match D. All of the options are false

All of the options are false

Which of the following statements about Trace Precedents are correct? A. It can help point out if a formula has been applied to a wrong cell range B. You can access Trace Precedents on the Formula Auditing toolbar C. By clicking Trace Precedents again, you can identify the next level of cells that provide data to the active cell D. All of the options provided are correct

All of the options provided are correct

Which of the following function(s) can be used to count error values in a range? A. All of the provided choices are valid B. ISERROR() C. ISERR() D. ISNA()

All of the provided choices are valid

How are some icons hidden in Icon Set conditional formatting? A. By choosing "No Cell Icon" in the Edit the Rule Description box. B. By clicking on Conditional Formatting, followed by "icon hide". C. By clicking on New Rule in Conditional Formatting. D. By clicking on the "Clear Some Rules" button in Conditional Formatting.

By choosing "No Cell Icon" in the Edit the Rule Description box.

Please select the Excel function which allows us to answer the question: "How many days of stock data do I have?" You may refer to the image in the question. A. MIN() B. MAX() C. COUNT() D. SUM()

COUNT()

charts_example Examine the attached workbook. How can you resize the chart title in Chart 1 of Sheet 1? A. Drag its corner B. Change text alignment C. Rotate D. Change font size

Change font size

Which of the following is TRUE regarding the HTML files that Excel creates? A. Excel cannot open an HTML file. B. Formulas and charts in an HTML file created from an Excel workbook can be edited. C. Changes to graphs and formulas cannot be made to an HTML file created from Excel. D. In the HTML files, formulas can be edited, but the charts appear as static graphic images.

Changes to graphs and formulas cannot be made to an HTML file created from Excel.

What command (within Conditional Formatting) is used to delete all the rules relevant to a particular group of cells? A. Alter Rulebook / edit B. Alt-F4 C. Clear Rules D. New Rule E. Clear All

Clear Rules

To gain access to the Table Tools / Design tab in the Excel Ribbon when working with tables, you should do the following: A. click anywhere on the table B. such a tab does not exist, the only tab you can get access to is: table format/design C. click on the home tab on the ribbon, and check the appropriate box to make it appear D. The table tools/design tab does not provide any useful commands, so it is pointless trying to access it

Click anywhere on the table

How do you set up a data validation (using the data validation dialog box) that restricts data entry to a text of a specified length? A. Click the Settings tab (in the Data Validation box). In the Allow box, select 'date'. In the Data box, select 'greater than'. B. Click the Settings tab (in the Data Validation box). In the Allow box, select 'Text Length', 'equal to', and specify the length. C. Click the Settings tab (in the Data Validation box). In the Allow box, select 'Decimal'. In the Data box, select 'between'. D. Home (tab) -> Editing (group) -> Find & Select -> Data Validation

Click the Settings tab (in the Data Validation box). In the Allow box, select 'Text Length', 'equal to', and specify the length.

In Excel in order to avoid incomplete calculation errors and to ensure that formulas are fully calculated, you would press _________ . A. Ctrl + Alt + Space +F9 B. Ctrl + Alt + Shift + F9 C. Ctrl + Alt + Fn + F9 D. Ctrl + Alt + Shift + F10

Ctrl + Alt + Shift + F9

What is the shortcut to open the Find and Replace dialog box with the "Replace" tab open? A. Ctrl + F B. Ctrl + A C. Ctrl + H D. Ctrl + FIND

Ctrl + H

Which of the following is the keyboard shortcut that creates a new blank workbook in Excel? A. Ctrl + N B. Ctrl + E C. Ctrl + W D. Ctrl + B

Ctrl + N

Which of the following procedures will NOT close your Excel workbook? A. Ctrl+S B. Ctrl+F4 C. Ctrl+W D. File -> Close

Ctrl + S

Which of the following keyboard shortcuts can be used in Excel 2016 to create a table? A. Ctrl + F2 B. Ctrl + L C. Alt + T D. Ctrl + Alt + L

Ctrl+L

worksheet_file1.xlsx worksheet_file2.xlsx Please refer to the attached workbooks. The worksheet in "worksheet_file1.xlsx" is split into 4 panes (shown in "worksheet_file2.xlsx"). What cell must be selected prior to applying the split function in "worksheet_file1.xlsx" so that it looks exactly like what you see "worksheet_file2.xlsx"? A. C6 B. C7 C. D7 D. D6

D7

When you have imported data from an external data source, you can change the properties of the 'external data range' by using the External Data Properties dialog box. How will you open this dialog box? A. Data (tab) -> Connections (group) -> Properties B. Data (tab) -> Connections C. Data (tab) -> Connections (group) -> Connections D. Data (tab) -> Connections (group) -> Refresh

Data (tab) -> Connections (group) -> Properties

Which is one of the ways to refresh a query used to import data from external data source using the query wizard? A. Data (tab) -> Connections (group) -> Properties B. Data (tab) -> Connections (group) -> Refresh All C. Refresh D. Data (tab) -> Connections (group) -> All

Data (tab) -> Connections (group) -> Refresh All

datavalidation.xlsx Please see the attached workbook. In the EmpID table, enter the salary in cell H8 as 100. Given the error message, why do you think Excel does not let you input that value? A. 100 is a whole number B. Data validation settings require the salary value to be greater than the hours worked value, 120. C. Data validation settings require the salary value should be less than the hours worked value, 120. D. The cell is designed to permit text input only

Data validation settings require the salary value to be greater than the hours worked value, 120.

Which of the following is a VALID sequence of options for selecting a different chart layout? A. Design (from Chart Tools) --> Type group B. Design (from Chart Tools) --> Chart Styles C. Insert --> Charts --> Other Charts D. Design (from Chart Tools) --> Chart Layouts

Design (from Chart Tools) --> Chart Layouts

charts_example.xlsx Examine the attached workbook. If you format a chart element with the font seen in Chart 2, how can you reset it to its original state? A. Format tab (chart tools) --> Reset to Match Style (under the "Current Selection" group) B. Right click on the chart --> Reset to Match Style C. Either of the previous options (resetting through the menu or the right-click options work) D. Home tab --> Wrap Text, followed by, Paste

Either of the previous options (resetting through the menu or the right-click options work).

When you apply currency formatting to a Table column and then add a new row to the table, what happens to data entered in the new cell? A. Currency formatting is not applied to the new cell B. Excel applies currency formatting to the new value in that column cell C. A special type of formatting other than currency formatting is applied to the new value D. Currency formatting is temporarily applied to the new value (but only persists until the workbook is closed)

Excel applies currency formatting to the new value in that column cell.

Which older version of Excel file can Excel 2016 NO LONGER open? A. Excel can open all of these files B. Excel 4 C. Excel 97 D. Excel 2000

Excel can open all of these files

Excel uses column headers to create labels for each _____ on data entry form. A. Record B. Data Form C. Row D. Field

Field

Which of the following command sequences will allow you to save a copy of your workbook with a different name? A. Ctrl, A B. Ribbon, Save C. File, Save As D. Data, Create Copy

File, Save As

external_ex1.xlsx Please use the attached workbook as an external data source. The workbook has a table called 'Employee.' When you import data, after selecting all the columns from the table using the query wizard (Data(tab -> Get External Data (group) -> From Other Sources -> From Microsoft Query), how will you filter the records to retrieve those that have a value that starts with 'T' for 'City' column. A. From the Column to Filter list, select City. In the right panel, select 'equals' from the first drop-down list and then select 'Tucson' from the second drop-down list B. From the Column to Filter list, select City. In the right panel, select 'begins with' from the first drop-down list and then type 'T' in the second drop-down list box C. From the Column to Filter list, select City. In the right panel, select 'ends with' from the first drop-down list and then type 'T' in the second drop-down list box D. From the Column to Filter list, select Name. In the right panel, select 'equals' from the first drop-down list and then select 'Sam' from the second drop-down list

From the Column to Filter list, select City. In the right panel, select 'begins with' from the first drop-down list and then type 'T' in the second drop-down list box

external_ex1.xlsx Please the attached workbook as an external data source. The workbook has a table called 'Employee'. When you import data, after selecting all the columns from the table using the query wizard (Data(tab) -> Get External Data (group) -> From Other Sources -> From Microsoft Query), how will you filter the records to retrieve those that have a value lesser than 30000 for 'Salary' column. A. From the Column to Filter list, select Salary. In the right panel, select 'is less than' from the first drop-down list and then select '30000.0' from the second drop-down list. B. From the Column to Filter list, select Salary. In the right panel, select 'is less than' from the first drop-down list and then select '3.0' from the second drop-down list. C. From the Column to Filter list, select Name. In the right panel, select 'equal' from the first drop-down list and type 'm' in the second drop-down list box. D. From the Column to Filter list, select Salary. In the right panel, select 'equals' from the first drop-down list and then select '30000.0' from the second drop-down list.

From the Column to Filter list, select Salary. In the right panel, select 'is less than' from the first drop-down list and then select '30000.0' from the second drop-down list.

Which function is used to create a shortcut that opens a document stored on a network server, an intranet, or the Internet? A. HYPERLINK() B. SUM() C. INDEX() D. MATCH()

HYPERLINK()

After pressing "Alt + H" while in an open Excel worksheet, a number of Key Tips are displayed. Which ribbon tab do these Key Tips relate to? A. Home B. Review C. Insert D. Formulas

Home

Which of the following statements is TRUE? A. The Analysis Toolpak lets you generate random numbers and histograms only, hence is not very useful B. You use the FREQUENCY() function in Excel to find Fourier wave frequencies. C. The main difference between SUMIF() and COUNTIF() is that the former is used with multiple criteria, but the latter (i.e., COUNTIF()) only allows a single criteria. D. If I have a worksheet with student names (First, Middle, Last), you can use the COUNTA() function to find how many student names are on the worksheet

If I have a worksheet with student names (First, Middle, Last), you can use the COUNTA() function to find how many student names are on the worksheet

When (or why) does Excel draw arrows to indicate an error source? A. For finding dependencies between two images pasted in different worksheets B. In Tracing Error Values C. Error-checking the sheet Background D. Because Functions Must Have Arrows

In Tracing Error Values

In the Analysis ToolPak for Excel, which of the following parameters can you specify when creating a Histogram? A. Filename (to import Data from Access) B. Ignore duplicates vs. Permit Duplicates C. Simple vs Compound Interest D. Input Range and Bin Range

Input Range and Bin Range

When a column is sorted within a table, which of the following is true about the drop-down list in the header row? A. the header row background fill color changes to a darker shade B. It displays a different graphic to remind you that the table is sorted by the column C. there is no change to the header for that column before or after sorting D. the header row (for that column) is now disabled and until the sort is cleared, no further actions are permitted.

It displays a different graphic to remind you that the table is sorted by that column

Which of the following is the MOST compelling motivation for using HLOOKUP()? A. It improves the formatting of your data and makes style-usage possible B. It usually reduces duplicate data storage C. Pie charts work better if the data is generated using HLOOKUP() formulas D. HLOOKUP() is superior in design to VLOOKUP()

It usually reduces duplicate data storage

Which keyboard shortcuts can be used to move the active cell one column to the left? A. Left arrow or Shift + Tab B. Left arrow or Tab C. Left arrow or Ctrl + Tab D. Left arrow or Alt + Tab

Left arrow or Shift + Tab

The HYPERLINK function syntax has two arguments. Which argument has the jump text or numeric value that is displayed in the cell? A. Link_location B. Friendly_name C. Name D. File

Link_location

To display the Total Row in a Table: choose Table Tools / Design -> Table Style Options (command group) and put a check mark next to Total Row. Selecting a cell in the Total Row and clicking on the drop-down arrow that appears in that cell will NOT allow you to select which of the following summary formulas? A. Average B. Count C. Sum D. Mode

Mode

tables_duplicate_rows.xlsx For this question, please download the attached Excel workbook. Convert the data on Sheet1 into a table. Next answer the following question: If you proceed to remove duplicate values using the "Remove Duplicates" feature in "Table Tools", will Excel remove one of the two table rows? A. yes, because excel can see that the dates in the two cells are the same value B. no, because dates that are a duplicate cannot be removed C. no, because excel does not let you remove duplicates when you only have 2 rows in a table (removing a row makes it a 1-row table, which is not really a table) D. yes, because the duplicate values are determined by the value displayed in the cell and not necessarily by the value stored in the cell.

No, these are not considered duplicates by Excel. To remove the duplicate data you must ensure that the date in the second row is formatted the same way as the date in the first row.

What is the primary purpose of "Protected View?" A. Protect you from malware B. Prevent your peers from accidentally editing data C. Make it impossible for people the change your data unless they have a password D. Encrypt your spreadsheet data

Protect you from malware

Suppose your worksheet has a comment in cell A1 and a comment in cell B5. Your active cell is B1. Which command can you use to navigate to the comment in cell B5? A. Review (tab) -> Comments (command group) -> Next B. Review (tab) -> Comments (command group) -> Previous C. Home (tab) -> Navigate (command group) -> Next Comment D. Page Layout (tab) -. Search (command group) -> Jump to Comment

Review (tab) -> Comments (command group) -> Next

Please refer to the figure. What Excel element is shown in the red box? A. File Button B. Quick Access toolbar C. Scrollbar D. Ribbon

Ribbon

The last step in the Query Wizard, "Finish", lets you do which of the following? A. Save a query so that you can reuse it B. Determine the order of sorting the output C. Choose hyperlinks that will appear for the query D. Decide whether the query will use relative or absolute cell references

Save a query so that you can reuse it

Please examine the attached workbook. Give the data in Sheet1, which of the following steps should be followed to obtain the data in Sheet2? A. Select cells A1 through C4 in Sheet 1 -> Copy -> Go to Sheet2 -> Paste -> Formulas B. Select cells A1 through C4 in Sheet1 -> Copy -> Transpose -> Go to Sheet2 -> Paste C. It is only possible if done manually D. Select cells A1 through C4 in Sheet1 -> Copy -> Go to Sheet2 -> Paste -> Transpose

Select cells A1 through C4 in Sheet 1 -> Copy -> Go to Sheet2 -> Paste -> Transpose

To view or edit a comment, you can select the cell where a comment has been attached and press the following keyboard shortcut: __________. A. Ctrl+Alt B. Shift+F2 C. F5 D. Ctrl+A

Shift+F2

Which of the following is a shortcut key used to calculate only the formulas in the active worksheet? A. Shift + F9 B. all of the choices provided are valid C. F9 D. Ctrl + Alt + F9

Shift+F9

To create a single file web page, what file format does a user have to select from the Save as Type drop-down list? A. XML data B. Single File Web Page C. Web Page D. Excel Binary Workbook

Single File Web Page

Example_6.xlsx Please see the attached workbook. Row 5 is hidden and contains a word in cell A5. What is the result of using the spell checker when the word is hidden? A. The spell check will not find a misspelled word because the cell is hidden. B. Spell checker will be able to check the spelling of the word because the contents of hidden rows and columns are checked. C. The result is that the word is spelled correctly. D. A dialog box will appear, stating that an error occurred while performing spell check

Spell checker will be able to check the spelling of the word because the contents of hidden rows and columns are checked.

While comparing two sheets using the "View Side by Side" feature, there is an option which lets you scroll the two sheets in sync. What is that option called? A. Toggle B. Side View C. None of the (other) options provided are correct D. Synchronous Scrolling

Synchronous Scrolling

What function removes leading and trailing spaces from a cell? A. TRIM B. CUT C. COMPACT D. CLEAN

TRIM

When importing a text file into Excel, the _______ helps specify how the data will be imported. A. Text Import Wizard B. External Data Wizard C. CSV Wizard D. Text Preview Panel

Text Import Wizard

If the formula =ROWS(Asset) * COLUMNS(Asset) that is used to calculate the number of cells in a range named Asset doesn't work, what does it imply? A. The range Asset consists of non-contiguous cells B. The formula syntax is incorrect, e.g., it should read =columns (asset) * Rows (asset) C. The range Asset is a rectangular range of contiguous cells D. Some cells in the range are empty

The range Asset consists of non-contiguous cells

The "Go to Special" dialog box in Excel allows a user to select all of the following EXCEPT: A. Cells that contain comments B. Blank cells C. All cells that differ from the active cell in a selected column D. The second to last cell in a selected column that contains data or formatting

The second to last cell in a selected column that contains data or formatting

themes_styles_formats.xlsx Please see the attached workbook, Sheet1. What happens when you apply a theme on Sheet1 of the Excel sheet by going to Page Layout -> Themes A. The theme is applied to Sheet1 only B. The theme is applied to all the sheets in the workbook C. The theme is applied to Sheet 2 only D. The theme is applied to Sheet3 (or the last worksheet in the workbook) only

The theme is applied to all the sheets in the workbook

The formula =COUNTIF(range, "*budget*") will count the number of cells in the specified range containing the word "budget" in certain positions within the text. Which of the following best describes the cells counted? A. The word "budget" must appear in the cells surrounded by asterisks (i.e., the * character) B. The word "budget" can appear anywhere within the cell. C. It looks specifically for cells contains budget multiples by some number. For example, a cell contains the formula: "=budget*0.25" D. Only cells with the text "budget constraint" is counted, but a cell with "Previous budget estimates" is not counted

The word "budget" can appear anywhere within the cell.

When a row of data is very ____ and requires repeated horizontal scrolling, consider using a data form to add, edit, find, and delete rows A. Wide B. Narrow C. Transformed D. Empty

Wide

You can use a data Form to add, find, and edit rows in a range or table. Is this correct? A. yes, all these are valid form operations B. incorrect, as adding rows is not supported. C. false, data forms work only with tables, not with ranges D. no, data forms work only with ranges, not with tables

Yes, all these are valid form operations

cells_sales.xlsx Please see the attached workbook. If you copy cell "B6" and paste it as a formula in cell "C6," will it produce the correct total for Year 2? A. No, because the result will be the total for Quarter 1 B. No, because you must directly enter the formula for each quarter in cell C6 C. Yes for Excel 2013, but not for pervious versions of Excel D. Yes, the formula range will be adjusted automatically

Yes, the formula range will be adjusted automatically

Which of the following does not hold true for Watch Windows? A. You select the cells that you want to watch, from the Formulas (tab) --> Formula Auditing --> click Show Watch Window B. You can see all the formulas in a worksheet and print them for later reference C. It helps you to keep an eye on a cell and its formula while you work, even when the cells are out of view D. You can move the Watch window toolbar around your open window

You can see all the formulas in a worksheet and print them for later reference

What characters are NOT allowed in sheet names? A. [] B. , C. All options are correct D. !

[]

Which of the following best describes what a Sparkline is? A. a mini-chart that is displayed in a single cell B. a blinking line to separate two cells C. a highlighted cell D. a dynamic line chart where the lines have metallic color effects

a mini-chart that is displayed in a single cell

Which of the following file formats can be opened using Excel 2016? A. all the other options are correct B. XLS C. XLT D. XLA

all the other options are correct

If there are two or more name / value pairs passed in a URL query string, separate them with the following character: _____________ . A. comma (,) B. semi-colon (;) C. ampersand (&) D. colon (:)

ampersand (&)

While creating a parameterized web query the question mark (?) is used to _______. A. being the URL query string B. delimit the name from the value C. delimit each name/value pair D. delimit the parameter from the prompt

being the URL query string

Please refer to the figure. The address of the active cell consists of which of the following? A. column letter, row number B. column height, row number C. column letter D. row number

column letter, row number

In Excel, the ISNONTEXT() function returns TRUE if its argument refers to which of the following? A. content other than text (e.g., a date or a number) or a blank cell B. a cell containing data in numeric or date format C. this function is used primarily for blank or empty cells D. a cell with alphanumeric characters

content other than text (e.g., a date or a number) or a blank cell

Which of the following does the function =NOW() return? A. current date B. current time C. current date and time D. nothing, it is not valid Excel function

current date and time

When you enter "FALSE" for the range_lookup parameter in HLOOKUP, you are telling Excel that you want to find a(n) ________ match A. exact B. different C. dissimilar D. orderly

exact

A chart in an Excel worksheet provides a visual representation of which of the following? A. numeric values B. forumlae C. text values D. objects

numeric values

You select a cell with data "120" in an Excel worksheet and press the "Delete" key on your keyboard. Which of the following holds TRUE? A. only the contents of the cell are removed B. the contents and formatting of the cell are removed C. only the formatting cell is removed D. no changes occur

only the contents of the cell are removed

Data retrieved from Web Queries do not include __________ and contents of __________. A. text, data B. text, pictures C. pictures, scripts D. text, tables

pictures, scripts

What is the Skip blanks option (under Excel Paste Special option) used for while copying a range to another area? A. to prevent blank cells in the copied rang from overwriting existing data B. to skip pasting content into the blank cells C. to copy the blank cells from the copied range into the paste area D. none of the above

to prevent blank cells in the copied range from overwriting existing data

Which of the following methods will keep a heading at the top of a table visible while you scroll through rows lower down in the worksheet? A. use Freeze Panes B. This cannot be done in Excel 2013 C. use the Split or Hide functions D. you must print a copy of the worksheet to do this

use Freeze Panes

What form of LOOKUP is used when you have a large list of values to look up or when the values may change over time? A. array form B. static form C. vector form D. dynamic form

vector form