Microsoft Excel specialists

Excel training courses

See dates of all courses
Contact us

  








  WHAT YOU GET

Course manual
& Excel files

20  minutes’
hotline time

Excel expertise
28 years’
specialisation

Major topic courses

Formulas & functions

     Duration: 1 day  
     Format: Interactive seminar with quizzes  
   Arrival and refreshments: 8:45am  
   Start 9:00am  
   Finish: 4:30pm  

Discover the Excel formulas and functions (and their combinations) that you need to solve common problems quickly. Pick up tips on better, faster, clearer and more maintainable formula construction. Find file-links, as well as all cells involved in circular reference warnings, quickly. Avoid formula changes when you insert or delete.

  YOUR POWER-PACKED AGENDA

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.


Prerequisites

Accountants (and many non-accountants, too) who have been using spreadsheets for some time and are quite familiar with the basics of formula construction, formatting and printing.


  LEARN IN A POSITIVE ENVIRONMENT
 
  • Be assured the advice you get is well-proven and utilised.
  • Feel free to ask questions.
  • Get time away from the office and constant interruptions.
  • Enjoy a friendly environment where you don’t feel you’re holding others up.
  • Instantly apply what you’ve learned on return to work.

Presenters

AbleOwl has a number of different presenters, all of whom are Excel specialists.

See dates of all courses  

  COURSE CANCELLATION POLICY
 
A course booking is considered final when received in writing (online, email, fax or post) by AbleOwl, and can be cancelled up to ten working days before the course. After that, payment is expected in full and no refund will be given. All cancellations must be notified in writing, i.e. post, fax or email. Another delegate may be substituted at any time.