Over the past few weeks we’ve been showing you the many ways in which you can use Excel – and subsequently the charts and graphs feature in Excel – to track youth attendance.  It’s valuable for many reasons, but two main ones are for proving outcomes in monthly and annual reports and to provide quantitative data for potential or current funders and donors.

Our final charts and graphs today will show individual youth attendance. This is particularly useful if you have an ongoing program like a faith-based youth group or any other kind of youth club. You’ll be able to see what Johnny’s attendance was each year he was in your program, or if Jane’s attendance steadily increased or when Billy’s attendance dropped off after his parent’s divorce.

If your data will be used outside of your organization remember confidentiality and use codes to identify the youth in your program. In our example, we’ve just numbered the youth 1-16. However, if you’re using this purely for internal data you can also change the axis labels to show youth names.

Firstly, a column chart to clearly show side by side the attendance of your youth from year to year.

1)  Open the youth attendance spreadsheet

2) Select or highlight the data from Column BU Row 2 to Column BU Row 17

3) Click the ‘insert’ tab at the top

 

4) Choose ‘column’ and click on the first 2D column chart

5) Right mouse click on the chart and choose ‘select data’ from the menu that appears

6) Under Legend Entries (Series) Select ‘Series 1′ and click ‘Edit’

7) In ‘Series Name’ type ’2012′ and click ‘ok’

8) In the box that appears now click ‘edit’ under the Horizontal (Category) Axis Labels

9) A data range box has appeared, select Column A Row 2 through Column A Row 17 and click ‘ok’, then click ‘ok’ again. (This is where you can choose to use student names if you want and you would instead choose Column B Row 2 through Column B Row 17)

Now you have the basics of your column chart (n.b. you may need to scroll along the spreadsheet to see the graph displayed).

To add 2013′s data to the same line graph, follow a similar process:

1) Right mouse click on the chart and choose ‘select data’ from the menu that appears

2) Under Legend Entries (Series) Select ’2012′ and click ‘Add’

3) In ‘Series Name’ type ’2013′. In the ‘Series Values’ box, delete the symbols there. Go to the bottom of your spreadsheet and click on the 2013 tab, select the data from Column BT Row 2 through Column BT Row 17 and click ‘ok’

4) In the box that appears now click ‘edit’ under the Horizontal (Category) Axis Labels

5) A data range box has appeared; click the 2013 tab and select Column A Row 2 through Column A Row 17 and click ‘ok’, then ’ok’ again.

Now you have a column chart that shows the attendance for each individual youth side by side for 2012 and 2013.

You can also easily view this same data as a line graph with markers

1) Right click on your column chart

2) Select ‘Change Series Chart Type’

3) Select the fourth line graph shown ‘line graph with markers’ and click ‘ok’

This shows you the same data as the side by side comparison of the column chart, but it might also show the relationship between youth attendance in each year more clearly as well.

This concludes our series on how to use Excel, charts and graphs to track youth attendance.

Question: How useful has this series been for you? Please share your thoughts in the comments section below.

You can also connect with us by:

1. Signing up to receive our posts via email
2. Following us on Twitter
3. Liking us on Facebook
4. Signing up to our RSS feed

 

We’re currently working our way through a series that shows you how to use Excel to track your youth work attendance and how to use bar graphs, pie charts and line graphs to create visuals of that data. These charts and graphs can be very useful in monthly and annual reports and when showing data to prospective donors.

Today we’re going to share with you the steps to creating a line graph which will give a visual of the attendance data from both 2012 and 2013. You will be able to see the two years compared to one another and it will show trends in the data over the course of the year. In the detailed steps below, we’re using our example attendance spreadsheet. Remember the spreadsheet is simply an example – your data will not have such repetitive patterns in it.

1) Open the youth attendance spreadsheet

2) Select or highlight the data from Column U Row 19 to Column BT Row 19

3) Click the ‘insert’ tab at the top

4) Choose ‘line’ and click on the first 2D line graph

5) Right mouse click on the chart and choose ‘select data’ from the menu that appears

6) Under Legend Entries (Series) Select ‘Series 1’ and click ‘Edit’

7) In ‘Series Name’ type ‘2012’ and click ‘ok’

8) In the box that appears now click ‘edit’ under the Horizontal (Category) Axis Labels

9) A data range box has appeared, select Column U Row 1 through Column BT Row 1 and click ‘ok’, then click ‘ok’ again.

Now you have the basics of your line graph (n.b. you may need to scroll along the spreadsheet to see the graph displayed).

To add 2013’s data to the same line graph, follow a similar process:

1) Right mouse click on the chart and choose ‘select data’ from the menu that appears

2) Under Legend Entries (Series) Select ‘2012’ and click ‘Add’

3) In ‘Series Name’ type ‘2013’. In the ‘Series Values’ box, delete the symbols there. Go to the bottom of your spreadsheet and click on the 2013 tab, select the data from Column U Row 19 through Column BS Row 19 and click ‘ok’

4) In the box that appears now click ‘edit’ under the Horizontal (Category) Axis Labels

5) A data range box has appeared; click the 2013 tab and select Column U Row 1 through Column BS Row 1 and click ‘ok’, then ‘ok’ again.

Now you have a line graph that shows the relationship between your attendance in 2012 and 2013.

You might see a trend in youth attendance that peaks or drops at different times of the year. By tracking the data year after year and putting it in this visual format, you’ll be able to see more easily the attendance at your youth work programs.

Question: Are there any aspects of Excel you’d like to know more about to help with your youth work administration? Let us know in the comments below and we’ll try and cover this in the future.

You can also connect with us by:

1. Signing up to receive our posts via email
2. Following us on Twitter
3. Liking us on Facebook
4. Signing up to our RSS feed

 

We’re currently working on a series about how to use Excel to track youth attendance. Last week we showed you how to track youth ages from one year to the next. This week, we’re going to show how you can use the data to create charts and graphs to track youth grades by attendance from one year to the next.

Over time, you may notice a trend in weeks that certain grades are more likely to attend or years when grade attendance fluctuated. You may want to track this if you’re planning to make changes to your group based on their grades; for example, you might be planning to split your Jr. High from your High School groups.

Alternatively, you may want to track that you have a large number of seniors who are about to graduate and may want to make even more of an effort with new youth recruitment, particularly if you run something like a youth council that counts on new youth each and every year for sustainability.

Remember: the data doesn’t have to be based on youth attendance. If you’re collecting quantitative session evaluations, the results of these could be converted to chart form in order to analyze patterns of how your youth view the sessions you’re running.

Here’s another 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. The spreadsheet has two tabs – one for 2012 and one for 2013. The chart we’ll be producing will be based on the grades of the young people at the program, so we’ll be using columns R-T.

2. With your spreadsheet open, click on “Insert” and select the type of chart or graph that you want to produce. For this example, 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 track the grades of youth in your program, so highlight the totals for each grade – in this case, cells R19, S19 & T19. To highlight the cells, click and drag so that these three cells are highlighted. This will autofill the chart data range to say =’2012′!$R$19:$T$19

5. Next, beneath where it says “Legend Entries (Series)”, click “Edit”. As this series will be displaying grades of youth in 2012, we’ll name the series “2012″ and click OK.

6. This will display the columns as 1, 2 & 3 rather than the relevant grades. To ensure the grades are listed in the chart, click on “Edit” beneath where it says “Horizontal (Category) Axis Labels”. The grades are shown in cells R1, S1 & T1, so highlight these cells in the same way that you highlighted the total of the grades in step 4, then click OK. This will now display the grades on the chart (see image below).

7. We want to compare the grades of youth in your programs in 2012 against the grades in 2013. We therefore need to add an additional entry for 2013. To do this, click “Add” beneath ”Legend Entries (Series)” and click on the tab at the bottom of the page for “2013″. Rename the series as “2013″. In the box for “Series Values”, delete the value that’s pre-populated (in our example it says ={1}). Replace it with the 2013 grade total data, by again highlighting cells R19, S19 & T19, then click OK.

8. Click OK on the main box and it’ll display your chart. You can move this on the spreadsheet by dragging it, or you can copy and paste it into another document, such as 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:

1. Signing up to receive our posts via email
2. Following us on Twitter
3. Liking us on Facebook
4. Signing up to our RSS feed

 

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:

1. Signing up to receive our posts via email
2. Following us on Twitter
3. Liking us on Facebook
4. Signing up to our RSS feed