Welcome to the video tutorial on using Census data in Excel.
I am Alexandra Barker, Data Dissemination Specialist with the Census Bureau.
During this tutorial, you will learn about data access using the American FactFinder
and table formatting in Excel.
One easy way to access data from the Census Bureau is to use the American FactFinder - or
for short, AFF.
The American FactFinder is an online self-service tool designed to search a variety of demographic,
socioeconomic, housing, and business data.
After we download data from the American FactFinder, we will use the Excel to manipulate the data.
Excel is a familiar tool to many people.
They use it every day.
Excel can be leveraged to unlock the value of open data of all kinds and it's particularly
well-suited to transforming, analyzing, and visualizing Census data.
This course will show how to use Excel to format your data.
Now let's go to the American FactFinder.
So, from Census.gov all you have to do is click on the data menu and select Data Tools
and Apps.
And just click on the American FactFinder.
The American FactFinder provides three ways for you to search for your data.
Community Facts gives you access to basic information along with the most popular tables
about an area.
You can also follow the steps provided by the guided search feature.
It lets you narrow down to the most popular and detailed tables.
You can use the Advanced Search to build a query.
It gives you access to all data sets and features available in the American FactFinder.
In all cases the data from this tool can be modified prior to being downloaded.
We'll start by looking at Community Facts to find your data.
When you click on Community Facts, you are able to enter a single geography - it could
be a state, a county, a city, town, or zip code.
The United States is the default.
Then you can click on any of the topics to see the estimates.
So, let's say that our example today you want to see the population growth or decline in
the US.
We can click on Population and then choose any other sources for population data.
Let's choose the 2016 Population Estimates.
You have the single estimate here for the population of the US, but you also have some
suggested tables related to the topic.
Let's click on the Annual Population Estimates right here in the bottom.
So now you have a view of the population estimates table for the United States with trends over
time of population in the country.
You also have multiple years available.
For example, when we bring into Excel, let's say we want to look at data for every county,
not just the US, for the past six years.
So, you're gonna need to add more geographies.
The only way to add more geographies is now to move from the Community Facts into the
Advanced Search option of the American FactFinder.
So, let's do that by clicking in Advanced Search and I'm gonna start a brand-new search.
If you click on continue you will import the default geography, which is the US right now.
I'm just gonna have a brand-new search here and the Advanced Search feature will allow
me to add as many geographies as I would like to my query.
So, we'll start by selecting the Geography option.
Once you click in Geography from the list tab, I can select the geographic level I want
to work from.
In this case, let's look at all counties in the US.
So, I click in County - and I don't have to select a state, since I want to hold counties
in the US.
Once I click in All Counties within the US, I can add them to my selections, or to my
"shopping basket" as I like to call it.
So, now I can close the geography search and you have all the tables available for this
geography.
From the first few tables I have all the population estimates - you can see the source here on
the right - and I'm just gonna check the annual estimates of the resident populations from
April 1, 2010 to July 1st, 2016.
It's exact the same table we accessed through Community Facts, however now as you see I
have all the geographies that I selected available, not only the US.
So here are all the counties in the US, a very extensive list that we will be able to
see it better in Excel.
So, one of the many benefits of using Excel is that it can be used to easily transform
your data - in this case we're going to start modifying our table using the Census tools
and then we'll put the finishing touches on the structure using Excel.
You can do all of this in Excel, but this example shows some useful methods of cleaning
up the data before you download.
So, let's do that.
So, you see now a table with April 2010 Census data and all the yearly population estimates.
So, let's say we only want to download the yearly estimates excluding the April 1st,
2010 columns right here.
So, I go to Modify Table and it will allow you to uncheck the option Census and Estimate
Base - we won't need that because we're only gonna be looking at the columns with each
individual year.
And then I leave all the counties alone.
So just a very basic modification.
So, once you're done modifying your table you can just download it.
Just click "Use the data" and leave both boxes checked.
Click OK, and once the download is ready, you get your zip file.
So, I'm just gonna open and always choose the file that shows "with annotation."
This is the only way you're gonna get the table with all the details definitely in it.
So, it's a best practice to get in the habit of saving your file as a workbook right away.
So, let's first do that.
So, go to File, Save As, find the right spot in your computer, so I have my right folder
here.
I change to Excel Workbook, and then just choose a title.
In this case I'm choosing "Pop Estimates 1," which I already had saved there, so I'm just
gonna replace that file and now we can start modifying.
So, first thing let's clean up by removing the columns and rows which we don't need.
We won't need the GEO IDs, so let's right away remove the GEO IDs.
We also won't need this row here - the field name - so let me delete this one as well.
So, once you remove the unnecessary columns and rows, just make sure it's all looking
right.
You can format this as a table, so let's select the range we need for our table and just click
on "Format As Table."
Choose any style.
So, I'm picking this one.
Always check "My table has headers" and click OK.
You notice that it gives arrows next to the headers.
Click on the arrows and you'll see options for storing and filtering your data.
This all comes with being formatted as a table.
This can be very useful in finding specific information.
For example, let's say you want to find population data for Cook County, so all you have to do
here is just start searching and type "Cook."
But wait!
There are several counties with "Cook" in their names, and you just wanted the one for
Cook County, Illinois.
It would be better if the state were in its own column, separate from the county.
That way you could filter the state to Illinois and the county to Cook.
So, let's do a very common transformation with Census data.
So, let's break up a cell into multiple cells.
So, we start by cancel and click on the column B and just insert a brand-new column.
What you have to do next is just select the column that you are breaking into two different
categories, and you go to the data menu and select "Text to Columns," and you can set
up the parameters from here.
So, you see that you have some options for how you really want to just split up the column.
You can use a delimiter, meaning the Excel should split up the column based on a character.
In this case it'll be a comma.
Or you can use a fixed width, which you sometimes need for data coming off a legacy system like
mainframes.
But in this case, we are delimited by the comma that separates the county from the states.
So, all I have to do now is click Next and make sure comma is selected.
You don't have to remove tab, it's really up to you, but just click Next again and then
click Finish.
And then click OK.
And now you see that your state is no longer sharing the same column with the county name!
So, you have two separate columns right now.
Let's rename this column.
Choose "State."
Now you can do things like analyzing population data change for a single state.
For example, let's say we just want to see data for the state of Hawaii.
So, I can go here in the state and start typing "Hawaii" and you see now your table is only
gonna show the rows that are associated with the state of Hawaii!
You now have a fully functioning table.
Yet another best practice is to name your table.
This will make it obvious which data you're referring to you when you do more advanced
things like working with formulas and maps.
To name this table, simply click on the design tab, so here we go, and change the table name.
This concludes our overview about Census data using the American FactFinder in table format
in Excel.
For more video tutorials visit Census.gov and subscribe to our YouTube channel.
And don't forget to follow us on social media!
Thank you!