Excel 2013 Dashboard Design
4.5 (75 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
805 students enrolled
Wishlisted Wishlist

Please confirm that you want to add Excel 2013 Dashboard Design to your Wishlist.

Add to Wishlist

Excel 2013 Dashboard Design

Create stunning and fully interactive data visualizations in Excel 2013
4.5 (75 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
805 students enrolled
Created by Packt Publishing
Last updated 10/2014
Current price: $12 Original price: $85 Discount: 86% off
3 days left at this price!
30-Day Money-Back Guarantee
  • 2.5 hours on-demand video
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion

Training 5 or more people?

Get your team access to Udemy's top 2,000 courses anytime, anywhere.

Try Udemy for Business
What Will I Learn?
  • In this course, every step is demonstrated on-screen and explained practically, so you can effectively apply the techniques to your own projects. Learning the “why” behind each task is the key to being able to implement what you learn, and this is emphasized with every stride along the way.
View Curriculum
  • This course has in-depth coverage of Excel Dashboards for a duration of 4 hours. Our course is focused on Dashboard designing only.

Excel 2013 Dashboard Design will show you how to turn your organization’s data into a powerful and interactive dashboard. By learning these easy-to-implement, advanced techniques, you can impress and empower the decision-makers in your organization, making you an irreplaceable asset.

Excel 2013 Dashboard Design begins by creating a vision for the dashboard with the help of stakeholders, and then moves on to gathering the data and building each element of the dashboard. The course ends with comparing distribution strategies appropriate for every size and type of organization.

The first section, Outputs and Inputs, covers the strategic aspects of planning your finished product and bringing in all of the necessary data from your database or Excel workbook. We then explore PowerPivot, a powerful bridge between your raw data and Excel. PivotTables will help us slice our data in a flexible manner, and Charts give us at-a-glance comprehension of important metrics and trends. Stylizing and Interactivity enhance the user experience and present the information in a manageable format. Publication can be problematic without proper planning, but we’ll cover the most effective ways to distribute your dashboard to any type or size of audience.

Excel 2013 Dashboard Design will guide you through your entire project, start to finish, covering every part of the process in depth. Since you’ll not only learn how to do each task, but also why it would be beneficial for you, you’ll be able to easily translate your learning into your own dashboard design project.

About the Author

Tony Kau is a Tableau Desktop 8 Qualified Associate, and he is putting its power to use in his role as a reporting analyst for a Fortune 500 company. His analytical background spans a decade, during which he has used a variety of business intelligence software, though none were better than Tableau. His passion is efficiently leveraging data to inform strategic business decisions.

He holds a degree in Business Administration from the University of Oregon, and his background includes web design, programming, and financial analysis.

Who is the target audience?
  • If you want to substantially increase your value to any organization, as an employee, consultant, or freelancer, opening up the door for raises, promotions, or additional contracts, then dashboard design is an extremely worthwhile and marketable skill to learn. If you’re an analyst of any kind, you know the value of effectively presenting your work. This course is designed to take your intermediate-level Excel skills to the next level and beyond.
Compare to Other Excel Dashboard Courses
Curriculum For This Course
39 Lectures
Dashboard Outputs and Inputs
5 Lectures 16:57

Discover available features and limitations in Excel early in the planning process to create the best dashboard for your audience.

Preview 03:50

Single-dimension dashboards might work in a pinch, but the best dashboards put the control in the hands of the end user.

Empowering your Audience

Your data is stored in Access, but you need to pull it into Excel for your dashboard. Create this connection in Excel.

Connecting to MS Access

Your data is stored in SQL Server, but you need to pull it into Excel for your dashboard. Create this connection in Excel.

Connecting to MS SQL Server

Your data is stored in an Excel Worksheet, but you need to aggregate it in Excel for your dashboard. Create a PivotTable in Excel.

Using data in your Excel Sheet
Implementing PowerPivot
5 Lectures 16:07

Your data is stored in Access, but you have a lot of data. To help us utilize it in Excel, we can set it up using PowerPivot.

Importing Data into PowerPivot

Part of your data might be stored in the Excel Workbook, so we need to link it to our data model using PowerPivot.

Adding Data from Excel to PowerPivot

You want to use data from multiple sources or tables in your dashboard and need to tie them together. Relationships and Hierarchy can link these together.

PowerPivot Relationships and Hierarchy

All your data is imported, but you need to perform additional calculations or functions before you can use it. We'll learn how to add calculated fields in PowerPivot.

Creating Calculated Fields

You want to be able to see at a glance whether you are meeting certain business goals, so we'll create a KPI to measure and display this progress.

Implementing KPIs
Working with PivotTables
5 Lectures 22:06

You have your data imported and preprocessed in PowerPivot, but now you need to display it in Excel. We'll create PivotTables to do this for us.

Constructing PivotTables (part 1)

You are importing your PowerPivot data into PivotTables, but you need to use an advanced chart type to display your data. We'll create an intermediary table that will give us a higher level of control of the chart.

Constructing PivotTables (part 2)

You have just created a PivotTable, but to use it effectively, you need to make some adjustments. We'll format, sort, and limit it to meet our desired output.

PivotTable Formatting and Options

Your PivotTable is displaying the correct data, but is using the default style and options. We'll customize the table to match our desired look and feel.

PivotTable Design and Layout

You aren't using PowerPivot to manage your data model, but you still want to implement calculated fields and KPIs. We'll use PivotTable options and conditional formatting as a workaround.

Adding a Calculated Field and KPI
Constructing Your Framework
5 Lectures 20:57

After setting up your PivotTable, now you want to create a PivotChart. We'll create the chart and discuss key chart features.

Creating a PivotChart

PivotCharts almost always have to be customized to be useful. We'll cover the steps to modify the main chart elements.

Customizing a PivotChart

Charts can be more useful when they are combined, for example, a bar chart and a line chart to easily compare multiple metrics. We will walkthrough how to create these combo charts.

Creating a Combo Chart

Charts almost always have to be customized to be useful. We'll cover the steps to modify the main chart elements.

Customizing a Combo Chart

A written sentence can sometimes be necessary to make a strong point. We will create a dynamic sentence using static text and a formatted number from our data.

Creating Text-highlight Cells
Stylizing Your Data
5 Lectures 25:03

Arranging your dashboard effectively is a very important step. We'll cover techniques and tips to present our data and save you frustration in the process.

Formatting the Dashboard Sheet

PivotTables can expand and contract with the data behind them, which can be useful, but also impractical. When you need complete control, reference the PivotTable in a range.

Referencing a PivotTable

If you would like to highlight any range based on its value (or the value of a related cell), you can implement conditional formatting to change colors or fonts, or add icons or color bars.

Conditional Formatting

You would like to show an underlying trend line without taking up much space. Sparklines allow you to create a basic chart in a single cell.

Implementing Sparklines

Sparkline source ranges remain static unlike normal and PivotCharts in Excel. In order to reflect the selected time period in our dashboard, we need to use named ranges and VBA code to make them dynamic.

Enhancing Sparklines using Named Ranges
Adding Interactivity
4 Lectures 16:47

To make an interactive experience with a dashboard based on PivotTables, we can use slicers to let the end user focus as wide or as narrow as they would like, at the click of a button.

Preview 03:42

If the default slicer settings and color scheme is not acceptable for the dashboard, we can customize them.

Preview 04:42

If we deal with a range of dates, we can easily extend filtering capabilities to the end user by including a timeline slicer.

Preview 05:15

Space can be limited on a dashboard, so drilling down or getting expanded views can be impractical on the dashboard itself. We can use hyperlinks to intuitively provide the additional information in one click.

Preview 03:08
Utilizing Power View
5 Lectures 15:48

If you need to quickly assemble a functional, interactive dashboard tied to your Excel data model or an external connection, Power View allows you to create a presentation-ready view in very few clicks.

Creating a Power View

If your data needs to be visualized in a hierarchy (for example, Category, Subcategory, and Item), consider using a drill down to provide context and details for your data.

Creating Drill-downs in Power View

In order to focus on just the relevant data, you can implement filters on chart elements or the entire view.

Using Filters in Power View

If you would like to see your data one slice at a time, you can use tiles to create a visual interface for selecting each slice.

Implementing Tiles in Power View

If you would like to separate your data into slices such as tiles, but view all tiles at the same time, Multiples allows you to show all the slices together.

Visualizing Multiples in Power View
Publishing Your Dashboard
5 Lectures 22:57

Your dashboard is complete, but we need to lock down the information that we don't want the users to focus on. We can do this by preventing the modification of cells and hiding sheets.

Cleaning Up and Locking Down

Your users may use different versions of Excel, which could cause serious functionality issues if they try to use the workbook on their own computer. Use SharePoint 2013 to allow users to navigate your dashboard reliably from their web browser.

Backwards Compatibility and SharePoint Distribution

If you need to share your dashboard on a small scale, or your dashboard does not use PowerPivot, you can conveniently host it on SkyDrive.

Sharing on Microsoft SkyDrive

If your organization does not have a SharePoint 2013 server, but you need to distribute to a group who may not be using Excel 2013, use Office 365 SharePoint Online.

Publishing on Office 365 SharePoint

If you need to distribute a paper copy or PDF of your dashboard, as is sometimes necessary, utilize print area, scaling, and margins for the most optimal presentation.

Printing and Exporting to PDF
About the Instructor
Packt Publishing
3.9 Average rating
8,274 Reviews
59,300 Students
689 Courses
Tech Knowledge in Motion

Packt has been committed to developer learning since 2004. A lot has changed in software since then - but Packt has remained responsive to these changes, continuing to look forward at the trends and tools defining the way we work and live. And how to put them to work.

With an extensive library of content - more than 4000 books and video courses -Packt's mission is to help developers stay relevant in a rapidly changing world. From new web frameworks and programming languages, to cutting edge data analytics, and DevOps, Packt takes software professionals in every field to what's important to them now.

From skills that will help you to develop and future proof your career to immediate solutions to every day tech challenges, Packt is a go-to resource to make you a better, smarter developer.

Packt Udemy courses continue this tradition, bringing you comprehensive yet concise video courses straight from the experts.