MIS Lab Final Review (PS 1-12)

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

#N/A

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

.xls

What are the minimum and maximum zoom ranges that can be displayed in an open Excel worksheet (respectively)?

10%, 400%

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)

For Windows systems, Excel uses the standard 8-bit ANSI character set. This character set consists of ___ characters.

255

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

3

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

3

A single cell in Excel can hold up to approximately ________________ characters.

32000

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 d, yyyy")

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) + 1, MONTH(A1), DAY(A1))

Examine the image in the question. In "C3," we have the supplier address "Tucson, AZ 85721" appearing in a single cell (the formula combines data from cells "E3," "F3," "G3"). Which of the following formulas will achieve this result?

=E3 &", " &F3 &" " &G3

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

Which of the following formulas can be used to determine the quarter in the calendar for a date in cell P5? (e.g., quarter "1")

=ROUNDUP(MONTH(P5)/3,0)

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

?, *

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

What does it mean when Excel displays the following error? #DIV/0!

A number is divided by zero

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

How do you group Pivot Table items manually?

After creating the pivot table, select the items to be grouped and then choose Analyze (tab) under PivotTable Tools and select Group (group) -> Group Selection

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

Consider the function =COUNTIF(A1:A200, ">200"). The information between the parentheses in a function are known as the ____________ .

Arguments

How are some icons hidden in Icon Set conditional formatting?

By choosing "No Cell Icon" in the Edit the Rule Description box.

What command would you use to import a CSV file?

Choose Data --> Get External Data --> From Text

What command (within Conditional Formatting) is used to delete all the rules relevant to a particular group of cells?

Clear rules

If you want to create a rule to highlight cells that contain the word 'good', which option would you select?

Conditional Formatting -> Highlight Cells Rules -> Text that contains

To open an HTML file stored on a Web server using Excel, what needs to be done?

Copy the URL and paste it into the Name Field in the Open dialog box.

Which of the following keyboard shortcuts can be used in Excel 2013 to create a table?

Ctrl + L

Which of the following procedures will NOT close your Excel workbook?

Ctrl + S

Issuing the Print command (in Excel) with a mouse takes three clicks. A slightly more efficient method is to press _____________ and then click the Print button

Ctrl+P

Which key combination can a user press to toggle between formula view and normal view within Excel?

Ctrl+`

What does the following formula return? =CHAR(68)

D

When you have imported data from an external data source, you can change the properties of the 'external data range' by using the External Data Properties dialog box. How will you open this dialog box?

Data (tab) -> Connections (group) -> Properties

Which rule category under Conditional formatting can be used if you want to apply graphic bars directly in the cells, proportional to the cell's value?

Data Bars

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

Displays the current date

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.

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

The _____________ command can extract specific pieces of information from cells, as long as the values are stored consistently.

Flash Fill

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

What function helps ensure that the formula will continue to reference the intended cells if the pivot table layout is changed?

GETPIVOTDATA

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?

INFEROR

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 is the MOST compelling motivation for using HLOOKUP()?

It usually reduces duplicate data storage

Which of the following functions looks in a one-row or one-column range (lookup_vector) for a value (lookup_value) and returns a value from the same position in a second one-row or one-column range (result_vector).

LOOKUP

The HYPERLINK function syntax has two arguments. Which argument has the path and file name to the document to be opened?

Link_Location

Which of the following is FALSE about Pivot Table Slicers?

None of the answers provided is false

Which of the following types of data may NOT be contained in a cell in an Excel worksheet?

Objects

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

To calculate the present value of an investment, which is the total amount that a series of future payments is worth now, the function used is:

PV()

Which of the following options would you choose if you want to insert a page break in the worksheet?

Page Layout (tab) -> Page Setup (group) -> Breaks -> Insert Page Break

_______ refers to the non-printing areas along the sides, top, and bottom of a printed page. You do not see data from worksheet cells here, but may see a header or footer.

Page Margins

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

Passwords are case sensitive

Which of the following is TRUE about Web queries?

Performing Web queries copies information from an HTML file into the current workbook

A _________ is a graphical data representation of data displayed in a pivot table.

Pivot Chart

What is the general formula to calculate compound interest (i.e., the future value of an investment that includes both Principal and interest)?

Principal * (1 + periodic rate) ^ number of periods

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

The ________ function is one solution to account for for floating point number errors. It also serves to round off values to the desired number of decimal places.

ROUND()

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

Range (and) criteria

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

The first field you add to a PivotTable report that does not contain numbers will automatically be added in the _______ part of the report.

Row Labels

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!

Which of the following is a shortcut key used to calculate the formulas in the active worksheet?

Shift + F9

Which of the following is TRUE about sorting a table?

Sorting a table re-arranges the rows based on the contents of a particular column.

To add rows or columns within the table, right-click and choose Insert from the shortcut menu. Which of the following commands is NOT displayed on this menu?

Table Column Above

Excel formatting tools are available at the following locations:

The Format Cells dialog box, the Home Tab of the Ribbon, the Mini toolbar

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

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

Fill in the blanks: Conditional formatting effects help us _______ values in cells making it easier to locate data, find exceptions and spot ______ quickly.

Visualize, trends

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

You enter "(500)" into an unformatted cell in an Excel worksheet (without quotation marks). Which of the following will be generated by your entry?

a negative number

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

a range

Examine the 6th argument in the formula below. What kind of argument is it? =SUM(B1, 5, 6, 7, SQRT(4), A1:A5, TRUE)

a range reference

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

Which of the following statements is true about excel workbooks?

a workbook can have many worksheets

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

Which of the following types (chart types) is a VALID option in the Charts ribbon group on the Insert ribbon tab?

all of the options provided are valid

When a range of cells is selected, which of the following occurs?

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

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

array form

charts_example.xlsx Examine the attached workbook. How can you resize the chart title in Chart 1 of Sheet 1?

change font size

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

change the chart type, formatting, and data series

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

clear

A data form provides a convenient means to enter, display and search for data within a table. It also permits you to do the following: ____________

delete a record/row

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

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

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

numeric values

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

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

Which of the following is NOT one of the VLOOKUP function arguments?

row_index_num

Please refer to the figure. Which of the following View ribbon tab options was used to display the horizontal bar between the nonconsecutive rows containing "Astoria, OR" and "Victoria, TX"?

split

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 is NOT a method of creating frequency distributions?

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

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

An Excel workbook can contain one or more of which of the following?

worksheets

Which of the following is NOT a tool in Analysis ToolPak for Excel?

y-Test

When you try to import data from MS-Access into Excel, which of the following is TRUE?

you can import a table or query