[ Music ]
Welcome to Using Census Data with Excel.
I'm Adam Heckman.
Let's say that you wanted to use Census to do some
comparisons of population growth or decline
over
a couple of consecutive years.
Here I am at data.census.gov,
and you can see that it has some powerful searching capabilities
that make finding this data easy.
So we'll type that into the search bar.
And
here are
the tables
that
meet my search.
I'm going to do some further analysis of some of the
underlying data.
So let's take a look at the estimates
of the resident population
by state.
And here I can see all the states,
and notice that I can customize this table as well.
For example, I can change the
geographic level or drill into one specific state.
From here, I can also download
the data
to my machine.
Downloading gives you the entire table
as is, without customization.
Excel will give you the output
that captures any customizations that you make
to the table
to display on data.census.gov.
So for me,
I'm not going to make any changes,
so either one is acceptable. So I'll click on
Export CSV.
And down at the bottom,
open this up in Excel.
Now, once it opens, you can see that it's
pretty bare bones. It is a CSV file.
It gives me the Geographic Name,
which is the state; gives me the Date
Description, which is
the date of the
census that was taken; and the Population.
Once you download it into
Excel,
now you can start playing around with it.
Now you can start
making it look the way you
want it to look. For example,
right now, you take -- you see that each state appears
multiple times. That's because in each state,
we have a row
for each census
in the year in question.
Slicing and dicing data in different ways
is a great use case
for Excel pivot tables.
So let me go ahead and
click on Insert,
and then we'll click on
Pivot Tables
in the Table section.
And here I have my Create Pivot Table button
and dialog box.
And Excel is smart enough to predict the range
that I want to use, as is the case here.
And note that you also have the option
of where you want to put your new pivot table.
I can put it in a new sheet,
or I can put it in the sheet that I already created.
In this case, I'll just accept the defaults.
Now, over here,
you can see
where
I have sort of a canvas upon which I can
build
my pivot table or a blank slate.
So
what I want to do here
is
I want to take -- notice here that it's taken the
names of the column headings and put it here
as my fields. And what I want to do
is I want to get
data for a couple of years --
let's say years 2018 and 2019 --
and have them by state.
So
since we want our rows
to be the Geography in this case, I'll take the Geography
and Area Name and drag that to Rows.
And when I do that,
you can see, on the left,
it's starting to build out my pivot table for me.
And since I want to see the Population estimate
as my values,
I'll drag Population
as Values.
Again, I can see that
the pivot table is starting to get built here.
Finally, I want my columns to be
for the years for which we want to see
the population estimates.
So I'm going to take the Date Description
and put that in the Columns.
Now,
this gives us
every year from 2010 on.
I may not want to see every year.
I may just want to see
a few years.
The dates from the Census Table are in the Date Description field.
So,
I can take that Date Description field
and bring it into the Filter box,
and now I can filter out
which ones I specifically want to see. So I'll say
Select Multiple Items.
And now I'm free to select a few. Let's look
at 2017, 2018, and 2019.
I'll check those.
And now I'll put that back as my Column Description,
and you can see Population Estimates from 2017,
2018,
2019,
and a Grand Total.
There's quite a bit of data visualization that you can do
in these pivot tables just as they are.
For example,
I can take this data
and
say Insert,
and then Recommended Charts.
And you can see if maybe I just want to --
it's recommending that I build a
column chart
to see all 50 states for all three years.
And I didn't really have to do much to get that.
Now,
what we're going to do here
is
we're going to get rid of that column chart.
And
you have the option
to take this data and view it
exactly the way you want it.
So we're going to make some changes to this.
For example, you can see that
automatically, by default,
pivot tables is giving me a grand total on the right
for all of my rows
and a grand total for my columns, I might not want that.
So I can right click on the table,
click on Pivot Table Options,
and here I have multiple tabs.
I'll go to Tools and Filters,
and I'll turn off
grand totals for rows,
and I'll turn off
grand total for columns.
So now I have
the table looking exactly the way I want it.
Let's give this a name.
We'll call this rename
State Pivot Table.
And let's keep this as is,
but we're going to go ahead and take the data
and copy it to
another worksheet
to do some other stuff with it.
So let me
create another worksheet.
We'll select all of the data,
and I'm using the shortcut Ctrl + A to select it.
And I'll copy this.
And I'll go into my new sheet, and I'll say Paste Special.
And when you say Paste Special, you can
see that there's different ways that you can
paste it.
You can paste it
with the source formatting or
the destination formatting.
In this case, I just want to paste the values,
not the formatting.
So, I'll say Paste Values.
And now I have just the raw values.
I can get rid of this
top row here.
Next,
we're going to format this as a table.
It's always a good best practice
to do two things in Excel when you're working with data.
The first, is to
format
raw data like this as a table,
and I'll pick my favorite style here.
And I'll say that my table has headers up here.
And we'll change this
from Row Labels to State,
and we'll just give this a little bit of a shorter name.
We'll say 2017 Population,
2018 Population,
and 2019 Population.
That's a little bit easier to look at
and a little bit easier to read.
The other thing
that you should get in the practice of doing is
if you bring something in as a CSV,
you should save it as an Excel workbook
so that you have all of the features
of Excel at your disposal. So I'll say File,
Save As,
and I can keep the name as it is.
All I have to do is click on this
drop-down
and choose Excel workbook.
And this will change the extension from CSV
to the Excel extension,
which is XLSX.
In the next video, we're going to go take this and
take a look at how we can do some
data analysis and some visualization on the same data.
[ Music ]