Test 1 Excel - review

Which function would you use if you wanted to count the number of values, but ignore the cells that have text or are empty

COUNT

If you wanted to reference a cell in a formula and you did not want that cell to change when copying and pasting you would use what kind of cell reference?

Absolute

Where does excel automatically display statistics like count, average, and sum when a range of values is selected?

Status bar

What describes a function's purpose?

Name

Which of the following would not give the same result for "=(5+32+75+21)/4"?

=AVERAGE IF(5+32+75+21,4)

If the cell reference $A5 in a formula is copied and pasted over two columns and down two rows from its original cell, what will the cell reference become in the new cell?

$A7

True or false - The following formulas will all return the system clock's date?

=NOW()

=TODAY()

=DATE()

true

Functions can be selected by using the _________.

Insert function dialogue box

Which of the following is not a valid test in an =IF statement?

a. A4-B4

b. A4A4

d. B4<=B4

A4-B4

If A7= 21 which of the following =IF statements would produce the result of "Much Greater"?

=IF(A7<14,"Greater",IF(A7>20,"Much Greater", "Not greater than 20"))

Which of the following would not produce the correct monthly payment on a loan of $13000 which will be paid off in 5 years with an annual interest rate of 6%?

=PMT(6%,60,-13000

What is the default method of referencing cells in formulas?

Relative

To look up an exact match, what should you enter in the range_lookup argument?

False

Which of the following is not an argument associated with the IF function?

Value if neither true nor false

If a cell contains =$N$21 and then two new columns are inserted IN FRONT OF column N and three rows are inserted above row 21, what happens to the cell that contained =$N$21?

it become =$P$24

Which of the following is not a valid =IF statement

=IF(A5>119,"Greater","Not Greater")

What is not a direct option on the AutoSum drop down menu?

Median

If you wanted to count the number of entries in a list that contained numbers, text, and blank cells, but ignore the blank cells, which function would you use?

COUNTA

If a cell containing the formula =$B$14 is not copy and pasted, but move four columns over and four rows down, what would the resultant cell reference become?

=$B$14

What function computes the value in which one-half of the data is above and one-half is below?

Median

Which of the following does not comply with the preferred way of using the SUM function?

=SUM(A4/A11)

What should you not select when selecting data for a chart?

Worksheet titles

Which tab displays the tools for creating charts?

Insert

A 100% stacked column chart is similar to what other chart in that it converts values to percentages?

Pie

Which chart type is preferable when the category names are long?

Bar

What can you not do from the Select Data source Dialog box?

Change the chart type

Which chart type is a visual "dashboard" of your data?

Sparkline

What is a disadvantage of a stacked column chart?

a. Segments do not start at the same point

b. Segments do not end at the same point

c. there is a limit to how many segments can be displayed

d. This is only useful for one data series

Segments do not start at the same point

Which chart type is not listed in the All charts window?

Sparkline

What does excel not determine about the value axis when you create a chart?

Average Value

Which chart type is best when you want to chart two unrelated data types?

Combo cart

What is not a purpose of using an effective chart?

Reveals even subtle errors in formulas

Which chart type cannot be displayed as a sparkline?

Bar chart

Including what, distorts the effectiveness of charting data points?

Aggregates

In a bar chart, which axis displays the categories?

Vertical

True or false - Pie charts can only display ONE data series

True

True or False - When you create a chart Excel automatically creates the chart on its own sheet and labels it Chart 1

False

True or False - You can link to a cell in a datasheet for the title of a chart?

True

Line chart

Line connect data points

Pie chart-

Uses slices to display data points

Bar chart-

Uses horizontal bars

Column chart-

Uses vertical bars

Sparkline

Chart resides in one cell

X-axis-

Horizontal border

Y-axis-

Vertical border

Chart area-

Holds the entire chart

Plot area-

Both exes form a border for this

Legend

Uses color or patterns to identify each data series

On which tab will you find options if you have to print a large dataset?

Page layout

Which of the following is not a freeze option?

Freeze section

In a structured reference what do you use to indicate a column heading or field name?

Brackets [ ]

In the formula =SUBTOTAL(102,[PAID]), what does the 102 represent?

The function used for the SUBTOTAL tool

If you wanted every cell that has a value over $1,251 to have a purple fill, you would use what feature in excel?

Conditional Formatting

What is true about adding a new field to the right side of an existing data table?

You can add it to the right of the data table and excel will extend the table to include the new field

Where does excel display the Total row in a table?

Below the last row in the table

In the formula =SUBTOTAL(102,[PAID]), what does the word PAID represent?

The range to be included in the calculation

Using the data bars conditional formatting, how is the highest value represented?

By the longest bar

What is not true about using the AND function in a custom conditional formatting rule?

All the tests must be of the same data type, for example: all numbers or all text.

In a data table each row is considered a(n) __________.

Data point

True or false -- You cannot sort cells by fill color

False

Conditional Formatting: Text contains

Formats any cell that has context that matches the first content in the first selected cell

Conditional Formatting: Duplicate Values

Format cells that have exactly the same values

Conditional Formatting: Unique Values

Formats cells that have no matching values

Conditional Formatting: Equal to

Formats cells that have the value

Conditional Formatting: Clear format

Removes an conditional formatting

A subtotal row must contain at least one ________ function.

Aggregate

What does a + button indicate to the left of a row heading in an outline?

You can expand the details of the category

Which function is not a Subtotal function?

AGGREGATE

A ________ is an interactive table which uses calculations to consolidate and summarize information.

PivotTable

Moving one of two fields from the ROWS area to the COLUMNS area of a PivotTable would _________.

Consolidate data and remove redundant labels

Which of the following is not a valid method to remove a field from a PivotTable?

Select the field to be removed in the PivotTable and press DELETE

The ______ function is the default summary statistic for PivotTables.

SUM

What would you do to format all values as Currency in the Sum of Extended Price field no matter how you pivot the table?

Select number format and choose currency in the list of options and then click OK

You created a PivotTable and later made changes to values in the underlying data. What happens to the pivot table?

Changes in the dataset do not affect the PivotTable until you refresh the PivotTable

Using the % of Grand Total Calculation Option will display the value as ________.

A percentage of the grand total

A(n) ________ associates matching fields between 2 tables?

Relationship

Which of these characters is not an acceptable character to use in a range name?

!

What is a benefit of using a range name in Excel?

Range names are easier for people to remember than cell references.

Which of these would be a good candidate for a one-variable data table and payment function?

Checking scenarios with variable down payments

_______ enables you to experiment with different variables or assumptions so you can compare the related outcomes.

What-if analysis

Where would you setup the two substitutions values in a two-variable data table?

In the first row and first column in the table

Goal seek differs from one and two-variable data analysis by ______

Providing a single best answer rather than a table of possible answers

Which is not true about Scenario Manager?

Is rarely used in business models

Which of the following functions should be used to insert the current date and time in a cell?

=NOW()

Which of the following is not an argument of the IF function?

lookup_value

If you want to show exact values for a data series in a bar chart, which chart element should you display?

Data labels

Which of the following does not display automatically when you create a clustered column chart?

Data labels

Which Conditional Formatting rule is best suited to apply formatting to the top five values in a range of values?

Top 10 Items

Which date filter option enables you to restrict the view to only dates that occur in March of 2018?

Between

Which of the following is an unqualified structured reference?

=Sales[Purchase_Price]-Sales[Down_Payment]

If you would like to set a conditional formatting rule based on the function =AND(G6="Finance", H7<7000), which formatting rule type is needed?

Use a formula to determine which cells to format

A worksheet contains a PivotTable placeholder and the PivotTable Fields List. No fields have been added to the PivotTable yet. If you click the College Major field check box in the PivotTable Fields List, where does Excel place this field?

ROWS area