Visually Effective Excel Dashboards
- 11 hours on-demand video
- 14 articles
- 25 downloadable resources
- Full lifetime access
- Access on mobile and TV
- Certificate of Completion
Get your team access to Udemy's top 3,000+ courses anytime, anywhere.Try Udemy for Business
- How to approach an Excel dashboard project from idea to delivery
How to add interactivity to your Excel reports - No VBA required
Impress management with reports that make the decision-making process faster
- Clever use of Excel core functionality for responsive spreadsheets
- Learn how to create a professional KPI dashboard from scratch
- Create impressive, non-standard Excel charts using smart techniques
- Build Pivot Table-based dashboards for easy interactivity
- Use powerful visual techniques so your content has more effect
- Familiarity with Excel
- You are able to write basic formulas and are familiar with the Excel spreadsheet environment
- Demonstration is done with Excel 2016 but the techniques work with any version of Excel - unless stated in the individual videos
Creating an impressive, interactive Excel Dashboard from scratch is not easy!
I used to stare at my empty sheet and wonder where do I even start?
You need a structured process in place to go from idea to outcome in a smooth way.
You'll find out all about it in the course. We setup two fully functioning dashboards from scratch.
This course doesn't just teach you a set of functions and features, but how you can use them together to create impressive reports.
It teaches you how to approach a new Dashboard project and how to structure your work so maintaining the dashboard becomes very easy not just for yourself but for anyone else.
★ Note: Course recently updated to include new content on dependent combo-boxes and info-graphic charts in Excel ★
What students are saying
"This course provides a lot of great tips which you can immediately apply to your spreadsheets, no matter what standard of user you are. I have been working in excel for a number of years and I am amazed at the number of simple tips I have learnt in such a short time. Fantastic." -- Carl Postians
"I learned something new and very useful in the first 10 minutes...awesome!" -- Nancy Bowens
What you learn
This course teaches you to create well-designed Excel dashboards so you and your colleagues can see trends and make quick decisions informed by data.
This Excel Dashboard course comes with templates, tools and checklists that can be applied directly to your Excel reports.
Excel dashboard & Report templates
Checklists and design guidelines
Excel workbook to follow along
What differentiates this course from the other dashboard courses? It includes:
The entire process from idea to delivery - every single step without skipping key points.
Tried-and-trusted techniques that are not only applicable to Excel dashboards but any Excel report.
Includes Excel templates, tools and checklists that you can use for your own Excel reports.
Relevant and up-to-date with industry needs right now, as it’s based on feedback and suggestions from finance professionals.
Lots of ideas and inspiration to help you come up with own report design
Complex topics are taught in a simple way by an instructor with 17 years of experience.
The course is designed to have an even balance between talking head and screen-cast.
Content is clearly organised so more advanced learners can jump in at any point and follow along with the relevant project file.
- Business Professionals who use Excel to create dashboards and reports
- Data analysts
- You currently use Excel and want to improve your skills
- You'd like to learn how to create interactive reports
- You'd like to improve the design of your existing Excel reports
This lecture introduces you to the topics covered in this online Excel Dashboard course - make sure you watch it to see if the course is for you.
I provide a brief summary of the structure of this online Excel report and dashboard course. Depending on your needs right now and how you'd like to learn, you could either jump in when I create the Excel KPI dashboard or the Regional (pivot slicer) dashboard from scratch or you can take the course from beginning to end.
You can download the main course files here - you will find a demo workbook where you can follow along with me when I cover the formulas required for dashboards, Excel Form Controls and advanced Excel charting techniques. You can also download the files from the source data section when I demonstrate different raw data sources and also the final KPI dashboard and Regional (Slicer/ Pivot table) dashboards.
How should you setup your Excel spreadsheet report? What's the optimal method of organizing your report and calculations? How should the data flow? In this lecture I share with you the optimal method for creating Excel dashboards and reports and how to organize your tabs for best results.
What makes a good Excel report? How you design and present the information makes a big difference in how readable your reports are. Find out what makes a good report layout in this lecture.
In this video, I show you how to set your own default theme in Excel so that every time you create a new workbook, the theme colors that are used are the ones set by you and NOT the Microsoft Office Default theme. Unfortunately setting your own default theme in Excel is not as easy as in Microsoft Word or PowerPoint where you can just tick mark - "set as default theme". In Excel you have to follow a few steps which I show in the video.
The source data for your dashboard could come from different systems. This lecture provides you with a quick overview of what to expect in this section and how you can prepare for different source systems.
One possible source is to have the data in an Excel spreadsheet. The data could be in separate tabs - it could be in an Excel table or it could be in an Excel Pivot table. It could be in a tabular format or a non-tabular format. What do I mean by tabular and how do you handle cases where data is non-tabular? Find out in this tutorial.
One possible source for the raw data of your Excel dashboard is Microsoft Access. In this lecture I show you how to import data from Ms. Access to Excel - either as an Excel Table or as a Pivot Table. I also show you how to optimize the connection properties so that when the data is changed in Access, the updated information is available in Excel.
CSV files (data file or text files) are easily extracted from most systems. It's tempting to open the files directly in Excel and then copy and paste the data in your Excel dashboard report. But doing this, is a manual process which you have to repeat every single time. A better way would be to set up an open connection to the CSV file so that every time the file is replaced or appended, you just have to refresh your data set in Excel and have the latest data available. No copy and pasting necessary. Find out how in this lecture.
This lecture provides an overview of the formulas covered in this section. These are Excel formulas that help you handle any situation (whether tabular or not) to transform your data and prepare it the way you need for your reports. You can download the Demo Excel workbook and follow along with me.
INDEX MATCH is my most used formula when I create Excel reports. This lecture takes you through the basics of the INDEX and then the MATCH formula and how these two formulas work well together. This formula combination is not just for dashboards but for any type of analysis you need to do where you have large data sets.
SUMIFS is a great formula, not to just to sum by different criteria but also to grab data out of data tables by criteria. COUNTIFS, does a count based on different criteria and AVERAGEIFS calculates the average based on single or multiple criteria. Watch this lecture to find out more about the potential of these great formulas.
When you create ranking reports in Excel, you need to be sure that you have unique ranks - even if two categories get the same rank and have an identical number, you still need to create a unique rank so you can organize them one after the other. Watch this lecture to find out how the ROW function could help you with that.
GetPivotData is a great Excel function when you want to extract your data from your pivot table in a dynamic way. It is independent of the cell position. The only requirement is that the value you want to extract is "visible" in your pivot table. Why would you want to extract data from a pivot table in a dynamic way? Find out more in this lecture.
INDIRECT function can be a bit confusing at first but it's a great way to get dynamic ranges. In this lecture I show you how this works and later when we create the Slicer based regional dashboard we use this to a lookup on images.
Learn how to use Excel's Combo Box Form control here.
When you want to restrict the options available to the user to just one option, you can use Excel's Option Button form control. Find out what you need to do if you are using more than one option button groups in your reports and how you need to configure the option button form control in Excel.
The error bar technique is one I often use when I can't get the effect I want in a chart using the standard chart options. It's meant for statistical analysis, but error bars can do so much more for business reports. Find out how it works here and later we use this technique to create a non-standard chart for the KPI dashboard.
This video shows you how to create dynamic chart ranges - i.e. "From - To" Charts. This means the user selects from which category or date they want to view the data, and to which category / date, and the chart only visualizes the values for the categories in between. This means the chart needs to work with dynamic ranges which change in size.
In this lecture you can see an overview of the finished KPI dashboard in Excel - this will give you a good idea of what we're setting out to achieve in the next few sections.
Now we start to setup the calculation sheet - here we will prepare the data for the dashboard. Organization is key for this tab - we need to break down the sheet to different sections where we do the calculations for each part of the dashboard. We can limit formatting here to some color coding but we don't need to worry about any extensive formatting here since the purpose of this tab is for data preparation only.
In this lecture we add another level of complexity to our dashboard by adding a scroll bar so we can scroll through our table on the dashboard page. This scroll bar, will depend on the calculations we did in the previous lecture - we just need to write smart formulas on the dashboard page to get the scrolling effect.