MIS 112

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)

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

What value should be given to the 'match type' argument in the MATCH function if you want the MATCH function to find the first value exactly equal to lookup_value?

0

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

Please see the attached workbook. The formula =LOOKUP("Mary",A2:A5,C2:C5) returns what value?

28

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

3

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 does the following formula return? =FIND("m","Big Mama Thornton",1)

5

The Excel CODE function returns the character code for its argument. What does the following formula return? =CODE("B")

66

Examine the attached Excel file. When the formula in "D2" is copied to the cells below ie, cell "D3" will contain what formula?

=(B3*C3)*$B$7

Please choose the formula that will calculate the future value of a $6,500 investment at 4.55 percent compounded continuously for 4 years.

=6500*EXP(4.55%*4)

In the attached Excel workbook, which of the following formulas can be used to calculate Sophie's total score in both the exams?

=B4+C4

Use the attached spreadsheet to answer the following question. Which formula can be used to find: How many songs were sung by singers whose last names canton exactly six letters?

=COUNTIF(B2:B23,"??????"

Use the attached spreadsheet to answer the following question. Which formula can be used to find: How many songs were sung by singers whose names begin with S?

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

Please see the attached workbook. What formula (in cell B3) would return the tax rate for the income in cell B2?

=HLOOKUP(B2,E1:J3,3)

If you want to use the HYPERLINK function to jump to a cell E2 in the same workbook and display "Hello" as the link text, which of the following functions would you use? Assume that the workbook name is Example.xlsx.

=HYPERLINK("[Example.xlsx]E2","Hello")

Please see the attached workbook. If you have to use a formula to retrieve the age of the student from the given array of data, which of the following formulas will you use?

=INDEX(J4:L8,3,3)

Which Excel function returns the serial number of current date and time?

=NOW()

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

=NOW()

Examine the attached worksheet. Which of the following formulas, when entered in cell B12, will give the total discounted coat 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))

Which of the following formulas is used to determine the date of the Sunday that was most recent?

=TODAY()-MOD(TODAY()-1,7)

Which of the following does the search string ~?NULL~? match when entered in the find and replace dialog box?

?NULL?

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

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 the F4 key)

Absolute

Please refer to the attached workbook. The _______, ________ and __________ fields are in the Column, Rows and Values section respectively.

AcctType, branch, count of amount

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

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.

What is the keyboard shortcut to clear the formatting in a cell?

Alt -> h-> e -> f

There are two options if you want to save an Excel workbook, so that it can be viewed on the World Wide Web. What are the two options?

An HTML file and a single-file web page

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 Low Point. What is its purpose?

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

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

Bottom

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

What command would you use to import a CSV file?

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

If your worksheet is a bit too wide to print on a single page, but you want to shrink it to fit on a single printed page, which of the following should choose?

Choose the print scaling option of "Fit All Columns on One Page"

How do you set up a data validation (using the data validation dialog box) that restricts data entry to a text of a specified length?

Click the Settings tab (in the Data Validation box). In the Allow box, select 'Text Length', 'equal to', and specify the length

How do you set up a data visualization (using the data validation dialog box) that restricts data entry to a time less than a specified time?

Click the Settings tab (in the Data Validation box). In the Allow box, select 'Time". In the Data box, select 'less than' and enter the end time

The Pivot Table Field List shows which of the following?

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

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

Ctrl + F

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

Ctrl + S

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

Ctrl+L

Which is one of the ways to refresh a query used to import data from external data source using the query wizard?

Data (tab) -> Connections (group) -> Refresh All

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

Excel uses column headers to create labels for each _____ on data entry form.

Field

If you want to print a copy of a worksheet, which of the following would you choose?

File (tab) -> Print and then choose the PRINT command

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

File, Save As

The Analysis Toolpak can be activated through which sequence of steps?

File-->Options-->Add ins-->Manage Excel Add-Ins-->Go

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 ends with 'm' for 'Name'.

From the Column to Filter list, select Name. In the right panel, select 'ends with' from the first drop-down list and then type 'm' in the second drop-down list box

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

Please refer to the attached workbook. In Table 1, the Pivot table shows which of the following:

Gender is a Column Label

You can control how a sparkline handles empty cells in a range (and thus how the sparkline id displayed) by using the _______ dialog box.

Hidden & Empty Cell Settings

If you want to find the cells on the worksheet that have data validation, which option would you select?

Home (tab) -> Editing (group) -> Find & Select -> Data Validation

Please examine any Excel 2016 (Windows) workbook to better answer this question: You can create your own style through which of the following steps?

Home (tab) -> Styles (command group) -> Cell Styles

Which of the following 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 function(s) can be used to count error values in a range?

ISERROR(), ISERR(), ISNA()

______ is used to insert a nicely formatted mathematical equation as a graphic object.

Insert (tab) --> Symbols (group) --> Equation

Which of the following is one of the ways to create a Pivot Chart?

Insert (tab) -> Charts (group) -> Pivot Chart

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

Link_location

What is Not the purpose of using Slicers?

Make table more beautiful

What command is used to *create your own rule* in conditional formatting?

New Rule

Which function is used to calculate depreciation in Excel using the straight line method?

None of the above

How do you copy a Pivot table (including its design format) to a new worksheet?

None of the answers provided is valid

When you use the HLOOKUP() function, which of the following parameters represents the row number from which the matching value will be returned?

None of the options provided are correct (correct answer is row_index_num)

How do you format a valid date serial number as a date?

None of the options provided is correct

If you accidentally create a circular reference formula, Excel displays which of the following warning message:

None of the options provided is correct

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

How can you define a Print Area after selecting the required cells on the worksheet?

On the Page Layout tab, in the Page Setup group, click Print Area, and then click Set Print Area

Please refer to the attached workbook. What happens if the following value filter is added to the existing report? Value filter: Sum of Sales does not equal 5000.

Only one record corresponding to the 'CA' state remains as its total sales amount is greater then 5000

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 is TRUE about Web queries?

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

What keys so you need to press to select the newly created table so that it can be copied or moved to a different workbook?

Press Ctrl+A

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

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

Examine the attached workbook. The data represents the amount of money a customer has spent, in a quarter, to buy a product. Create a PivotTable based on the (entire) table of data in the Sales worksheet. For Row Labels, choose the Product column. Include Columns C, D, E and F in the Values-area. Which quarter saw the highest revenue (sales in dollars) for the product 'TOFU'?

Qtr 2

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

What command under the Table Tool /Design Tab can you use to add two more rows to a table?

Resize table

Adding comments to a cell is available through which tab of the Excel ribbon?

Review

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

When you have several drawings on a worksheet, you may want to align or evenly space these objects. What is the best way to do it?

Select the appropriate command from: Drawing tools -> Format (tab) -> Arrange (command group) -> Align (drop-down list)

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

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 names 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 procedures will NOT save (nor give you an option to save) your Excel workbook?

Shift + S

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

Shift+F9

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

Slicer

After you create a Sparkline, changing color is easy. Use the controls in the ______.

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

Please see the attached workbook. Row 5 is hidden and contains a word in cell A5. What is the result of using the spell checker when the word is hidden?

Spell checker will be able to check the spelling of the word because the contents of hidden rows and columns are checked.

The _______ is a function that will allow you to display a value (after conversion to a string) in a specific format, for example with a thousands comma separator and a currency ($) symbol.

TEXT()

The ________ function will be used as part of a formula to calculate how many days old you are today.

TODAY()

What function removes leading and trailing spaces from a cell?

TRIM

To create Sparkline, users do NOT need to enter or specify which of the following _____.

The count and sum of cells

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

Examine the attached Excel data file (Histogram.xlsx) and image (Histogram.png). If you used the Excel data file to generate a histogram (you can use the Analysis Toolpak for that), 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

A calculated field must reside in the __________ area of the pivot table.

Values

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

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

You can import a table or a query

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

The Insert (tab) --> Illustrations (group) --> Pictures command can be used to insert pictures from _______.

Your computer

If one attempts to enter a date that falls outside of the range of supported dates, Excel will interpret it as _______

a text entry

To find an asterisk (i.e., the symbol: *) within a cell, you enter which of the following in an Excel form search criteria?

a tilde followed by a asterisk

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

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

Most pivot tables are created from numeric data, but pivot tables are also useful with some types of non-numeric data. Because you cannot sun non-numbers, this technique involves ___________.

counting

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

equal intervals

By default, Page Breaks __________ displayed in the Normal view of worksheet.

may or may not be

By pressing the spacebar, the contents of a cell seem to erase because pressing the spacebar inserts a / an _______ space character

non visable

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

Using VLOOKUP in some ways is similar to using a(n) _________.

phonebook

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

pivot chart

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

row_index_num

In Excel, the Ctrl + Spacebar (keyboard shortcut) is used to ____.

select a column

A Sparkline is best described as a _____.

small chart typically displayed in a single cell

A data filter in Pivot Tables is use to ________.

specify a time period for which you need the data

The real power of data validation becomes apparent when you use data validation formulas. The formula that you specify must be a logical formula that returns either TRUE or FALSE. If the formula evaluates to TRUE, what does it mean?

the data is considered valid and remains in the cell

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