MIS 112 Final Exam

What is the file format (file extension) used when you save an Excel workbook as a single file webpage?

.mht or .mhtml

What steps would you take if you wanted to adjust the outline 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 Styles group, select a different outline.

Examine the attached workbook. The data represents the amount of money a customer has spent, in a quarter, to buy a product. Create a PivotTable based on the (entire) table of data in the Sales worksheet. For Row Labels, choose the Product column. Include Columns C, D, E and F in the Values-area. For the product 'Carnarvon Tigers', what is the average revenue (sales in dollars) for Qtr 1?

150

Please refer to the attached workbook. What is the total new deposit amount on Sep - 08 in North County branch?

196188

With a single IF() function, how many actions can you ask Excel to carry out?

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

Please see the attached workbook. What value of tax rate is returned when the entered income is $22,566?

28%

In Excel, the DATE function takes _____ arguments.

3

The formula =COUNTIF(range, "???") returns the number of cells in the range containing exactly ______ characters of text

3

What is the maximum number of characters a worksheet name can contain?

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?

4500

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?

5??2

What does the following formula return? =FIND("m","Big Mama Thornton",1)

7

Which of the following formulas will display both the current date and time when entered into a cell in an Excel worksheet?

= NOW()

Examine the attached worksheet. Which of the following formulas, when entered in cell B12, will give the total number of items for which there was no discount offered (i.e., discount is 0)?

=COUNTIF(E2:E8, 0)

Which of the following formulas returns the number of days in the corresponding month for a date in cell B2?

=DAY(DATE(YEAR(B2), MONTH(B2)+1,0))

Suppose you deposit $100 at the beginning of each month (for 12 months) into an account that pays 3.5 percent annual interest compounded monthly. Which formula calculates the future value of your series of deposits?

=FV(3.5%/12,12,-100,,1)

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

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

Create a new workbook and enter a date in A1 that is NOT a weekend. Which of the following formulas will generate a series of weekdays only (no Saturdays or Sundays)?

=IF(WEEKDAY(A1)=6,A1+3,A1+1)

If cell "A1" contains JOHN F. SMITH and you want this value to be changed into a proper case like John F. Smith, what Excel function would you use?

=PROPER(A1)

Which of the following formulae must be entered in cell "C8" (shaded in blue) to get the total widget sales for Quarter 2?

=SUM(C4:C7)

Please see the attached workbook. What formula (in cell B3) returns the tax rate for the income in cell B2?

=VLOOKUP(B2, D2:F7, 3)

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

=WEEKDAY(A1)=1

Which of the following does the search string ~?NULL~? match when entered in the find and replace dialog box?

?NULL?

Which of the following is TRUE for Pivot Tables?

A Pivot table can be sorted by any column selected in the Pivot table field list

Suppose you have a column of file names such as: 001-Chapter1.txt, ..., 015-Chapter15.txt, ..., 104-Chapter104.txt, etc.You want to use a formula to consistently remove the prefix numbers, e.g., make the file names into: Chapter1.txt or Chapter12.txt.Which of the following text functions can you use to make this change?

A combination of: FIND(), LEN(), MID()

When using the formula "=WORKDAY(DATE(2014,1,4),10)", what does the output show?

A date that is ten working days from (after) January 4, 2014

Which of the following statements is true?

A workbook can have many worksheets.

Examine the attached workbook. If you want to remove chart labels, where do you find the options to do so within the Design tab (Chart Tools)?

Add Chart Element > Data Labels

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.

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

Which of the following is TRUE with respect to charts?

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

Which of the following is a type of Sparkline in Excel?

All of the options provided are correct

Which of the following statements about Trace Precedents are correct?

All of the options provided are correct

Choosing: Conditional Formatting -> Color Scales -> More Rules -> Format Style: allows you to change formatting rules for a color scale. Which of the following formatting options are available?

All options: 2-Color Scale, 3-Color Scale and Data Bar are available

Which of the following types of Cell Formating can be achieved using the Format Cells feature of Excel?

All the other options are correct

What is the keyboard shortcut to clear the formatting in a cell ?

Alt -> h -> e -> f

In the Top Salespersons list, which feature can be used to populate the numbers "3," "4," and "5" in cells "A17," "A18," and "A19," respectively?

Autofill

Please see the attached workbook. Why does the #DIV/0! error occur if the formula in cell D2 (also copied to the cells below it) is: "=(C2 - B2) / C2"

Because data in column C is missing or is equal to zero

With regards to Depreciation Calculation functions in Excel, which of the following are true about the function DB()?

Both A and B

Which of the following is true for printing a worksheet that contains objects / shapes?

Both of the above

Which of the following functions can be used to count specific types of errors?

COUNTIF

Which of the following concepts are useful in understanding relationships between cells and formulas?

Cell Dependents

Which of the following is an object that contains all other elements in an Excel chart?

Chart Area

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

Examine the attached workbook. To move "Chart 1" in "Sheet1" to a new Excel worksheet named "MyChart", which options will you use?

Click on: (Chart Tools) Design tab --> Move chart (Location) --> New Sheet (option)

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.

What does the following formula =A1=C12 do?

Compares cell A1 with cell C12. If the cells are identical, the formula returns TRUE; otherwise, it returns FALSE

Which of the following is the keyboard shortcut that creates a new blank workbook in Excel?

Ctrl + N

Which of the following keyboard shortcuts can be used to activate a different sheet?

Ctrl + PageUp

Which of the following keyboard shortcuts will add the current time to a cell in an Excel worksheet?

Ctrl + Shift + :

What is the shortcut key to create hyperlink when you select an object or a cell?

Ctrl+K

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) -> Refresh All

If you are trying to display trends with a Sparkline, you may have to work with irregular time periods in the underlying data. To properly do so, you can click on the Group (command group), then click on "Axis", and choose _________ to format the shape of the chart.

Date Axis Type

Which of the following is a VALID sequence of options for selecting a different chart layout?

Design (from Chart Tools) --> Chart Layouts

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

Developer

Which of these is NOT a Bitmap based file format image?

EPS

The Analysis Toolpak can be activated through which sequence of steps?

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

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 'begins with' from the first drop-down list and then type 'T' in the second drop-down list box

How does a missing data point show in a Sparkline graphic?

Gap

To print a worksheet in a Landscape orientation, you:

Go to the Page Layout tab, in the Page Setup group, click Orientation, and then click Landscape

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

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?

IFERROR

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

ISLOGICAL

Which of the following statements is TRUE?

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

Which of the following is one of the ways to create a Pivot Chart?

Insert (tab) -> Charts (group) -> PivotChart

When a column is sorted within a table, which of the following is true about the drop-down list in the header row?

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

The _________ function is used to return a specific number of letters or characters from the start of a text string.

LEFT()

Which of the following is NOT an advantage of using defined range names to work with ranges in Excel?

Moving to areas of the worksheet is controlled

Which of the following is NOT one of the four quick margin settings for adjusting margins?

No Margins

Please see the attached workbook. Is =VLOOKUP(E3,TABLE1,3,FALSE) a valid lookup function when used in cell E4?

No: because the table has only two columns but the function refers to column 3

Which of the following entries does Excel automatically recognize as a date (using the U.S. settings)?

None of the answers provided is correct

______ cells are by default disregarded when data validation rules are created. These cells can be included in the validation rule by unchecking the: "Ignore blank" checkbox.

None of the options provided is valid

What is the correct syntax for the PMT function, if we are only considering the required parameters/arguments for the function? (Note: "nper" means Number of Payments)

PMT(rate,nper,pv)

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+A

________ can be used to check whether all of the columns fit on one page before printing an Excel worksheet.

Print Preview

In the following formula, Excel sums up the data in which range? =SUMIF(Products!I2:I50,"=Canned Fruit & Vegetables",Products!F2:F50)

Products!F2:F50

What is the syntax for the RATE() function?

RATE(nper,pmt,pv,[fv],[type],[guess])

The _____________ function is used to return a specified number of letters or characters from the end of a text string.

RIGHT()

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

Range

You may refer to the attached workbook to answer the following question. What command under the Table Tool /Design Tab can you use to add two more rows to the table?

Resize table

What does the following formula do? =COUNTIF(Data, TODAY())

Returns the number of cells containing the current date in the range named Data.

Adding comments to a cell is available through which tab of the Excel ribbon?

Review

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?

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

Please examine the attached workbook. Given the data in Sheet1, which of the following steps should be followed to obtain the data in Sheet2 ?

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

In the attached image, how would you modify the pivot table to include only Joseph and Lawrence from the Middle region and nothing else?

Select the required cells and then right-click, point to Filter, and click Keep Only Selected Items.

To avoid printing more pages than necessary in Excel, you can use the following command: ________ .

Set Print Area

Which of the following procedures will NOT save (nor give you an option to save) your Excel workbook?

Shift + S

To view or edit a comment, you can select the cell where a comment has been attached and press the following keyboard shortcut: __________.

Shift+F2

What keyboard shortcut can you press to move to the PREVIOUS field in an Excel data Form ?

Shift+Tab

There are a few commands that could be used to customize sparklines to highlight parts of data. One of them is Markers. What is its purpose?

Show data markers in the Sparkline

Which of the following is TRUE about Pivot Charts?

Slicers also work with pivot charts

After you create a Sparkline, changing color is easy. Use the controls in the _______________.

Sparkline Tools -> Design (tab) -> Style (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 ?

Synchronous Scrolling

When importing a text file into Excel, the _____________ helps specify how the data will be imported.

Text Import Wizard

Which of the following options is TRUE about the different lookup functions?

The CHOOSE() function returns a specific value, corresponding to the index number, from a supplied list of values.

The "Go to Special" dialog box in Excel allows a user to select all of the following EXCEPT:

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

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

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?

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

Which of the following chart-related items identifies general trends in your data?

Trendline

Which of the following is an advantage of using Excel tables?

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

Which option provided in the View ribbon tab is best to compare two open Excel workbooks simultaneously in Excel 2016?

View side by side

From the Settings tab of the Data Validation dialog box, you can specify a wide variety of data validation criteria. Many options are available from the Allow drop-down list. Which of the following options would you select if you want the user to enter a number between the range of 1-250?

Whole Number

When a row of data is very ____ and requires repeated horizontal scrolling, consider using a data form to add, edit, find, and delete rows

Wide

You ask your customers to provide you with inventory usage data to help you plan your production schedule. Which of the following file formats will you ask them to provide data in (so that data can be easily imported into Excel)?

XML

Is it possible to convert a cell with text data into a .png format ?

Yes

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

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

If you copy cell "B6" and paste it as a formula in cell "C6," will it produce the correct total for Year 2?

Yes, the formula range will be adjusted automatically

Which of the following BEST applies to the INDEX function?

You can use it with the MATCH() function to do reverse lookups when the data is not sorted (e.g., to find the ProductID for a product named "Chocolate Cake", assuming product names are not sorted)

Regarding the search-and-replace features provided by the "Find and Replace" dialog box, what is NOT true?

You cannot replace a type of formatting in a cell with another type of formatting.

How can you insert a header or footer into the workbook?

a and c

In Excel, if one enters a time greater than ten thousand hours, it is interpreted as ________ instead of a time value.

a text string

If there are two or more name / value pairs passed in a URL query string, separate them with the following character: _____________ .

ampersand (&)

Using the =NA() function in a cell, makes it clear that data is not _______ and hasn't been ______ accidentally.

available, deleted

A single element in a worksheet that can hold a value, some text, or a formula is referred to as which of the following?

cell

A Web query contains information about the path to the ___________ and can pull the __________ data directly from that source.

data source, updated

What command (within Conditional Formatting, Manage Rules) is used to delete a specific rule from a worksheet?

delete rule

Which of the following is NOT a type of cell reference?

dynamic

A pivot table calculated field consists of a calculation that involves other _______.

fields

What does the Excel keyboard shortcut Ctrl+Alt+F9 do?

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

What happens when you click the icon in a Slicer's upper-right corner?

it removes the effects of filtering

Fill in the blanks: A cell in an Excel worksheet is identified by its address, which consists of a column ___ and row ___.

letter, number

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?

missing data

In Excel, what does the MODE function in a range return?

most frequently occurring value in a range

A chart in an Excel worksheet provides a visual representation of which of the following?

numeric values

You can protect your Excel workbook with a password. Which of the following is TRUE about password protected workbooks?

passwords are case sensitive

When do you use the following sequence of commands: PivotTable Tools -> Analyze -> PivotTable -> Options -> Generate GetPivot Data?

prevent Excel from using GETPIVOTDATA function

Step 3 of the Query Wizard lets you specify how you want the ______.

records to be sorted

The HLOOKUP function searches for data in ______.

rows

What is the Skip blanks option (under Excel Paste Special option) used for while copying a range to another area?

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

A _____ data table in Excel shows the result of a single calculation for different values of two input cells

two-way

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?

use Freeze Panes

The data source for a pivot table can reside in a _________ or in a / an ____________.

worksheet, external data file

The ____ command is used to display text on multiple lines in a cell.

wrap text

Would you be able to apply the "watch window" option in multiple sheets at the same time ?

yes