|
Make formulas easier to follow
- Shorten formulas by knowing the order of precedence.
- Use names to build formulas more quickly and make them easier to follow.
- Modify the layout for ease of understanding.
Understand the calculation options
- Know the pros and cons of Precision as Displayed.
- Beware of traps with Manual and Iteration options.
Common formulas problems
- Find the sources of circular references.
- Discover solutions to the problem of formulas that refuse to calculate.
- Be aware of the problems caused by decimal to binary conversions and their solutions.
- Avoid problems when inserting rows, columns or sheets.
- Know why some formulas are slow to calculate.
- Discover volatile functions.
- Hide very long formulas.
- Beware of the Extend formulas feature.
Array formulas
- Discover array formulas and how to create them.
- Hear the pros and cons.
- Sum absolute values.
- Sum rounded values.
- Find max of numbers entered as text.
- Check whether data is sorted.
File-link formulas
- Know the best way to create file-link formulas.
- Be aware of the many traps and the solutions.
- Find the source of unwanted "update links" messages.
- Remove file-links.
Maintainable formulas
- Make formulas easy to track.
- Build formulas that don't need changing every month.
- Build self-maintaining formulas.
- Reduce errors through properly constructed cross-checks.
- Build crosschecks that can't be missed.
- Include comments in formulas.
Functions and formulas
- Sum positives.
- Sum but ignore errors.
- Prevent duplicate entries.
- Sum alternate rows or columns.
- Sum the largest five entries.
- Round to nearest 5, 20, or any number.
- Round up or down.
- Avoid divide-by-zero errors.
- Learn how to build complex IF formulas.
- Use MIN and MAX as a shorter alternative to IF.
- Return a particular month's figure from a range.
- Calculate year-to-date for the specified month.
- Prevent formulas being corrupted by a move.
- Sum the last several months.
- Create self-extending ranges for charts, PivotTables and formulas.
- Use lookups to create self-maintaining systems.
- Return item from left of search column.
- Return name of biggest item.
- Use faster alternatives to VLOOKUP and SUMIF.
- Discover alternatives to confusing multiple-nested IFs.
- Sum only visible rows.
- Create any criteria you require for use with the "D" functions.
- Analyse and cross-tabulate database data.
- Count the entries in each category.
- Display path filename, day, date, time.
- Display date and time across multiple columns.
- Calculate with dates and times.
- Generate a series of month-end dates.
- Calculate working days between two dates.
- List working days in month.
- Discover text formulas and their uses.
- Join numbers, dates and items of text into a single paragraph.
- Convert numeric entries to text and vice versa.
- Split full names into first name and last name.
- Make subtotal numbers bold.
- Discover Conditional Formatting.
- Convert text case to upper, lower or proper.
Learn new excel 2007/2010 functions
- Such as SUMIF, COUNTIFS, AVERAGEIFS, IFERROR and AGGREGATE.
Functions covered include: SUMPRODUCT, SUBTOTAL, SUMIF, SUMIFS, COUNTIF, COUNTIFS,
IF, AVERAGEIFS, LARGE, ROUND, INT, INDEX, VLOOKUP, TRANSPOSE, MATCH, DSUM, MAX,
MIN, CELL, COUNT, NOW, CHOOSE, ABS, &, T, LEFT, MID, RIGHT, MOD, OFFSET, FIND,
SEARCH, TRIM, UPPER, PROPER, VALUE, TEXT, DATE, DATEVALUE, TIME, TIMEVALUE, NETWORKDAYS,
WORKDAY, ISERROR, IFERROR, ISNA, AGGREGATE, FREQUENCY... and more.
|