Excel Training and Consultancy Solutions



MS Excel : Expert Level

We'd strongly recommend that if you'd like to attend this Expert course, you are already comfortable with using most of the topics already covered in our Level 1 (Beginner), Level 2 (Intermediate) and Level 3 (Advanced) level 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. Boolean logic We'll show you basics of Boolean logic and how that fits into Excel.
 results Understanding how Boolean works, allows us to manipulate many of the
 co-oercion functions we use to our advantage and allows us to employ some very
  useful tricks via co-oercion of Boolean results. Understanding the
  Boolean handling of results as you build up your formulae also helps
  you understand how and why a lot of the more advanced functions
  (which we'll be covering here) actually work.
  An example would be that if A1 contained "7", then B1 should contain
  "7", otherwise B1 should contain "0". Mosts users would employ the
  =IF(A1=7,7,0) in B1, however we can use the shorter Boolean approach :
  =(A1=7)*7
     
2. Array formulae   It's estimated that 95% of most Excel users are unaware that array formulae
  exist. We'll teach you how to use them and how to recognise where they
  can be used more efficiently than a whole range of standard formulae.
  Array formulae allow us to apply a single formula to a range of cells
  where, normally, we'd be entering multiple formulae.
  An example would be that if A1:A10 contained "1" to "10" and we wanted
  the smallest number that was larger than 7, we could use the following
  single-celled array formula, rather than using separate =IF statements followed
  by a summarising =MIN function :
  {=MIN(IF(A1:A10>7,A1:A10,""))}
     
3. Functions =TEXT How to incorporate linked numbers and dates into your textual sentences so
  that they appear correctly formatted. This function is particularly useful
  when we're writing summaries that include linked results : here, the month and
  total figures are links from existing info on the sales sheet :
  "Sales for the month of May 2005 were 25,799.45" via the following formula :
  ="Sales for the month of "&TEXT('Sales'!B50,"MMM YYYY")&
  " were "&TEXT('Sales'!A1,"#,###.00")
 =CODE How to return the ASCii code of a specified character. This is useful if
  you're continually having to edit text that contains odd ascii characters.
  For example, your column of names comes imported with copyright characters
  peppered within it : =CODE(LEFT(A1,1)) will allow you to quickly find what
  ascii code the copyright sign is.
 =CHAR Similarly, =CHAR(ascii code number) will return the character. With the above
  example returning 169, we can then replace them all as follows :
  =SUBSTITUTE(A1,CHAR(169),"")
 =ROW How to return the row number and/or the column number of a cell and then how
 =COLUMN to use this to excellent effect in our array formulae and how to save
  time by using them in your standard formulae (ie =VLOOKUP) as you
  copy across or down
 =LEN How to find the length of the entry in a cell
 =SEARCH How to ascertain the position of specified text within a section of larger text.
  Once we combine =LEN and =SEARCH, we can begin to automate links, full
  filenames and filepaths very effectively.
 =ISNUMBER How to test if your cell contains a numeric entry, and thusly, how to prevent
  any #VALUE! errors. Used with =ISNUMBER and =SEARCH, we can search
  for parts of text and particular cell contents, following up with actions.
 =ISERROR How to intercept errors and perform alternative actions when they occur.
 =ISNA How to intercept that dreaded #N/A error
 =INDIRECT We'll teach you how to use =INDIRECT so that you can set up dynamic
  links : for example a link that looks at the "Sales" sheet if A1 contains
  "Sales" and looks at the "Profits" sheet if A1 changes to "Profits" etc etc.
  We'll also show how to set up multi-dependant data-validation dropdowns : for
  example, if your first dropdown choice is "England", the 2nd dropdown will
  only display English cities; and if "London" is then selected, the 3rd dropdown
  will only display London postcodes; similarly : Wales / Wrexham / Wrexham
  postcodes etc etc
 =MATCH How to find the position of an item within a list.
 =OFFSET Again, a different method of making your links dynamic, dependant upon
  values elsewhere in the sheet. Most commonly applied to simulate a VLOOKUP
  that looks left rather than right along a table of values once a match is found.
 =SUMPRODUCT An extension of =SUMIF and =COUNTIF where we are not restricted to just
  a single criteria, but as many as you need. For example: count the amount of
  entries in your database where the salesperson is "John" and his sales are in
  the "North" and the product is "Widgets" and the sale value is over 5,000
 =SQL.REQUEST For those of you who know your SQL, you'll love this function. Here, we'll show
  you how to bring back sets of information from your workbook or from a
  different workbook, open or closed. As the results are an array, we're not
  limited to single-celled or single-record results, but whole results, as you'd
  expect with SQL. So, for example, if you want a table of results (Teams,
  Stadiums, Referees, scores, crowd size and scorers) where Arsenal played
  Chelsea from a database of football matches, =SQL.REQUEST will achieve
  this easily
     
4. Named ranges dynamicism Named ranges are a valuable tool. However, when we make them dynamic, they
 dynamic print area come into their own and save us from ever having to worry about whether their
 dynamic graphs sizes have altered. Here, we'll show you how to make them dynamic, expanding
 dynamic formulae and shrinking as your data gets added to or reduced and how this can be applied
  to our print ranges, our graphs' data ranges and our everyday formulae so that
  we save hours not having to update our ranges
     
5. Named formulae   Create your own functions. For example, if you're a VAT accountant and you're
  continually having to convert gross amounts to net amounts, or net amounts to
  VAT amounts, we'll show you how to set up your own function, say, =GROSSTONET
  that converts your VAT-inclusive GROSS figures to VAT-exclusive NET figures, and
  say, =NETTOVAT that converts your VAT-exclusive NET figures
  to VAT amounts; all without the need for macros, programming or addins.
     
6. D-functions =DSUM An alternative to array formulae and =SUMPRODUCT if you find these
 =DCOUNT powerful functions slowing down your worksheets as they compute their
  answers. We'll look at 2 of the Dfunctions which illustrate how to get your
  results where you criteria are shown more user-friendlier onscreen rather than
  buried in a formula which other users may not be able to follow easily.
     
7. Automatic sorting   We'll show you how to automate your sorting of info, without having to use the
  sort button manually, by use of the =RANK function and =COUNTIF or =ROW
  functions to cope with "ties". If you regularly have to design leagues or
  present tables that are sorted by criteria, then this should help you a lot
     
8. Random numbers =RAND How to generate random sets of numbers for use in models, games etc. If you need
 =RANDBETWEEN to see how your profits vary given random sales each month between 5,000 and
  6,000 units; these random functions will help you achieve this
     
9. Volatile functions   Here, we'll discuss which of Excel's functions are volatile : ie, which
  re-calculate whenever you enter data. Knowing which functions are volatile and
  slow down your workbook, allows us to plan which functions and methods to
  use, or ignore, so we end up with a quicker workbook



[Place a provisional booking]






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