Join Nate Makdad for an in-depth discussion in this video Topics in this course, part of Creating Interactive Dashboards in Excel 2013.
- Alright in the last course we talked about why you'd want to build a dynamic dashboard and in this section I just want to talk through one of the four components of the dashboard. A well structured dashboard will be easy to use by a wide variety of users and these four sections are going to take us from start to finish. So the four topics for building the template are Data management, Visual design, working with Pivot Tables and slicers and then also designing macros through Visual Basic programming.
So first let's talk a little bit more about Data management. The first part is Excel is not a database. So though you can certainly use Excel as a database as a flat file for larger data sets it is not as ideal. Also depending on the formulas that you write into it or the calculations it can chew up a lot of internal memory and so as you start to think about what am I going to use this for how big is my file Excel may not be the best place to store your data.
We'll also talk about the Data Flow how we're going to walk from the raw data extraction. So whether you're pulling it from a Web Intelligence or BusinessObjects platform or downloading it like we're going to from an external source, how we walk from that raw data file all the way through to the completed cleaned up data set that we're going to then feed back to our Excel template. And then also talk through some very standard Formulas or calculated fields that we can add to our raw data that really enhance the data set to give us some additional dimensions to analyze the data by.
We'll also talk about Visual Design and without picking out any particular examples if you want to go ahead and pause this movie and go to your favorite search engine and type in worst Excel charts and you'll find plenty of blogs, news articles and comments around good data that has gone into bad charts. And so why does this matter? Well one we want to make sure we represent our data fairly but we want to Focus on improving readability. Time is precious for your viewers.
If we think again to the who is this template built for, a lot of times for me it's about people that want to digest the information but are higher up in the organization and so don't have a lot of time or it's packaged as part of a much larger piece. So time is precious. People need to be able to see and analyze data quickly. So just as one example some things that we're going to pay attention to. This is two charts that I built out of the sample data that we're going to be working from and the sample data is an extract of open jobs for the city of New York.
And so this was the total number of Openings available at the time that I downloaded the report based on whether or not the job was posted internally and externally. And these two charts represent the same data. So believe it or not what you're looking at here is exactly the same chart. What's different about them is the scale. And so the point of putting this is now when we talk about visual design is we visually process this information very quickly. And so on the left hand chart it looks like we have significantly higher levels of Internal Openings versus External Openings.
But on the right hand side it looks a bit more even. And so the visual design here is around being able to process this information quickly because we can see the scale and reference. We're showing the data appropriately because we're showing the full scale all the way to zero. So we'll talk more about visual design down the road but just wanted to throw this in as an example around why visual design is so important. We'll also talk in detail more of a refresher around Pivot tables and how they're laid out.
If you view pivot tables a lot this will probably be a very casual refresher. Really what we want to draw out of this is make sure that you understand the four dimensions of a pivot table. The row labels, the column labels, the data and then the filters because we're going to be referencing these through the visual basic. We're also going to talk a lot more about Slicers and how we can insert them into the report, how they work and then also how we can design them so that they match the over all visual design or visual feel of the report as well.
And get into the Visual Basic and the recording and using of macros. And so we've talked a bit about macros but I just wanted to kind of illustrate what we're going to allow our macros to do or what we're going to program our macros to do. So I'm going to take that same chart that we showed before around number of openings by posting type internal and external. And the idea behind the macro is that we're going to change that x-axis. So when we talk about changing the layout of the chart we're going to change that x-axis on the chart. So when I go ahead and push the button and there's going to be a button here for agency then it's going to change here the dimensions.
So I went from my posting type to my agencies by pushing a button. Then I can also go ahead and Apply a filter so that now I can see my Number of Openings by agency for where they were posted externally. And so this is really walking through the whole process of how we're going to analyze data visually using this template. So by covering these four areas we'll be able to build a dynamic dashboard that lets users filter and view data by entirely customizable list of fields and then see that output visually.
- Defining your requirements
- Setting up data
- Adding reference tables
- Creating hierarchy in the layout
- Choosing color and typography
- Importing data
- Inserting slicers
- Recording a macro
- Editing VBA code
- Improving the user experience