MIS 111 LAB Final Exam

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)

The HLOOKUP function works like VLOOKUP function except that the lookup table is arranged ___________ instead of __________.

"horizontally, vertically"

Which of the following is the file format of the Excel 97-2003 workbook?

.xls

By default, what is the extension used when you save a workbook in Excel 2016?

.xlsx

Using the ANOVA: Single Factor Data Analysis Tool (available through the Analysis ToolPak Add-in) on A1:C6, which of the following options is the correct value for the p-value of the data in the worksheet?

0.00388865177938274

By default, Excel uses which of the following fonts?

11 point (pt) Calibri

Within the "Number" tab, how many categories are available for displaying numbers?

12 categories

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)

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

Which of the following formulas, when entered in cell E6, will give the Discount percentage for Pick Axe?

=(1-(F6/D6))*100

Which of the following formulas, when entered in cell B10, will give the average quantity of all the items purchased?

=AVERAGE(B2:B8)

Which formula can be used to find: How many songs were sung by singers whose names begin with S?

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

Converting a string (e.g., an email address) to purely lowercase, can be done using which of the following functions?

=LOWER()

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

Which of the following statements is FALSE?

A pivot chart is always based on a pivot table, i.e., changes to the pivot chart must be made by changing the underlying table

In an Excel worksheet, what column name comes directly after Column Z?

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.

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

Choosing: Conditional Formatting -> Color Scales -> More Rules -> Format Styles: 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

The SUM() function does which of the following in Excel?

Allows you to total up numbers in a specified range of cells

There is a type of investment in which you make a stream of fixed payments (e.g., as a series of investment deposits into an account). What is this type of investment called?

Annuity

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 negative values in the Sparkline

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

Apply a different color to the lowest data point in the Sparkline

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

B. passwords are case sensitive

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

C. A workbook can have many worksheets.

Please see the attached workbook. In the EmpID table, enter the empID in cell F8 as 12345. Given the error message, what will you change in the Data Validation dialog box > settings (tab) for 12345 to be a valid entry?

Change the settings to whole number > less than > 100000

What is NOT true about Autocorrect?

Changes made by the Autocorrect feature cannot be undone.

What is the name of the plus sign next to graph

Chart elements

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

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

Computes depreciation at a fixed rate. Its arguments are: Cost, Salvage, Life, Period, [Month]

What is the purpose of the function TIME()?

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

Which of the following shortcuts allows you to enter the same data into multiple selected cells?Example: In a new Excel worksheet select cells A1:B4 and type "MIS111". The text is initially entered into cell A1. What could you do to replicate the same text "MIS111" in all the selected cells?

Ctrl + Enter

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

Ctrl + N

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

Ctrl + Shift + :

What is the shortcut to open the Find and Replace dialog box with the "Find" tab open?

Ctrl+F

What is the shortcut to open the Find and Replace dialog box with the "Replace" tab open?

Ctrl+H

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 date such that it falls on a Sunday?

Custom

Which conditional formatting rule is applied? (like a bar graph, but on the cells)

Data Bars

Please see the attached workbook. In the EmpID table, enter the salary in cell H8 as 100. Given the error message, why do you think Excel does not let you input that value?

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

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

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

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: &[Pages] ?

Displays the total number of pages to be printed

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

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) OR Right click on the chart --> Reset to Match Style Either of the options (resetting through the menu or the right-click options work)

#NAME? error occurs under which of the following conditions?

Formula contains an undefined range or cell name Formula contains text that Excel interprets as an undefined name Formula uses a worksheet function that's defined in an add-in or the add-in is not installed

Which of the followings steps are to be followed in order to identify the cell that contains an error?

Formulas -> Formula Auditing -> Error Checking -> Trace Error

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

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

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

June 18 2014 Jun-18 2014 Jun-18/20 ---NOne o f the above

The __________ function can be used to find the count of characters in a word or text string.

LEN()

Which of the following options provides correct syntax for the LOOKUP function?

LOOKUP(lookup_value,lookup_vector,[result_vector])

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

Left arrow or Shift + Tab

Vector based images are represented by?

Mathematical equations

Which of the following is NOT one of the options for highlighting certain data points in a Sparkline?

Middle point

When you right-click a graphic object and choose Size and Properties from shortcut menu, Format Shape pane opens. If you expand the Properties section which of the following choices you get?

Move and Size with cells Move But Don't Size with cells Don't Move or Size with Cells

Which function returns the number of payment periods for a loan, given the loan's amount, interest rate, and periodic payment amount?

NPER()

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

No

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

No margins

If you want to trace cell relationships for formulas, which of the following commands / options could you use?

None of the options provided is correct

Automatic grouping of Pivot Table items is possible when a field contains which of the following types of values?

Numbers Dates Times

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

By default, Excel prints worksheets in ___________ orientation

Portrait

______________ replaces part of text string that appears in a specific location within a string. Hint: This function can be used when you know the character location of the text to be replaced.

REPLACE()

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

which of the following steps should be followed to obtain the data in Sheet2

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

What does selecting the option "Current region" within the: Go To Special Dialog Box (Home (tab) -> Editing (command group) -> Find & Select) do?

Selects a rectangular range of cells around the active cell

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

Sheet1!#REF!

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

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

Single-factor without replication

Which of the following is NOT one of the types of Sparkline graphics?

Slicer

Which command helps you to override the automatic behavior and control max and min value for Sparklines?

Sparkline tools-> Design-> Group -> Axis

What is the result of the following formula? =COUNTIF(A1:A10, MODE(A1:A10))

The number of cells containing the mode within the cell range A1 - A10

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.

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 or Synchronous Scrolling

Consider the following formula: =SUMIFS(Inventory_Cost, Category,"=Beverages", Suppliers,"*4*"). Which of the following statements is TRUE?

Two criteria are checked for this formula to include a value in the total: a) the product category must be "Beverages", and b) the supplier must be "4"

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

Which of the following BEST applies to the INDEX function?

Which of the following BEST applies to the INDEX function?

If you copy the formula in cell "B8" and paste it into cell "D8" (shaded in green), will it produce the correct total sales value for Quarter 3?

YES

Is it possible to change the direction of bars in DATA BARS conditional formatting?

YES

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

YES

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

The #VALUE! error can occur in the following conditions EXCEPT for:

You cut a cell and paste it to a cell that is referenced by other formulas

What characters are NOT allowed in sheet names ?

[ ]

What does the small red triangle on the upper right corner of cell B1 denote?

a comment exists for that cell

Which of the following is the difference between a Pie and Doughnut chart in Excel?

a doughnut chart can display more than one series of data

If one attempts to format a serial number that falls outside the range of supported dates, the value displays as __________.

a series of hash (#) marks

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

What does the WEEKDAY function return when you provide a date as an argument?

an integer between 1 and 7

After you create a chart, which of the following actions can you perform on it?

change the chart type, formatting, and data series

The address of the active cell consists of which of the following?

column letter, row number

Which keyboard shortcut is used to select all cells in an open Excel worksheet?

control A

In Excel, data displayed in a Sparkline is assumed to be at ________.

equal intervals

What are the small bars highlighted by the orange ovals known as? (lines on graphs that look like a capital I)

error bars

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

feild

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

it removes the effects of filtering

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

most frequently occurring value in a range

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?

no

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

number of cells containing the current date

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

numeric values

A _____________ data table shows the results of any number of calculations for different values of a single input cell. For example, using a single loan amount, you can calculate a table of monthly payments each for a different interest rate.

one-way

The range_lookup parameter in VLOOKUP and HLOOKUP function is ________

optional

What does the middle button (circled in red) in the row of "Paste Value" do? (% 123)

pastes values and number formatting

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

prevent Excel from using GETPIVOTDATA function

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

range

The COUNTIF() function used for single-criterion counting formula takes two arguments (or parameters) _______ and ______.

range (and) criteria

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

All manual page breaks are shown as _______ lines instead of ____ lines (when you use the view mode of Page Break Preview).

solid, dashed

The worksheet in "worksheet 1" is split into 4 panes (shown in "worksheet 2"). What cell must be selected prior to applying the split function in "worksheet_1" so that it looks exactly like what you see "worksheet2"?

split on cell thats last seen

what is the ribbon tool ?

the top section of the screen where you can find fonts, formatting, etc

Please see the attached workbook. What does '3' in the expression =VLOOKUP("John",A2:D13,3,FALSE) represent?

third column

The =TODAY() function is used:

to insert the current date which is updated each time a workbook is opened or recalculated

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

trendline

What best describes the connection between a Pivot Table and a Pivot Chart?

two-way link (updates made in the Pivot Chart are reflected in the PivotTable)

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

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

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