Excel Training and Consultancy Solutions

MS Excel : Intermediate Level

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

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. Toolbars customising We'll start straight off by customising some of our toolbars to
  save some time later
2. Functions =IF Give your workbook's formulae the power to make decisions
 =COUNT How to count numbers within a range of data
 =COUNTA How to count non-blank cells within a range of cells
 =COUNTBLANK How to count blank cells within a range
 =MAX Finding the maximum number in a range of data
 =MIN Finding the minimum number in a range of data
 =UPPER How to convert text to all uppercase (all capital letters)
 =LOWER How to convert text to all lowercase (no capital letters)
 =PROPER How to convert text to all propercase (capitalising each first letter)
 =AND How to incorporate more than 1 criteria in your =IF formulae
 =OR How to incorporate more than 1 criteria in your =IF formulae where any of them
  can be true
 =VLOOKUP Here, we will master the use of Excel's most-loved function, used when
  we exceed 7 nested =IF statements
 =HLOOKUP VLOOKUP's sister function, used "H"orizontally rather than "V"ertically
 =COUNTIF How to count based on a criteria. For example, count a range of fruit names
  only where the fruit is an orange
 =SUMIF How to sum based on a criteria. For example, sum a range of fruit prices
  only where the fruit is an orange
 =LEFT How to return x number of characters from the left from a cell's content
 =RIGHT How to return x number of characters from the right from a cell's content
 =MID How to return x number of characters from a starting point
  you specify from a cell's content
3. Error messages #DIV/0! We all get error messages in our spreadsheets. Here, we discuss what
 #VALUE! the differences are between the various error messages we receive,
 #REF! what they actually mean in so far as what we've done wrong somewhere
 #NAME? and how we go about recognising where to look, what to look
 #NUM! for to fix them and some forward planning in order that they don't
 #N/A keep cropping up in the future. Hopefully, by the end of this section,
 #NULL! the error message will be your friend
  and not an enemy.
4. Tools / options calculation How to manage how your spreadsheets, in particular the
  larger ones, take time to re-calculate
 default file location Instruct Excel to always point to your most-handy file folder
 background error checking Here's how we instruct Excel to notify us of any discrepancies
  it spots in our vast range of data; or how to switch off this option
  should it begin to be a hinderance
5. Freeze panes   Freeze specified rows and/or columns so that they're always
  visible whilst you scroll horizontally and/or vertically
6. Split panes   Split your worksheet into different panes so you can keep track
  of a number of different areas at the same time
7. New window same workbook Show different areas of your workbook and other workbooks
 different workbook in different windows - very useful for copying info without having
  to continually minimise and maximise between them
8. Grouping data   A much more efficient method to hiding columns or rows of
  data. Unhide them too, at the click of a button.
9. Custom lists setup Here, we show you how to setup your own custom lists for use when
  you want to autofill to save time in the future.
 importing Following on from the above - import your lists from existing
  data lists in your workbooks. This is ideal for people who regularly
  need to fill in staff lists, or department lists, or product lists etc
10. Protection cells How to protect certain cells, or even a whole sheet or even the entire
 sheet workbook so that nobody can overwrite your hard work accidentally
 workbook without your permission or the password you set
11. Named ranges overview Name your ranges. This is perhaps the beginning of the difference between
  an average Excel user and a more advanced Excel user.
 in formulae We'll show you how to make your formulae actually mean something
  tangible, logical and resemble the english language, rather than an
  unintelligible formula of anchored and relative cell references : for
  example : =VLOOKUP(A1,sales,3,0) rather than =VLOOKUP(A1,$F$3:$K$1200,3,0)
 for use in HTML anchoring For those of you who also know your HTML, here's how we can utilise
  our named ranges for anchoring within an HTML webpage
12. Conditional formatting absolute Format a cell or range of cells based on a condition, or conditions. We'll
 relative show you how to achieve this based on an absolute cell or a relative cell
 cell value is reference, and how to achieve this given standard criteria or your own
 formula is formulae. For example : how to colour a cell red and bold the font *IF* the
  cell value is over 5,000; or again, *IF* the cell value is the 4th
  largest value in a range of cells
13. Data validation lists on same sheet Data validation can drastically reduce the level of errors you'll get when
 lists on different sheet people use your spreadsheets. Here, we show you how to restrict what types
  of data are entered into specific cells or ranges of cells, including how
  to present the restricted data via dropdown selections for your users
14. Filtering   When working with large ranges of data, filtering allows us to only show
  rows that include certain information, which we specify. So,
  if you deal with car parts, for example, filtering allows you to only show
  rows of data where the part description begins with "SUSP"

[Place a provisional booking]

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