=INDEX(state_population_data, MATCH(lookup_date,state_population_data,0), MATCH(lookup_state,state_population_data,0)) The population data in this column is then pulled in from the State population raw data sheet using an index/match formula, using the date variable and state as lookup values (this is for illustration only, in practise it is filled out with specific cell and range references):
This table is set up so that the year at the top of the column (1900 in this screen grab) is linked to the year selected in the dashboard, so it will change to reflect the users choice (or the animation). The State Population – dynamic table contains the data used for the state population column and chart in the dashboard. I then added a new sheet called “Staging” and set it up as follows: I use it in all the lookups in the staging sheet and in the dashboard sheet to get hold of the necessary data.
Since my lookup formulas all run off this variable year, the data returned by the lookups changes when a different year is selected.Ĭell G5 contains this year variable and so drives the dashboard.
DASHBOARD IN EXCEL 2007 TUTORIAL DRIVER
The overall driver of all the visuals in the dashboard is the YEAR selected by the user (or automatically by the animation). When I say “Staging the data”, I mean preparing the data and setting it up into aggregated tables ready to drive the dashboard. So my workbook has four sheets containing raw data: Presidential Data, State Voting Data, Congress Data and State Population Data. After some further reading, I found that these states weren’t actually formed until the 50’s, hence the missing data. Similarly, I got data on the history of US State voting records from Wikipedia and again used the importhtml() formula to scrape that data into a Google Sheet as a first step.ĭigging around in here I was able to get state population estimates from 1900 all the way to 2012, for example:įinally, I noticed that certain states, Alaska and Hawaii, were “missing” population estimates for the early part of the century, which at first seemed odd.
I retrieved the list of US Presidents from Wikipedia and used Google Sheets to do a quick web scrape of the data using the following function: Next up I busted out the “I <3 Spreadsheets" mug! It's guaranteed to give an extra 10% to one's Excel skills when filled with strong English tea. You can see several elements in the sketch made it into the final dashboard at the top of this page, namely the table of states, the Presidents data and the two charts bottom right (representing the Senate and the House of Representatives). Yes, it’s pretty rough but it helped shape my initial ideas. I came up with the following notes and sketch as my initial prototype: Following that, I thought adding US State population estimates would also be interesting. I knew it would be interesting to look at a century’s worth of voting data alongside information about the Presidents serving during that period. This project began with pen and paper, simply sketching out a few ideas of what info and charts I wanted to include. Step 1: Initial concept and sketching out ideas So I went hunting for voting data, State population data and Presidential data for the past century. I figured historical US Political data would provide a rich and interesting dataset to visualize and animate.
So I set aside a couple of days to execute this idea from concept through to publishing this blog post (it ended up taking more like a week all told – time comes in small blocks these days with a newborn ? ). The timing and motivation finally came together when I recently started teaching the Data Analytics course for General Assembly, which involves Excel and dashboarding, so it tied in nicely and was a chance to revisit some interesting facets of Excel. This was a project I’ve had at the back of my mind for a long while. If it appears a little ragged, that’s because it probably is! Most likely because I’m writing this bleary eyed at 1am, between feeds and diaper changes of my 6 week old son.
DASHBOARD IN EXCEL 2007 TUTORIAL FULL
It’s not a full cell-by-cell account of how I did it, because that would require an article at least twice as long, but rather a look at the various steps and thought processes along the way. In the following post, I delve into the details of how I created this dashboard. political data, showing party trends, state populations and sitting presidents over time: Here’s one I’ve been working on recently, a data visualization of historical U.S. Who doesn’t love a dynamic, animated Excel dashboard?