Excel Intermediate – Formulas, Functions and Lists

Formula Calculations

Formulas are used to perform calculations in Excel. They always begin with '=' and can include cell references, numbers, and functions. For example, typing '=A1+B1' will sum the values in A1 and B1. The formula bar displays the actual formula, while the cell shows the result. This understanding is essential for building dynamic spreadsheets.

Relative and Absolute References

Excel uses relative references by default, which adjust when formulas are copied. Absolute references (e.g., $A$1) do not change when copied and are useful for constants like tax rates. Mixed references (e.g., A$1 or $A1) combine both behaviors. Understanding how to apply each type prevents calculation errors.

Precedents and Dependents

Precedents are cells referred to by a formula, while dependents are cells that reference others. Excel’s Trace Precedents and Trace Dependents tools help visualize these relationships, useful when troubleshooting complex spreadsheets.

Formula Auditing

This involves checking formulas for errors and verifying their logic. Tools like 'Evaluate Formula' allow step-by-step debugging. Error indicators and messages can also point out issues like #DIV/0 or #REF.

Text to Columns

This feature splits data in one column into multiple columns. Useful for separating names, addresses, or dates formatted as text. You can use delimiters (like commas or spaces) or fixed widths to divide the data.

What-If Analysis

This Excel tool allows you to explore different scenarios. Goal Seek is used to find the input needed to achieve a specific result in a formula. For example, finding the interest rate needed to reach a desired monthly repayment.

Functions

Functions are built-in formulas like SUM(), AVERAGE(), or VLOOKUP(). Understanding function arguments and syntax is key. Named ranges can make functions more readable and easier to maintain.

Text Functions

Text functions like LEN, LEFT, RIGHT, MID, FIND, and TRIM help clean and extract parts of text. INDIRECT lets you refer to a cell using a text string, which is helpful for dynamic referencing.

List Functions and Features

Well-designed lists support filtering, sorting, and referencing. Auto-complete speeds up data entry. Data validation ensures accuracy. Gaps can be filled with Flash Fill or formulas. Structuring lists properly makes data more usable.

Go To Special, Freeze Panes, Named Areas

Go To Special selects specific types of cells (e.g., blanks, constants). Freeze Panes keeps row/column headers visible while scrolling. Naming areas simplifies navigation and function writing