Excel Advanced Extra – 1 Day

This Excel Advanced extra course is designed for delegates who have attended our advanced course and want to take their knowledge and understanding of Excel functions to an expert level. This course is really designed for people carrying out detailed, complex data analysis and manipulation tasks who would benefit from understanding all of the functionality that Excel can offer them.

This is a hands-on course in which we get delegates working on data to practice and reinforce the concepts as they are explained. This way we make sure that you have plenty of practice in using these advanced techniques.

It is recommended that students have already attended our Advanced course or have equivalent advanced knowledge of Microsoft Excel. This includes the ability to:

  • Use IF and Nested IF functions
  • Use LOOKUP functions and PivotTables
  • Work with database tools, including database filtering and functions

The aim is that delegates leave this course with a very thorough understanding of using Excel’s advanced functions. They will understand and be very comfortable, importing data to and exporting data from Excel. In addition they will be very comfortable working with text strings, dates and times and financial information as well as Excel’s statistical and mathematical functions.

Our courses run from 9.30 am to approximately 4.30pm. While you are with us we are focused on offering you a relaxed and productive learning environment. When you book a course with use you get the following:
  • A full colour advanced Excel training manual.
  • The exercises that you have worked on during your course to allow you to practice afterwards.
  • USB stick to take the exercises away (where relevant).
  • Freshly prepared lunch in our onsite cafeteria.
  • Refreshments though out the day.
  • Training in fully air conditioned facilities from an experienced Excel instructor.

Text is straight forward to work with in Excel, however by learning to use the Text functions in Excel you will learn how to manipulate and manage strings of text data in your spreadsheets.

The key topics covered are:

  • Understanding Text Functions
  • Using the PROPER Function
  • Using UPPER and LOWER Functions
  • Using the CONCATENATE Function
  • Using the LEFT and RIGHT Functions
  • Using the MID Function
  • Using the LEN Function
  • Using the SUBSTITUTE Function
  • Using the T Function
  • Using the TEXT Function
  • Using the VALUE Function

Excel’s information functions give you information about the data contained with in a cell or or a range of cells.  This can be extremely useful when working with lists of mixed data.

The key topics covered are:

  • Understanding Information Functions
  • Using the CELL Function
  • Using the ISBLANK Function
  • Using the ISERR Function
  • Using the ISODD and ISEVEN Functions
  • Using the ISNUMBER and ISTEXTFunctions
  • Using the TYPE Function

This session will show you the maths functions that Excel contains. These can be extremely useful when manipulating numbers. For example, the ability to round a number that is a currency figure is very important to keep the figures sensible in a financial spreadsheet.

The key topics covered are:

  • Understanding Maths Functions
  • Using SQRT
  • Using ABS
  • Using INT
  • Using TRUNC
  • Using ROUND
  • Using ROUNDDOWN and ROUNDUP
  • Using ODD and EVEN
  • Using CEILING
  • Using MROUND
  • Using PRODUCT
  • USING SUMPRODUCT

This session introduces the statistical functions that Excel can easily calculate. Excel is not designed as a specialist statistical package but does offer some very good functionality for relatively straight forward statistical analysis.

The key topics covered include:

  • Understanding Statistical Functions
  • Status Bar Statistics
  • Using COUNTBLANK
  • Using MODE
  • Using MEDIAN
  • Using LARGE and SMALL
  • Using STDV
  • Using VAR

Excel is very widely used to build financial forecasts for businesses. This session runs through the financial functions that Excel offers to automate analysis of financial data.

The key topics covered include:

  • Understanding Financial Functions
  • Using PMT
  • Using FV
  • Using NPV
  • Using RATE
  • Using EFFECT
  • Using NOMINAL

This session focuses on how Excel handles and manipulates dates. It shows you how to use Excel to calculate future days and dates and also how to work with calendar functions.

This key topics covered include:

  • Understanding Date and Time Functions
  • Using NOW
  • Using HOUR and MINUTE
  • Using TODAY
  • Calculate Future Dates
  • Using DATE
  • Using Calendaring Functions
  • Using WEEKDAY
  • Using WEEKNUM
  • Using WORKDAY
  • Using EOMONTH

Given that Excel is the standard spreadsheet package used in offices worldwide it is very often used for data management and manipulation. Where data is held in another software program and needs manipulating and presenting it is very likely that it will be imported into Excel before being worked on. This session leads you through the tools that Excel contains to help make data imports as quick and simple as possible.

The key topics covered include:

  • Understanding Data Importing
  • Importing from an Earlier Version
  • Understanding Text File Formats
  • Importing Tab Delimited Text
  • Importing Comma Delimited Text
  • Importing Space Delimited Text
  • Importing Access Data
  • Working with Connected Data
  • Unlinking Connections
  • Exporting to Microsoft Word
  • Exporting Data as Text

This session covers some of the more advanced techniques used when working with macros. For example how to test your macro and also how to create a button which when clicked will trigger your macro to run.

The key topics covered include:

  • Preparing Data for an Application
  • Recording a Summation Macro
  • Recording Consolidations
  • Recording Divisional Macros
  • Testing Macros
  • Creating Objects to Run Macros
  • Assigning a Macro to an object

Related courses