Last week we showed you how you can use Excel to track youth attendance. This week, we’re going to show how you can use that data to create charts and graphs of youth attendance.
This can be especially useful when producing end-of-year / end of youth program reports, as the charts and graphs can be easily inserted into the reports. This displays the data in a more interesting fashion and can help the reader identify patterns of attendance.
The data doesn’t have to be based on youth attendance though. If you’re collecting quantitative session evaluations, the results of these could be converted to chart form in order to analyze patterns of how young people view your sessions.
Here’s a step-by-step guide to creating charts and graphs using Excel:
1. Download a copy of the youth attendance spreadsheet, as we’ll be using this in the examples below. There are two tabs on the spreadsheet – one for 2012 and one for 2013. The chart we’ll be producing will be based on the age of the young people at the program, using columns N-P.
2. With your spreadsheet open, click on “Insert” and then select the type of chart or graph that you want to produce. In this instance we’ll be creating a “Column” chart, which will be a “2D column”.
3. This will display a blank rectangle on the spreadsheet. Right-click on the rectangle and choose “Select Data”. This will bring up the box displayed below.
4. Click in the box where it says “Chart data range”. As mentioned above, we’re going to be tracking the ages of youth in the program, so we’ll highlight the totals for each age – in this case, cells N19, O19 & P19. To highlight these cells, just click and drag so that these three cells are highlighted. This will autofill the chart data range to say =’2012′!$N$19:$P$19
5. Next, beneath where it says “Legend Entries (Series)”, click “Edit”. As this series will be displaying ages for youth in 2012, we’ll give the series name as “2012” and click OK.
6. This currently displays the columns as 1, 2 & 3 rather than the relevant ages. To include this data, click on “Edit” beneath where it says “Horizontal (Category) Axis Labels”. As the ages are displayed in cells N1, O1 & P1, highlight these cells in the same way that you’d highlight the total of the ages in step 4, then click OK. You should now be able to see the ages displayed on the chart (see image below).
7. We want to compare the ages of youth in your programs in 2012 against the ages in 2013. We therefore need to add an entry for 2013. To do this, click “Add” beneath “Legend Entries (Series)” and then click on the tab at the bottom of the page for “2013”. Rename the series name as “2013”. In the box for “Series Values”, delete the value that’s in there (which in our example says ={1}). Replace it with the 2013 age total data, by again highlighting cells N19, O19 & P19, then click OK.
8. Click OK on the main box and you’ll see your chart. This can be moved on the spreadsheet by dragging it, or can be copied and pasted into another document (like a report in Word or a Powerpoint presentation).
Question: Do you produce charts and graphs for your youth programs? If so, what data do you collate? Let us know in the comments below.
You can also connect with us by:
- Signing up to receive our posts via email
- Following us on Twitter
- Liking us on Facebook
- Signing up to our RSS feed