Using Excel to Visualize Early Childhood Data

Visualizing your data can help you identify and communicate issues that are important to your community, especially in times of a crisis like the pandemic. In fact, transforming spreadsheets into charts, maps, and infographics can be one of the most powerful strategies you can use to help make decisions when time is short, and prioritizing critical.

Yet people sometimes feel that visualizations are difficult or time-consuming to make, and/or that they require specialized software and tools, or data expertise. However, the truth is anyone can make a data visualization in short time using tools freely available online or on their computer.

Using Microsoft Excel for Visualizations

Line chart showing change in number of children living in families with incomes below 100% FPL
To make a chart like this, follow the instructions below.

While there are many free online tools for making charts, maps, and infographics, the instructions below are for making visualizations using Microsoft Excel, a common (and often preinstalled) tool available with the Microsoft Office 365 bundle. An added advantage to using Excel is that much of the data you can find on IECAM can be downloaded as an Excel spreadsheet, so you can get to work right away.

For example, let’s say you want to create a chart like the one on the left, showing the change over time of the number of children five years and under living in families with incomes below 100% of the FPL in a particular municipality (or other geographic area of your choice). This is one way you could do it.

Three Steps

Step One: Download your data from IECAM’s online database.

These instructions will be for working with poverty data available on IECAM’s online database, however similar charts can be made for a wide variety of demographic characteristics.

  • Under Year check “Search data across multiple years.”
  • Under Early Childhood Service Types & Demographics scroll down and check the 0-100% Federal Poverty Level box under the “Population, by income-to-poverty rations and age (0-5)” heading. Note: you can check a different income-to-poverty ratio if so desired.
  • Under Region check the “Municipality” box (or desired area) along with the city desired (see box on right-hand side of screen)
  • Under Year check all the boxes from 2010 to 2018 (or your desired range). Note that 2018 is the latest year available from the Census.
  • Click the submit button and then download this information as an Excel file. If you get a security message, click “download it anyway.”
Picture of the spreadsheet  produced from an IECAM database search, with an arrow pointing to the "Download as an Excel File" button.

Step Two: Format your spreadsheet

  • First, be sure to click on the “Enable Editing” button on the top yellow ribbon of the spreadsheet. This will allow you to make changes, that is, prepare the data and add a chart to the spreadsheet.
  • Now save this spreadsheet for future reference.
  • Next, we will be deleting columns in order to focus on all children 5 and under (and not a specific year of age).
  • Delete all columns except “number of children 5 years and under” for each year. An easy way to do this is to click on letter of the column, and then right-click and choose delete. Or you can click and drag to delete multiple columns at once, keeping the cursor just above the selection (and using right-click to delete). You may also choose to copy the columns or numbers for this age group onto a new Excel spreadsheet.
Picture of a spreadsheet with the words "Delete all columns except this one for each year" with an arrow pointing to column H "number of children 5 and under"
  • Now, we will be deleting rows (not columns) in order to simplify your final chart’s appearance.
  • Remove the first three rows by clicking on the number one and dragging to include the second and third row as well. Right-click delete.
  • Add an empty row by clicking on the number 1 and then choosing the “Insert cells” icon > “Insert sheet row” from the top ribbon. A row should now appear at the top of the spreadsheet.
Picture of a spreadsheet with the instructions "Delete first 3 rows, click on the "1" then insert and empty row at the top, with an arrow pointing to the insert icon.
  • Now, type the year of the data above each column (be sure to check the numbers are correct by cross-checking with the original Excel sheet you saved). Your final spreadsheet should look like this:
Picture of an Excel spreadsheet with two rows: a date (year) row and a row with numbers in each cell (the number of children age 5 and under at 100% FPL).
  • Be sure to save your spreadsheet. If you are creating a new spreadsheet, it should look similar to the picture above (note that the picture above does not capture columns H, I, J, and K).

Step Three: Create the visualization and refine the details

  • Now, highlight your table by clicking and dragging, starting with the empty upper left hand cell. Be sure to only highlight cells in the first two rows, ending with the 2018 column.
  • Click on “insert” on the tool ribbon upper tabs and click on the line chart icon in the charts section. Choose the first chart on the upper left. This will insert a line chart into your spreadsheet. If you want an even quicker chart, skip this step and when you have your entire spreadsheet highlighted, hold down the ALT and F1 keys at the same time. Excel will automatically insert a chart type for your data configuration (in this case, a bar chart will show up, which you may choose to keep).
A spreadsheet with two arrows: one pointing to the insert tab, and the other pointing to the line graph icon in the charts section of the tool ribbon.
  • You are now ready to work on the details of the chart.
Picture of the chart produced by Excel, on the lower half of the spreadsheet.
Excel automatically generates this chart from the spreadsheet
  • Double click on the title area to change the title of the chart. “Number of children 5 and under Living in Families w/Income Below 100% FPL in Champaign 2010-18” may not be catchy, but it is accurate!
  • Double click on the numbers along the left hand side of the chart to bring up formatting options. You can do the same with the horizontal axis. Clicking on the chart itself will bring up lots of options, including fill and color options. You can also click on the plus sign next to the chart for labeling, legend, grid lines, and more.
  • Tip: you can click on “Recommended Charts” on the tool ribbon to change the type of chart (say, from a line chart to a bar graph), or if you double click on the chart itself, you can choose different designs from the tool ribbon.
  • Right click to copy to Word, PowerPoint, Publisher, or more, then save. If you save a copy of the spreadsheet, the chart will be saved as well.
  • Final note: don’t forget to cite your data source, either on the chart itself, or directly below.
Line chart showing change in number of children living in families with incomes below 100% FPL
Data Source: IECAM/U.S. Census Bureau

If you have questions about any step in this process, do not hesitate to contact us at iecam@illinois.edu. Happy visualizing!