This advanced Microsoft Excel training course will teach you to use the advanced features of Excel in any version to their full extent. By the end of this course, you will be able to report on data in a list using PivotTables, edit and analyse complex data using advanced features such as Scenarios, Goal Seek and Solver, incorporate built-in functions into a worksheet, work with controls and record macros.
This course is instructor led, and involves lots of examples and exercises in a workshop environment. Led by highly experienced Microsoft Excel trainers with many years of experience our hands-on courses mean that you will leave with lots of practical Excel experience as well as useful tips and tricks. We train on version 2016 which is almost identical to 2010 and 2013, please make the trainer aware on the day if you use a another version and they can point out anything that may be slightly different.
Once you have completed this you will be ready to go on to our Excel Advanced Extra course or maybe Excel VBA
Course Walk-Through Video
It is recommended that students have experience with the fundamental use and features of Microsoft Excel including the ability to:
If you have not attended our Essential or Intermediate training courses we do send out an exercise once you have booked this course. This is just to double-check that you are familiar with all the topics covered previously.
Our courses run from 9.30 am to approximately 4.15pm. While you are with us we are focused on offering you a relaxed and productive learning environment. When you book a course with us you get the following:
The first session of this course reviews the concept of creating and using names for cells which is covered in our Intermediate Excel Course. In doing so it also quickly reviews formulas and selections.
The key topics covered include:
This section of the course introduces logical functions. This is a key part of more advanced Excel use. It focuses on the use of IF functions and how they can be combined to produce Nested IF functions and also combined with AND, NOT or OR in more complicated situations.
The key topics covered include:
When working with large amounts of data the ability to validate data using Excel is critical. This is especially true when working in combination with logical formulas which require that all data is in the appropriate format to work. This session covers both data validation, which is checking that the data in the cells meets certain criteria and also the ways that you can then highlight data that does not meet the criteria.
The key topics covered include:
Lookup functions are another very powerful tool to master when working with large amounts of data. They allow individual items in a list to be found and then the corresponding value from another column or row in the same data table. For example, if working with a series including dates and values of a variable, they offer the ability to find a certain data and have the formula return the value of that variable on the specific date being search for.
The key topics covered include:
Excel contains a number of features that allow you to very quickly summarise large amounts of data. This shorter session will show you how to very quickly use these features to create sub-totals and other summary statistics.
The key topics covered include:
PivotTables are one of Excel’s most powerful functions. They are extremely versatile and make it very easy to extract information from large tables of data without the use of formulas. They are also very quick to use as by moving, or pivoting, fields of data from one location to another using drag and drop functionality they allow you to look at the same data in a number of different ways.
The key topics covered include:
The session builds on the previous PivotTable session which introduced the concept. This takes the use of PivotTables one step further and leads delegates through summarising, grouping, sorting and names the data held in the PivotTable that they have created. This session demonstrates how powerful Excel can be when used correctly. Thousands of data records can be quickly and simply manipulated, analysed and presented using PivotTables in a matter of minutes.
The key topics covered include:
Excel’s Consolidate feature allows you to merge and summarize values from multiple workbooks. It’s a great tool for combining data when several users work with different instances of the same file but can also be used when the data to be consolidated is held in differernt formats.
The key topics covered include:
The scenario function lets you take a forecast in Excel and quickly see what would happen if you used a different input value. For example, you might create a spreadsheet to forecast the financial results of a business. What if you lowered the average selling price by 10%, or perhaps 15%. Scenarios can be saved, so that you can apply them with a quick click of the mouse.
The key topics covered include:
Solver takes the scenario analysis tools of Data tables and scenarios one step further. It allows you to set an output cell which you would like to either minimise or maximise. It then lets you set the constraints under which the spreadsheet will operate and the cells which can be varied. Excel will then calculate the maximum or minimum that can be achieved in the objective cell and report the values of the variable cells which achieve this outcome.
The key topics covered include:
Excel macros save time by automating tasks that you perform frequently. A macro is a precise set of actions, performed one after another that carry out the task. Also macros can be written using Excel VBA that is beyond the scope of this course.
The most efficient way to create a macro is to record one in Excel. Excel will store every action that you take one after another while the macro recorder is on and then repeat that series of actions each time that macro is run. However, as Excel records every action that you carry out when recording a macro you need to plan your macro carefully to ensure that each action is applicable everytime you run the macro.
The key topics covered include: