MIS Lab Final

The Excel function: ISERR() returns TRUE if the cell contains any error value except which of the following?

#N/A

What does Excel return when your MATCH() function uses an exact match option and searches for an item that is not part of the lookup_array or lookup_vector?

#N/A

In the attached image, examine the formula in cell "E9." What result will this formula produce?

#VALUE!

Examine the attached file: 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?

$24,914

Which of the following is a VALID format for a URL query string in Excel?

---URL---?<Name>=<Val>

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

.xlsx

What is the correct order of steps to be followed when you use a query wizard?

1. choosing the columns, 2. filtering data, 3. sort order, and 4. finish

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

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?

14787.45

Please see the attached workbook. How many names does the workbook have defined?

2

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)

How many sort options/commands do we have in Excel under the 'Data' tab?

3

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

=NOW()

Which formula can be used to find: How many songs were sung by either Eminem or Spears?

=SUM(COUNTIF(B2:B23,{"Eminem","Spears"}))

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 two wildcard characters are supported by the "Find and Replace" dialog box in an Excel worksheet?

?, *

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

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

Using the Normal view for a workbook, which of the following actions can be performed for Page Breaks?

All of the above

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

If you want to format cells that have values between 50 and 60, which option under 'Highlight Cells Rules (Conditional Formatting)' category would you select?

Between

How are some icons hidden in Icon Set conditional formatting?

By choosing "No cell Icon" in the Edit the Rule Description box

Please see the attached workbook. What will be the grade of Adams if the following formula is used in the cell C2? =LOOKUP(B2,{45,50,60,70,80,90},{"F","E","D","C","B","A"})

C

The formula: =CHAR(CODE("C")) returns which of the following?

C

In Excel, in order to calculate the cumulative principal paid between any two payment periods, what function will you use?

CUMPRINC()

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

Which of the following is a way of accessing the "Evaluate Formula" dialog box that helps debug a formula?

Choose Formula ->Formula Auditing ->Evaluate Formula

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)

The Pivot Table Field List shows which of the following?

Columns from the source data and Groups defined in the Pivot Table

What option in the Table Tools / Design tab removes a table without losing the data in it?

Convert to Range

How do you clear/remove a Print Area that has already been defined on the worksheet?

Correct On the Page Layout tab, in the Page Setup group, click Print Area, and then click Clear Print Area.

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

Ctrl+K

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.

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

Displays the current time

In Excel 2013, when you open multiple workbooks, how does the workbook show?

Each workbook always appears in its own window

Where do you find the command to add a pivot table on your worksheet?

Excel Ribbon: Insert (tab) ->Tables

When you apply currency formatting to a Table column and then add a new row to the table, what happens to data entered in the new cell?

Excel applies currency formatting to the new value in that column cell.

What are the #REF! and #NUM! that may appear in a cell?

Excel error codes

Which of the following is NOT a state an Excel workbook can be in?

Expanded

Examine the screen-capture from Excel. Which cell is currently active in the figure?

F4

Examine the image in the question. You can store the results of a formula entered in the formula bar (e.g., =(25*10)/2 ) as an actual number (e.g., 125) in the active cell. What keyboard shortcut do you need to press while editing the cell to do this?

F9

Which of the following command sequences will allow you to save a copy of your workbook with a different name?

File, Save as

Light, Medium and Dark are the categories of styles found under the ______ command [within the Home (tab) -> Styles (command group)].

Format as Table

Which of the following rule types does not apply to color scale formatting?

Format cells differently based on the font styles in each cell

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

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

Which function is used to create a shortcut that opens a document stored on a network server, an intranet, or the Internet?

HYPERLINK()

When specifying what to print, a user is NOT able to choose the following option: ____ .

Hidden sheets

Please see the attached workbook, go to Sheet1. How do you find all the cells that have conditional formatting?

Home (tab) -> Editing (command group) -> Find & Select -> Conditional Formatting

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

In the Analysis ToolPak for Excel, which of the following parameters can you specify when creating a Histogram?

Input Range and Bin range

The basic formula for computing simple interest is:

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 a background image to the currently active worksheet

What is the purpose of the Text to Columns command?

It parses strings into their component parts.

Examine the attached workbook. Which of the following occurs if you select an embedded chart of a sheet and then click on "File" --> "Print"?

It prints only the chart on that sheet

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

LEFT()

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

Layouts

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)

Left Align

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

Left arrow or Shift + Tab

Please examine the attached image. A part of the workbook is boxed in red. Fill in the blanks: The boxed area is called ____. What is displayed in this boxed area when a range of cells is selected? _______

Name box. The number of rows and columns in the selection is displayed here

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

No margins

Must a chart be embedded in the same worksheet as its data source?

No, a chart an be embedded or placed in a sheet separate from that of its data source.

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?

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

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

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?

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

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.

Please refer to the figure. What Excel element is shown in the red box?

Quick access toolbar (where the save button and back arrows are)

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

RIGHT()

Please refer to the figure. What Excel element is shown in the red box?

Ribbon (where the clipboard, font, alignment, etc are)

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

In the Share tab of Backstage View, which of the options is available?

Send using e-mail

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

______ is an interactive control that makes it easy to filter data in a pivot table.

Slicer

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

Text Import Wizard

While converting from military time, when would the formula TIMEVALUE(LEFT(A1,2)&":"&RIGHT(A1,2)) return an incorrect result?

The contents of cell A1 do not comprise four digits

A formula refers to a value in the Total Row of a table. What will happen to the formula result if the option for "Total Row" (from the Excel ribbon: Table Tools / Design tab) is unchecked?

The formula returns an error

Please look at the attached worksheet. 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

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

The pivot chart remains

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

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

Third column

Examine the attached Excel data file (Histogram.xlsx) and image (Histogram.png). If you used the Excel data file to generate a histogram, and compared it with the histogram in the image, which of the following options best describes what would you see?

Three of the Histogram bins will have a different count

Excel formatting tools are used to achieve which of the following?

To make the end result attractive and more functional

Please see the attached workbook. The highest 5 salaries for employees can be viewed using which of the following options under Conditional Formatting?

Top 10 Items under Top/Bottom Rules

Which function will you use to calculate depreciation for multiple periods in Excel?

VDB()

You can use a data Form to add, find, and edit rows in a range or table. Is this correct?

Yes, all these are valid form operations

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?

Yes, the formula range will be adjusted automatically

Which of the following does not hold true for Watch Windows?

You can see all the formulas in a worksheet and print them for later reference

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)

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

_

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

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

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

an integer between 1 and 7

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

array form

Please see the attached workbook. In cell A2, how is the data validation done?

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

When working with Pivot Tables you can remove all the fields from the report so that you can start over. To do this, from the Excel Ribbon, on the Analyze tab, go to the Actions group. Then click the arrow on the Clear button, and select ______.

clear all

A calculated item must reside in the ________ labels, ______ labels or ___________ area of a pivot table.

column, row, filter

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

Which of the following does the function =NOW() return?

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

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

dynamic

When you specify which rows should be included in your query (selection criteria), you are satisfying which step of the Query Wizard?

filter data

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

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

If the user copies a cell that does not use data validation and pastes it to a cell that uses data validation, the data validation rules are _____

ignored/do not work

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

last day of current month

What is the value to be found in the first row of the table while using the HLOOKUP function?

lookup value

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

Which of the following options best describe what the following formula would do? =VLOOKUP(D2,PAGES!A2:B10,2,TRUE)

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

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

range (and) criteria

A date filter in Pivot Tables is used to ____________.

specify a time period for which you need the data

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

Please refer to the attached workbook. The ________ and ________fields are in the Rows and values section respectively.

weekday, amount