- Alright in the last course we talked aboutwhy you'd want to build a dynamic dashboardand in this section I just want to talk throughone of the four components of the dashboard.A well structured dashboard will be easy to useby a wide variety of users and these four sectionsare going to take us from start to finish.So the four topics for building the template areData management, Visual design,working with Pivot Tables and slicersand then also designing macrosthrough 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 Excelas a database as a flat filefor larger data sets it is not as ideal.Also depending on the formulasthat you write into it or the calculationsit can chew up a lot of internal memoryand so as you start to think aboutwhat am I going to use this forhow big is my file Excel may not bethe best place to store your data.

We'll also talk about the Data Flowhow we're going to walk from the raw data extraction.So whether you're pulling it froma Web Intelligence or BusinessObjects platformor downloading it like we're going tofrom an external source,how we walk from that raw data fileall the way through to the completedcleaned up data set that we're goingto then feed back to our Excel template.And then also talk through somevery standard Formulas or calculated fieldsthat we can add to our raw datathat really enhance the data setto give us some additional dimensionsto analyze the data by.

We'll also talk about Visual Designand without picking out any particular examplesif you want to go ahead and pause this movieand go to your favorite search engineand type in worst Excel chartsand you'll find plenty of blogs,news articles and comments aroundgood data that has gone into bad charts.And so why does this matter?Well one we want to make sure we represent our data fairlybut 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 aboutpeople that want to digest the informationbut are higher up in the organizationand so don't have a lot of timeor 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 thingsthat we're going to pay attention to.This is two charts that I built out of the sample datathat we're going to be working fromand the sample data is an extractof open jobs for the city of New York.

And so this was the total number of Openings availableat the time that I downloaded the reportbased on whether or not the jobwas posted internally and externally.And these two charts represent the same data.So believe it or not what you're looking athere is exactly the same chart.What's different about them is the scale.And so the point of putting this is nowwhen we talk about visual designis we visually process this information very quickly.And so on the left hand chart it looks likewe have significantly higher levelsof Internal Openings versus External Openings.

But on the right hand side it looks a bit more even.And so the visual design here is aroundbeing able to process this information quicklybecause we can see the scale and reference.We're showing the data appropriatelybecause we're showing the full scale all the way to zero.So we'll talk more about visual design down the roadbut just wanted to throw this in as an examplearound why visual design is so important.We'll also talk in detail more of a refresher aroundPivot tables and how they're laid out.

If you view pivot tables a lot this willprobably be a very casual refresher.Really what we want to draw out of thisis make sure that you understandthe four dimensions of a pivot table.The row labels, the column labels,the data and then the filtersbecause we're going to be referencing thesethrough the visual basic.We're also going to talk a lot more about Slicersand how we can insert them into the report,how they work and then also how we can design themso that they match the over all visual designor visual feel of the report as well.

And get into the Visual Basicand the recording and using of macros.And so we've talked a bit about macrosbut I just wanted to kind of illustratewhat we're going to allow our macros to door what we're going to program our macros to do.So I'm going to take that same chartthat we showed before around number of openingsby posting type internal and external.And the idea behind the macro isthat we're going to change that x-axis.So when we talk about changing the layout of the chartwe're going to change that x-axis on the chart.So when I go ahead and push the buttonand there's going to be a button here for agencythen it's going to change here the dimensions.

So I went from my posting typeto my agencies by pushing a button.Then I can also go ahead and Apply a filterso that now I can see my Number of Openings by agencyfor where they were posted externally.And so this is really walking throughthe whole process of how we're goingto analyze data visually using this template.So by covering these four areaswe'll be able to build a dynamic dashboardthat lets users filter and view databy entirely customizable list of fieldsand then see that output visually.

Resume Transcript Auto-Scroll

Author

Released

7/3/2015

Learn how to explore and analyze your data by creating an interactive dashboard in Excel using pivot tables and a little Visual Basic. Using these lessons, you can create a dynamic template to generate reports for customers and to better understand your own data. Along the way, you'll learn how to import external data, design a template using pivot tables, and add slicers, macros, and action buttons to make the template more interactive. Author Nate Makdad also shares some basic principles of visual design to guide you in developing an easy-to-use and visually appealing report.

Topics include:

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

Skill Level Intermediate

2h 34m

Duration

353,112

Views

Show MoreShow Less

Q: Are the exercise files corrupt? When I try to open them, I get a message saying "The file is corrupt and cannot be opened."

A: Often .xlsm file (embedded macros) that are downloaded from the web
trigger security settings in Excel. Here is a Microsoft forum with information on
disabling these setting: