MIS 112 Final

Which operator in Excel combines multiple range references into a single reference (e.g., if I want to sum up the values in non-contiguous cell-ranges across the worksheet)? "," (comma) ":" (colon) ";" (semi colon) " " (space)

"," (comma)

Please see the attached workbook. Using the formula =B2/C2 what error is displayed? #N/A #NULL! #DIV/0! #NAME?

#DIV/0!

The Excel function: ISERR() returns TRUE if the cell contains any error value except which of the following? #N/A #REF! #VALUE! #NULL!

#N/A

Examine the attached file: PS1_formulas.xlsx Calculate the total yearly sales for the West Region and the South Region in cells "F5" and "F7" (shaded in orange). What is the total widget sales for all regions for the year? $9,019 $13,218 $16,060 $24,914

$24,914

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

$5,129

What is the file format (file extension) used when you save an Excel workbook as a single file webpage? .html .mht or .mhtml .xlsx .txt

.mht or .mhtml

Which of the following options, when used as a formula in the Custom Data Validation criteria for cell A1, will ensure that the entry in cell A1 begins with the letter P and contains exactly four characters? =COUNTIF(A1, "P???")=1 =COUNTIF(A1, "P???")=4 =COUNTIF(A1, P???)=4 =COUNTIF(A1, P???)=1

=COUNTIF(A1, "P???")=1

If you place the link target in cell C1 on the same worksheet, how will you use an absolute reference to that cell as the link_location in the HYPERLINK formulas? =HYPERLINK($C$1) =HYPERLINK(C1$) =HYPERLINK(C1$C1*) =HYPERLINK("","C1$C1*")

=HYPERLINK($C$1)

Based on the class lecture, which of the following is true about table renaming? Table renaming is not recommended in Excel as it will cause formulas to stop functioning properly Renaming a table is done automatically when a Table is extended (e.g., when you add a new row of data) A table can be renamed to better reflect its contents (e.g., PARTS or PRODUCTS) 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 is TRUE with respect to charts? After you create a chart, you have great deal of flexibility in customizing it. Data is NOT needed to create a chart. Plot area contains all the elements of the chart. A Pie chart is another name for a Column chart.

After you create a chart, you have great deal of flexibility in customizing it.

Which of the following is the default font type and font size in Excel 2016? Calibri, 11 point Arial, 12 point Times New Roman, 10 point Cambria, 14 point

Calibri, 11 point

Which of the following concepts are useful in understanding relationships between cells and formulas? Cell Credits Blue-on-Green Precedents Cell Dependents The One-Time Rule

Cell Dependents

How do you set up a data validation (using the data validation dialog box) that restricts data entry to a decimal number within limits? Click the Settings tab (in the Data Validation box). In the Allow box, select 'Decimal'. In the Data box, select 'between', and set the minimum and maximum. Home (tab) -> Editing (group) -> Find & Select -> Data Validation Formulas (tab) -> Calculation (group) Click the Settings tab (in the Data Validation box). In the Allow box, select 'Text Length'.

Click the Settings tab (in the Data Validation box). In the Allow box, select 'Decimal'. In the Data box, select 'between', and set the minimum and maximum.

Which option under conditional formatting applies background color proportional to the cell's value. Color scales Icon sets Data Bars Highlight Cells rules

Color scales

Please refer to the attached workbook. What does the pivot chart depict? Compares the amount in the three branches on the basis of AcctType. Compares number of accounts in the three branches by account type Compares the customer type in the three branches None of the answers provided is correct

Compares the amount in the three branches on the basis of AcctType.

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

Ctrl + N

Which of the following keyboard shortcuts will add the current time to a cell in an Excel worksheet? Ctrl + Shift + : Ctrl + ; Ctrl + ' Ctrl + T

Ctrl + Shift + :

Please refer to the figure. Which of the following Excel tabs is NOT visible in a worksheet by default? Developer Review Home Insert

Developer

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? Home Review Insert Formulas

Home

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

In Tracing Error Values

Fill in the blank: Every chart type has a set of ____ that you can choose from (with settings for chart elements such as a title, data labels, axes, and so on). Shapes Categories Layouts Attributes

Layouts

Please refer to the attached workbook. What happens if the following value filter is added to the existing report? Value filter: Sum of Sales is greater than 5000. Only one record corresponding to the 'CA' state remains as its total sales amount is greater than 5000. Only one record corresponding to the 'AZ' state remains as its total sales amount is greater than 5000. Nothing happens. Both the records remain. No records remain as they don't satisfy the condition specified.

Only one record corresponding to the 'CA' state remains as its total sales amount is greater than 5000.

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

Protect you from malware

Please refer to the figure. What Excel element is shown in the red box? File button Quick Access toolbar Scrollbar Ribbon

Quick Access toolbar

Please refer to the figure. Which of the following View ribbon tab options was used to display the horizontal bar between the nonconsecutive rows containing "Astoria, OR" and "Victoria, TX"? Split New Windows View Side by Side Hide

Split

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 ? Synchronous Scrolling None of the (other) options provided is correct Toggle Side View

Synchronous Scrolling

What type of cell reference does the formula in E2 use? (refer to the attached image, the formula is highlighted) price oriented absolute relative incorrect

absolute

What does the Excel keyboard shortcut Ctrl+Alt+F9 do? highlights error codes in the current workbook calculates only the formulas in the active worksheet. Other worksheets in the same workbook aren't calculated. forces a complete recalculation of all formulas in all open workbooks. Ctrl+Alt+F9 will not do anything in Excel

forces a complete recalculation of all formulas in all open workbooks.

In Excel, the Ctrl + Spacebar (keyboard shortcut) is used to ______. select a row select a column select a range select multiple rows or columns

select a column

Which of the following error codes indicates that a formula refers to a cell that is invalid? This type of error can occur if a referenced cell is deleted from the worksheet. #REF! #VALUE! #N/A #NAME?

#REF!

What steps would you take if you wanted to adjust the outline style of a rectangle shape that has already been inserted into your worksheet? 1) Click the shape. 2) Click (Drawing Tools) Format. 3) In the Shape Outline group, change the settings. 1) Click the shape. 2) Click Home (tab) --> Font (group) --> Fill Color 1) Click the shape. 2) Click (Drawing Tools) Format. 3) In the Shape Styles group, select a different outline feature. 1) Click the shape. 2) Hold shift while pressing the up/down arrow keys to increase/decrease the size of the outline.

1) Click the shape. 2) Click (Drawing Tools) Format. 3) In the Shape Styles group, select a different outline feature.

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

12

In the attached spreadsheet, please use an appropriate formula to compute the total of the Cells C2 through C11 (surrounded by a red border in the worksheet). What is this total? 14537.45 14875.5 14787.45 9787.45

14787.45

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

5

Which of the following formulas will display both the current date and time when entered into a cell in an Excel worksheet? = NOW() = DATE() = TIME() = TODAY()

= NOW()

Examine the attached Excel file. When the formula in "D2" is copied to the cells below it, cell "D3" will contain what formula? =(B3*C3)*$B$7 =(B3*C3)*B8 =($B$3*$C$3)*$B$7 =(B3*C3)*B7

=(B3*C3)*$B$7

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? ={B4+C4} =B4*C4 =B3+C3 =B4+C4

=B4+C4

Please see the attached workbook. The grade of the student should be "Pass" if his/her score is greater than 65. Otherwise, the grade should be "Fail". Which of the following formulas could you use for this scenario? =IF(D2>65,"Fail","Pass") =IF(D1>65,"Pass","Fail") =IF(D2>65,"Pass","Fail") =IF(E2>65,"Pass","Fail")

=IF(D2>65,"Pass","Fail")

What is one of the DRAWBACKS of using a pivot table? A Pivot Table does not update automatically when information changes in source data A Pivot Table updates automatically when information is changed in source data Pivot Table totals do not update automatically when you choose a different field for a Row Label Pivot Table totals update automatically when you choose a different field for a Row Label

A Pivot Table does not update automatically when information changes in source data

A Loan amortization schedule in Excel is : A table of values that shows various types of information for each payment period of a loan A table of values that shows various types of information for any one payment period of a loan A table of values that shows only Present Value information for the entire payment period of a loan None of the above

A table of values that shows various types of information for each payment period of a loan

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

A workbook can have many worksheets.

If Row 15 needs to be on the first row of a new page and only a horizontal page-break is needed, what cell should be selected to create a page break? B15 A14 A15 B16

A15

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

AA

If you copy a formula down a column, and need a cell reference to stay the same (for example, you don't want B2 to change to B3), what type of cell reference do you need? (Example: Use of F4 key) Absolute Relative Frozen Automated

Absolute

If you write a lookup formula to look up the text 'education', the formula considers which of the following a match? EDUCATION Education education All answers provided are correct

All answers provided are correct

An Excel formula may return which of the following errors? #REF! #DIV/0! #VALUE! All of the above

All of the above

Which of the following is TRUE of the "Protect" option in an Excel Workbook? it helps prevent unauthorized users from copying existing worksheets it helps prevent unauthorized users from revealing hidden sheets it helps prevent unauthorized users from inserting new worksheets All of the above options are true

All of the above options are true

How can you edit the contents of cell (by entering cell-edit mode)? Choose the best answer. All of the options provided are correct Double-click the cell Select a Cell and press F2 Select a Cell and then click inside Formula bar

All of the options provided are correct

Which of the following actions can be performed on objects placed on the drawing layer with no effect on any other elements in the worksheet? move resize delete All of the options provided are correct

All of the options provided are correct

Which of the following is a type of Sparkline in Excel? Line Column Win/Loss All of the options provided are correct

All of the options provided are correct

How will you refresh the query after moving the cell pointer anywhere within the external data table in the worksheet? Right-click and choose Refresh from the shortcut menu Choose: Data (tab) -> Connections (command group) -> Refresh All. Click "Refresh" in the Workbook Connections dialog box (displayed by choosing: Data (tab) -> Connections (command group) -> Connections ). All the options in this question are valid

All the options in this question are valid

There are a few commands that could be used to customize sparklines to highlight parts of data. One of them is Negative Points. What is its purpose? Apply a different color to the highest data point in the Sparkline Apply a different color to negative values in the Sparkline Apply a different color to the lowest data point in the Sparkline Apply a different color to the first data point in the Sparkline

Apply a different color to negative values in the Sparkline

Examine the attached workbook. Fill in the blank: You can add a title to the vertical axis of the chart by using Chart Layouts (Chart Tools "Design" tab) --> Add Chart Element --> __________ Chart Titles Legend Axis Titles Data Labels

Axis Titles

With regards to Depreciation Calculation functions in Excel, which of the following are true about the function DDB()? Computes depreciation at an accelerated rate. Depreciation is highest in the first period and decreases in successive periods. Its arguments are: Cost, Salvage, Life, Period, [Factor] Both A and B None of the above

Both A and B

How can you access the dialog box for Pivot Table options? PivotTable Tools (tab) ->Data PivotTable Tools-> Analyze (tab) -> Pivot Table (command group) -> Options Right Click on a cell within the pivot table and choose "PivotTable Options" Both b and c

Both b and c

Which of the following is true for printing a worksheet that contains objects / shapes? Objects are printed along in the worksheet by default You can avoid printing the object by making changes to the properties of the shape Both of the above None of the above

Both of the above

What command would you use to import a CSV file? Choose Data --> Get External Data --> From Text Choose Data --> Get External Data --> From CSV Choose Data --> Import Data --> External None of the provided options is correct

Choose Data --> Get External Data --> From Text

To gain access to the Table Tools / Design tab in the Excel Ribbon when working with tables, you should do the following: Click anywhere on the table Such a tab does not exist, the only tab you can get access to is: Table Format/Design Click on the Home tab on the Ribbon, and check the appropriate box to make it appear The Table Tools/Design tab does not provide any useful commands, so it is pointless trying to access it

Click anywhere on the table

What is the shortcut to open the Find and Replace dialog box with the "Find" tab open? Ctrl+H Ctrl+F Ctrl+A Ctrl+Alt+F

Ctrl+F

What is the shortcut key to create hyperlink when you select an object or a cell? Ctrl+H Ctrl+I Ctrl+J Ctrl+K

Ctrl+K

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? Format tab (chart tools) --> Reset to Match Style (under the "Current Selection" group) Right click on the chart --> Reset to Match Style Either of the previous options (resetting through the menu or the right-click options work) Home tab --> Wrap Text, followed by, Paste

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

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

Excel can open all of these files.

What is the result of the formula=2^3 * (,10*10 Excel tells you that the formula contains an error 800 600 150

Excel tells you that the formula contains an error

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 ends with 'm' for 'Name'. From the Column to Filter list, select Name. In the right panel, select 'ends with' from the first drop-down list and then type 'm' in the second drop-down list box From the Column to Filter list, select Name. In the right panel, select 'begins with' from the first drop-down list and then type 'm' in the second drop-down list box From the Column to Filter list, select Name. In the right panel, select 'equals' from the first drop-down list and then type 'm' in the second drop-down list box 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 Name. In the right panel, select 'ends with' from the first drop-down list and then type 'm' in the second drop-down list box

How does a missing data point show in a Sparkline graphic? Gap Zero Automatically connected to the next data point A Horizontal line

Gap

Which of the following is a VALID method of hiding rows or columns? Home (ribbon tab), (then) Format (in the Cells group) --> Hide & Unhide (from the drop down list) Click on the Row/Column header, (then) right-click and choose Delete Right-click on the cell, (then) choose "Insert" Home (ribbon tab), (then) Paste (in Clipboard group)

Home (ribbon tab), (then) Format (in the Cells group) --> Hide & Unhide (from the drop down list)

Which of the following functions could be used if you want to display "NOT FOUND" when you lookup for an item that is not part of the lookup_vector? SUM MATCH INDEX IFERROR

IFERROR

_________ is used to insert a nicely formatted mathematical equation as a graphic object. Insert (tab) --> Illustrations (group) --> Math Insert (tab) --> Smart Art --> Text box Insert (tab) --> Sparklines --> Sparkline type Insert (tab) --> Symbols (group) --> Equation

Insert (tab) --> Symbols (group) --> Equation

To add one or more Slicers to a worksheet, start by selecting any cell in a pivot table and then choose ______________. Insert (tab) -> Filters (group) -> Slicer. The Insert Slicers dialog box appears. Insert (tab) -> Slicer (group) -> Filters. The Insert Slicers dialog box appears. Right Click on it. The Insert Slicers menu option can be selected. All of the answers provided are correct

Insert (tab) -> Filters (group) -> Slicer. The Insert Slicers dialog box appears.

The basic formula for computing simple interest is: Interest = Principal * Rate * Term Interest = Principal * Rate Interest = Principal * Term Interest = Simple * Term * Principal * Normal

Interest = Principal * Rate * Term

Please see the attached Excel workbook. Suppose you are on Sheet1 and you have selected cells A5:B5. What happens when you insert a background image through the following sequence of steps? Page Layout (tab) -> Page setup (command group) -> Background? It adds the background image to the selected cells It adds the selected background image to Sheet1, Sheet2, Sheet3 in the current workbook It adds a background image to the currently active worksheet It adds a chart with the background image embedded

It adds a background image to the currently active worksheet

When a column is sorted within a table, which of the following is true about the drop-down list in the header row? The header row background fill color changes to a darker shade It displays a different graphic to remind you that the table is sorted by that column There is no change to the header for that column before or after sorting 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

Does deleting a cell by pressing the Delete key also delete the conditional formatting on the cell? No, it only deletes the cell's style No Yes, but only if the data is text Yes

No

Please examine the attached workbook. The chart was created based on data in cells A1 through C6. If new data is added in cells A7 through C7, will the chart include the new data automatically? PS2_Chart1 No, this feature is only supported for Clustered Column charts Yes, but only because it is a Stacked Column chart No Yes

No

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? Yes, because Excel can see that the dates in the two cells are the same value. No, because dates that are a duplicate cannot be removed. 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). 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.

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.

Can you print data in an Excel Form attached to a table? No, you cannot Yes, you can always perform an action like Printing in Excel You can print forms with the "Print" button on the Form only Printing is disabled unless you buy the "Print Form" Excel Professional feature app from Microsoft

No, you cannot

Which type of chart does not allow format changes to the Plot area? Bar chart Treemap Thermometer chart None of the (other) options provided is correct

None of the (other) options provided is correct

When you use the HLOOKUP() function, which of the following parameters represents the row number from which the matching value will be returned? range lookup lookup_value row_care_param None of the options provided is correct

None of the options provided is correct

Please see the attached workbook. What value of Discount is returned when you use an VLOOKUP function with a quantity entered (i.e., value looked up) of 1001 and a range-lookup parameter of FALSE? 3% 6% 12% None of the other answers are correct

None of the other answers are correct

Please see the attached Excel workbook image. The cells highlighted in yellow contain a Total Row. How can such a Total Row be added to a table? Once you have a table, you can click the "Table Tools / Design" tab. Then, under "Table Style Options" check the box for "Total Row." This cannot be done anymore, it is an unsupported feature in Excel 2013 The most effective way is to add a new row, and manually add total options Adding a Total Row requires knowledge of Visual Basic which is not covered in this course

Once you have a table, you can click the "Table Tools / Design" tab. Then, under "Table Style Options" check the box for "Total Row."

Please refer to the attached Excel workbook. How can you add a dollar ($) symbol to values in a column as seen in the "Grand Total" column? The simplest way is to click on the "Wrap text" button in Home tab. From the dialog box that pops up, pick the default $-symbol formatting. This is automatically provided to you when you make an Excel Table One way to do this is by going through the "Format Cells" dialog, and selecting "Accounting" (with a $ symbol and 2 decimal places) as your formatting option from within the Number tab The correct way to do this is by going through the "Format Table" menu, and selecting the option of "Number" (with 2 decimal places and a $ symbol)

One way to do this is by going through the "Format Cells" dialog, and selecting "Accounting" (with a $ symbol and 2 decimal places) as your formatting option from within the Number tab

Which financial function returns the principal part of a loan payment for a given period, assuming constant payment amounts and a fixed interest rate? CUMPRINC() IPMT() PMT() PPMT()

PPMT()

If you need to take a printout of a report, how can you specify the paper size you re using? Page Layout (tab) -> Page Setup (group) -> Breaks Page Layout (tab) -> Page Setup (group) -> Background Page Layout (tab) -> Page Setup (group) -> Size None of the above

Page Layout (tab) -> Page Setup (group) -> Size

_______ refers to the non-printing areas along the sides, top, and bottom of a printed page. You do not see data from worksheet cells here, but may see a header or footer. Watermarks Page Margins A Background Image Hidden text

Page Margins

Which of the following is TRUE about Web queries? Performing Web queries creates a new HTML file Performing Web queries copies information from an HTML file into the current workbook Web queries are not useful in Excel because an external data can change None of the options provided is true

Performing Web queries copies information from an HTML file into the current workbook

A calculated item, unlike a calculated field, does not appear in the _________ Field List. PivotChart PivotTable Both options A & B None of the above

PivotTable

________ can be used to check whether all of the columns fit on one page before printing an Excel worksheet. Quick Print Print Preview Page Layout A Pivot Table

Print Preview

Which of the following is NOT a summary function for Pivot tables? StdDev Varp Var Regression

Regression

Please see the attached workbook. Which of the following steps allow you to add a comment to a specific cell? (When you move over the cell with your mouse, the comment will pop up) Review -> Comments -> New Comment Review -> Comments -> Show Ink Formulas -> Lookup&Reference Review -> Changes -> Allow Users to Edit Ranges

Review -> Comments -> New Comment

Please refer to the figure. What Excel element is shown in the red box? File button Quick Access toolbar Scrollbar Ribbon

Ribbon

cumulativesum.xlsx Please see the attached workbook. Cell C7 currently has a number entered manually. This number should be replaced with a formula. What is the best formula to use in cell C7? SUM(C2:C6) SUM(B[2:7]) SUM(B$2:B7) SUM(B&2:B7)

SUM(B$2:B7)

Which of the following methods is a VALID way to get into cell edit mode? Clicking the cell one time enables you to edit the cell contents Selecting the cell that you want to edit, and then clicking in the Formula bar Pressing F9, which enables you to edit the cell contents directly in the cell Pressing F4 allows you to the edit the cell contents

Selecting the cell that you want to edit, and then clicking in the Formula bar

To avoid printing more pages than necessary in Excel, you can use the following command: ________ . Set Print Area Reset Page Breaks Page Layout: Squashed Background Printing

Set Print Area

Excel formatting tools are available at the following locations: The Format Cells dialog box and the Home Tab of the Ribbon On the Home Tab of the Ribbon only On the Mini toolbar that appears when you right-click a range or a cell, and the Home Tab of the Excel Ribbon The Format Cells dialog box, the Home Tab of the Ribbon, the Mini toolbar

The Format Cells dialog box, the Home Tab of the Ribbon, the Mini toolbar

What part(s) of a table can be selected? An entire column, but not an entire table An entire row, but not an entire column The entire table or an entire row only The entire table, an entire row, or an entire column from a table can be selected

The entire table, an entire row, or an entire column from a table can be selected

What is NOT correct about headers and footers? The header appears at the bottom of each page while the footer appears at the top of each page. The footer appears at the bottom of each page while the header appears at the top of each page. The headers and footers consist of three sections: left, center, right. The headers and footers can be specified using the Header/Footer tab of the Page Setup dialog box

The header appears at the bottom of each page while the footer appears at the top of each page.

f a pivot chart is created and the underlying table is deleted, what occurs? The pivot chart is also deleted. The pivot chart remains. The data the Chart is based on is invalid since the Pivot Table has vanished. The chart is not deleted, but it no longer contains the data from the underlying table.

The pivot chart remains.

In Excel when calculating loan information, what does the PV() function return? The time-adjusted value of the original loan amount The interest part of a loan amount for a single period The periodic interest part of a loan None of the above

The time-adjusted value of the original loan amount

What is the output of the following command sequence: click the Analyze tab under PivotTable tools -> Actions (group) -> Clear -> Clear Filters? This removes all the filters at once This removes only filters on the selected column (or row) This does not remove any filters This is not a valid command sequence for any pivot table

This removes all the filters at once

Excel formatting tools are used to achieve which of the following? To create programming code in Java/C++ To make the end result attractive and more functional To distract readers from inaccuracies in text and figures The primary use of formatting is to create Excel Pie Charts All of the options listed are valid

To make the end result attractive and more functional

Which formula would you use if you want to accept only text that begins with a specific character 'a' for a given cell 'A1'? Use a formula: =LEFT(A1)="a" Use a formula: =RIGHT(A1)='a' Use a formula: =UPPER(A1)='A' None of the options provided is valid

Use a formula: =LEFT(A1)="a"

Which of the following is an advantage of using Excel tables? Tables are the best way to display percentages of a whole (e.g., portfolio percentage composition) By sorting results largest-to-smallest, tables allow you to search for hidden patterns in data, much like playing a record backwards Tables encrypt our data so a hacker is unable to read it by obtaining a copy of the Excel workbook Using table and column names in formulas provides greater flexibility as extending a table does not require re-writing of formulas

Using table and column names in formulas provides greater flexibility as extending a table does not require re-writing of formulas

Which of the following options provides correct syntax for the VLOOKUP function? VLOOKUP(lookup_value, table_array, col_index_num, range_lookup) VLOOKUP(lookup_value, table_array, row_index_num, range_lookup) Either of the two previous choices will work None of the options provided is valid

VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

Please see the attached screen capture. If we add one more month to the table (i.e., September), will the Sparkline update its Data range automatically? Yes, the data range is automatically updated No, it will not be updated automatically We have to make the changes manually to the graph None of the options provided is correct

Yes, the data range is automatically updated

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? No, because the result will be the total for Quarter 1. No, because you must directly enter the formula for each quarter in cell C6. Yes for Excel 2013, but not for previous versions of Excel. Yes, the formula range will be adjusted automatically

Yes, the formula range will be adjusted automatically

Different table formatting styles can be previewed by mousing over different options within "Table Styles" (i.e., you can see what the appearance will be like without actually applying a new style). Is this statement accurate? Yes, the statement is accurate No, this is a false statement The statement is accurate only for the first 7 styles previewed. After that, to conserve memory, Excel will apply a style and you can start the preview process over again. Yes, previews are possible as long as the Total Row option is not checked

Yes, the statement is accurate

When you try to import data from MS-Access into Excel, which of the following is TRUE? You can import a table or a query Excel no longer supports this type of import Excel imports reports, forms and query SQL You must convert the database into XML output before the import

You can import a table or a query

Which of the following characters is VALID for use in an Excel workbook file name? _ : " \

_

Which of the following is a VALID range name in an Excel workbook? Income Taxes _2ndyear 3rdQuarter Total_percent%

_2ndyear

Please see the attached workbook. The formula in cell C4 is "=B4 + (B4 * $C$1)". Formulas in range C4:C7 use a / an _________ reference to cell C1. absolute columnar mixed none of the options provided is correct

absolute

The Linked Picture paste option in Excel ________. allows you to link to a clip-art, instead of saving it within your workbook is a dynamic / live picture of the range you copy Both options (A) and (B) is primarily used with WordArt

allows you to link to a clip-art, instead of saving it within your workbook

A pivot table and a pivot chart are joined in a two-way link, if you make structural or filtering changes to one, the other _______. also changes remains unchanged shows a sparkline graphic to highlight the change can be eliminated to avoid duplicates

also changes

Consider the HLOOKUP function in Excel. If the "range_lookup" argument is set to TRUE, we need to sort the first column of the range in ____________ order descending ascending color coded ranged

ascending

When you create one or more Sparklines in Excel, by default they all use _________ axis scaling. automatic manual batch linear

automatic

Please see the attached workbook. In cell A2, how is the data validation done? by checking if there are no numbers present by checking if there are no special characters such as '#','$','*' etc. by checking if the number of characters is between 4 and 20 by checking to ensure there are at least 5 characters present

by checking if the number of characters is between 4 and 20

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

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

Which of the following can be retrieved from a web page using web queries? .gif image contents of scripts .jpeg images data in a table

data in a table

While creating a parameterized web query the ampersand character (&) is used to ______. delimit the parameter from the prompt delimit the name from the value delimit each name/value pair begin the URL query string

delimit each name/value pair

When you create a PivotTable report, each column of your source data becomes a _____ that you can use in the report. row record field sheet

field

What is the use of the following data validation formula: =ISTEXT(A1) ? forces the cell A1 to accept only text forces the cell A1 to accept only numbers forces the cell A1 to accept only ' * ' forces the cell A1 to accept only ' + '

forces the cell A1 to accept only text

Please see the attached workbook. What is the URL Query String for the extract in the attached worksheet? http://www.imdb.com/ http://www.imdb.com/careers http://www.imdb.com/latest http://blackboard.eller.arizona.edu/

http://www.imdb.com/

A #REF! error occurs in Excel when a formula uses a / an ________ cell reference valid invalid multi-cell average summation within a

invalid

Consider a Column Sparkline with three columns. A wide area of blank space between two columns in a sparkline corresponds to which of the following? boolean data missing data a right aligned cell a value formatted as currency (not supported by sparklines)

missing data

In Excel, what does the MODE function in a range return? most frequently occurring value in a range mean of the values in the range most frequently occurring text entry in a range middle value of the range

most frequently occurring value in a range

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? only the contents of the cell are removed only the formatting of the cell is removed the contents and formatting of the cell are removed no changes occur

only the contents of the cell are removed

You can protect your Excel workbook with a password. Which of the following is TRUE about password protected workbooks? passwords are NOT case sensitive passwords are case sensitive if you forget your workbook password, you can email the TA to retrieve it for you workbook passwords automatically expire in 360 days

passwords are case sensitive

Which of the following options best describe what the following formula would do? =VLOOKUP(D2,PAGES!A2:B10,2,TRUE) perform the vlookup in a different worksheet which has an exact match perform a vlookup in the same worksheet (exact match) perform a vlookup in a different worksheet which does not have an exact match None of the (other) options provided is valid

perform a vlookup in a different worksheet which does not have an exact match

A group of cells is referred to as which of the following? group range pack class

range

For VLOOKUP() and HLOOKUP(), the lookup_value argument can include wildcard characters if the lookup_value argument is ______ and the range_lookup argument is ________ numeric; true numeric; false text; false text; true

text; false

A _____ data table in Excel shows the result of a single calculation for different values of two input cells multiple one-way two-way None of the above

two-way

The data source for a pivot table can reside in a _________ or in a / an ____________. worksheet, external data file database, lookup list bar chart, lookup list database, textbox

worksheet, external data file