MIS problem sets

What is the file format (file extension) used when you save an Excel workbook as a single file webpage?

.mht or .mhtml

What steps would you take if you wanted to adjust the outline of a rectangle shape that has already been inserted into your worksheet?

1) Click the shape. 2) Click (Drawing Tools) Format. 3) In the Shape Styles group, select a different outline.

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

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. For the product 'Carnarvon Tigers', what is the average revenue (sales in dollars) for Qtr 1?

150

Which of the following is the default font type and font size in Excel 2016?

Calibri, 11 point

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

Which of the following shows the correct syntax for referencing cell "A2" in another worksheet?

=A1*Sheet2!A2

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

Examine the attached worksheet. Which of the following formulas, when entered in cell B12, will give the total number of items for which there was no discount offered (i.e., discount is 0)?

=COUNTIF(E2:E8, 0)

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

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

If you place the link target in cell C1 on the same worksheet, how will you use an absolute reference to that cell as the link_location in the HYPERLINK formulas?

=HYPERLINK($C$1)

You may use the attached spreadsheet, "IF_formulas.xlsx" to answering this question. Which of the following IF() functions evaluate as being TRUE?

=IF(B10>100,"true","false")

Examine the attached worksheet. We want to display in cells H2 to H8 the names of the items whose quantity purchased is greater than the Average quantity reported in cell B10. If the quantity purchased is equal or lower, we want to display 0. Which formula, entered in H2 and extended to H8, will achieve this?

=IF(B2>$B$10, A2,0)

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)

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

=LOWER()

If cell "A1" contains JOHN F. SMITH and you want this value to be changed into a proper case like John F. Smith, what Excel function would you use?

=PROPER(A1)

Which of the following formulas rounds a time in cell B2 to the closest minute? (You can assume relevant cells have Excel's Time formatting)

=ROUND(B2*1440,0)/1440

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

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

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

______ is a feature which allows you to apply a number of preset Photoshop-like effects to an image.

Artistic effects

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

What is the result of the formula =2^3 * (,10*10

Excel tells you that the formula contains an error

True or false: Excel can only import HTML files from your local computer.

False, Excel can import HTML files from the local computer or the web.

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

In order to delete information using Find and Replace in Excel, enter the text to be deleted in the ________ field and leave the Replace With field _____.

Find What, empty

How does the Excel search/replace function work for dates?

It searches for a date as displayed in the Formula bar, and then does a replace

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

No

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

Refers to the Variable-declining balance function. Computes the depreciation of an asset for any period (including partial periods) using the double-declining balance method or some other method you specify.

Which of the following is NOT a summary function for Pivot tables?

Regression

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

In Excel, Shift + Spacebar (keyboard shortcut) is used to _______.

Select a row

How do you create a Sparkline?

Select an empty cell or group of empty cells in which you want to insert one or more sparklines. On the Insert tab, in the Sparklines group, click the type of sparkline that you want to create.

In the attached image, how would you modify the pivot table to include only Joseph and Lawrence from the Middle region and nothing else?

Select the required cells and then right-click, point to Filter, and click Keep Only Selected Items.

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

Shift+F9

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

Microsoft Office Excel can automatically generate a data form (which allows for convenient data entry, among other things) for your range or _______________.

Table

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

With respect to Sparklines, what is FALSE?

The Create Sparklines dialog box does NOT allow users to specify a different sheet for the Data Range.

What part(s) of a table can be selected?

The entire table, an entire row, or an entire column from a table can be selected

Which of the following returns a #REF! error value?

The formula contains an invalid cell reference

Increasing the size of a Bitmap image (in contrast to increasing the size of a vector image) has which of the following effects?

The image loses clarity

Which of the following chart types does NOT support the Data Table chart element?

Treemap

Please see the attached workbook. What does the small red triangle on the upper right corner of cell B1 denote?

a comment exists for that cell

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

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

How can you insert a header or footer into the workbook?

a) Click File > Print > Page Setup and edit the header and footer. and c) In the Page Layout view, click on the placeholder for the header or footer, and add the information.

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

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

an integer between 1 and 7

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

arguments

When you create one or more Sparklines in Excel, by default they all use _________ axis scaling.

automatic

A single element in a worksheet that can hold a value, some text, or a formula is referred to as which of the following?

cell

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

clear

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

In Step 1 of the Query Wizard, you select database _________ that you want to appear in your query.

columns

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

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

counting

Which of the following can be retrieved from a web page using web queries?

data in a table

Input messages and error alerts (for data validation) appear only when ___.

data is typed directly into the cells

By default, if you hide rows or columns that are used in a Sparkline graphic, the hidden data _____.

does not appear in the Sparkline

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

dynamic

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

equal intervals

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

field

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

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

Examine the attached workbook. To create a duplicate copy of a chart (on same Excel worksheet, but different location), you can activate the chart and take what action?

hold the "Ctrl" key while dragging the chart to the new location

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

For a range called Data, what does the function =COUNTIF(Data, "<>0") return?

number of cells not equal to 0

In Excel, dates are stored as _________.

numbers

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

row_index_num

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 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. What is the total new deposit amount on Sep - 08 in North County branch?

196188

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 see the attached workbook. What value of tax rate is returned when the entered income is $22,566?

28%

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

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

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

Which of the following options, when used as a formula in the Custom Data Validation criteria for cell A1, will ensure that the cell entry is a date, and that thedate is a Sunday? Hint: Date format: MM/DD/YYYY

=WEEKDAY(A1)=1

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

?, *

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

A pivot chart is which of the following?

A graphical representation of a data summarized in a pivot table

Examine the image in the question. Which of the following is true for the new name being defined?

A name is being applied to a range B4:E4. This name "UA", can be used in formulas anywhere in the current workbook.

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

A number is divided by zero!

Which of the following is TRUE about a Watermark in Excel?

A watermark is an image (or text) that appears on each printed page. A watermark can be a faint company logo, or a word such as DRAFT. Excel doesn t have an official command to print a watermark, but you can add a watermark by inserting a picture in the page header or footer. All of the above statements are TRUE.

Which of the following types of Cell Formating can be achieved using the Format Cells feature of Excel?

All the other options are correct Alignment Fill Protection

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

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

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

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

How are some icons hidden in Icon Set conditional formatting?

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

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

C

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

To gain access to the Table Tools / Design tab in the Excel Ribbon when working with tables, you should do the following:

Click anywhere on the table

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

Click the Settings tab (in the Data Validation box). In the Allow box, select 'date'. In the Data box, select 'greater than', and specify the start date.

When customizing a sparkline, which of the following is NOT one of the options?

Convert the Sparkline into a table.

What is the purpose of the function TIME()?

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

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

Ctrl + N

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

Ctrl+K

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

Please refer to the attached workbook. Column A has first and last names. The goal is to extract the first name in column B as shown in cell B1. With B2 as the active cell, which of the following is the correct set of steps to achieve that?

Data --> Data Tools --> Flash Fill

Please examine the attached workbook. Which conditional formatting rule is applied?

Data Bars

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 (command group) -> From Other Sources -> From Microsoft Query), how will you filter the records to retrieve those that have a value 'Tucson' for 'City' column.

From the Column to Filter list, select City. In the right panel, select 'equals' from the first drop-down list and then select 'Tucson' from the second drop-down list

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

Microsoft Query provides you with a number of features while importing data. Which of the following is NOT an option you can specify or customize through Microsoft Query?

Graphing of data from tables

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

Hidden & Empty Cell Settings

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

If there are negative values in your data, you can emphasize this by showing a(n) __________ axis in your sparkline.

Horizontal

Which function returns the contents of a cell at the intersection of a specified row and column from a range?

INDEX()

Which of the following function(s) can be used to count error values in a range?

ISERROR() ISERR() ISNA() (all)

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

Insert (tab) -> Charts (group) -> PivotChart

If you need to display lots of text in a worksheet, you can consider using a text box. How will you insert a textbox?

Insert (tab) -> Text (command group) -> Text Box

Which of the following is the MOST compelling motivation for using HLOOKUP()?

It usually reduces duplicate data storage

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

The _________ function can return a specified number of characters beginning at any position within a string.

MID()

What is Not the purpose of using Slicers?

Make table more beautiful

What are the two ways Excel offers to group Pivot table items?

Manually, Automatically

Vector based images are represented by?

Mathematical equations

How do you remove a filter in the PivotTable Field List?

Move the cursor over the filter icon next to the field name you want to remove the filter from. Click the arrow that appears, and then choose the Clear Filter From option.

If cell A1 contained the text MR. JOHN Q. PUBLIC, what would the formula (=PROPER(A1)) return?

Mr. John Q. Public

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

NETWORKDAYS()

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.

______ cells are by default disregarded when data validation rules are created. These cells can be included in the validation rule by unchecking the: "Ignore blank" checkbox.

None of the options provided is valid

Which of the following is true for printing a worksheet that contains objects / shapes?

Objects are printed along in the worksheet by default AND You can avoid printing the object by making changes to the properties of the shape

_______ 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

Which of the following is TRUE about Web queries?

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

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

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

RATE()

To avoid printing more pages than necessary in Excel, you can use the following command: ________ .

Set Print Area

Which of the following is NOT one of the worksheet views offered by Excel?

Simplified Layout

Which of the following is TRUE about Pivot Charts?

Slicers also work with pivot charts

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

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

After you create a Sparkline or group of them, you can change the type by choosing the Sparkline and clicking one of the three icons in the ________________.

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

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

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 ?

Synchronous Scrolling

In Excel 2016, in order to change the date's default format, one needs to change ___________ settings.

System-wide (i.e., all of Windows)

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 the sheets in the workbook

In Excel when calculating loan information, what does the PV() function return?

The time-adjusted value of the original loan amount

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.

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

Values

In Excel, a/an ________ is created or run to retrieve text or data from a Web Page.

Web Query

What is an advantage of using formulas instead of Autofill to create a series of dates? (e.g., for a work-week schedule)

When the first date is changed, the others update automatically

Which of the following file types can Excel import?

XML Files (.xml) Comma separated values (.csv) Microsoft Access (.accdb) All of the file types

If you copy the whole cell "B8" (not only its formula) 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

Is it possible to get the data back after deleting it using keyboard ?

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

Different table formatting styles can be previewed by mousing over different options within "Table Styles" (i.e., you can see what the appearance will be like without actually applying a new style). Is this statement accurate?

Yes, the statement is accurate

Excel has many Shapes to choose from, but sometimes the Shape you need is not in the gallery. In such a case, you may modify one of the existing shapes using a few techniques. One of them is 'Group Multiple Shapes'. What is its purpose / how can it be used?

You can create the Shape you need by combining two or more Shapes and then grouping them.

Which of the following is TRUE about the DATEDIF() function?

You must enter it manually

Which of the following are TRUE in the context of using VLOOKUP?

You use a range or table with data in columns; the lookup_value is found in the leftmost column of the lookup table The displayed result comes from a column number specified as a parameter You can indicate whether the matches are approximate or exact All answers provided are correct

Which of these characters is not allowed in sheet names

[ ]

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

_

While importing data from a web page into Excel, creating a refreshable Web Query is used to ______.

keep data dynamic or updateable

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

numeric values

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

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

passwords are case sensitive

A Sparkline is best described as a _____.

small chart typically displayed in a single cell

The Find and Replace dialog box cannot find background color formatting in tables that was applied using __________ or formatting that is applied based on ____________

table style, conditional formatting