MIS 112 Final

If you click outside of the layout area (of a PivotTable report), the PivotTable Field List ________. increases in size goes away decreases in size turns blue

goes away

To extract a student's first name (e.g., "Herbert") from their full name (e.g., "Herbert Smith"), which of the following functions will you typically use? A combination of DATE(), FIND() and FIRST() A combination of REPT() and CONCATENATE() A combination of FIND() and MID() The function FIRST()

A combination of FIND() and MID()

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? 4500 500000 4750 6750

4500

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

5

The Excel CODE function returns the character code for its argument. What does the following formula return? =CODE("B") 65 66 68 1

66

To display today's date in the format like "Today is Friday April 4, 2014", which formula should you use? ="Today is "&TEXT(TODAY(),"dddd, mmmm, yy") ="Today is "&TEXT(TODAY(),"dddd, mmmm d, yy") ="Today is "&TEXT(TODAY(),"dddd, mmmm, yyyy") ="Today is "&TEXT(TODAY(),"dddd, mmmm d, yyyy")

="Today is "&TEXT(TODAY(),"dddd, mmmm d, yyyy")

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.

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

Ctrl+L

Examine the attached Excel file PS1_formatting.xlsx In Row 2 (Col B), which of the following formatting types has been applied to "3419.25041" to make it appear as "$3,419.25" ? formatting.xlsx General Number Currency Special

Currency

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

An Excel workbook can contain one or more of which of the following? worksheets files notebooks filenames

worksheets

The ____ command is used to display text on multiple lines in a cell. shrink to fit wrap text merge cells icon formatting

wrap text

Microsoft Office Excel can automatically generate a data form (which allows for convenient data entry, among other things) for your range or _______________. Chart Field Table Record

Table

In Step 1 of the Query Wizard, you select database _________ that you want to appear in your query. columns records rows sort order

columns

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

A pivot table calculated field consists of a calculation that involves other _______. fields chart example None of the answers provided is correct

fields

When choosing a page orientation, ____ is one of the two valid options. Landscape Narrow Wide Tall

Landscape

Which of the following is NOT a formatting option offered through the Mini Toolbar? (Hint: for more information on the mini-toolbar, you can right-click on an Excel cell) Comma Style Format Painter Left Align Center Align

Left Align

How do you change the calculation mode while dealing with a calculation intensive data table? Formulas (tab) -> Calculation (group) -> Calculation Options -> Automatic Except For Data Tables Formulas (tab) -> Calculation (group) -> Calculation Options -> Automatic Formulas (tab) -> Calculation (group) -> Calculation Options -> Manual Press Ctrl+C, followed by: Alt+P

Formulas (tab) -> Calculation (group) -> Calculation Options -> Automatic Except For Data Tables

What does Excel display when you enter the formula =DATE(2014,13,1)? "1/1/2015" "1/1/2014" "1/31/2014" "1/31/2015"

"1/1/2015"

The HLOOKUP function works like VLOOKUP function except that the lookup table is arranged ___________ instead of __________. "vertically, horizontally" "vertically, index" "horizontally, vertically" "by match, by index"

"horizontally, vertically"

What result does the following formula display? =DOLLAR(5742.999,2) $5,742.99 $5742.99 $2,999,2475 $5,743.00

$5,743.00

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

By default, what is the extension used when you save a workbook in Excel 2016? .xlsx .xslx .xls .xsl

.xlsx

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

12

With a single IF() function, how many actions can you ask Excel to carry out? 1 (i.e., an action if the criteria is true) 2 (i.e., an action if the criteria is true, or an an action if the criteria is false) 3 (i.e., an action if the criteria is any one of true, false, unknown) 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)

Please choose the formula that will calculate the future value of a $6,500 investment at 4.55 percent compounded continuously for 4 years. =6500+EXP(4.55%*4) =6500*EXP(4.55%*4) =6500/EXP(4.55%*4) =6500+EXP(4.55%+4)

=6500*EXP(4.55%*4)

Use the attached spreadsheet to answer the following question. Which formula can be used to find: How many songs were sung by singers whose names begin with S? =COUNTIF(B2:B23,"S*") =COUNTIF(B2:B23,"*S") =COUNTIF(B2:B23,"S") =COUNTIF(B2:B23,"S?")

=COUNTIF(B2:B23,"S*")

To generate a series of dates 1 year apart in Excel, which of the following formulas is correct? (assuming the first date is entered in cell A1, and you are trying to compute the next year in the series) =DATE(YEAR(A1), MONTH(A1) + 1, DAY(A1)) =A1 + 7 =DATE(YEAR(A1) + 1, MONTH(A1), DAY(A1)) =DATE(YEAR(A1), MONTH(A1), DAY(A1) + 1)

=DATE(YEAR(A1) + 1, MONTH(A1), DAY(A1))

Which of the following is the correct formula for converting the hexadecimal argument "3BC" to its decimal equivalent? =HEX2DEC("3BC") =HEXTODEC("3BC") =HEX2DEC(3BC) =HEXTODEC(3BC)

=HEX2DEC("3BC")

If you want to use the HYPERLINK function to jump to a cell E2 in the same workbook and display "Hello" as the link text, which of the following functions would you use? Assume that the workbook name is Example.xlsx. =HYPERLINK("[Example.xlsx]E2", "Hello") =HYPERLINK("[Example1]E2", "Hello") =HYPERLINK("[Example.xlsx]E12", "Hello") =HYPERLINK("[Example.xlsx]E2", "")

=HYPERLINK("[Example.xlsx]E2", "Hello")

Please see the attached workbook. Which formula is used to provide a link to cell A4 in the same workbook with link text same as text present in A4? =HYPERLINK("[hyperlink_examples.xlsx]A4",A4) =HYPERLINK("[hyperlink_examples.xlsx]A3",A3) =HYPERLINK("[hyperlink_examples.xlsx]A2",A2) Web saving/pages

=HYPERLINK("[hyperlink_examples.xlsx]A4",A4)

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)

Which of the following formulas rounds a time in cell B2 to the closest minute? (You can assume relevant cells have Excel's Time formatting) =ROUND(B2*24,0)/24 =ROUND(B2*60,0)/60 =ROUND(B2*30,0)/30 =ROUND(B2*1440,0)/1440

=ROUND(B2*1440,0)/1440

To eliminate redundant blank space from the start or end of a string (e.g., " Tucson "), which Excel function will you use? =NOBLANKS() =SPLICE() =PROPER() =TRIM()

=TRIM()

What changes will you make in the formula =VLOOKUP(D2,PAGES!A2:B10,2,FALSE) so that it checks for an approximate match? =VLOOKUP(D2,PAGES!A2:B10,3,FALSE) =VLOOKUP(D2,PAGES!A2:B10,2,TRUE) =VLOOKUP(D3,PAGES!A2:B10,3,FALSE) =HLOOKUP(D2,PAGES!A2:B10,3,FALSE)

=VLOOKUP(D2,PAGES!A2:B10,2,TRUE)

Which two wildcard characters are supported by the "Find and Replace" dialog box in an Excel worksheet? ?, * *, / ?, / ?, ^

?, *

A pivot chart is which of the following? A bar chart that can pivot to a pie chart form with a single click A graphical representation of a data summarized in a pivot table A chart that is joined to a pivot table in a 4-way link A chart that cannot be used with a slicer

A graphical representation of a data summarized in a pivot table

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)

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

What will be returned if an Excel LOOKUP() based formula has an entry for the lookup_value and the lookup_vector, but no entry for a result_vector parameter? #N/A An error message (other than #N/A) Excel will not let you enter such a function (it will tell you there's an error in the way you're entering the function and offer to help you correct it) A value from the lookup_vector

A value from the lookup_vector

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

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

AA

You are reviewing a Spreadsheet that records daily sales for Burgers, Hotdogs and Chili. Monday of last week shows exceptionally high sales for Burgers. You want to email the spreadsheet back to your assistant so he can look into whether the data was correctly entered.Which of the following methods should you use to explain your request to your assistant? Add a comment to the sales figure cell, and explain to your assistant what you want him to do Delete the sales figure for Monday (seeing the the blank cell will make things clear to the assistant) Set a fill color of dark red for the sales figure cell Make a pie-chart to explain the ratio of sales of Monday to other days in the week so the assistant sees which day has the highest sale

Add a comment to the sales figure cell, and explain to your assistant what you want him to do

You use Page Break Preview to : To adjust page breaks so that you can see how other changes that you make (such as page orientation and formatting changes) affect the automatic page breaks To see how a change that you make to the row height and column width affects the placement of the automatic page breaks To differentiate between an automatic page break (using dashed lines) and a manual page break (using solid lines) All of the above

All of the above

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 types of Cell Formating can be achieved using the Format Cells feature of Excel? All the other options are correct Alignment Fill Protection

All the other options are correct

What is the keyboard shortcut to clear the formatting in a cell ? Shift -> e -> c -> f Cntrl -> Shift -> Delete Alt -> h -> e -> f Cntrl -> h -> e -> f

Alt -> h -> e -> f

Which of the following options moves the active cell right one screen? Alt+PgDn Alt+PgUp PgDn PgUp

Alt+PgDn

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. MIN() MAX() COUNT() SUM()

COUNT()

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

What command (within Conditional Formatting) is used to delete all the rules relevant to a particular group of cells? Alter Rulebook / edit Alt-F4 Clear Rules New Rule 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: 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

Please see the attached Excel workbook image in answering this question. In order to sort the 'Salesperson' column in a descending order, which of the following steps is correct? Click on any cell containing Salespersons data, from the Ribbon pick the Data tab, and then click on the "Sort Z to A" command button Select the entire Salesperson Column, then Click on the Review tab (in the Excel ribbon), followed by clicking on Group and "Sort" The Salesperson column cannot be sorted Convert the data into a Table, right click on the Table header and select the A-to-Z sort option

Click on any cell containing Salespersons data, from the Ribbon pick the Data tab, and then click on the "Sort Z to A" command button

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 is one of the ways to refresh a query used to import data from external data source using the query wizard? Data (tab) -> Connections (group) -> Properties Data (tab) -> Connections (group) -> Refresh All Refresh Data (tab) -> Connections (group) -> All

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

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

Developer

When a header or footer section is activated, you can insert any of several element codes from the Header & Footer Elements group. Each button inserts a code into the selected section. What is the purpose of the following code: &[Path]&[File] ? Displays the workbook s complete path Displays the workbook s complete path and filename Displays the workbook s filename None of the above

Displays the workbook s complete path and filename

Which of these is NOT a Bitmap based file format image? BMP PNG GIF EPS

EPS

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.

If you want to print a copy of a worksheet, which of the following would you choose? File (tab) -> Print and then choose the PRINT command. Home (tab) -> Print and then press the PRINT button. Page (tab) -> Print and then press the PRINTOUT button. Press Alt+N, and then press the PRINTOUT command

File (tab) -> Print and then choose the PRINT command.

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

File, Save As

The Analysis Toolpak can be activated through which sequence of steps? File-->Options-->Add ins -->Manage Excel Add-Ins --> Go Data-->Data Validation-->Manage Excel Add-Ins --> OK Tools-->Options-->Data-->What-if Analysis Home-->Ribbon-->Analyze-->Analysis Toolpak

File-->Options-->Add ins -->Manage Excel Add-Ins --> Go

Assuming you have a report that contains multiple products from XYZ company. What Pivot table feature would you use to see the data of any one product? Filter Find & Replace Conditional Deletion High Low Close charts

Filter

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

Friendly_name

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. 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 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 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 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

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 lesser than 30000 for 'Salary' column. 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 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 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 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

The HLOOKUP and LOOKUP functions can be found under Formulas (tab) ->_________. Function Library (command group) -> Lookup & Reference Function Library (command group) -> Text Function Library (command group) -> Financial Function Library (command group) -> Logical

Function Library (command group) -> Lookup & Reference

Microsoft Query provides you with a number of features while importing data. Which of the following is NOT an option you can specify or customize through Microsoft Query? Tables to select from an external database Criteria to select specific records from a table Sorting of results from a table Graphing of data from tables

Graphing of data from tables

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

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

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

IPMT()

Which of the following is NOT one of the Excel functions to determine whether a cell contains an error value? ISERROR ISNA ISLOGICAL ISERR

ISLOGICAL

Please see the image below. Which of the following conditional formatting styles is used in creating rules for the "Defects" column? Icon sets 3-Color Scale Data Bars Defects bars

Icon sets

Which of the following statements is true? They all relate to the formula: =IF( OR(Category="Pasta",Category="Soups"), Inventory_Cost,0 ) This type of IF() functions with an embedded OR() is no longer supported in Excel 2013. Such IF() statements are too complicated for use in business. They are only used by NASA. In this formula, the second statement in the OR (i.e., checking for the category ="Soups") is only checked/evaluated if the first part (i.e., checking if category ="Pasta") evaluates to FALSE Using a nested IF() in a formula is not recommended because the formula result is different based on the IQ level of the user.

In this formula, the second statement in the OR (i.e., checking for the category ="Soups") is only checked/evaluated if the first part (i.e., checking if category ="Pasta") evaluates to FALSE

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

Input Range and Bin Range

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.

After you have selected the data to create a chart, which of the following is the correct sequence of options needed to create a clustered column chart? Insert --> Charts --> Column --> Clustered Column Insert --> Charts --> Bar --> Clustered Column Insert --> Charts --> Clustered Column Insert --> Charts --> Other Charts

Insert --> Charts --> Clustered Column

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

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

It usually reduces duplicate data storage

When using the MATCH function, the match_type "-1" is best described by which of the following options? It will find the smallest value that is greater than or equal to the "lookup_value" It will find the first value that is equal to "lookup_value" It will find the largest value that is less than or equal to "lookup_value" It returns a random value from within a table or range

It will find the smallest value that is greater than or equal to the "lookup_value"

To apply specific formatting to a sparkline, use the Sparkline Color or the ___________ commands. Sparkline Editor Sparkline Style Marker Color Sparkline Developer

Marker Color

What command is used to *create your own rule* in conditional formatting? New Rule Manage Rule Custom Rule Include Rule

New Rule

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.

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."

Which of the following is an object that contains the actual data in the chart? Plot area Pie area Bar area Column area

Plot area

For this question, please download the attached Excel workbook. Convert the data on Sheet1 into a table. Next answer the following question: What keys do you need to press to select the newly created table so that it can be copied or moved to a different worksheet? Press Crtl+X Press Ctrl+Z Press Crtl+V Press Crtl+A

Press Crtl+A

With regards to Depreciation Calculation functions in Excel, which of the following are true about the function VDB()? Refers to the Variable-declining balance function. Computes the depreciation of an asset for any period (including partial periods) using the double-declining balance method or some other method you specify. It refers to the Visual Depreciation Barchart method, and uses a bar chart to display the falling amounts of asset values This method of calculating depreciation uses random numbers to fairly distribute depreciation over the lifetime of an asset None of the above

Refers to the Variable-declining balance function. Computes the depreciation of an asset for any period (including partial periods) using the double-declining balance method or some other method you specify.

Adding comments to a cell is available through which tab of the Excel ribbon? Home Formulas Data Review

Review

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

Ribbon

Please examine the attached workbook. Given the data in Sheet1, which of the following steps should be followed to obtain the data in Sheet2 ? PS1_Sheets Select cells A1 through D3 in Sheet1 -> Copy -> Go to Sheet2 -> Paste -> Transpose Select cells A1 through D3 in Sheet1 -> Copy -> Transpose -> Go to Sheet2 -> Paste It is only possible if done manually Select cells A1 through D3 in Sheet1 -> Copy -> Go to Sheet2 -> Paste -> Formulas

Select cells A1 through D3 in Sheet1 -> Copy -> Go to Sheet2 -> Paste -> Transpose

In the Share tab of Backstage View, which of the options is available? Enable Google Indexing Collaborate in Real-time Publish to Word Services Send using E-mail

Send using E-mail

Suppose you have a named range called "myrange" on Sheet1 of your workbook. If you delete the rows or columns that contain the range "myrange", the name (in Name Manager) contains an invalid reference. If you open name manager and examine what "myrange" refers to, you will see the missing cells denoted by ______. #VALUE! Sheet1!#REF! #DELETEDROWS Sheet1!#INVALIDREF!

Sheet1!#REF!

Which of the following procedures will NOT save (nor give you an option to save) your Excel workbook? Shift + S Ctrl + S (Excel ribbon tab) File -> Save clicking the Close (X) button in the workbook's title bar

Shift + S

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

Shift+F9

What keyboard shortcut can you press to move to the PREVIOUS field in an Excel data Form ? Tab Ctrl+Tab Ctrl+Tab+Enter Shift+Tab

Shift+Tab

After you create a Sparkline, changing color is easy. Use the controls in the _______________. Sparkline Tools -> Design (tab) -> Type (group) Sparkline Tools -> Design (tab) -> Style (group) Sparkline Tools -> Design (tab) -> Color (group) Sparkline Tools -> Design (tab) -> Font (group)

Sparkline Tools -> Design (tab) -> Style (group)

After you create a Sparkline or group of them, you can change the type by choosing the Sparkline and clicking one of the three icons in the ________________. Sparkline Tools -> Design (tab) -> Type (group) Sparkline Tools -> Design (tab) -> Names Sparkline Tools -> Design (tab) -> Other Items Design (tab) -> Type (group) -> Sparkline Tools

Sparkline Tools -> Design (tab) -> Type (group)

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

Synchronous Scrolling

What function removes leading and trailing spaces from a cell? TRIM CUT COMPACT CLEAN

TRIM

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

Text Import Wizard

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)

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)

To create Sparklines, users do NOT need to enter or specify which of the following: _____. Data to be depicted in the Sparkline Location for the Sparkline to be displayed Select one of the Sparkline types The count and sum of cells

The count and sum of cells

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

If 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.

What of the following is FALSE about filtering a table? You can filter by multiple values in a column by using multiple check marks. You can filter a table using any number of columns. You can choose "Text Filters" or "Number Filters" to display only the rows you are interested in. When you copy data from a filtered table, both visible data and rows hidden by filtering are copied.

When you copy data from a filtered table, both visible data and rows hidden by filtering are copied.

If you copy the whole cell "B8" (not only its formula) and paste it into cell "D8" (shaded in green), will it produce the correct total sales value for Quarter 3? Yes No, because you will end up with the total for Quarter 1 in that cell No, because you must re-enter the formula for each quarter Answer depends on whether Excel 2007 or Excel 2010 is used

Yes

Would you be able to apply the "watch window" option in multiple sheets at the same time ? This option was available in Excel 2013 but not in Excel 2016 No Yes, but only if there are no more than 3 sheets in the workbook Yes

Yes

Would you be able to apply the "watch window" option in multiple sheets at the same time ? Yes Yes, but only if there are no more than 3 sheets in the workbook No This option was available in Excel 2013 but not in Excel 2016

Yes

Can you apply conditional formatting to an Excel table that already has its own formatting? No you cannot No you can only apply to a sheet in the workbook Yes you can Yes you can apply it to a table, but not to multiple tables on your worksheet

Yes you can

In an Excel Form, when finding a row by entering search criteria: Will the question mark symbol "?" find or match any single character? Yes, the "?" symbol is used to find/match a single character No, the asterisk or "*" symbol is used to match/find a single character The symbol for a single character is the tilde: ~ One of the limitations of form-based searching is the inability to find/match single characters

Yes, the "?" symbol is used to find/match a single character

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

Which of the following is TRUE about the DATEDIF() function? It can be found under the dropdown function for the Date & Time category It appears in the Insert function dialog box You must enter it manually The function has four arguments

You must enter it manually

What characters are NOT allowed in sheet names ? [ ] All options are correct , !

[ ]

When using Icon Sets (Conditional Formatting), Excel formats each cell with _______ depending on the cell value. a different color between 1-3 different graphs a distinct icon 1-3 icons

a distinct icon

To find an asterisk (i.e., the symbol: * ) within a cell, you enter which of the following in an Excel form search criteria? a tilde followed by a question mark a tilde followed by an asterisk a tilde by itself this is a type of search you cannot do with Excel Forms

a tilde followed by an asterisk

When a range of cells is selected, which of the following occurs? the selected cells are all highlighted in the same color none of the cells is highlighted in any color a box marks the boundary of the selected cells, but none of the cells are highlighted all selected cells are bounded by a selection box, and all but the active cell in the range are highlighted/shaded

all selected cells are bounded by a selection box, and all but the active cell in the range are highlighted/shaded

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

ampersand (&)

In Excel, a parameter query is a query that prompts for ________ every time it is run. an input a password an image a fingerprint

an input

What form of LOOKUP is used when you have a small list of values and the values remain constant over time? dynamic form vector form static form array form

array form

To remove a single individual field from a report, _______ the check box beside the field name in the PivotTable Field List. clear check fill drag

clear

Which of the following does the function =NOW() return? current date current time current date and time nothing, it is not a valid Excel function

current date and time

A Web query contains information about the path to the ___________ and can pull the __________ data directly from that source. data source, updated query, latest SQL, refresh excel file, word document

data source, updated

In Excel, data displayed in a Sparkline is assumed to be at ________. random intervals equal intervals distributed interval None of the (other) options provided is correct

equal intervals

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

exact

What is referred to as the "0th" day of next month while working with dates and times in Excel? first day of next month last day of next month last day of current month first day of current month

last day of current month

By default, Page Breaks __________ displayed in the Normal view of worksheet. are exclusively may or may not be are always None of the above

may or may not be

Worksheet background images are for ______ display only, the images do not appear when the worksheet is ________. offline, printed onscreen, printed offline, saved onscreen, scanned

onscreen, printed

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

pictures, scripts

Fill in the blank:The COUNTIF() function used for single-criterion counting formula takes two arguments (or parameters) _______ and ______. range (and) constant expression (and) criteria constant (and) expression range (and) criteria

range (and) criteria

Step 3 of the Query Wizard lets you specify how you want the ______. records to be filtered record selection criteria named records to be sorted query to be saved

records to be sorted

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

select a row

A Sparkline is best described as a _____. small table small chart typically displayed in a single cell chart displayed in multiple cells chart that displays multiple series of data

small chart typically displayed in a single cell

Which of the following is NOT a method of creating frequency distributions? use the Review (Proofing) tool of Spelling Error Frequency per Worksheet Write your own formula or function Use the Pivot table feature Use the Analysis ToolPak add-in

use the Review (Proofing) tool of Spelling Error Frequency per Worksheet