MIS 112 Problem Sets #1-13

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

A pivot chart is which of the following?

A graphical representation of a data summarized in a pivot table

A loan amortization schedule in Excel is:

A table of values that shows types of information for each payment period of a loan

Which of the following statements is true?

A workbook can have many worksheets.

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 Shape. 2) Click (Drawing Tools) Format. 3) In the Shape Styles group, select a different outline.

What command (within Conditional Formatting, Manage Rules) is used to delete a specific rule from a worksheet?

Delete Rule

When a header or a 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

Which of the following does the Formula Autocomplete feature in excel take into account?

Excel built-in functions, user-defined functions, and defined names

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

Expanded

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

Field

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

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

The HYPERLINK function syntax has two arguments. Which argument has the hump text or numeric value that is displayed in the cell?

Friendly_name

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 the name of the company that created Excel?

Microsoft

Which of the following is NOT one of the options for highlighting certain data points in a Sparkline?

Middle point

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

No

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

No, you cannot

Which financial function returns the loan payment (principal plus interest) per period, assuming constant payment amounts and a fixed interest rate?

PMT()

Which of the following is an object that contains the actual data in the chart?

Plot area

Which function can be used to make crude/simple horizontal separators between cells?

REPT()

What happens if you try to store a number greater than 15 digits in Excel?

Replace the digits exceeding 15 with the number zero

What does the following formula do? =COUNTIF(Data, TODAY()))

Returns the number of cells containing the current date in the range named Data.

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

Which of the following is true with regards to row and column titles and headers?

Row and column titles describe field names in a table or list while headers appear at the top of each page.

The function _____________ updates specific characters in a string with a new value (i.e., with new text). Hint: This function is used when you know the text to be replaced but not the specific location.

SUBSTITUTE()

Which of the following file formats does NOT use a bitmap representation or format? (Bitmap images usually look good at their original size but lose clarity if you increase the size)

SVG

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

To view or edit a comment, you can select the cell where a comment has been attached and press the following and press the following keyboard shortcut: __________.

Shift+F2

What characters are NOT allowed in sheet names?

[]

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

_

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

an integer between 1 and 7

Consider the HLOOKUP function in Excel. If the "range_lookup" argument is set to TRUE, we need to sort the first column of the range in ___________ order.

ascending

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

automatic

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

columns

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

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

filter data

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

keep data dynamic or updateable

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

Data retrieved from Web Queries do not include __________ and contents of ___________.

pictures, scripts

When do you use the following sequence of commands: PivotTable Tools -> Analyze -> PivotTable -> Options -> Generate GetPivot Data?

prevent Excel from using GETPIVOTDATA function

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

All manual page breaks are shown as ______ lines instead of ____ lines (when you use the view mode of Page Break Preview).

solid, dashed

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

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

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 action if the criteria is false)

In excel, the DATE function takes _____ arguments.

3

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

3

Which of the following is TRUE regarding the HTML files that Excel creates?

Changes to graphs and formulas cannot be made to an HTML file created from Excel.

If the date serial number of March 15, 2014 is represented as 41713 then how will noon (half way through the day) be represented?

41713.5

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

Which of the following formulas returns the number of days in the corresponding month for a date in cell B2?

=DAY(DATE(YEAR(B2), MONTH(B2)+1,0))

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

?,*

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

?NULL?

What command would you use to import a CSV file?

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

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

Which of the following features for Sparklines is missing in excel 2016 but can be simulated artificially by transforming the data?

Adding Reference line

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

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

Area, Scatter, and Column

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

Arguments

Which of the following functions can be used to count specific types of errors?

COUNTIF

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

Calibri, 11 point

A bar chart is essentially which of the following chart types rotated 90 degrees clockwise?

Column

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

Computes depreciation at a fixed rate and its arguments are: Cost, Salvage, Life, Period, [Month]

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

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

Convert to Range

Which of the following keyboard shortcuts can be used to activate a different sheet?

Ctrl + PageUp

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

Ctrl + S

Which of the following keyboard shortcuts will add the current time to a cell in an excel worksheet?

Ctrl + Shift + :

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

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 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 select 'T' from 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 / Formulas

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 following is a VALID method of hiding rows or columns?

Home (ribbon tab), (then) Format in the Cells group) --> Hide & Unhide (from the drop down list)

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

ISERROR(), ISERR(), and ISNA()

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 if category = "Soups") is only checked/evaluated if the first part (i.e., checking if category = "Pasta") evaluates to FALSE.

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

Insert Page Breaks, Remove Page Breaks, Reset All Page Breaks

The basic formula for computing simple interest is:

Interest = (Principal) x (Rate) x (Time)

When a column is sorted within a table, which of the following is true about the drop-down list in the header row?

It displays a different graphic to remind you that the table is sorted by that column

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

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

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

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

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

Slicer

Which of the following is TRUE about Pivot Charts?

Slicers also work with pivot charts

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.

After you create a Sparkline, chn aging 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)

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

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

What function removes leading and trailing from a cell?

TRIM

The formula =COUNTIF(range, "*budget*") will count the number of cells in the specified range containing the words "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.

There are four boxes/area below the PivotTable Field List. Which of the following is NOT one of these four boxes?

Tools

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

Values

When a row of data is very ____ and requires repeated horizontal scrolling, consider using a data form to ass, edit, find, and delete rows.

Wide

The ____ command is used to display text on multiple lines in a cell.

Wrap text

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

Yes

In Excel, can you insert a Shape into a chart?

Yes, select the chart and then click inside the chart to insert the Shape.