MIS 112 Final Exam

Comparing frequency:

You can use a common histogram, for example, to display the number (or percentage) of students who scored within a particular grade range.

Regarding the search-and-replace features provided by the "Find and Replace" dialog box, what is NOT true?

You cannot replace a type of formatting in a cell with another type of formatting.

What characters are NOT allowed in sheet names ?

[]

area chart

a line chart in which the area below the line has been colored in. Figure 19.25 shows an example of a stacked area chart.

xlsx

a workbook file that does not contain macros

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

all of the options provided for this answer are valid

Which of the following options moves the active cell right one screen?

alt+pgdn

stock chart

are most useful for displaying stock-market information. These charts require 19 three to five data series, depending on the subtype.

bubble chart

as an XY chart that can display an additional data series, which is represented by the size of the bubbles. As with an XY chart, both axes are value axes. (There is no category axis.)

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

FREQUENCY()

calculates how often values occur within a range of values, and then returns a vertical array of numbers.

PPMT function

calculates the amount of a loan payment applied to the principal

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

clear

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

columns

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

ctrl + F

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

ctrl + H

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

ctrl + shift + :

current time

ctrl + shift + ;

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

current date and time

A Web query contains information about the path to the ___________ and can pull the __________ data directly from that source.

data source, updated

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

Please refer to the figure. Which of the following Excel tabs is NOT visible in a worksheet by default?

developer

xy chart

differs from most other chart types in that both axes display values. (An XY chart has no category axis.)

surface chart

display two or more data series on a surface

histogram chart

displays the count of data items in each of several discrete bins. With a bit of effort, you can create a histogram by using a standard column chart or by using the Analysis ToolPak

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

dynamic

#NULL! errors

error occurs when a formula attempts to use an intersection of two ranges that don't actually intersect

Examine the screen-capture from Excel. What is the name of the open workbook?

example_111.xlsx

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

expanded

FIND

finds a substring within another text string and returns the starting position of the substring

SEARCH

finds a substring within another text string and returns the starting position of the substring.

Examine the attached workbook. Which ribbon tab provides the option to resize charts? (e.g., change the height & width)

format

bar chart

has been rotated 90 degrees clockwise. One distinct advantage to using a bar chart is that the category labels may be easier to read.

Bitmap:

images are made up of discrete dots. They usually look pretty good at their original size but often lose clarity if you increase the size. Examples of common bitmap file formats include BMP, PNG, JPEG, TIFF, and GIF.

pareto chart

is a combination chart in which columns are displayed in descending order, and the columns use the left axis. The line shows the cumulative percentage and uses the right axis.

F-test

is a commonly used statistical test that enables you to compare two population variances.

gantt chart

is a horizontal bar chart often used in project management applications. Although Excel doesn't support Gantt charts per se, creating a simple Gantt chart is possible. The key is getting your data set up properly.

OR()

is a logical function to test multiple conditions at the same time. OR returns either TRUE or FALSE. For example, to test A1 for either "x" or "y", use =OR(A1="x",A1="y").

table

is a rectangular range of structured data. Each row in the table corresponds to a single entity.

cell

is a single element in a worksheet that can hold a value, some text, or a formula. A cell is identified by its address, which consists of its column letter and row number. For example, cell D9 is the cell in the fourth column and the ninth row.

sparklines

is a small chart that's displayed in a single cell. A Sparkline allows you to quickly spot time-based trends or variations in data. Because they're so compact, Sparklines are almost always used in a group.

radar chart

is a specialized chart that has a separate axis for each category, and the axes extend outward from the center of the chart.

exponential smoothing

is a technique for predicting data that is based on the previous data point and the previously predicted data point.

correlation

is a widely used statistic that measures the degree to which two sets of values vary together. For example, if higher values in one data set are typically associated with higher values in the second data set, the two data sets have a positive correlation

analysis toolpak

is an add-in that provides analytical capability that normally isn't available in Excel.

chart area

is an object that contains all other elements in the chart. You can think of it as a chart's master background or container.

hyperlink

is clickable text that provides a quick way to jump to other workbooks and files.

pivot table

is essentially a dynamic summary report generated from a database. The database can reside in a worksheet (in the form of a table) or in an external data file.

sunburst charts

is like a pie chart with multiple concentric layers. This chart type is most useful for data that's organized hierarchically.

add-ins tab

is visible only if you loaded an older workbook or add-in that customizes the menu or toolbars. Because menus and toolbars are no longer avail- able in Excel 2016, these user interface customizations appear on the Add-Ins tab

incorrect reference errors

logic of the formula is correct, but the formula uses an incorrect cell reference. As a simple example, the range reference in a Sum formula may not include all the data that you want to sum

What is the value to be found in the first row of the table while using the HLOOKUP function?

lookup value

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

no

For a range called Data, what does the function =COUNTIF(Data, TODAY()) return?

number of cells containing the current date

circular error

occurs when a formula refers to its own cell, either directly or indirectly. Circular references are useful in a few cases, but most of the time, a circular reference indicates a problem.

#REF! errors

occurs when a formula uses an invalid cell reference.

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

range (and) criteria

While using web queries Rich text formatting is typically used if we wish to ______.

reproduce web page data in a format close to that of the original

lookup formula

returns a value from a table by looking up another related value. A common tele- phone directory (remember those?) provides a good analogy.

AVERAGE()

returns the average (arithmetic means) of its arguments

MAX()

returns the largest value

NPER function

returns the number of payment periods for a loan, given the loan's amount, interest rate, and periodic payment amount.

MIN()

returns the smallest value

INDEX()

returns the value at a given position in a range or array. You can use index to retrieve individual values or entire rows and columns.

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

review

MATCH function

searches for a value in an array and returns the relative position of that item.

What does Excel display when you enter the formula =DATE(2014,13,1)?

"1/1/2015"

What characters can't you use in the sheets names?

- colon - slash - backslash - square brackets - question marks - asterisk

By default, what is the extension used when you save a workbook in Excel 2016?

.xlsx

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

10%, 400%

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

2000

Please refer the attached workbook. What is the total amount of Sales for the Arizona (AZ) state in the month of January (Jan)?

2300

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

255

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

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

Please refer to the figure. How many worksheets are contained in the workbook shown?

5

The formula to calculate the value of one minute, that can be added to a date serial number is:

=1 / (24 * 60)

Which formula can be used to find: How many songs were sung by singers whose names begin with S?

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

Which of the following is the correct formula for converting the hexadecimal argument "3BC" to its decimal equivalent?

=HEX2DEC("3BC")

Please see the attached workbook. The grade of the student should be "Pass" if his/her score is greater than 65. Otherwise, the grade should be "Fail". Which of the following formulas could you use for this scenario?

=IF(D2>65,"Pass","Fail")

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 is used to determine the date of the Sunday that was most recent?

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

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

?, *

Which of the following is the correct output of the formula '=TRIM(" A B C D ")'?

A B C D

xlsk

A backup file

xlsb

A binary file similar to the old XLS format but able to accommodate the new features

Comparing data over time:

A chart may display sales by month and indicate trends over time.

IPMT function

A financial function that calculates periodic interest for a fixed-term, fixed-rate loan or investment

range

A group of cells

#NUM! error

A problem with a value exists; for example, you specified a negative number where a positive number is expected.

lookup_vector

A single-column or single-row range that contains the values to be looked up. These values must be in ascending order

Based on the class lecture, which of the following is true about table renaming?

A table can be renamed to better reflect its contents (e.g., PARTS or PRODUCTS)

tw0-factor without replication

A two-way analysis of variance, with a single sample (or replication) for each group of data

xlsm

A workbook file that contains macros

Ctrl + PgUp

Activates the previous sheet, if one exists

blank row

Add a blank row between items to improve readability.

manually

After creating the pivot table, select the items to be grouped and then choose PivotTable Tools ➪ Analyze ➪ Group ➪ Group Selection. Or you can select the items, right-click, and choose Group from the shortcut menu.

General:

Aligns numbers to the right, aligns text to the left, and centers logical and error values. This option is the default alignment.

left:

Aligns the cell contents to the left side of the cell. If the text is wider than the cell, the text spills over to the cell on the right. If the cell on the right isn't empty, the text is truncated and not completely visible. Also available on the Ribbon.

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

All of the above

Data validation on a cell or range can do which of the following?

All of the options provided are valid

Comparing data relationships

An XY chart is ideal for this comparison. For example, you might show the relationship between monthly marketing expenditures and sales.

xlsa

An add-in file

cell precedents

Applicable only to cells that contain a formula, a formula cell's precedents are all the cells that contribute to the formula's result. A direct precedent is a cell that you use directly in the formula. An indirect precedent is a cell that isn't used directly in the formula but is used by a cell that you refer to in the formula.

CTRL+SHIFT+$

Applies the Currency format with two decimal places (negative numbers in parentheses).

CTRL+SHIFT+#

Applies the Date format with the day, month, and year.

To calculate the cumulative interest paid between any two payment periods, use the ______________ financial function.

CUMIPMT()

center:

Centers the cell contents in the cell. If the text is wider than the cell, the text spills over to cells on either side if they're empty. If the adjacent cells aren't empty, the text is truncated and not completely visible. Also available on the Ribbon

In the image shown below, identify the correct chart types. You can use the data in the attached Excel workbook (charts_SampleData.xlsx) to try different charts.

Chart 1: Column, Chart 2: Bar

report layouts

Choose from three different layout styles (compact, outline, or tabular). You can also choose to hide repeating labels.

grand totals

Choose which types, if any, to display.

Text manipulation:

Clean up and standardize text-based data

clear formats

Clears only the formatting and leaves the value, text, or formula.

clear comments

Clears the comment (if one exists) attached to the cell

simple buttons

Click the button, and it does its thing. An example of a simple button is the Increase Font Size button in the Font group of the Home tab. Some buttons perform the action immediately; others display a dialog box so that you can enter additional information. Button controls may or may not be accompanied by a descriptive label.

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

Convert the Sparkline into a table.

DATEVALUE function

Converts a date in the form of text to a serial number.

DAY function

Converts a serial number to a day of the month.

WEEKDAY function

Converts a serial number to a day of the week

DCOUNT

Counts the number of records that meet specified criteria; used with a work- sheet database

Creating charts:

Create a variety of highly customizable charts.

Number crunching:

Create budgets, tabulate expenses, analyze survey results, and perform just about any type of financial analysis you can think of.

current date

Ctrl + ;

If you are trying to display trends with a Sparkline, you may have to work with irregular time periods in the underlying data. To properly do so, you can click on the Group (command group), then click on "Axis", and choose _________ to format the shape of the chart.

Date Axis Type

DB

Declining balance. Computes depreciation at a fixed rate.

What is the use of 'Icon sets' under Conditional formatting?

Displays icons directly in the cells. The icons depend on the cell's value.

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

Displays the current time

#DIV/0! errors

Division by zero is not a valid operation

fixed width

Each component occupies the same number of characters.

mixed

Either the row or the column reference is relative, and the other is absolute. Only one of the address parts is absolute (for example, $A4 or A$4).

Where do you find the command to add a pivot table on your worksheet?

Excel Ribbon: Insert (tab) ->Tables

data tab

Excel's data-related commands are on this tab, including data validation commands.

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

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

Maximized:

Fills the entire screen. To maximize a window, click its Maximize button.

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 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 type 'T' in the second drop-down list box

Ctrl+Shift+~

General number format (that is, unformatted values)

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

what is the syntax for HLOOKUP?

HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)

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

HYPERLINK()

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

Minimized:

Hidden but still open. To minimize a window, clicks its Minimize button.

Which of the following is NOT one of the Excel functions to determine whether a cell contains an error value?

ISLOGICAL

Match entire cell contents

If this check box is selected, a match occurs if the cell contains only the search string (and nothing else). For example, searching for Excel doesn't locate a cell that contains Microsoft Excel. When using wildcard characters, an exact match is not required.

match case

If this check box is selected, the case of the text must match exactly. For example, searching for smith does not locate Smith.

Identifying outliers or unusual situations

If you have thousands of data points, creating a chart may help identify data that isn't representative.

Accessing other data:

Import data from a variety of sources.

When (or why) does Excel draw arrows to indicate an error source?

In Tracing Error Values

In the Analysis ToolPak for Excel, which of the following parameters can you specify when creating a Histogram?

Input Range and Bin Range

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

Interest rate:

Interest is a percentage of the principal, usually expressed on an annual basis. For example, you may earn 2.5% annual interest on a bank CD (certificate of deposit).

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 HYPERLINK function syntax has two arguments. Which argument has the path and file name to the document to be opened?

Link_location

To apply specific formatting to a sparkline, use the Sparkline Color or the ___________ commands.

Marker Color

Vector based images are represented by?

Mathematical equations

merge cells

Merges the selected cells without applying the Center attribute.

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

NETWORKDAYS()

Fill in the blanks: The boxed area is called ____. What is displayed in this boxed area when a range of cells is selected? _______

Name box. The number of rows and columns in the selection is displayed here.

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

No, you cannot

______ 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

range_lookup

Optional. If TRUE or omitted, an approximate match is returned. (If an exact match is not found, the next largest value that is less than lookup_ value is returned.) If FALSE, VLOOKUP will search for an exact match. If VLOOKUP can't find an exact match, the function returns #N/A.

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.

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

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)

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 TRUE about Pivot Charts?

Slicers also work with pivot charts

Unlike charts on an Excel worksheet, _________ are not objects. Instead these are tiny charts in the background of a cell.

Sparklines

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.

table_array

The range that contains the lookup table.

relative

The row and column references can change when you copy the formula to another cell because the references are actually offsets from the current row and column. By default, Excel creates relative cell references in formulas.

absolute

The row and column references don't change when you copy the formula because the reference is to an actual cell address. An absolute reference uses two dollar signs in its address: one for the column letter and one for the row number (for example, $A$5).

access files

These files have various extensions, including .mdb and .accdb.

Excel formatting tools are used to achieve which of the following?

To make the end result attractive and more functional

Ctrl+Shift+!

Two decimal places, thousands separator, and a hyphen for negative values

unmerge cells

Unmerges the selected cells.

Creating graphics and diagrams:

Use Shapes and SmartArt to create professional- looking diagrams.

merge across

When a multi-row range is selected, this command creates multiple merged cells — one for each row.

The HLOOKUP function works like VLOOKUP function except that the lookup table is arranged ___________ instead of __________.

"horizontally, vertically"

#N/A errors

#N/A error occurs if any cell referenced by a formula displays #N/A and when a LOOKUP function (HLOOKUP, LOOKUP, MATCH, or VLOOKUP) can't find a match

What is total widget sales for the West Region for the year?

$5,129

#VALUE! errors

- An argument for a function is of an incorrect data type, or the formula attempts to perform an operation using incorrect data. For example, a formula that adds a value to a text string returns the #VALUE! error. - A function's argument is a range when it should be a single value. A custom worksheet function is not calculated. You can press Ctrl+Alt+F9 to force a recalculation. - A custom worksheet function attempts to perform an operation that is not valid. For example, custom functions can't modify the Excel environment or make changes to other cells. - You forget to press Ctrl+Shift+Enter when entering an Array formula.

#NUM! errors

- You pass a nonnumeric argument to a function when a numeric argument is expected (for example, $1,000 instead of 1000). - You pass an invalid argument to a function, such as attempting to calculate the square root of a negative number. This formula returns #NUM!: =SQRT(-12) - A function that uses iteration can't calculate a result. Examples of functions that use iteration are IRR and RATE. - A formula returns a value that is too large or too small. Excel supports values between -1E-307 and 1E+3

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

If you enter 9/19/2014 25:00:00, how will it be interpreted by Excel? (Note: assume a Short Date format)

9/20/2014 1:00:00am

Comparing an item to other items

: A chart may compare sales in each of a company's sales regions.

Making relative comparisons

: A common pie chart can depict relative proportions in terms of pie "slices."

two factor with replication

: A two-way analysis of variance, with multiple samples (or replications) for each group of data

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)

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

Please see the attached workbook. If the 'amount' for the given 'date' is to be retrieved from the given range of data (I4:K7), which of the following formulas should be used?

=INDEX(I4:K7,MATCH(B2,I4:I7,0),3)

Examine the attached worksheet. Which of the following formulas, when entered in cell B12, will give the total discounted cost of all the items purchased?

=TOTAL(F2:F8)

check boxes

A check box control turns something on or off. An example is the Gridlines control in the Show group of the View tab. When the Gridlines check box is checked, the sheet displays gridlines. When the control isn't checked, the gridlines don't appear.

logical error

A formula doesn't return an error, but it contains a logical flaw that causes it to return an incorrect result.

a calculated field

A new field created from other fields in the pivot table. If your pivot table source is a worksheet table, an alternative to using a calculated field is to add a new column to the table and create a formula to perform the desired calculation

Restored:

A non-maximized size. To restore a maximized window, click its Restore Down button. To restore a minimized window, click its icon in the Windows taskbar. A window in this state can be resized and moved.

single-factor:

A one-way analysis of variance, with only one sample for each group of data

Which of the following statements is true?

A workbook can have many worksheets.

xltm

A workbook template file that contains macros

xltx

A workbook template file that does not contain macros

Please refer to the figure. In an Excel worksheet, what column name comes directly after Column Z?

AA

Ctrl + PgDn

Activates the next sheet, if one exists

Automatic grouping of Pivot Table items is possible when a field contains which of the following types of values?

All of the answers provided are correct

semantic error

An example is a function name that is spelled incorrectly. Excel will attempt to interpret it as a name and will display the #NAME? error.

CTRL+SHIFT+%

Applies the Percentage format with no decimal places.

CTRL+SHIFT+^

Applies the Scientific number format with two decimal places.

CTRL+SHIFT+@

Applies the Time format with the hour and minute, and AM or PM.

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

clear all:

Clears everything from the cell — its contents, its formatting, and its cell comment (if it has one).

clear contents

Clears only the cell's contents and leaves the formatting. This has the same effect as pressing Delete.

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

DDB

Double declining balance. Computes depreciation at an accelerated rate. Depreciation is highest in the first period and decreases in successive periods.

CODE function

E function returns the character code for its argument. The formula that follows returns 65, the character code for uppercase A: =CODE("A")

spinners

Excel's Ribbon has only one spinner control: the Scale to Fit group of the Page Layout tab. Click the top part of the spinner to increase the value; click the bottom part of the spinner to decrease the value.

If your formula uses named cells or ranges, you can either type the name in place of the address, or choose the name from a list and have Excel insert the name for you automatically. You can also press the ____ button which displays the Paste Name Dialog box. If no names are defined, pressing this button has no effect.

F3

Subtotals

Hide subtotal, or choose where to display them (above or below the data).

Please see the attached workbook. Which of the following options can you choose with a Sparkline if you want to highlight the budget for the third year?

Highlight the: Low point

Please see the attached workbook, go to Sheet1. How do you find all the cells that have conditional formatting?

Home (tab) -> Editing (command group) -> Find & Select -> Conditional Formatting

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

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

HLOOKUP

Horizontal lookup. Searches for a value in the top row of a table and returns a value in the same column from a row you specify in the table.

simple drop-downs

If the Ribbon command has a small down arrow, the command is a drop-down. Click it, and additional commands appear below it. An example of a simple drop-down is the Conditional Formatting command in the Styles group of the Home tab. When you click this control, you see several options related to conditional formatting.

IFERROR*

If the first argument returns an error, the second argument is evaluated and returned. If the first argument does not return an error, then it is evaluated and returned.

automatically

If the items are numeric (or dates), use the Grouping dialog box to specify how you would like to group the items. Select any single item and then choose PivotTable Tools ➪ Analyze ➪ Group ➪ Group Field.

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 these dates will Excel store as a text string rather than a serial number?

June 23 2014

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

what is the LOOKUP syntax?

LOOKUP(lookup_value,lookup_vector,result_vector)

_______ 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

Automating complex tasks:

Perform a tedious task with a single mouse click with Excel's macro capabilities.

dBase files

Produced by dBase III and dBase IV. Excel does not support dBase II files

A single file web page

Produces a MIME HTML file (*.mht; *.mhtml). Not all browsers can open these files.

An HTML file

Produces a static web page, plus a folder that contains support files. You can create the HTML file from the entire workbook or from a specific sheet

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

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 Qtr 3, which product was bought by the highest number of customers?

Raclette Courdavault

clear hyperlinks

Removes hyperlinks contained in the selected cells. The text and formatting remain, so the cell still looks like it has a hyperlink, but it no longer functions as a hyperlink

remove hyperlinks

Removes hyperlinks in the selected cells, including the cell formatting.

OFFSET

Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells.

LOOKUP

Returns a value either from a one-row or one-column range. Another form of the LOOKUP function works like VLOOKUP but is restricted to returning a value from the last column of a range

IF

Returns one value if a condition you specify is TRUE, and returns another value if the condition is FALSE.

COUNTBLANK

Returns the number of blank cells.

COUNT

Returns the number of cells that contain a numeric value.

COUNTIF

Returns the number of cells that meet a specified criterion.

COUNTIFS*

Returns the number of cells that meet multiple criteria.

COUNTA

Returns the number of nonblank cells.

NETWORKDAYS* function

Returns the number of whole work days between two dates.

MATCH

Returns the relative position of an item in a range that matches a specified value.

DATE function

Returns the serial number of a particular date.

NOW function

Returns the serial number of the current date and time.

TODAY function

Returns the serial number of today's date.

SUMIF

Returns the sum of cells that meet a specified criterion.

SUM

Returns the sum of its arguments

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

Please refer to the figure. What Excel element is shown in the red box?

Ribbon

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.

insert tab

Select this tab when you need to insert something into a worksheet — a table, a diagram, a chart, a symbol, and so on.

Data Validation

Selects cells that are set up for data entry validation (by choosing Data ➪ Date Tools ➪ Data Validation). The All option selects all such cells. The Same option selects only the cells that have the same validation rules as the active cell

Conditional Formats

Selects cells that have a conditional format applied (by choosing Home ➪ Styles ➪ Conditional Formatting). The All option selects all such cells. The Same option selects only the cells that have the same conditional formatting as the active cell.

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

Send using E-mail

SLN

Straight line. The asset depreciates by the same amount each year of its life.

Creating graphical dashboards:

Summarize a large amount of business information in a concise format.

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

AND()

TRUE if all its arguments evaluate to TRUE, and returns FALSE if one or more arguments evaluate to FALSE

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

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

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

col_index_num

The column number within the table from which the matching value is returned.

delimited

The data to be split is separated by delimiters such as commas, spaces, slashes, or other characters.

#NAME? errors

The formula contains an undefined range or cell name. ■The formula contains text that Excel interprets as an undefined name. A misspelled function name, for example, generates a #NAME? error. ■The formula contains text that isn't enclosed in quotation marks. ■The formula contains a range reference that omits the colon between the cell addresses. ■The formula uses a worksheet function that's defined in an add-in, and the add-in is not installed

#VALUE! error

The formula includes an argument or operand of the wrong type. (An operand is a value or cell reference that a formula uses to calculate a result.)

#N/A error

The formula is referring (directly or indirectly) to a cell that uses the NA function to signal that data is not available. Some functions (for example, VLOOKUP) can also return #N/A.

#DIV/0! error

The formula is trying to divide by zero. This also occurs when the formula attempts to divide by what's in a cell that is empty (that is, by nothing).

#REF! error

The formula refers to a cell that isn't valid. This can happen if the cell has been deleted from the worksheet.

#NAME? error

The formula uses a name that Excel doesn't recognize. This can happen if you delete a name that's used in the formula or if you have unmatched quotes when using text.

#NULL! error

The formula uses an intersection of two ranges that don't intersect. (This concept is described later in the chapter.)

incomplete calculation error

The formulas simply aren't calculated fully. To ensure that your formulas are fully calculated, press Ctrl+Alt+Shift+F9.

result_vector

The single-column or single-row range that contains the values to be returned. It must be the same size as the lookup_vector.

look_up

The value to be looked up in the first column of the lookup table.

lookup_value

The value to be looked up in the lookup_vector.

cell dependents

These formula cells depend upon a particular cell. A cell's dependents consist of all formula cells that use the cell. Again, the formula cell can be a direct dependent or an indirect dependent.

add a digital signature

This command allows you to "sign" a workbook digitally.

protect workbook structure

This command lets you protect the structure of a workbook. It displays the same dialog box as Review ➪ Changes ➪ Protect Workbook.

protect current sheet

This command lets you protect various elements of a work- sheet. It displays the same dialog box as the Review ➪ Changes ➪ Protect Sheet command

look in

This drop-down list has three options: Values, Formulas, and Comments. If, for example, Values is selected, searching for 900 doesn't find a cell that contains 900 if that value is generated by a formula (unless the formula itself contains 900).

Payment (PMT)

This is either principal or principal plus interest. If you deposit $100 per month into a savings account, $100 is the payment.

Term:

This is the amount of time of interest. A 12-month bank CD has a term of one year. A 30-year mortgage loan has a term of 360 months

Present value (PV)

This is the principal amount. If you deposit $5,000 in a bank savings account, this amount represents the principal, or present value, of the money you invested.

Future value (FV)

This is the principal plus interest. If you invest $5,000 for five years and earn 3% annual interest, your investment is worth $5,796.37 at the end of the five-year term.

periodic

This represents the point in time when interest is paid or earned (for exam- ple, a bank CD that pays interest quarterly, or an auto loan that requires monthly payments).

page layout tab

This tab contains commands that affect the overall appearance of your worksheet, including some settings that deal with printing.

review tab

This tab contains tools to check spelling, translate words, add comments, or protect sheets.

developer tab

This tab isn't visible by default. It contains commands that are useful for programmers. To display the Developer tab, choose File ➪ Options and then select Customize Ribbon. In the Customize the Ribbon section on the right, make sure Main Tabs is selected in the drop-down control, and place a check mark next to Developer.

filtering table with slicers

To add one or more slicers, activate any cell in the table and choose Table Tools ➪ Design ➪ Tools ➪ Insert Slicer. Excel responds with a dialog box that displays each header in the table

Organizing lists:

Use the row-and-column layout to store lists efficiently.

chart elements

Use these tools to hide or display specific elements in the chart. Note that each item can be expanded to show additional options. To expand an item in the Chart Elements list, hover your mouse over the item and click the arrow that appears.

Encrypt with Password

Use this command to specify a password that is required to open the workbook. (See "Password-Protecting a Workbook," earlier in this chapter.)

chart filters

Use this icon to hide or display data series and specific points in a data series, or hide and display categories. Some chart types do not display the Chart Filters button

chart styles

Use this icon to select from prebuilt chart styles or change the color scheme of the chart.

mark as final

Use this option to designate the workbook as "final." The document is saved as a read-only file to prevent changes. This isn't a security feature. Rather, the Mark as Final command is useful to let others know that you're sharing a completed version of a workbook.

formulas tab

Use this tab to insert a formula, name a cell or a range, access the formula auditing tools, or control the way Excel performs calculations.

a calculated items

Uses the contents of other items within a field of the pivot table. If your pivot table source is a worksheet table, an alternative to using a calculated item is to insert one or more rows and write formulas that use values in other rows.

How can a manual Page Break be removed from a worksheet?

Using the: Reset All Page Breaks command

Which of the following options provides correct syntax for the VLOOKUP function?

VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

what is the VLOOKUP syntax?

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

VLOOKUP

Vertical lookup. Searches for a value in the first column of a table and returns a value in the same row from a column you specify in the table

array formula entry error

When entering (or editing) an array formula, you must press Ctrl+Shift+Enter to enter the formula. If you fail to do so, Excel doesn't recognize the formula as an array formula, and you may get an error or incorrect results.

You ask your customers to provide you with inventory usage data to help you plan your production schedule. Which of the following file formats will you ask them to provide data in (so that data can be easily imported into Excel)?

XML

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

Yes, but only if there are no more than 3 sheets in the workbook

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

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

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

syntax error

You have a problem with the syntax of a formula. For example, a formula may have mismatched parentheses, or a function may not have the correct number of arguments.

Home tab

You'll probably spend most of your time with the Home tab selected. This tab contains the basic Clipboard commands, formatting commands, style commands, commands to insert and delete rows or columns, plus an assortment of worksheet editing commands.

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

_

toggle buttons

and conveys some type of information by displaying two different colors. An example is the Bold button in the Font group of the Home tab. If the active cell isn't bold, the Bold button displays in its normal color. If the active cell is already bold, the Bold button displays a different background color. If you click the Bold button, it toggles the Bold attribute for the selection.

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

Vector

are comprised of points and paths that are represented by mathematical equations, so they retain their crispness regardless of their size. Examples of common vector file formats include CGM, WMF, and EPS.

Please see the attached workbook. In cell A2, how is the data validation done?

by checking if the number of characters is between 4 and 20

Which of the following is an object that contains all other elements in an Excel chart?

chart area

split buttons

combines a one-click button with a drop-down. If you click the button part, the command is executed. If you click the drop-down part (a down arrow), you choose from a list of related commands. An example of a split button is the Merge & Center command in the Alignment group of the Home tab (see Figure 1.7). Clicking the left part of this control merges and centers text in the selected cells. If you click the arrow part of the control (on the right), you get a list of commands related to merging cells.

view tab

contains commands that control various aspects of how a sheet is viewed. Some commands on this tab are also available in the status bar.

PROPER

converts the text to Proper Case (the first letter in each word is capitalized, as in a proper name)

CHAR function

is essentially the opposite of the CODE function. Its argument should be a value between 1 and 255, and the function returns the corresponding character. The fol- lowing formula, for example, returns the letter A: =CHAR(65)

box and whisker chart

is often used to visually summarize data. In the past, it was possible to create such charts using Excel, but it required quite a bit of setup work. In Excel 2016, it's simple.

treemap charts

is suited for hierarchical data. The data, however, is represented as rectangles. Figure 19.40 shows the data from the previous example, plotted as a treemap chart.

What happens when you click the icon in a Slicer's upper-right corner?

it removes the effects of filtering

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

pie area

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

prevent Excel from using GETPIVOTDATA function

Please refer to the figure. What Excel element is shown in the red box?

quick access toolbar

You may refer to the attached workbook to answer the following question. What command under the Table Tool /Design Tab can you use to add two more rows to the table?

resize table

RATE()

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

PV function

returns the present value (that is, the original loan amount) for a loan, given the interest rate, the number of periods, and the periodic payment amount

FV function

to calculate the value of the investment at the end of the term

A _____ data table in Excel shows the result of a single calculation for different values of two input cells

two-way

line chart

used to plot continuous data and are useful for identifying trends. For example, plotting daily sales as a line chart may enable you to identify sales fluctuations over time.

pie chart

useful when you want to show relative proportions or contributions to a whole. A pie chart uses only one data series.

column chart

which displays each data point as a vertical column, the height of which corresponds to the value. The value scale is dis- played on the vertical axis, which is usually on the left side of the chart.

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

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

wrap text

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