MIS 112 Final University of Arizona

What result does the following formula display? =DOLLAR(5742.999,2)

$5,743.00

In Excel, the DATE function takes _____ arguments.

3

Which of the following is TRUE about Pivot tables?

All of the answers provided are TRUE

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

Axis Titles

What is the purpose of the function TIME()?

Converts the input hours, minutes and seconds to a serial number

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

Ctrl + N

What is metadata?

Descriptive information about a workbook

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

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 statements is true? They all relate to the formula: =IF( OR(Category="Pasta",Category="Soups"), Inventory_Cost,0 )

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

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.

Excel has many Shapes to choose from, but sometimes the Shape you need is not in the gallery. In such a case, you may modify one of the existing shapes using a few techniques. One of them is 'Group Multiple Shapes'. What is its purpose / how can it be used?

You can create the Shape you need by combining two or more Shapes and then grouping them.

How do you zoom in on something in excel?

Zoom to selection

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

_

The Linked Picture paste option in Excel ________.

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

For a range called Data, what does the function =COUNTIF(Data, TODAY()) return?

number of cells containing the current

In Excel, dates are stored as _________.

numbers

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

Examine the image provided. What does the middle button (circled in red) in the row of "Paste Value" do?

pastes values and number formatting

If a pivot chart is created and the underlying table is deleted, what occurs?

the pivot chart remains

There are four boxes/areas below the PivotTable Field List. Which of the following is NOT one of these four boxes?

tools

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

trendline

Which of the following is NOT a method of creating frequency distributions?

use the review (proofing) tool of spelling error frequency per worksheet

What is an advantage of using formulas instead of Autofill to create a series of dates? (e.g., for a work-week schedule)

when the first date is changed, the others update automatically

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

12

Please refer to the attached image. The sparkline displayed in cell F6 gets the data from cells A6 through E6 and displays a Win/Loss bar chart that shows only whether the year had a gain or a loss. In which year was there a loss, according to the Win/Loss bar chart?

2013

Please refer to the attached workbook. What is the TOTAL amount of Sales in the west regions of Arizona (AZ) and California (CA)?

2200 and 4100 respectively

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

Please refer to the figure. How many worksheets are contained in the workbook shown?

5

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

7

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

To get rid of ALL blank spaces in a string (e.g., in "Jo hn Doe "), which Excel function will you use?

=SUBSTITUTE()

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 NOT a reason to name a range of cells?

A named range (e.g., 'Order_Total'), works across operating systems (e.g., works on both Macs & Windows), while using the cell references (e.g., H2:H100) does not work on different operating systems

Which of the following statements is true?

A workbook can have many worksheets.

How can you access the dialog box for Pivot Table options?

Both B and C

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.

COUNT()

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

COUNTIF

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 NOT a state an excel workbook can be in?

Expanded

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

Fields

The easiest way to enter a function into a formula is to use Formula AutoComplete (the drop-down list that Excel displays while you type a formula). Another way to insert a function is to use the ______ on the _________ tab.

Function Library group (on the) Formulas

Which keyboard shortcuts can be used to move the active cell one colunn to the left?

Left arrow or Shift + Tab

What is the name of the company that created Excel?

Microsoft

Does deleting a cell by pressing the Delete key also delete the conditional formatting on the cell?

No

Examine the survey results in the attached workbook (in Sheet2). What type of chart would be most suitable to represent the survey response data?

Pie

Which of the following is an object that contains the actual data in the chart?

Plot Area

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

What Excel element is shown in the red box?

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 ? (switches x and y labels and data)

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

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

Shift+F2

If you want to use commands to customize Sparklines to highlight certain parts of the data, which of the following options would you select?

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

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

In Excel 2016, in order to change the date's default format, one needs to change ___________ settings.

System-wide (i.e., all of Windows)

Which of the following options is TRUE with respect to the function PROPER() in Excel?

The PROPER() function doesn't handle names with an embedded capital letter, such as McDonald.

Is it possible to get the data back after deleting it using keyboard ?

Yes

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

Yes

Charts are dynamic, i.e., they change as the underlying data changes. Is this an accurate statement?

Yes, by default they are dynamic.

Which of the following features for Sparklines is missing in Excel 2016 but can be simulated artificially by transforming the data?

adding reference line

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?

all of the above

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

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

developer

By default, if you hide rows or columns that are used in a Sparkline graphic, the hidden data _____.

does not appear in the sparkline

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

dynamic

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

marker color

A group of cells is referred to as the following

range

The function of value filter in a pivot table is to ___________.

read the data select those rows with cells that match your criteria

Which of the following is NOT a type of ANOVA (Analysis of Variance) performed using tools in the Analysis ToolPak?

single-factor without replication

A Sparkline is best described as a _____.

small chart typically displayed in a single cell