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 how to conduct a quick analysis of Census
data using Excel.
There are some very simple ways to create quick and effective analysis in Excel.
A table like this with year-over-year data is very useful for you to see trends.
You can certainly create a "Line Chart" that shows the trend for each state.
But, with so many counties it would be difficult to see the trend for any particular county.
You would just get lost in your chart!
So we have a "Sparklines" to take care of that.
It can come in very handy.
Sparklines are like little charts that live inside a single cell.
They can be used to visually represent and show a trend in your data.
The sparklines can draw attention to important items, in this case population changes.
You can also use them to highlight the maximum and minimum values in a row.
So let's check it out.
So let's choose the column right after the population estimates for 2016.
Let's call it "Trends".
Notice that Excel automatically makes this part of the table for you.
Now we'll use a cell to create a sparkline to see the population trend for a county.
First thing you do is just click on the cell and then go to insert.
And when you find the sparklines, just select the line "Sparkline."
We're gonna start with this one.
So once you click, you just have to select the "Data Range."
So let's select the estimates for every available year for the first county in this table.
And then click "OK."
You'll see that now we have a sparkline showing the trends for this county.
Now we can copy the sparkline for every county.
One of my favorite ways is just to drag down.
But of course there are other methods to do that.
Just drag down for a little bit so we can see.
Here we go.
Now you can quickly see the trends for each county.
This is a lot of great insight in a single cell, but it would be also very useful if
you could see where the highs and lows are.
So let's do this.
Note in the show section that you have a variety of things that you can show in each cell.
Let's select the high point and the low point.
Do you see it now?
How we have red dots along our sparkline?
Those dots indicate the highs and the lows in our data.
There are other things you can do.
You can always change the color of your sparkline.
You can also choose different other types of lines, of sparklines.
We can choose "Column" and see how it changes and shows that in a column format.
You can choose a "Win/Loss," you can experiment with these, but the trend is easiest to view
with the "Line Chart" for this particular scenario.
There is also another fun feature here in Excel.
It's called a "Quick Analysis Tool."
It provides a trove of insights that you can edge your Census data.
You can instantly create different types of charts, including line and column charts or
sparklines.
You can also apply a table style, create Pivot Tables, Quick Links or Totals, and apply Conditional
Formatting.
Here, for this example, we will add icons, which is another way to do quick visual analysis
right from within the cell.
To experiment with this, select the cells in the first row of data.
So let me demonstrate this to you.
Once my data is selected I can use a shortcut for the Quick Analysis Tools.
On your keyboard you can press Ctrl+Q or Command+Q to access the Quick Analysis Tools.
You have several options here; I'm just gonna show you the icon set.
The icon set puts icons directly in the cell based on conditions.
In this example it tells you if the population went up, down, or stayed relatively flat.
Next let's select another row of data for the second count in our table.
And let's use again the Quick Analysis Tool.
Before this example, let's select the "Data Bars."
This shades the cell based on the data in those cells.
You can also experiment with any of the other options here.
With the color option you can look at greater, you can look at top 10%.
There's a lot to explore here, including sparklines again and charts.
These tools are actually examples of conditional formatting.
As you saw, conditional formatting rules help you to think the built-in formatting rules
can do.
To see what they look like, click on the "Conditional Formatting" in the Style section of the "Home"
tab.
At the bottom, click on "Manage Rules."
Here, you can only see the wide variety of icons, data bars, and color schemes you can
use, but you can manage how these visualizations are applied.
This concludes our overview on how to perform quick analysis of Census data using 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!