Excel Analysis and Dashboards Training
Learn to use Excel tools to build effective actionable dashboards.
Learn to use Excel tools to build effective actionable dashboards.
4.68
All courses available in-class or remotely.
To attend remotely, select "Remote Online" as your location on book now.
Excel’s ability to integrate and visualise data is the focus of this day long course, using the latest features in Excel. Work through multiple exercises to learn to master the tools of data modelling, analysis and building visuals for effective Dashboards.
Participants will create a data model with a range of data sources using Power Pivot and use Get and Transform to connect and manipulate a range of data sources including cloud databases, websites and Facebook. Learn how to fully utilise Excel to create interactive Dashboards, Charting tools and Visualisation techniques.
We complete the course by working through a Case Study pulling all the aspects we teach on the day together.
Business Case Study – We start with raw sales data and we model the data using PowerPivot, creating relationships and the necessary calculations to build our interactive Dashboard for assessing Sales performance and profitability across various cities. Read our full course outline here.
By the end of this course, participants will be able to:
This course is suitable for users with advanced Excel skills who want to explore Excel’s full capabilities. Attendance in other courses is not required. PC users must have the Power Pivot add-in enabled; this course is not recommended for Mac users.
Financial ModellingLearn to build financial models in line with best practice. Work through case studies, led by our instructors.
Excel Macros/VBA 2 DayLearn to build solutions to automate tasks. Record macros, write procedures, work with functions and more.
Excel Specialist Digital Credential
Data Modelling
Starting a dataset in Excel
Multiple Tables
Data Modelling
Get & Transform
Understanding Get & Transform
Understanding the Navigator Pane
Creating a New Query From a File
Creating a New Query From the Web
Understanding the Query Editor
Displaying the Query Editor
Managing Data Columns
Reducing Data Row
Adding a Data Column
Transforming Data
Editing Query Steps
Merging Queries
Working With Merged Queries
Saving and Sharing Queries
The Advanced Editor
Power Pivot
Understanding Relational Data
Common Sense Data Modelling
Enabling Power Pivot
Connecting to a Data Source
Working with The Data Model
Working with Data Model Fields
Changing A Power Pivot View
Creating A Data Model PivotTable
Using Related Power Pivot Fields
Creating A Calculated Field
Creating A Concatenated Field
Formatting Data Model Fields
Using Calculated Fields
Creating A Timeline
Adding Slicers
Great Functions for Analysis
Understanding Data Lookup Functions
Using CHOOSE
Using VLOOKUP
Using VLOOKUP For Exact Matches
Using HLOOKUP
Using INDEX
Using SUMIF
Using SUMIFS
Using SUMPRODUCT
Data Validation
Validation Criteria
Input Messages & Error Messages
Drop-Down Lists
Formulas
Customised Validation Criteria
Creating A Number Range Validation
Testing A Validation
Creating an Error Message
Using Formulas as Validation Criteria
Circling Invalid Data
Removing Invalid Circles
Conditional Formatting & Sparklines
Using Conditional Formatting with a Dashboard
Top 10 & Custom Formatting
Data Bars
Show data bars outside the data cell
Colour Scales
Icon Sets
Creating Rules Based Icon Set
Removing unnecessary icons
Using Symbols in Reporting
Using the Camera Tool
What is a Sparkline?
Types of Sparklines
Showing Sparklines only
Specifying a Date Axis
Hidden Data and Sparklines
Sparklines and Targets
PivotTables & PivotCharts
Structure of Pivot Tables
Using Compound Fields
Counting in A PivotTable
Formatting PivotTable Values
Working with Grand Total & Subtotals
Finding the Percentage of Total
Finding the Difference From
Grouping in PivotTable Reports
Creating Running Totals
Creating Calculated Fields
Providing Custom Names
Creating Calculated Items
PivotTable Options
Sorting in a PivotTable
Top and Bottom Views
Date Grouping Options
Hiding or Showing Data Items
Conditional Formatting and Sparklines in Pivot Tables
Pivot Caches and File Size
Inserting a PivotChart
Defining the PivotChart Structure
Changing the PivotChart Type
Using the PivotChart Filter Field Buttons
Moving Pivot Charts to Chart Sheets
Slicers in Reports
What are Slicers?
Creating Slicers
Using a Slicer on Multiple Pivot Tables
Renaming Pivot Tables
Timeline Slicer
Trending Charts
Why do we use Trending Charts?
Appropriate Chart Types for Trending
Vertical or Y-Axis Scales
Chart Titles linking to a Cell
Comparative Trending
Labelling
Using a Secondary Axis
Formatting Key Data Points
How to display Actuals and Forecasts
Averages and Data Smoothing
Other Report Charts
Top and Bottom Charts
How to show Top or Bottom in Data Labels
Waterfall Charts
Histograms
Creating Histograms using Formulas
Creating Histograms using Pivot Tables
Creating Histograms using Excel’s Statistical Charts
Charting Performance Against a Target
Performance against Targets
Creating Thermometer Chart
Bullet Graph
Defining Dashboards
Purpose of a Dashboard
Working out what is needed
What are the data sources
Will the audience need further data to drill-down to?
How often will/can the data refresh?
Does it need to be maintained?
How easy will it be to maintain?
Dashboard Design Principles
Thirteen common mistakes in dashboard design
Making an Interface
Using Macros with Dashboards
Recording a Macro
Navigation using Macros
Macros to Change Chart types
Macros and Pivots
Pulling it all together – Case Study
We start with raw sales data for a Comedy Roadshow. We model a the data using PowerPivot, creating relationships and the necessary calculations to build our interactive Dashboard for assessing Sales performance and profitability across various cities.
Which Excel Course is right for me?
Everyone uses Excel differently but as previous Excel Consultants we are aware of the core concepts relevant to all workplaces. Our Excel Specialist courses focus on different aspects of Excel and how its is used across different roles. The Financial Modelling course introduces building models in excel in line with best practice. Analysis and Dashboards concentrates on building more advanced visuals and introducing Power Query. For those seeking to automate tasks in Excel, our VBA course will be the best fit.
Which courses are available if I am working from home?
Currently all of our Public Courses are available to be delivered remotely. Book any course as normal and you will receive login details and instructions the evening before your course.
What is Remote Training?
Remote training at Nexacu, means our team of experienced trainers will deliver your training virtually. Students can access our usual classroom training courses via video conferencing, ask questions, participate in discussion and share their screen with the trainer if they need help at any point in the course. Students have the same level of participation and access to the trainer as they would in classroom training sessions.
How may students are typically in a Excel Specialist Training Course?
While this varies from session to session, we typically have 4 - 6 students in our Excel Specialist classes. We cap our classes at 10 students. This is to ensure the quality of training remains high and that all students can ask questions and engage in discussion.
I previously attended a course with Excel Consulting, will the training be similar?
Yes, we rebranded from Excel Consulting in October 2019. The business quickly outgrew its original name. Our new brand Nexacu, better reflects our direction, continued innovation and commitment to deliver next level learning. We have always refined and continue to update our courses but retain our excellent trainers and deliver the same high quality content.
Where is the training held?
We are a national training company with office in 7 cities around Australia; Melbourne, Sydney, Brisbane, Perth, Adelaide, Parramatta and Canberra. We also deliver Excel training in the workplace nationwide.
Excel Specialist Courses Training in Brisbane
View Dates
Excel Specialist Courses Training in Melbourne
View Dates
Excel Specialist Courses Training in Sydney
View Dates
Excel Specialist Courses Training in Adelaide
View Dates
Excel Specialist Courses Training in Perth
View Dates
Excel Specialist Courses Training in Canberra
View Dates
Excel Specialist Courses Training in Remote Online
View Dates Financial ModellingLearn to build financial models in line with best practice. Work through case studies, led by our instructors.
Excel Macros/VBA 2 DayLearn to build solutions to automate tasks. Record macros, write procedures, work with functions and more.
Related Digital Credential
Excel Specialist Courses Training in Brisbane
View Dates
Excel Specialist Courses Training in Melbourne
View Dates
Excel Specialist Courses Training in Sydney
View Dates
Excel Specialist Courses Training in Adelaide
View Dates
Excel Specialist Courses Training in Perth
View Dates
Excel Specialist Courses Training in Canberra
View Dates
Excel Specialist Courses Training in Remote Online
View Datesat Nexacu, we're proud to be the trusted training partner for hundreds of leading organisations accross Australia and New Zealand. From government departments to top corporates, we help teams upskill and succeed everyday
Students
4 & 5 Star Reviews
Businesses Trust Nexacu
Google Reviews
Custom workbook included with a step by step exercises
Resit your course for free within 6 Months
Valued by Individuals
