[ Music ]
Welcome to Using Census Data with Excel.
I'm Adam Heckman.
In this module, we're going to look at data analysis.
There's some very simple ways to
create very quick and effective analyses in Excel.
Later, we're going to talk about more advanced data
analysis, but in this case we'll focus on
some easy ways to quickly
derive insights from census data.
New to Excel 2019 is something called
"data types".
With data types, you can get more specific
geography data.
Geography
is a data type,
so I'll click on the state
and data
and choose the geography data type.
And it's considered
a linked data type because what it does is it
brings you information
from online data sources
on your geography into your workbook,
so this allows me to bring back very rich,
interesting information that I can work with
and refresh later.
Now you'll notice that when I
chose geography,
there's a little icon
that was added to the states.
This tells me that
this
column has
data from an outside data source, and if I click on "Show Card",
I can see more information
on, in this case,
the state of Illinois. There's the seal,
talks about the elected leaders,
some demographic data,
some economic data,
population data,
things of that nature.
And so this is a very easy way
to get rich data into your spreadsheets.
There's also data types for stocks.
Here's more coming soon.
Now, with a table like this,
with year over year data,
it's very useful to look at trends.
You can certainly create
a line chart that shows you the trend for each state,
but with 50 states here,
there's probably some better ways
to see the trends for any particular state
right in the spreadsheet itself and this is where sparklines come in handy.
Sparklines are like little charts that live
inside a single cell,
and they can be used to visually represent
and show a trend in your data.
Sparklines can draw attention to important items,
in this case, population changes,
but we can also use them to highlight the
minimum and maximum values in a row.
Now I can
click on Insert
and Insert Sparklines
from the Sparklines section
or
I can click anywhere in my table
and hit Ctrl+Q,
which gives me the shortcut
for a lot of quick analysis tools, one of them being
sparklines.
So I can choose the type of sparkline I want.
I have line charts, column charts,
and what's called win or loss charts,
but since I'm looking at trends, I'm just going to
choose the line chart here.
So this placed the sparkline in a
new column and we can call that column
"trend".
And now I can very quickly, at a glance,
for each state,
see how that population
is trending for the last 3 years.
So here you're only looking at 3 years' worth of data.
Imagine if you were looking at a decade or more of data.
Sparklines could tell you a lot at a glance.
Now you can quickly see the trends for each state,
and that's a lot of great insight in a single cell
but it's also very useful to see
where are the highs and where are the lows.
To make that more obvious,
what I can do
is
click on the Sparkline tab
on my ribbon.
I can say,
tell me the high point and tell me the low point,
and I can change the marker color
so that the high point is a different color
from the low point.
Let's make the
high point
green
and the low point red.
And now you can very easily see
the high points and the low points.
And again, this is only one of the chart options
that you can use with sparklines.
When I hit
Ctrl+Q
before, you probably noticed that sparklines weren't
alone here, there's a number
of Quick Analysis Tools in addition to sparklines.
In fact, Quick Analysis Tools
provides you a trove of insights that
you can add to your census data.
You can instantly create different types of charts,
including line charts and column charts and stack charts.
You can also
go back and apply table styles,
and in our case,
what I'd like to do here is choose
data bars so I can see data visualization
within the cells themselves.
So
here I can compare the populations
for each state and you'll notice here California
is very big,
so
the bar
is very long.
And other states like Maine or Montana
are not so heavily populated,
so their bar is shorter.
There's a number of other Quick Analysis Tools that you
can play around with, like
the icon set
and the tables, etcetera.
Go ahead and
play around with these and see what is useful for your types of
analysis that you're doing with census data.
[ Music ]