You teach because you love music. Not because you love keeping detailed budgets, organizing your repertoire library, or monitoring instrument rentals. As tedious as that work can be, it’s vital to the success of your program.  Fortunately, with the right knowledge, you don’t have to spend all your time on it.

Google Sheets (Google’s version of Microsoft Excel) makes mastering and managing all of that data easy. Plus, you never have to worry about losing valuable information should your computer crash because it’s all hosted safely online.

Using Google Sheets effectively means you’ll never have to put data in more than one place again or waste hours sorting through your repertoire library for just the right piece.

Become a Google Sheets power user – and master your music program’s data – with these seven tips.

Note: If you’ve never used Google Sheets before, that’s okay! Chances are you’ve used Excel, and Sheets is nearly identical in many ways.  If you’re brand new to using spreadsheets, you’ll catch on quickly! You may want to learn the very basics with this tutorial from SmartSheet before you begin.

1. Good Organization

If you’re working with a lot of information and you have to sum it up in multiple ways, you need to keep that information organized. (Hats off to those educators managing 300+ students, 40-year-old-repertoire libraries, and seemingly endless gift-wrap fundraisers. You’re doing good work.)  

Google Sheets allows you to create documents with multiple tabs. It also gives you the flexibility to work with one data set across all of them. Put all of the numbers in one tab, then use formulas, charts, graphs – all the fun stuff – in others.

You should never need multiple spreadsheets for the same data. Google Sheets will do all the hard work for you, I promise.

In most cases, I like to use a dedicated tab in every worksheet for raw data. This is the only tab of a spreadsheet where I input information, and also the only one I almost never reference; it simply serves as a data dump zone.

Then, when I want at-a-glance summaries, I’ll create new tabs to reference the raw data using cross-tab formulas, pivot tables, and charts. That way I’m only seeing the information I need at any given time–and nothing more.

2. Keyboard Shortcuts

Keyboard shortcuts are likely the most underrated tools in Google Sheets. Instead of moving your mouse to the toolbar to perform every function, save yourself time by learning the faster key commands. From copying and pasting to navigating to the beginning and end of columns, there’s a shortcut for just about everything.

Most of the above commands are navigational and they do take some time to learn, but you’ll use them frequently.

My favorite function and key command, though, is the little-known “Paste Values.” When you’re copying and pasting data into an existing spreadsheet with its own formatting, it’s easy to get frustrated with the time it takes to go back and reformat with the right fonts, colors, etc. That’s where “Paste Values” comes in. Pressing Ctrl + Shift + V allows you to import the information without having to redo the sheet’s existing formatting.

(This is also helpful when you want only the resulting values of a formula. Simply copy the range, paste values, and the formula will disappear leaving only the remaining values behind.)

3. Conditional Formatting

Conditional formatting can help you instantaneously identify cells that match almost any criteria. For example, you could tell it to color any cells that show a grade of 80+ green, 70-80 yellow, and 70 and below red. This helps draw your eye to any students who may be struggling.

In the example above we’ve used conditional formatting to call out audition scores below the average with a red-to-green color scale.

But conditional formatting isn’t limited to single color or single cell criteria. It can do so much more! If you want to keep track of instrument rentals by date, you could use conditional formatting to automatically highlight dates that are more than 30 days in the past.

Do you have to check out uniforms or concert dresses? Use conditional formatting in tandem with formulas (we’ll talk about those later) to sum the numbers of each size checked out, tell you how many remain, and highlight the sizes that may be running low.

To use conditional formatting, select the range to which you want it to apply, select “Format” from the menu, then “Conditional Formatting” and “add a new rule.” You’ll have the option to change colors and color ranges, and you can totally customize the criteria on which a cell is formatted.

4. Format Painter

I like to keep my spreadsheets as clean as possible, and the easiest way to do this is using consistent formatting. Beyond using the “Paste Values” function, you can also use format painter. See that little paint roller at the top left of your screen? That’s it.

The format painter button allows you to take existing formatting from any cell or range of cells and apply it anywhere else within the same spreadsheet (even across multiple tabs).

To use it, select the cell or cells whose formatting you want to use elsewhere and click your format painter button. Then, select the cell or cells you want to be formatted the same way. Voila! There’s no faster way to get clean and consistent formatting across multiple cells and sheets.

5. Formulas

Formulas do the real heavy lifting in spreadsheets and they’re not terribly difficult to use. A good rule of thumb: if you can imagine it, formulas can do it. They function across worksheets, workbooks, and can reference data both relatively and absolutely; they can be combined and stretched and manipulated. There’s almost always a formula to solve any data problem you may have.

Here are a few of my most frequently used formulas:

Some formulas can seem overwhelming at first, but Google has excellent documentation for learning to use them. Plus, most formulas work the same across MS Excel and Google Sheets, so if you know how to make something work in one, you can likely make it work in both.

Note: If you ever need a quick average, count, sum, or any similar simple calculation, highlight the range in which you’re interested. In the bottom right corner, you’ll see the sum of any highlighted numbers. When you click on that button, Google Sheets will display the count, average, minimum, maximum, and more.

6. Absolute vs Relative References

Have you ever tried clicking and dragging a formula across multiple cells? You may have noticed that the formula automatically changes to reference the relative cell. That can be frustrating in scenarios when you’re trying to compare all student grades to one class average or calculate the percent contribution of each student for a fundraiser.

Knowing how to use absolute and relative references in formulas will save that headache every time.  Just put a dollar sign before any row/column reference that you never want to move, no matter where you drag or paste the formula.

For example:

=$A$2 will always reference cell A2
=$A2 will always reference column A, but will change the cell row relatively
=A$2 will change the cell column relatively but always reference row 2
=A2 will change cell row and column as you drag/paste

7. Pivot Tables

Pivot Tables may be the trickiest thing in this list to learn, but they are by far the most powerful. They allow you to take raw data from one tab and interactively summarize it, reorganize it, and determine the significance of it. They grant you the opportunity to present your data in a clearer, more understandable way.  

For example, if you have every piece from your repertoire library in one spreadsheet, you could use a Pivot Table to determine how many pieces you already own for each difficulty level.

The example at the beginning of this article shows audition scores that have been organized by section. Because we pulled that raw data into a pivot table, we can now quickly identify which sections need help with scales, etudes, and sightreading.

To create your first pivot table, select all of the data that you want to summarize or analyze. Under “Data,” on the menu bar, select “Pivot Table.” This will create a new tab and your report editor will appear on the right side.

The easiest way to start using Pivot Tables is to first add your desired row fields (students, ensemble, instrument, and vocal part could all work well here). Then, add whatever values you want to see for each of those rows. (For example, audition scores, total students on that part, etc.). This will give you a good idea of how Pivot Tables can work, and will hopefully inspire you to experiment until you find your best use case!

Like formulas, Pivot Tables can feel rather overwhelming the first time you dive in, the few minutes it takes to learn how to create them can save you weeks of work in the end. AbleBits has a great tutorial to help you get comfortable with them if it feels like too much.

Quick Tips Bonus Round

To Automatically Adjust Column Width:

1. Select all cells in spreadsheet
2. Double click the line between column headers

To Autofill a Column with Values or a Formula:

1. Select the cell with the desired values or formula
2. Double click on the bottom right corner of that cell
3. Note: this only works when there are values in the column to the left

If You Can Dream It, Sheets Can Do It

Google Sheets is an advanced tool with a lot of functionality. If ever you’re in Sheets and you’re feeling frustrated by work that seems tedious, there’s likely a better way.  It pays to Google questions you may have about it; there are lots of resources available!

Take the time now to learn how Google can do the heavy lifting for you, and you’ll be happy to spend more time making music and less time managing your music program’s data down the road.