Excel Formulas, Functions, and Data Analysis Techniques for Students

Evaluate Formula

Examines each part of a formula step by step in the Formula Auditing group.

Remove Tracer Arrows

Clears tracer arrows from the worksheet.

Trace Dependents

Displays arrows to cells that depend on the active cell.

Trace Precedents

Shows arrows to the cells that a formula references; useful for finding errors like #NAME?.

Watch Window

Allows you to monitor values in selected cells across worksheets without switching tabs.

Gross Profit

Sales Revenue − Cost of Goods Sold.

Time Value of Money

Money today is worth more than the same amount in the future.

NPER Function

Calculates number of payments for a loan or investment goal.

RATE Function

Returns the interest rate per period; syntax: RATE(Nper, Pmt, Pv, [Fv=0], [Type=0], [Guess=0.1]).

RATE Type Argument

0 for end-of-period payments; 1 for beginning-of-period.

FV (Future Value) Function

Calculates the future value of a loan or investment.

PV (Present Value) Function

Calculates current value when payments vary by year.

IRR (Internal Rate of Return)

Calculates investment profitability; compare with NPV for best choice.

CUMIPMT Function

Calculates total interest paid over a loan term.

SLN (Straight-Line Depreciation)

Depreciates an asset equally each year.

DB (Declining Balance)

Applies accelerated depreciation each year.

DDB Function

Calculates double-declining balance depreciation; use for specific years like Year 4.

Running Totals

Adds cumulative totals using the Totals tab in Quick Analysis.

Currency Format

Displays numeric results formatted as currency.

Negative Values Format

Shown in red and enclosed in parentheses.

Conditional Formatting

Highlights data with Data Bars, Color Scales, or Icon Sets to show extremes.

Manual Grouping

Combines items into a new field manually.

Group by Capacity

Groups ranges like 1-20, 21-40, etc.

PivotTable Summary

Use PivotTable function to summarize data.

Tabular Layout

Displays fields in separate columns with subtotals at the bottom.

Compact Layout

Nests fields in one column for concise display.

Outline Layout

Fields in separate columns with subtotals at the top.

Sort Methods

Includes ascending, descending, and manual sorting.

Custom Sort List

Allows non-alphabetical sorting, e.g., Sunday to Monday.

Report Filter Search

Narrows long lists in a PivotTable filter.

Ungroup/Regroup

Adjusts how grouped data is displayed.

% Of Calculation

Displays each value as a percentage of another cell.

Calculated Fields

Allows creation of multiple calculated fields within a PivotTable.

DCOUNT

Counts cells matching specified criteria.

DCOUNTA

Counts non-blank cells matching criteria.

DSUM

Adds values in a database that meet given criteria.