In this blog, I’ll show you some very effective and efficient ways to get vital information about all states of the USA, and eventually create visuals, then interactive visuals, then an interactive application online. All from starting with the basic names of states.
I wish every kid and adults would have this type of application available freely in one place. Well, let’s build it! And I’ll walk you along the way on how to build such as an application from zero.
Quickly now let’s take a look a short animation of the interface I envision (we’ll get into each page’s details in this blog series as we continue)…
Let’s start with the names of all the states, and we’ll include D.C. as well.
In Excel, we we’ll have Alabama to Wyoming in a single column to start with. Let’s say it’s in column A and data starts at cell A2. We’ll then add some basic information available online such as bordering states, the year of statehood, the highest peak’s name and elevation.
So, a simple starting row will look like this:
State | Bordering States | Statehood | Highest Point | Elevation (Feet)
Alabama | Florida, Georgia, Mississippi, Tennessee | 1819 | Cheaha Mountain | 2413
Good start, but I want a lot more information. Specifically, I want to convert the names of bordering states to a number (from just text), so for example, the text above for Alabama would be 4 for its bordering states. I also want its capital, area in square miles, population, population density, largest city, median household income, population growth/change, timezone.
And I want to get those automatically updated with the latest data without having to enter them myself!
So, I’ll add the new columns with the above headers under which the data will be filled in.
Then, I will convert the name of the state Alabama into a special data type: Geography. In Excel, this is done from Data menu. Once I’ve turned the state’s name into this type, I can use formulas to get vital information via Excel.
For example, to get its Capital, I use =A2.Capital in the cell under Capital column. For area, use =A2.Area. For timezone, =A2.[Time zone(s)], =A8.[Population change (%)] for population growth, etc. (Refer to your Excel documentation for the full list).
Great! Because Area gave me in square kilometers, and we typically use miles in USA, I’ll just convert the returned values using simple algebra to convert them. (Divide the per square kilometer value by 2.59)
Remember I wanted to also convert the names of the bordering states to actual numbers? With this simple formula, we can turn the text such as “Florida, Georgia, Mississippi, Tennessee” to 4, =LEN(L2)-LEN(SUBSTITUTE(L2,”,”,””))+ ISNUMBER(SEARCH(“,”,L2))
Where L2 contains the text. This works perfectly for my needs whether or not there are zero or any number of states bordering!
So, now we have a row for Alabama as below:
Do the same for the rest of the states down the rows using the same method and we have a full list!
The only things we have to enter manually for each state are the ones that Excel won’t give you automatically, such as highest peak, their elevations, statehood which I covered above.
After all the formulas are copied down for all states + DC, we have enough dataset to create some meaningful charts already, such as:
and 
But we will do even better! Because of the width of the visual, it’s not always easy to read tiny states’ details (zoom in/out would be useful, so would dynamic filtering!). And also remember, we want to turn this into an interactive web application. I could continue with charting just in Excel and indeed make interactive and more sophisticated charts, but the idea is to move this into the cloud for anyone with or without Excel installed, or without Windows, and be able to run on any device (read: mobile devices including phones). In the next part, we’ll continue to enhance this and achieve that goal.
This is a two-part blog series. To follow along, see part 1 (this) followed by part 2.
