The only experience I have is my own. If you use other tools more effectively, I applaud you. Keep it up.
In my experience, a spreadsheet is the most efficient tool for tracking large amounts of data. Our church uses a database for obvious things like member management and financial tracking, but for smaller weekly stuff, I’m using a spreadsheet.
Create the Document
Create New Document. The MAIN spreadsheet I use weekly is what I call my “Music Tracking” sheet. Simple. It’s saved as a favorite in my Google Drive, and it’s bookmarked in my browser, so I type a few shortcut keys and pull it up: Command-T (on a Mac, Ctrl-T on Windows) opens a new tab, and “M-U-S-” loads the spreadsheet.
If you do not have a tracking spreadsheet, create one and save it in your Google Drive (or Microsoft Excel online, or Apple Numbers online).
Title & Favorite. Rename it something simple, and, if you are to use it weekly, “Star” it as a favorite document.
Set Up Tabs (Sheets)
Rename. At the bottom left of the spreadsheet, rename the sheet.
Add Sheets. Add a new sheet for every music item you would like to track. For example: Hymnal #s, Openers, Choir, and Special Music.
Set Up Columns
Within each sheet (we’ll use Hymnal as our example), think of each column as a box you might fill out in a database or form. i.e. First name, last name, DOB, etc. Ideally, each column should have only one entry, to make sorting easy.
Freeze Row One. In order to view your column titles even when you scroll past, choose View> Freeze> 1 Row from the top dropdown menu.
Add Descriptive Titles. If you are the only one using your spreadsheet, this does not matter as much. However, if you share this document with anyone else, it will help them to have descriptive column titles so they are not wondering which content goes where.
Keep the titles as short as possible without losing clarity. For example, our church switched to a new hymnal, but our orchestra still uses the old hymnal (better orchestration) when possible. We have a column called “Song #(Living Hymns)” AND a column called “Song # (The Hymnal)” to distinguish between the two.
Sort Like Crazy. Use the alphabetical sorting feature to find your information. This will rearrange the ENTIRE spreadsheet sorted by that ONE column. It will not rearrange the ROWS, so your information is always safe.
Sort by SONG TITLE
Sort by SONG NUMBER
Search Like Crazy. Or, use the SEARCH function to find what you need. Press Cmd-F (Ctrl-F on PC) to “Find” a word.
Add Content
Add and adapt your content as you go along. Here’s the main content we input on our spreadsheets. Feel free to copy as much as you would like:
“Hymnal #s” Sheet
The content for each column is as follows:
Song Number. Use your hymnal or an index to input the corresponding numbers.
Date. Our church sings through as much of the hymnal as we can, rotating through the songs over the course of almost a year. I input the DATE and SERVICE in the field (i.e. “04-14 am” for April 14 morning service). When we have sung through most of the hymnal and I want to start repeating songs, I do the following:
- “Close” the column out with the last date (For example, we might start on May 1, 2015 and sing through the last hymn on March 1, 2016. The column would be “open” until March 1, 2016.)
- Drag that column to the “ARCHIVE” section of the spreadsheet
- Create a new column with new dates
- Start over
NOTE: I include the “AM” so it does not auto-format to date format. You could use an additional column for “Service” if you want that further option. Also, I include the leading “0” so that when it sorts alphabetically, it does not put “10” before “4.”
Title. This is the song title as it would be found in the index.
# of Vss. We add this information so our orchestra knows which verse to go down on.
Type. Most of the music in the hymnal is congregational, but other types are OPENER, CHRISTMAS, CHORUS, or INVITATION. This column is more broad and final than the Category columns.
First Line (verse). Our hymnal index included the first line of each verse, so a secretary indicated this in our spreadsheet.
First Line (chorus). Our hymnal index included the first line of each chorus, too, so a secretary indicated this in our spreadsheet.
Category I. Some songs can have more than one topic. These category columns are like tagging a song with different styles like PATRIOTIC, THANKS, NEW YEAR, BLOOD, RESURRECTION, EASTER, MISSIONS and more.
Category II. The second column is for songs that fit more than one category (i.e. an “EASTER” song might also be a more general “RESURRECTION” song.)
Song # (old hymnal). When we got a new hymnal, we kept the old information archived for reference. Our orchestra prefers the arrangements in the OLD hymnal, so we refer often to the previous hymnal’s books.
Title (old hymnal). See note above.
ARCHIVE–>. This is an empty column with a title only (showing where to archive the information.)
Archived dates… . When we completely sing through the hymnal, we “close” and archive the date column, create a new column and start over.
Medley. Some songs are arranged in our hymnal as a medley, and are indicated with a simple number in this column. When I “Sort A to Z” in this column, it brings every row with an entry to the top of the column, and I can use a set of songs in a medley to create an unbroken song list for special occasions. NOTE: This was for The Hymnal For Worship and Celebration only, not Living Hymns.
Optional Ending. Some of our hymns have an optional ending that we use for special occasions or for choir openers. This is indicated in this column. NOTE: This was for The Hymnal For Worship and Celebration only, not Living Hymns.
Notes. Certain nuances are noted in this column for future reference (i.e. “Hold the fermata for two extra beats”)
Hymn Stories Number. We have several books of hymn stories, each with its own take on the hymn history, author, date, etc. This column and the other hymn story books columns give us a reference for each hymn in our hymnal (if they’re available in the hymn stories book). This column is a simple number, as the book is arranged in numerical format instead of date or title.
101 Hymn Stories. We used the index to input the page numbers that correspond with our hymnal.
Hymn Stories Book. We used the index to input the page numbers that correspond with our hymnal.
One Year Hymn Stories Book. This column includes the date format for the quickest reference to the hymn story.
“Openers” Sheet
Number. The number of the song in our opener book.
Title. The song title.
“Choir” Sheet
Music Count. Not a very descriptive title, I know. I’m breaking my own rule because I am the only one who updates this sheet. THIS column is to inventory all our purchased octavos.
Copies Count. This column inventories the copies we have made. I use this column as a reference for either retiring the song or replacing the copies with purchased octavos.
Purchased Licensed Prints. When the publisher is available, we contact them to purchase licenses to print our own copies of songs with the license number and a note from the publisher indicating that it was a purchased copy. This column indicates how many licenses we purchased and printed ourselves.
Location. We store our books alphabetically on shelves, our individual songs in file cabinets, and our seasonal music in storage boxes elsewhere (Christmas, Easter, patriotic.) This column indicates where the song is stored.
Book Title. If the song is included in a book, this is the title of that book, and it is stored on the shelves.
Type. This column indicates the various categories a song might fall into: patriotic, Christmas, Easter, fast, 3-part, etc.
Song Title. This is the title of the song as found in the book or on the music. We sort “The…” songs alphabetically under “T” because the spreadsheet sorts them this way.
Date (year). We input the date we are scheduled to sing this song. When we sing through our whole repertoire, we create a new column and start over.
Notes. This column includes all reference material: when we purchased more music, what key a song is, what sections we skipped, what arrangement we used, etc.
Link. If we can find the link to purchase online, it is copied to this column.
Arranger. Since multiple arrangers and publishers have various versions of the same song, we differentiate them by including the publisher info here.
“Special Music” Sheet
Date. The date they are scheduled to sing (or play).
Service. Which service the special will be (Sunday morning, Sunday night, Wednesday night.)
Song. This is tracked so that we do not overlap special music, or try to go from memory (“I think ____ sang that song 2 months ago. ??”
Book. If the song is from a book, it is indicated here. This helps for future repeat songs.
Notes. Any sort of note can be added here: who was in the trio, what key they sang it in, whether they used a capo, what line they repeated for the ending, etc.
Key. Especially for smaller groups learning songs by ear, this column is helpful to remember what key a song was done in in the past.
Type. This is the type of group it is (or was.)
DOWNLOAD THE TEMPLATE
For the ENTIRE SPREADSHEET as I use it weekly, please >>CLICK HERE<<
EXAMPLE
“OOPS! I messed my spreadsheet up. What do I do?!”
Never fear. Google has you covered with their amazing recovery feature.
There’s a trash can that holds all deleted files: Drive.google.com/drive/trash
OR: there’s a file history that shows every change you have made to the file itself. See File> See Revision History
“Does it work on mobile?”
Yes. Quite handily, too. Mobile versions are OK, but not as good as the dedicated apps.
I hope this is a help to you. For regular updates, subscribe to my email list (you’ll get a free bonus e-book). Let me know your thoughts in the comments below:
![ETHICS: Values In Action [Free Workbook Download]](https://i0.wp.com/ryanrench.com/wp-content/uploads/ryanrench.com-article-ethics-workbook-180x120_c.jpg)
