MIS 111 lab final

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 error codes indicates that a formula refers to a cell that is invalid? This type of error can occur if a referenced cell is deleted from the worksheet.

#REF!

Examine the attached file: PS1_formulas.xlsx 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

A formula in Excel can consist of which of the following?

------->all of the choices provided are valid text cell references mathematical operators

Which older version of Excel file can Excel 2016 NO LONGER open?

----->Excel can open all of these files. Excel 4 Excel 97 Excel 2000

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 area scatter column

Examine the attached worksheet. 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? DA_Test.xlsx

0.00388865177938274

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

12

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 does the formula "=VLOOKUP("John",A2:D13,3,FALSE)" return?

2000

In Excel, the maximum length of a Web query URL is _______ characters.

255

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

3

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?

3% 6% 12% --->None of the other answers are correct

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

Assume cell A1 contains the text Micro and cell A2 contains the text Age, which of the following formulas will return MicroAge?

=A1&A2

Examine the attached file: PS1_formulas.xlsx To calculate the percentage of the total widget sales made by the top 5 employees, which of the following formulae would you enter in cell "C22" (shaded purple)

=C20/F8

Please see the attached workbook. In the Student Rank table, notice that H15 and H19 have the same value. Since two students cannot have the same student ID, what formula would you use in the Data validation settings in this case to make sure duplicate entries are not allowed?

=COUNTIF($H$13:$H$19,H19)=1

Please see the attached workbook. In the Student Rank table, enter the rank in cell G19 as abcd. What is the data validation formula used in this case? You may look at the Data Validation settings to answer this question.

=ISNUMBER(G19)

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 of the following formulas, when entered in cell B12, will give the total discounted cost of all the items purchased?

=SUM(F2:F8)

Which of the following formulas can you use to change a text string of military time (e.g., 21:12) in cell Y3 to a standard value of time?

=TIMEVALUE(LEFT(Y3,2)&":"&RIGHT(Y3,2))

To eliminate redundant blank space from the start or end of a string (e.g., " Tucson "), which Excel function will you use?

=TRIM()

In Excel, the set of worksheet functions related to dates can be accessed via:

Formulas (tab) -> Function Library (command group) -> Date & Time

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

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

GETPIVOTDATA

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

HYPERLINK()

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 followings steps are to be followed in order to display the Go to Special dialog box which is an Excel Auditing Tool?

Home -> Editing -> Find & Select -> Go to Special

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 entries does Excel automatically recognize as a date (using the U.S. settings)?

June 18 2014 Jun-18 2014 Jun-18/20 --->None of the answers provided is correct

What statement about the two Hyperlink arguments Hyperlink(link_location,[friendly_name]) is TRUE?

Link_location is required, Friendly_name is optional

What is Not the purpose of using Slicers?

Make table more beautiful

To display the Total Row in a Table: choose Table Tools / Design -> Table Style Options (command group) and put a check mark next to Total Row. Selecting a cell in the Total Row and clicking on the drop-down arrow that appears in that cell will NOT allow you to select which of the following summary formulas?

Mode

In Excel, the ___________ function calculates the difference between 2 dates excluding weekend days.

NETWORKDAYS()

Please examine the attached workbook. 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? PS2_Chart1

No

Can you print data in an Excel Form attached to a table?

No, you cannot

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

Examine the attached workbook. What conditional formatting rule is applied in cells A1:A5? attachment1

Top/Bottom Rules -> Bottom 10%

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 C4 in Sheet1 -> Copy -> Go to Sheet2 -> Paste -> Transpose

When working with data returned from a Query Wizard, refreshing a query occasionally produces undesired results. If this occurs, you can use the _____ button to reverse the change. In other words to "un-refresh" the data.

Undo

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

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

Yes

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

Yes

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

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

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)

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

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

While creating a parameterized web query the question mark (?) is used to ______.

begin the URL query string

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

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

What form of LOOKUP is used when you have a large list of values to look up or when the values may change over time?

vector form

Please see the attached workbook. Using the formula =B2/C2 what error is displayed?

#DIV/0!

Assume the "range_lookup" parameter in an HLOOKUP function is FALSE. If an exact match is not found, what value is returned?

#N/A

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

#N/A

Please refer to the attached workbook. The ______ , ________ and ________fields are in the Column, Rows and Values section respectively.

AcctType, branch, count of amount

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

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. Which of the following methods should you use to explain your request to your assistant?

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

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

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

Examine the attached file: PS1_formulas.xlsx 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

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

Which of the following is an INVALID horizontal text alignment option provided in Excel?

Bottom

Examine the attached workbook.Chart_Gaps.xlsx There are two charts, Expenses_1 chart represents the data in cells A1:B13 and Expenses_2 chart represents the same data but the missing values are replaced by ZEROS. Which of the following options is the correct set of steps to obtain Expenses_2 chart from Expenses_1 chart?

Chart Tools --> Design --> Data --> Select Data --> Hidden and Empty Cells --> Zero --> OK --> OK

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

Clear Rules

Which option under conditional formatting applies background color proportional to the cell's value.

Color scales

When entering / editing an Array formula, press _________ to enter the formula (or Excel does not recognize the formula as an Array formula).

Ctrl + Shift + Enter

Which of the following options scrolls the screen so that the active cell is visible?

Ctrl+Backspace

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

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

Ctrl+F

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

Ctrl+N

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

Ctrl+S

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

How do you pick the Data Validation option that provides the user with a warning (instead of completely stopping data entry) when a validation check is violated?

Data(tab) -> Data Tools (group)-> Data Validation -> Error Alert -> Style: Warning

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

Design (from Chart Tools) --> Chart Layouts

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.

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

What do you use to toggle the display of the field headings in Pivot Tables?

PivotTable Tools (tab) -> Analyze (tab) -> Show (group) -> Field Headers

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

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

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

Which of the following options can be used to quickly remove data validation for a cell?

Select the cell and open the Data Validation dialog box (Data (tab) -> Data Tools (group)). On the Settings tab, click Clear All.

What sequence of steps will allow you to format the data into a table?

Select the data, then click "Table" under the "Insert" tab of the Excel Ribbon

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

Send using E-mail

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

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

Text Import Wizard

Examine the attached Excel file. In row 4, we have two different ways of displaying the August 25 date. Which of the following is TRUE about these dates?

The data stored in the two cells A4 and B4 are identical

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

The pivot chart remains.

If the formula =ROWS(Asset) * COLUMNS(Asset) that is used to calculate the number of cells in a range named Asset doesn't work, what does it imply?

The range Asset consists of non-contiguous cells

What is the output of the following command sequence: click the Analyze tab under PivotTable tools -> Actions (group) -> Clear -> Clear Filters?

This removes all the filters at once

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

current date and time

What is the name of the company that created Excel?

microsoft

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

most frequently occurring value in a range

The range_lookup parameter in VLOOKUP and HLOOKUP function is ________

optional

What is the default form for printing

portrait