The Magic of an Excel Dashboard

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've got order information. And right now it's just simple simple data I want to be able to analyze it. I want to be able 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 any specific customer's orders not every order just a specific customer's order. Well how do we do that. Well once again we're going to talk 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. voila 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 I'm going to have you rebuild this as you go through the course. I've got a little drop down here. Nice little comment box just letting us know select a customer. So I hop up here right now I'm looking at this customer. So 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 need us to know well for this customer how many orders of they placed. What's their average freight. What's their last order. And I've got a nice little chart over here on the right showing us graphically some of that information. I 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 grab that customer and my dashboard updates one change by me or by the user getting into 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 so on come in here and I can pick any customer and it'll update the dashboard for that customer. Now once again there's a lot going on in here. We got some v lookups 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 once again we'll break it down. I'm going to 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 for Applications. Now if you don't know any VBA no problem I'm going to take you step by step and it's really small really very small amount of VBA to be able to get this to happen right here a few lines of code. Nothing big. but I'm going to take you through each of the lines of code and we'll talk about each of them. 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'll also talk 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 add order button here when I give that button a click. Imagine you got a customer on the phone it's like Hey so-and-so you haven't placed an order since 1998 what's the deal let's place an order and they're like oh yeah great let's place an order so I hit the button. This opens up some prompts where I can fill out some information about the order enter the required date. Well when do they want it shipped to them let's say they want it shipped to them by the thirty first of October 2017. What's their shipping method let's say they're going to choose number three or federal shipping or speed Express or whatever they're doing what's 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 press 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 look good get it ready to create a dashboard a nice interactive screen for users to go to to interact with customer information and those customer's orders.