Excel 2013 Dashboard Design

Create stunning and fully interactive data visualizations in Excel 2013
4.5 (50 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.
734 students enrolled
$85
Take This Course
  • Lectures 39
  • Contents Video: 2.5 hours
  • Skill Level All Levels
  • Languages English
  • Includes Lifetime access
    30 day money back guarantee!
    Available on iOS and Android
    Certificate of Completion
Wishlisted Wishlist

How taking a course works

Discover

Find online courses made by experts from around the world.

Learn

Take your courses with you and learn anywhere, anytime.

Master

Learn and practice real-world skills and achieve your goals.

About This Course

Published 10/2014 English

Course Description

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.

What are the requirements?

  • This course has in-depth coverage of Excel Dashboards for a duration of 4 hours. Our course is focused on Dashboard designing only.

What am I going to get from this course?

  • 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.

What 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.

What you get with this course?

Not for you? No problem.
30 day money back guarantee.

Forever yours.
Lifetime access.

Learn on the go.
Desktop, iOS and Android.

Get rewarded.
Certificate of completion.

Curriculum

Section 1: Dashboard Outputs and Inputs
03:50

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

05:10

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

03:10

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

02:33

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

02:14

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

Section 2: Implementing PowerPivot
03:20

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.

02:26

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

02:49

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.

04:15

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.

03:17

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.

Section 3: Working with PivotTables
05:07

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.

04:36

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.

04:15

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.

04:07

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.

04:01

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.

Section 4: Constructing Your Framework
03:32

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

03:05

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

04:55

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.

05:41

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

03:44

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.

Section 5: Stylizing Your Data
05:17

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.

04:47

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.

04:07

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.

02:52

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.

08:00

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.

Section 6: Adding Interactivity
03:42

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.

04:42

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

05:15

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

03:08

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.

Section 7: Utilizing Power View
03:46

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.

02:48

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.

03:37

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

02:35

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.

03:02

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.

Section 8: Publishing Your Dashboard
06:32

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.

03:08

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.

04:57

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.

03:47

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.

04:33

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.

Students Who Viewed This Course Also Viewed

  • Loading
  • Loading
  • Loading

Instructor Biography

Packt Publishing, Tech Knowledge in Motion

Over the past ten years Packt Publishing has developed an extensive catalogue of over 2000 books, e-books and video courses aimed at keeping IT professionals ahead of the technology curve. From new takes on established technologies through to the latest guides on emerging platforms, topics and trends – Packt's focus has always been on giving our customers the working knowledge they need to get the job done. Our Udemy courses continue this tradition, bringing you comprehensive yet concise video courses straight from the experts.

Ready to start learning?
Take This Course