microsoft lesson 5

PivotChart

A chart based on the values in a PivotTable

PivotTable

A fluid report that can summarize and answer questions about a complex data set

Vlookup

A function that looks up a value from a vertical list

Filter

A restriction that excludes certain values from being computed or displayed

Quick Analysis

A shortcut to some of Excel's most common features.

Slicer

An easy-to-use filter that can be added to a PivotTable report

Sort

Arranging data based on its alphabetical or numerical order. Sorts can be in ascending or descending order.

Calculation Options

By default, Excel will automatically recalculate its formulas whenever you make a change. If you'd prefer to turn this functionality off, you can click Calculation Options > Manual. With this option selected, your formulas will only recalculate when you click Calculate Now.

Filters Area

Fields added to this area can be used to add custom filters to your PivotTable.

Columns Area

Fields added to this area will be used as column headings for your PivotTable.

Rows Area

Fields added to this area will show up as rows in your PivotTable. They will be grouped based on the order they appear in this area.

Values Area

Fields in this area will have a calculation performed on them, such as sum, average, or count.

Table

Formatting option that lets you easily sort, filter, and total your data

Conditional Formatting

Formatting that is applied to cells that meet a certain criteria

Outline

Grouping feature in Excel that makes it easy to collapse and expand data

Table Style Options

Let you customize a table's look, style, and features

Insert Function

The Insert Function dialog box allows you to search for and insert functions. This is especially useful when you're just learning Excel. If you know what you want to do but don't know which function to use, you can type your problem in normal language and Excel will suggest some functions that may be helpful.

Grand Total

The grand total shows the sum or average of all records in the outline.

Data Analysis

The process of evaluating data to identify useful information that can be used to support decision-making and problem-solving.

Collapse Outline

These outline symbols collapse (-) individual portions of an outline.

Outline levels

These outline symbols collapse or expand the entire outline to one of three levels. Level 1 shows only the grand total, level 2 shows the grand total and subtotals, and level 3 shows all data.

Expand Outline

These outline symbols or expand (+) individual portions of an outline.

Pivot Table Fields

This area shows all of the field names (column headings) from your data. To use these fields in your PivotTable, check their box or drag them to one of the four areas below

Trace

Trace Precedents will show all cells that are used in the calculation of the selected formula. Conversely, Trace Dependents shows all cells that have a calculation based on the selected cell. These buttons are especially useful when familiarizing yourself with workbooks that you did not create.

Group

Use this button to manually group the selected cells into a single outline level.

Ungroup

Use this button to manually remove the selected cells from an outline.

AutoSum

When you click the AutoSum button, Excel will guess which numbers you want to add and then insert the SUM function into the selected cell. Even though AutoSum is pretty good at guessing, you should always double-check the generated function before pressing Enter. The drop-down arrow on the AutoSum button opens a list of other functions that can be automatically inserted.

Show Formulas

button to switch between displaying and hiding formulas.

Pivot Table

in most spreadsheet software it is the name of the tool used to create summary tables.

Text

manipulate, format, and make calculations from text. The CLEAN function, for instance, removes all non-printable characters from a string of text.

Financial

mostly used to calculate financial information, such as interest rates, payments, and loan amortization.

Math & Trig

perform the same calculations as an advanced calculator. You can use the LOG function to find a logarithm; SIN, COS, and TAN to find trigonometric values; and even ROMAN to convert a number into Roman numerals.

Subtotal

used to create an outline for the data array that contains the selected cell. Subtotals show the sum or average of all data belonging to their own outline level.

Lookup & Reference

used to find individual values within a spreadsheet. When using a sheet with hundreds of rows, the VLOOKUP function allows you to quickly find the information you're looking for.

Logical

useful for when you want to find or analyze data based on some condition. The most common logical function is the IF function, which will perform an operation only if the desired condition is found.

Date & Time

useful when performing calculations that you'd normally do on a calendar. For example, the DAYS function returns the number of days between two specified dates.

Error Checking

works much like spell checking in Microsoft Word. It scans your current worksheet for common errors and displays recommendations for resolving the potential errors.

Defined Names

you can assign a name to the selected data. After a name has been defined, you can use it in your calculations. For example, if you defined a range as "MyData," you would enter =SUM(MyData) to add the cells in the range. Names can be given to individual cells, ranges, and tables. In addition to the Defined Names group on the Formula tab, names can be created by using the Name Box on the Home tab.