The Magic of an Excel Dashboard

Kyle Pew
A free video tutorial from Kyle Pew
Microsoft Certified Trainer
4.6 instructor rating • 19 courses • 717,920 students

Learn more from the full course

Microsoft Excel Data Analysis and Dashboard Reporting

Build Dynamic, Interactive Microsoft Excel Dashboards (Excel 2010, 2013, 2016) with a Best Selling Excel Instructor

03:36:44 of on-demand video • Updated April 2018

  • Understand and Identify the principles of data analysis
  • Effectively summarize and present data clearly
  • Use effective design principles when creating and presenting data
  • Take full advantage of the built in tools Microsoft Excel offers in order to analyze and build dashboard reports
  • Build interactive dashboard reports
  • Analyze Excel data using Excels TEXT based Functions
  • Data Mine using Excels Array Functions
  • Build Interactive Dashboard Reports with Buttons and Drop Down Menus
  • Streamline and Analyze Data with Excel PivotTables and Pivot Charts
English [Auto] So a coworker hands you this this raw data it's in Microsoft Excel which is great but the plain simple data and they say to you I want you to create a report something presentable so that we can look at customers and we look at their orders. I've got customer information there and I got order information. And right now it's just simple simple data ownable analyze it. I want to see things like how many orders have they placed. What's been the amount of their orders. What is a specific customer's orders. I just want to see a specific customers orders not every order just specific customers order them. Well how do we do that. Well once again we're talking about analyzing the data but then we'll talk about presenting it creating an interactive dashboard and using that as your report. Take a look. So I've got a third tab here called Customer dashboard. Well I've got a simple interactive dashboard that any user can come in to and look up specific information about a customer and their orders. Take a look. Now there's a lot going on in here we're going to talk about all this. We're going to break it down and have you rebuild this as you go through the course. I've got a little drop down here. Nice little comment boxes in his nose like the customer. So I have up here right now I'm looking at this customer. So I see I see things like the customer's contact information. Well where is that coming from. That's coming from the customer info sheet. I've got the order information. Specific orders to that customer. I've got some simple calculations here. They asked the well for this customer how many orders of they placed. What's their average freight. What's your last order. And I've got a nice little chart over here on the right showing us graphically some of that information. Come in here and I don't want to see this customer come down and say oh I want to see around the horn. So I grabbed that customer and my dashboard updates one change by me or by the use you're getting in here. And I've now updated the entire dashboard interactive right to show me that information. And I've got Thomas Hardy who's the contact for around the horn. I've got all the orders for that customer around the horn. Their calculations their chart and someone come in here and I can pick any customer and I'll update the dashboard for that customer. Now once again there's a lot going on in here. We got some Wii look ups to help us do some look up information. We've got some subtotal functions over here. We got some filtering going on. We got a pivot chart which is connected to a pivot table. There's some stuff that's going on in the background. And then once again we'll break it down. I mean I walk you step by step through each concept and then I'll take you into the project and help you build this right here. Now to make it really truly interactive there's a few ways you can do it. And what I've chosen to do here is with just a little bit of VBA Visual Basic replications. Now if you don't know any VBA no problem I'm going to take it step by step and it's really small really very small amount of the be able to get this to happen right here a few lines of code. Nothing big. Then I may take you through each of the lines of code and we'll talk about each of us. Now if you know VBA great I'm probably going to introduce you to some stuff that you probably know but you can build upon it you can make it even more interactive with any knowledge that you have currently of VBA so great whether you're brand new or you've been in there for a little while you're in the right spot. We're going to create this interactive dashboard based off of all of this raw ugly data get a nice presentation here. We're also talking about things like locking the worksheet so users can't change values. All they can do is click on the little dropdown and update the data they're looking for. Now I've also got an ad order button here when I give that button click. Imagine you got a customer on the forms a phone it's like Hey so-and-so you haven't placed an order since 1998 what's the deal with places or really really out great place nor do I hit the button. This opens up some prompts where I can fill out some information about the order into the court date. Well when do they want it shipped to them let's say they want to risk to by the thirty first of October 2017. What's their shipping method would say they're going to choose number three or federal shipping or speed Express or whatever they're doing what their order amount they're going to spend a hundred bucks and so on. And now I've got that new order ready for me right here inside the table for this customer. All through a button pressed again. I don't have to worry about going back to the other sheets and managing that. I just want it all right here in this one interface. So get ready. We're going to analyze data. Clean it up make it good get it ready to create a dashboard a nice interactive screen for users to go to to interact with customer information and those customers orders.