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!