Make a Ranked List in Excel

Our presbytery recently did a survey with its major committees to help prioritize our ministries and missions. This will help us focus on vital areas when we need to make budget cuts in the near future. This is a great use of Excel to compile, tally and sort all the data.

Start a new Excel spreadsheet. In the top-left cell write “Items” (or whatever the items are called, such as “ministries”). Down the A column list all the items to be prioritized. Each column after can be a person or group involved. The far column will be the Total. Write each column’s name across the top row.

Fill in the numbered rank each participant gave to each item, where 1 is the highest rank. (You can do it the other way around, too, but we’ll do 1 has highest for this example.)

In the Total column, write “=AVERAGE(” (without the quotes, with the open parenthesis). Click and drag across all the numbers in the row you’re on. Then type “)” to close your parenthesis. Hit Enter.

Since these are ranked items, you may want to hide the decimal places and only show whole numbers. Right-click the cell and choose Format Cells. In the list on the left, pick Number. There should be a box labeled “Decimal places:” – change the number beside it to zero (0).

Finish filling in all the rankings you’ve collected. Then select the cell you created a total for. Click the little square in the bottom-right of the cell’s border and drag it down the entire Total column, so it covers every row you’ve put data in for. It should automatically fill the rest of your totals in.

Last, select all the cells in the Total column (except the header cell that says “Total”). Look on your toolbar up top for a “Sort & Filter” button. You want to sort from A to Z. If it asks you to expand the selection, say yes.

And that’s it! Sorting will re-order your list so the highest ranked item moves to the top. All the ranks you typed will move along with it, so everything stays together.

If you have a lot of participants, you could create a separate sheet for each group, then on your original sheet instead of ranks you’d do another average. When you get to the part where you select cells, click over to the group’s sheet and select from there. Excel can do its formulas across multiple sheets – even across multiple files!

Track Attendance Trends in Excel

Here’s a walkthrough for creating a line graph in Excel (if you don’t have Excel there’s also Google Docs, described below) to track attendance.  It’s a little longish, but I think a lot of folks want to do something like this.  I’ll focus on Sunday worship, but you can use it for just about anything from small classes to total membership.  It’s useful for understanding the impact of decisions you make primarily, as well as uncontrollables like seasons, weather or local events.  If you track several things, you can see if big worship attendance impacts mid-week participation and vice-versa, for instance.

What You Need

First you need some data!  Odds are you’ve got at least one or two ushers.  Make a little sheet with all the categories of people you want to track.  Figure it out based on what your mission in worship is.

For our example we’ll track worship leaders and helpers (“Leaders” for here on), worshipping adults (“Adults”) and children under 16 (“Children”).  Beyond this, Leaders will be a total of the choir, minister, and other assistants (greeters, ushers, sound technician, pulpit assistant, etc.), and we’ll track men and women within Adults.

A simple half-sheet with your categories and blanks is plenty, so your ushers know what to do.  You can drop it in with the offering or have them leave it on someone’s desk.  If there’s a lot to count, you might have each usher count one section.

The Spreadsheet

Time to fire up Excel! (Skip down to Google Docs if you don’t have Excel.)  Excel is awesome for doing math and doing things in patterns, which is exactly what we’re going to do.  Note all the columns are labeled by letter, and the rows labeled by number.  Each place they cross is a “cell.”  Excel understands everything by these letter-number coordinates, so that’s how I’ll explain it.

Step 1, dates: The first thing you need is all the Sundays of the year down the A column (the far left).  In cell A1 just type “Date” and hit ENTER.  In cell A2, type the first Sunday of the year (for 2008 that’s “Jan 6″).  Excel will take a little liberty and reformat it, probably to “1/6/2008″ or something.  It can be changed, but I’ll skip that today.  Type all the first month’s Sundays and the first Sunday in the second (for 2008 that’s Jan 6, Jan 11, Jan 18, Jan 25 and Feb 3).

Now click and drag from the first Sunday to the last that you just typed (so the outline goes around all of them and nothing else).  Then click and drag the bottom right corner of your box (it should have a little square).  You’re going to drag it straight down, and Excel will fill in the rest of the Sundays automatically.  If you let go in the middle you can select the last couple months and keep going.  If you make a few extra select them and hit the DELETE key.

Step 2, categories: In B1 type “Total” for your total headcount.  Starting at C1 and going across, type each of the categories you decided to track.  Subtotals should go first.  For our example, we’ll put Leaders (C1), Adults (D1) and Children (E1) first, then Pulpit (F1; for everyone on the chancel leading worship), Choir (G1; including the director), Helpers (H1; ushers, greeters, etc.), and Men (I1) and Women (J1).  Different orders are possible, but this is easiest for what we’ll do with it later.

In Excel, typing “=” first means you’re starting a math function. There’s lots of help on functions in Excel. I’m just walking you through a couple of them, starting with SUM (which adds up the selected cells). After the function’s name, most functions look for a range of cells. Write these in parentheses. You can do a range using the colon, or individual cells separated by commas.

Step 3, Filling it in: Start by filling in your smallest counts first on row 2.  In the example, that means F1-J1.  Once you’ve got that, select the cell under one of your totals (we’ll start with Leaders) and for our example I’d type “=SUM(F1:H1)” which covers cells F1 through H1, all the subtotals under Leaders.  For Adults I’ll do the same, typing “=SUM(I1:J1).

Select all the cells you typed a function in (all your totals), then click and drag from that bottom corner all the way down your chart to the last Sunday of the year.  Now whenever you type data into a new Sunday it’ll figure your totals automatically!

Making the Chart

Excel charts have lots of options, but we’ll keep it very simple.  Select cell A1, hold CTRL+SHIFT and hit the DOWN ARROW, which should select all the dates in your chart.  Now hold SHIFT and hit the RIGHT ARROW to also select all the Totals.  What next depends on which version of Excel you’ve got.  In most versions, you should be able to right-click and select Chart Wizard, Insert Chart or something similar.  In Excel 2007 you’ve got to go to the Insert tab up top and pick the chart type.

From here Excel will walk you through the basics.  You want a line chart, with the dates across the bottom and the line showing your Total.  You should have your first chart ready!

But it Doesn’t Look Right!

There’s a lot of variables from here out as far making it look just right, and a lot depends on what you want.  I’d be glad to help you work through some of that.  Comment with your particular situation and I’ll try to help.  If it’s all too much, e-mail me (see the About page) and I can probably send you a ready-to-go Excel file.

Don’t Have Excel?

Never fear!  Google has a free application called Google Docs (www.google.com/docs).  You can make spreadsheets, including charts and graphs.  I don’t want to spend the time here detailing it out, but if you want to try it feel free to get in touch with me and I’ll help out!