About the Course

This course would also suit anyone looking to extend their knowledge of Excel to understand some of the more advanced features and how they can be used to work together. At the end of this course you will understand what makes a dashboard.
You will learn how to build some of the most useful components when constructing your own dashboard reports. During the course you will build three complete dashboard projects to give you inspiration for your own solutions.
Excel dashboards are a powerful way to leverage Excel functionality, build and manage better presentations and improve your Excel and data visualization skills. In this course we will show you how you can turn Excel into your own, personal Business Intelligence tool and create Interactive Charts and awesome Dashboards in Microsoft Excel.

 

Programme Requirements

This is a Research-Based Training Course: – the course curriculum has been developed and designed from research with actual industry practitioners and will in just 2 days give you all the tools and techniques needed to design models to suit a wide range of purposes.
The course is practical and focused: – each of the modules presented will include step-by-step explanations and hands-on examples. A comprehensive Course Reference Manual will be provided packed with ideas-techniques, graphics, flow charts and advanced Excel tools:  -the manual contains key steps for the exercises.
Laptop computer. This workshop requires the use of a laptop which should have either Windows 8 or later installed as well as a full installation of Excel 2016 or later

 

Who Should Attend

All staff members. All professional persons using Excel spreadsheets. Professions that have attended include Pas, Secretaries, accountants, actuaries, auditors, business analysts, chartered accountants, civil engineers, construction, consulting, corporate finance, engineers, farming, finance, hr function, landlords, lawyers, quantity surveyors, research analysts, retail, scientists and engineers, Directors of all divisions and all employees interested in learning Microsoft Excel Skills.

 

Course Outline

Pivot Tables

• How to (quickly) convert non-compliant data into
• Pivot friendly data
•  Pivot Tables – Step by Step
• Manipulating the Data Portion of a Pivot Table
• Manipulating the Rows of a Pivot Table
• Manipulating the columns in a pivot table
  Combining rows and columns to create meaningful Pivots
• Report Filter
  Grouping Data in the rows and columns
• Manual
• Dates
  Numbers
• Simple Pivot Charting
• Creating Calculated Fields within a pivot table
• Creating Calculated Item within a pivot table
• GETPIVOTDATA function
• Multiple Consolidation ranges in Pivot Tables
• Conditional Formatting on Pivot Tables (Excel 2007 and higher)
• Linking to an external database via Pivot Tables

 

ADVANCED EXCEL

• Advanced Excel Techniques for use in modelling
• How errors happen in models and how to avoid them
• Financial Model and spreadsheet design principals
  Essential Excel Knowledge
• Importing data into a model
• Data Cleanup using the Data Auto-filter and the Advanced Filter
• GOTO Special tool for working with cells
• Using the text-to-column tool
• Common pitfalls/problems at this stage

 

WORKING WITH DATA

• Working with imported text data
• CLEAN, CONCATENATE, DOLLAR, EXACT, FIND/SEARCH, LEFT/RIGHT/MID, LEN, LOWER/UPPER/ PROPER, REPLACE, T, TEXT, TRIM, VALUE
• Handling Dates in your models
• EOMONTH, DATE, DATEVALUE, DAY, EDATE, MONTH, NETWORKING DAYS, WEEKDAY, WORKDAY, YEAR
• Manipulating the data
• SUMIF, COUNTIF, SUMIFS, COUNTIFS, VLOOKUP
• Ranking Results
• Error checks and spreadsheet integrity

 

COMPLETION OF ADVANCED EXCEL

• Analyzing Results
• Data Filter
• Speech to Text
  Database Functions
• Reporting results
• Conditional Formatting
• Cell formatting
• Data Protection
• Getting the Data right for Graphing
• Understanding Excel graphs and incorporating them into your model
• Adding another series to a graph
• Creating a secondary axis in a chart
• Changing default colouring to match corporate raph
  Conditional Formatting Graphs
• Using a custom picture for your graphs
• Creating flexible graphs

DATA VISUALISATION

• Create a chart template for consistent use
• Different Chart Types
• Column Charts
• Bar Charts
• Pie Charts
• Area Charts
• Radar
• Waterfall graphs
• Combination Charts
• Trendlines in models
• Goal seeking straight off the graph
• Linking your Word documents directly to the Excel

 

What is a dashboard?

• Common features of a dashboard
• Why use Excel?
• Dashboard Do’s and Don’ts
• Data Layout Creating Dynamic Dashboards
• Merging and Consolidating Data Using
• Shapes to make Charts more attractive
• Using Alerts to draw attention to dashboards

 

PivotTables and Pivot Charts

• Creating PivotTables
• Formatting a PivotTable
• Refreshing a PivotTable
• Grouping fields
• Pivot Charts
• Slicers and Timelines

 

Useful functions

• Nested IFs
• COUNTIFS & SUMIFS
• EDATE
• INDEX & MATCH
• OFFSET
• CHOOSE

 

Conditional Formatting

• Formatting values
• Colour Scales to show heatmaps
• Icon Sets to show at a glance performance

 

Form Controls

• Understanding the different controls
• Using them on a dashboard

 

Workbooks

• Creating charts
• Formatting charts
• Secondary Axes

Summarising / calculating / organising

• Arranging the data that’s conducive for a high-level view
• Dynamic named ranges
• Expandable calculations

 

Working with your Data

• Understanding Data Quality issues
• Gathering data from different sources
• Linking Data
• Merging and Consolidating Data
• Excel Hint\ Tips when working with data

 

Working with Charts

• Creating charts
• Formatting charts
• Secondary Axes
• Combination charts
• Creating chart templates

 

Working with Sparklines

• Creating & modifying Sparklines
• Customizing Sparklines

 

Design & Layout

• Gridlines & outlines
• Lining up Excel objects
• Theming a dashboard

 

Sample Dashboard Projects

• Worldwide Salary Index
• Sales Performance Analysis
• Helpdesk Efficiency

 

Conditional Formatting

• Create dashboard heat maps
• Highlight top and bottom products/ people etc
• Highlight missed budgets and forecasts