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:
- Signing up to receive our posts via email
- Following us on Twitter
- Liking us on Facebook
- Signing up to our RSS feed