Our advanced Microsoft Excel training course will teach you how to use the advanced features of Excel in the current version to their full extent. Excel is truly broad but with our excercises, you will be able to know what it takes to be an advanced user of Microsoft Excel. You will be able to report on data in a list using PivotTables, edit and analyse complex data using advanced features such as 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.
|Session One||Introduction to Advanced Excel|
|Session TWO||Data Manipulation in Excel
· How Excel handles different data types
· Data consistency, starting with the end in view
· Building Datasheets that can easily scale
o Cascaded sorting
o Sorting across rows (left to right sorting, not the usual up to down sorting)
o Sorting and Conditional Formatting to identify trends.
· Data cleaning
o Removing duplicates
o Data Validation
o Conditional Formatti
|Session THREE||Data Manipulation in Excel
· Data formatting
o Using Tables (and when to convert to tables)
o Formatting for printing
o Formatting for email
o Data Review and formatting for 3rd party use
· Named Ranges
a. Chart types
· Line chart and when to use it
· Column chart and when to use it
· Bar chart and when to use it
· Pie chart and it’s dangers
b. Combining charts; when and how.
|Session SIX||Pivot Table, Pivot Chart and PowerPivot
· Default Pivot Table
· Tabular Pivot Table
· Pivot Table Filtering
· Making a very dynamic regular table from Pivot Table
· Calculations and Formula use with Pivot Table
· Advanced Pivot Table tricks
· Pivot Chart and its limitations
· Dynamic Pivot Charts
|Session SEVEN||Business Data Analysis
· Linking sheets
· Duplicating sheets (better than copy and paste)
· Inserting sheets, labeling and coloring the professional way
· Freezing Panes and splitting windows
· Conditional formatting
o To identify patterns
o Using formulae
o To make extremely intelligent reports
· Lookup functions
o Overcoming the limitations of Vlookup and Hlookup using index and match functions
· IF, IFERROR, AND, OR, ISBLANK, and others in the same family
· TEXT manipulative functions
· SUMIF, COUNTIF, AVERAGEIF, COUNTIFS, SUMIFS, AVERAGEIFS
· MATCH and INDEX
· Goal Seek, Scenario Manager and Solver
· Most useful Excel keyboard shortcuts
|Session EIGHT||Executive Dashboards and Reporting
Executive Dashboards· Executive Dashboards· Dynamic Reports· Determining the KPIs and tracking them· Strategic Insights & AnalysisData VisualizationE-mails and Excel reports
|Session NINE||Excel to PowerPoint and Word
· Linking PowerPoint/Word Charts to Excel
· Embedding Excel sheets in PowerPoint/Word
· Making a Powerful PowerPoint Presentation
· Recording Excel macros
Basic experience on Windows Operating System. It is recommended that students have experience with the fundamental use and features of Microsoft Excel including the ability to:
- Create formulae and basic functions
- Use Absolute Cell references
- Edit data using formatting and layout techniques including Charting
Work with database tools, including database filtering and functions
Who Should Attend?
Recommended for those who require knowledge on Advanced Microsoft Excel 2016.
What is included in this course?
Training materials and refreshment.
What is the Duration of this course?
The course is for 4 days
How to Apply?
Click here to register. A confirmation Email will be sent to you. You are not fully registered unless payment has being made.