Excel Training and Consultancy Solutions

MS Excel : Advanced Level

We'd strongly recommend that if you'd like to attend this Advanced course, you are already comfortable with using most of the topics already covered in our Level 1 (Beginner ) and Level 2 (Intermediate) courses.

The following table of topics outlines what this course will be covering :

Main topics specific areas A brief description of what we'll be covering
1. Dates and Times   An explanation of exactly how Excel treats dates
  and times
2. Functions =TODAY() Always return the current date
 =NOW() Always return the current date and time
 =DAY How to always extract the day number from a valid date
 =MONTH How to always extract the month number from a valid date
 =YEAR How to always extract the year number from a valid date
 =DATE How to build a date from its components (dd, mm and yy)
 =DATEVALUE How to co-erce excel into accepting a mis-typed date into a valid date
 =WEEKDAY How to always extract the weekday number from a valid
  date (ie 3 for a Wednesday)
 =SECOND How to always extract the seconds element from a time
 =MINUTE How to always extract the minutes element from a time
 =HOUR How to always extract the hours element from a time
 =TIME How to build a time from its components (hh, mm and ss)
 =TIMEVALUE How to co-erce excel into accepting a mis-typed time into a valid time
 =CELL We'll look at how to use this function to bring back a wealth of
  info regarding the cell and the workbook
 =MOD How to return the remainder after division
 =N How to bury useful notes within your formulae without affecting the answers
 =SUBTOTAL How to perform =SUM, =MIN, =MAX
  =AVERAGE etc but only on filtered, visible cells
 =SUBSTITUTE Always replace unwanted text with substitute text within a cell
 =REPLACE Replace x number of characters, starting at position y with
  substitute text within a cell
 =RANK Produce life-saving tables of data that are ranked by any of its amount fields.
  This function is ideal for leagues, top tens, performance stats etc
 nesting How to use functions together, one within another
3. Advanced filtering   Advanced filtering enables us to perform much more detailed filtering, enables
  us to filter with effectively our own formulae, enables
  us to send the results of our filtering to a different place in our workbook
  introduces us to the concept of an external criteria
  range, used by many experts who employ Excel's Database functions.
4. Sorting   How to sort our data so it appears in the order we want
5. Subtotalling   How to very quickly instruct Excel to subtotal our data at strategic points
6. Importing other files We all receive data we need to copy into our spreadsheets in different
  formats - here we learn how to do this very quickly
 from website Nowerdays, importing data directly from a website can also be very
  easy - again, we'll show you how
7. 3D formulae   How to write formulae that refer to a range of sheets, not just a single
  sheet, effectively giving you the power to produce 3d formulae
  across your sheets
8. Pictures and shapes   A quick look at pictures and shapes and how to colour, border and
  transparencise them effectively
9 .Goto special row differences Goto special allows us to select like sets of data very quickly should we
  need to colour them, or delete them or perform some maths on them.
 column differences We'll show you how to spot differences between two columns of data or
  two rows of data instantaneously
 data validation How to select cells that contain data validation instantaneously
 conditional format Ditto, but for cells that contain conditional formatting
 objects Ditto again, but for any objects - pictures, shapes etc
10. Hyperlinks automatic How to add hyperlinks that send you to other cells, other ranges and
  even other sheets.
 via formulae Taking the above a small step further, we'll show you how to make those
  hyperlinks dynamic, ie, they change to a new location based on the value
  that may change in one of your cells
11. Graphing   A quick look at graphing and how to make your graphs much more
  aesthetic than Excel's default
12. Pivot tables   Pivot tables enable us to analyse our data in a myriad of ways, saving us
  so much time and effort. Here, we'll look at the basics of pivot tables
  and what they can do for you
13. The Camera   If you struggle producing summarised reports or summary sheets that involve
  data with differing column widths, merged cells etc etc and you
  just cannot line it all up effectively, Excel's camera should make your life
  a lot easier. Here, we'll show you how to simply construct each
  piece of info seperately, then take a photo of each of them and arrange
  the photos on a summary sheet, nicely spaced out so your reports
  are perfect. As the photos retain real-time links, any time the underlying
  detail changes, so will your images

[Place a provisional booking]

Frequently asked questions       Contact us       Course documentations
Course locations       About us       Consultancy details