Excel part 3

When using advanced number filters, the custom auto filter dialog box allows you to use __ for any series of characters.

*

___________ organizes all of the data in your worksheet by one column.

Sort sheet

The __________ command allows you to automatically create groups and use common functions like SUM, COUNT, and AVERAGE to help summarize your data.

Subtotal

The AutoSum command will automatically select a cell range for the argument under all circumstances.

True

To remove all filters from your worksheet, click the Filter command on the Data tab.

True

To remove all groups without deleting the subtotals, click the Ungroup command drop-down arrow, then choose Clear Outline.

True

To ungroup data, select the grouped rows or columns, then click the Ungroup command.

True

If you have several windows open at the same time, you can use the ______ ______ command to rearrange them quickly.

Arrange All

The __________ command allows you to automatically insert the most common functions into your formula, including SUM, AVERAGE, COUNT, MIN, and MAX

AutoSum

The Freeze Panes option includes which of the following?

Freeze Top Column and Freeze First Row

Sometimes you may not want to keep subtotals in your worksheet, especially if you want to reorganize data in different ways. If you no longer want to use subtotaling, you'll need remove it from your worksheet. 1. Select the Data tab, then click the Subtotal command. 2. The Subtotal dialog box will appear. Click _______ ___.

Remove All

To show a hidden group, click the _____ _______ button Image of the Show detail button.

Show Detail

When sorting on values (numbers), the default is

Smallest to largest

In order for filtering to work correctly, your worksheet should include a header row, which is used to identify the name of each column.

True

You can double-click the filled cells to check their formulas for accuracy. The relative cell references should be different for each cell, depending on their rows.

True

You can use an absolute reference to keep a row and/or column constant.

True

To quickly deselect all data in a filter,

Uncheck the box next to Select All

To unfreeze rows or columns, click the Freeze Panes command, then select _________ ________ from the drop-down menu.

Unfreeze Panes

Multiple arguments must be separated by a

comma

Absolute references, on the other hand, remain _________, no matter where they are copied.

constant

You can apply multiple filters to help narrow down your results; filters are, therefore, said to be

cumulative

An absolute reference is designated in a formula by the addition of a _____ _______. It can precede the column reference, the row reference, or both.

dollar sign

Whenever you're making an absolute reference across multiple cells, be sure to use the:

dollar sign

When sorting data, it's important to first decide if you want the sort to apply to the ________ ________ or just a cell range.

entire worksheet

Rather than cell content, you can also choose to sort your worksheet by

formatting

In selecting Freeze Panes, the rows will be frozen in place, as indicated by the _____ line. You can scroll down the worksheet while continuing to view the frozen rows at the top.

gray

Arguments can refer to:

individual cells cell ranges

If you need more control over how your data is sorted, you can add multiple ______ to any sort.

levels

When you create subtotals, your worksheet it is divided into different _____.

levels

There is a feature that allows you to compare two different worksheets from the same workbook: Excel allows you to open _______ _________ for a single workbook at the same time.

multiple windows

When there is more than one operation in a formula, the _____ __ __________ tells Excel which operation to calculate first.

order of operations

You can also summarize different groups using the Subtotal command and create an ________ for your worksheet.

outline

What is the best way to define which calculations will be performed first in Excel?

parentheses

A function is a ______ ___________ that performs calculations using specific values in a particular order

predefined formula

If you press 'Enter' in the cell =B2*C2 and then click and hold the fill handle, the formula will be copied to the selected cells with ______ _________, and the values will be calculated in each cell.

relative reference

Relative references are especially convenient whenever you need to ________ the same calculation across multiple rows or columns.

repeat

In order to work correctly, a function must be written a specific way. This is called the _______.

syntax

In filtering data, content that doesn't match the criteria is

temporarily hidden

Excel calculates formulas based on the following order of operations:

1. Operations enclosed in parentheses 2. Exponential calculations (3^2, for example) 3. Multiplication and division, whichever comes first 4. Addition and subtraction, whichever comes first

Excel can accommodate up to ___ levels.

8

Custom lists may take the form of

A New List (defined by the user) Days of the Week Months of the Year

Sorting may be done on

Cell Color Font Color Values Cell Icon

To create a custom sort, select __________ from the Order by drop down box.

Custom List

To efficiently filter out certain words from the spreadsheet, use __________ from the Text Filters.

Does Not Contain

Advanced text filters can be used to display more specific information, such as cells that contain a certain number of characters, but not data that excludes a specific word or number.

False

Excel will always tell you if your formula contains an error, so you need not check all of your formulas.

False

Sorting a range may affect other content on the worksheet.

False

When you have ordered a multi-level sort, you cannot change the level orders.

False

You may use the Subtotal command and then correctly sort your data.

False

To hide a group, click the _____ _______ button Image of the Hide detail button.

Hide Detail

Categories of functions include

Logical, Text, Financial, and Date & Time

This function determines the lowest cell value included in the argument.

Min

Listed advanced filtering tools include

Text, Search, and Date

Excel allows you to refer to any cell on any worksheet, which can be especially helpful if you want to reference a specific value from one worksheet to another.

True

There may be times when you do not want a cell reference to change when filling cells. Unlike relative references, ________ _________ do not change when copied or filled.

absolute references

In order to use functions correctly, you'll need to understand the different parts of a function and how to create _________ to calculate values and cell references.

arguments

The basic syntax for a function is the equals sign (=), the function name (SUM, for example), and one or more ________.

arguments

Relative references _______ when a formula is copied to another cell.

change

If you wish to remove—or clear— a filter from your worksheet so you'll be able to filter content in different ways, you should

clear filter from ["column name"]