98.8% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Advanced Excel Dashboards
Advanced Excel DashboardsFace to face / Online closed & onsite training. Restaurant lunch included at STL venues.
Designed for Excel 365
- 1 day Instructor-led
Syllabus
Who is this course for?
This Advanced Excel Dashboards course is ideal for those looking to develop their existing dashboards skills in Excel. Roles that would benefit from this training include BI (business intelligence), data managers, data analysts, or Project Managers.
Objectives
By the end of this course you will be able to:
- Use advanced features to create dashboards in Excel.
- Become proficient in the planning, creation and maintenance of advanced Excel dashboards.
Prerequisites
Completion of our Excel Dashboards for Business Intelligence course or equivalent knowledge of the following:
- Basic charts
- PivotTables
- Named Ranges
- Form Controls
- Absolute & Relative cell references
Functions:
- If and Nested Ifs
- Sumifs, Countifs & Averageifs
- Vlookup and Hlookup
- Index & Match
Course Syllabus
Planning a Dashboard
Who is the audience?
Detail level
Time scale considerations
Managing Dashboards
Efficient dashboard updating
Managing sources
Source data for reporting
Prepare data (flat lists)
Link to external data sources
Multiple data sources dashboard (PowerPivot)
Reports based on data models (2013 & 2016)
Advanced charts for Dashboards
Waterfall Chart
Speedometer Chart
Gantt Chart
Forecasts & Trend analysis in Dashboards
Linear & seasonal data sources
Statistical formulas for Dashboards
Slope, Variance,
Multiple page Dashboard
Creating interactivity using macros
PowerView (2013 & 2016)
What you get
"What do I get on the day?"
Arguably, the most experienced and highest motivated trainers.
Face-to-face training
Training is held in our modern, comfortable, air-conditioned suites.
Modern-spec IT, fully networked with internet access
Lunch, breaks and timing
A hot lunch is provided at local restaurants near our venues:
- Bloomsbury
- Limehouse
Courses start at 9:30am.
Please aim to be with us for 9:15am.
Browse the sample menus and view joining information (how to get to our venues).
Refreshments
Available throughout the day:
- Hot beverages
- Clean, filtered water
- Biscuits
Online training
Regular breaks throughout the day.
Learning tools
In-course handbook
Contains unit objectives, exercises and space to write notes
Reference material
Available online. 100+ pages with step-by-step instructions
24 months access to Microsoft trainers
Your questions answered on our support forum.
Training formats & Services
Training Formats & Services
Training formats available
|
Testimonials
Summary
Aesica Pharmaceuticals
Veronica Glyn,
Product Stream Engineer
Brilliant course, Paul has saved us a lot of time with useful hints and tips.
Paul explained many methods for making our dashboards flexible and easy to update so that it does not require a lot of constant maintenance.
Similarly, whilst Paul went through the course material he also showed us how to lay our work out in a methodical way so that it would be easy to fault find and easy for others to check.
Advanced Excel Dashboards
FastNet Ltd
Chris Lamb,
Finance Director
This course was spot on, a great follow up to the previous BI/Dashboards one I did a few weeks ago. Its definitely worth booking these close to one another and puttin thiings into practice back at work in the meantime to get the best of the courses.
Jens was great and this time was perfectly timed in terms of pace, however the final part I thought was too much copying what was being typed from his instructions so we weren't really learning as such, and we weren't looking at how the background of the design of the dashboard was done. I think what would be good would be an excersise using some base data for us to try and design our own dashboard or perhaps formulate a choice of 2-3 things - then have some time to do so, then all look at each others' work.
Advanced Excel Dashboards
HSBC
Alexander Orlov,
Project Manager
Amazing, thank you , will keep in touch and will see you back shortly
Advanced Excel Dashboards
Get On-site/Closed quote
Excel range
Excellent
HSBC
Project Manager
Alexander Orlov
I was looking for a specific training for my data analytical team to equip them with more tools to slice and dice the data, building more trust worthy sophisticated propensity models, using SQL, VBA and Excel. I came across with STL training and my research and expectations were match by 100%. Spot on! Great professionals! Highly recommended for all banking and financial professionals that are using Excel on BAU basis.
Resources
Promotions
Latest Feedback
Latest Tweet
Training manual sample
Below are some extracts from our Excel training manuals.
1. Planning a Dashboard
Dashboards are a type of report. They help turn, often large amounts of data, into a digestible page that enables easy analysis. This form of data analysis can be part of Business Intelligence (BI).
Many companies have access to large amounts of data, perhaps in a data warehouse or spread across multiple file servers. Decision makers often want to cut through this data and look for patterns. Dashboards are an excellent way to turn large amounts of data into usable and actionable reports.
2. Managing Dashboards
Dashboards need to be maintained and managed. You can create Dashboards where you will need to spend a lot of time maintaining and managing the dashboards and this is of course not a good idea.
The source data can be based on a query in order to improve the efficiency of updating a dashboard. You may have the data from the last 36 months in the Dashboard. By writing a query which defines the number of periods you want to be shown in the dashboard, you will not need to spend time “cleaning” the source data when new periods are added to the source data.
Updating the Data Source
Have the source data linked to the dashboard workbook can be a good idea. This will keep the size of the dashboard workbook small.
You will also be able to control the source data and the source data do not need to be updated in many workbooks. The source data
could be in a shared folder and then each department could have a dashboard workbook with the specific information they will need.
3. Source Data for Reporting
A major consideration you need to think of is which data source or sources you work with and would it be a good idea to consolidate the date in Excel or in an external data source and then link the source to your dashboard. You have different options and it is very important to use the one which fits your situation best to create a successful dashboard. If you have a single flat list in Excel containing all the data you need, it is easier to create the dashboard, but you still need to think about how you create the dashboard to make the updates of the source data as efficient and less time consuming as possible.
Many Excel users can make a mistake when they create a new worksheet. They want it to look nice and it must be easy to enter the data.
This is not the best approach. You need to have the data structured so you can use the data.
Excel needs the data structured in a flat list if you need to analyse the data. If not, the work creating a dashboard will be much more
time -consuming and complicated.
Creating Web Queries
Web pages often contain information that is perfect for analysis in Excel. Using a web query, you can retrieve data that is either static or refreshable. That is, the data in Excel will update with the latest data on the Web page.
Importing Data from Other Sources
You can use Microsoft Query to import information from many database applications. The Query Wizard, part of Microsoft Query, guides you through the steps to create a query.
5. Advanced Charts for Dashboards
A Speedometer or gauge chart can be a way of showing progress, and how close something is to hitting a target.
Waterfall charts, sometimes known as bridge or step charts, are popular ways of showing how values are distributed, or how amounts are broken down from period to period.
They are often used in budgets, showing how the amounts grew or shrank from one year to the next. It could also be used to show how sales or profits progressed across a financial year.
6. Forecasts and Trend Analysis in Dashboards
Forecasting in Excel
Excel has a number of effective tools for forecasting. You can work with linear data, exponential data or seasonal data. Excel can forecast any source data as long as there is a pattern in your data. When you forecast you need to know the accuracy of your forecast to make sure that you are using the right forecasting model.
7. Statistical Formulas for Dashboards
Statistical Excel functions can provide the audience with useful information. Many statistical functions can be used in dashboards. In this handbook, specifically Trendline and forecasting functions are used.
The Slope function is a trend function which calculates the average trend increase/decrease for known values for each period.
The syntax: =SLOPE(known_y's, known_x's)
The Known_y’s is the known values and the Known_x’s is the known periods.
In the example below the sales is known for January to September (E6:E26). The known periods 1 to 21 (B6:B26). The average trend increase here is 306 for each period.
The Correl function calculate the correlation (the relationship) between two arrays.
The syntax: =CORREL(array1,array2)
In the example below the correlation is calculated between the array1 (Average temperature C7:C18) and array2 (the sales of air-condition systems D7:D18). The result 0.9 is telling you that there is a close positive relationship between the two arrays.
Thanks. Your download will begin shortly.
Please help us
Share or create a link to this manual today!
Just follow these simple instructions...
