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

 

Woohoo! Excel Spreadsheets!

I can feel your enthusiasm oozing from my screen even now as you read these words. I know, it’s hard to let go of your friend, the Word Document Table, but I promise you, there is room in your life for Excel. It may not be your friend now, but after a few more tips and tricks from us, you’ll wonder how you ever thought you could live without it in your life.

Keeping track of your youth attendance can be challenging. You may have tried sign-in sheets, registration or just actual counting. With smaller youth groups and programs you can use the youth’s name, maybe by having a registration, sign in sheet or by taking attendance. Then you can put it directly into an Excel spreadsheet and track attendance of a youth or all your youth over time.

You can also track numbers for larger youth groups. Do the count as normal and then input it into a basic Excel Spreadsheet. Just like your smaller youth group and program counterparts, you’ll be able to generate charts and graphs that map out your attendance.

You can also monitor attendance over many years in this way. By keeping track of the attendance of young people at your spring and fall youth retreats or summer youth camps, you can watch the attendance grow or dwindle. You can even keep track of attendance by session topics, which may over time help you know which sessions youth are finding more engaging.

Here we go, step-by-step, on how to create a basic Excel Spreadsheet for tracking youth attendance.

To keep track of these steps, download the sample template for youth program attendance.

1. Open a new Excel spreadsheet

2. In the first row, fill in the important information you need to capture. In our example it’s:

• First name
• Last name
• Parent’s name(s)
• Gender
• Address
• Phone number
• Date of birth
• Age
• Grade

Then include the dates that you need for your program. In our example, I’ve included every day from September 1, 2012 through September 30, 2012. However, you can also list dates by week if you only meet weekly with your groups (like on Sundays, Wednesdays or Tuesdays). Finally, put a total column at the end.

3. Fill in the information about your youth. In our example, it’s Jane Smith and John Roberts and put a 1 for each date that they attended sessions. If they didn’t attend just put a 0 or leave it blank.

4. Using ‘Auto Sum’, you can tally the total number of times Jane and John attended your program during the month of September. By adding a ‘total’ row at the bottom of the list of youth, you can also track how many youth attended on each day you had sessions. This can be helpful because you may find that you should move your sessions from a Tuesday to a Thursday based on attendance tracking.

5. You can also track age and grades of the youth in your programs by using the same techniques. You include all the possible ages and grades of the youth in your programs and put a 1 under each age or grade for each youth.

6. You can use colors to help you identify different weeks, total columns and rows or grades, ages or gender.

7. You can sort data using the ‘sort’ tool and group all your youth by age, gender, grade or alphabetical order.

8. Spreadsheets can be grouped by year, month or program. You can use the tabs at the bottom to identify which year, month or program it is within the spreadsheet. For example, you could have a spreadsheet for 2012 and each tab could be by month or by program name. Alternatively, you could have a spreadsheet that’s by group and each tab is a month or year. It all comes down to preference and ease of use for you.

Now that you’ve gathered all this information, you can start producing charts and graphs of youth attendance based on age or based on grade level.

Questions: Do you track data on your youth; if so, what kinds and how? Is this spreadsheet helpful? Please ask any questions or share your thoughts 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