This one-day course builds on the skills and concepts
taught in Excel: Intermediate.
Students will
work with advanced formulas, as well as lookup functions
such as VLOOKUP, MATCH, and INDEX. In addition, students
will learn about data validation and database functions
such as DSUM. They will learn how to work with
PivotTables and PivotCharts, how to import and export
data, and how to query external databases. Finally,
students will learn about the analytical features of
Excel (such as Goal Seek and Solver), running and
recording macros, and sharing Excel data via the Web.
Delivered in
Dundee,
Aberdeen,
Edinburgh,
Stirling,
Glasgow,
Fife,
London,
Aberdeenshire,
Angus,
Perth
and most UK locations.
After completing this course, students will know how to:
Advanced functions and formulas
Logical functions
Conditional functions
Financial functions
Text functions
Date and time
functions
Array formulas
Calculation options
Lookups and data tables
Using lookup
functions
Using MATCH and INDEX
Creating data tables
Advanced data management
Validating
cell entries
Advanced filtering
Conditional formatting functions
Advanced charting
Chart formatting
options
Combination charts
Graphical objects
PivotTables and PivotCharts
Working with
PivotTables
Modifying PivotTable data
Formatting PivotTables
Using
PivotCharts
Introduction to Power Pivot - the
course can be tailored to include this topic
Exporting and importing data
Exporting
and importing text files
Getting external
data
Get and Transform
Analytical tools
Goal Seek
Scenarios
Instant data analysis
Solver
Macros and Visual Basic
Running and
recording a macro
Working with VBA code
Topics covered will vary slightly depending upon the version you are using, e.g. 2010, 2013, 2016, 2019 or 365.
Before taking this course, you
should be familiar with personal computers and the use
of a keyboard and a mouse. Furthermore, this course
assumes that you've completed the Excel: Foundation
course or have equivalent experience.
Beforetaking this course, you should be comfortable using a personal computer and Microsoft Windows. You should have some experience using Microsoft Excel. You will get the most out of this course if your goal is to become proficient in such tasks as consolidating data, sorting and filtering lists, using special formatting options, using templates, using error tracing features, protecting worksheets, and linking worksheets and workbooks.
Courseware with supporting exercise files
will be provided and delegates will find this valuable for further study, revision and reference.
For comprehensive training,
you should complete all of the following courses: Excel Foundation, Excel Intermediate, Excel Advanced and Excel Power User.
Topics covered will vary slightly depending upon the version of Excel you are using, e.g 2007, 2010, 2013, 2016, 2019 or 365.
FILTER function
RANDARRAY function
SEQUENCE function
SINGLE function
SORT function
SORTBY function
UNIQUE function
Also new in Excel 365: Create a diagram in Excel 365 with the Visio Data Visualizer add-in
Excel 2016 for Windows has all the functionality and features you're used to with some added features and enhancements and the best new features from Office 2016. Here are some of the top new and improved features for Excel 2016:
Six new chart types
Get and transform (Query)
New Functions
One click forecasting
3D Maps
PivotTable enhancements
Publish and share your analysis with Power BI
Quick Shape Formatting
Insert pictures with the correct orientation
Do things quickly with Tell Me
Insights into what you're working on
Ink Equations
Simpler sharing
Improved version history
New themes
Data Loss Protection (DLP) in Excel
New Financial Templates
Take an Excel 2016 course to discover more!
Contact us for further information, to check available dates or to book a course.