Love it or hate it, spreadsheets are the number one data tracking tool in many community-based organizations. Unless you are, or have a spreadsheet geek on staff, most of us mainly learn enough to get by - perhaps a few functions, the basics of doing formulas, possibly graphs. For most people, entering and tracking data in spreadsheets is easy, but analyzing can be painful. Here is my wisdom learned from decades of spreadsheeting to make your spreadsheet going a bit easier.
Pretend you're somebody else. The seemingly inconsequential spreadsheet file that you’ve started may end up being a key file that is added on to, and used for years by multiple people, long after you’ve left the organization. Take the extra five minutes to imagine you are somebody else looking at it. Specify units. Add a title. Put in notes.
Pivot tables will rock your world. Take the time to figure out Pivot Tables. They are absolutely amazing for quickly analyzing data in a spreadsheet in presentable formats that you can easily pull out, and that can give you great insights into your finances, customer base, trends, etc. They may seem a little cryptic to use at first, especially if you have given yourself a space of ten minutes in the midst of a busy project. But they are worth it, so set aside a couple hours, find a little YouTube Pivot Table tutorial (there are over 10,000!), and trust me that you will save weeks of your life.
There probably is an easier way. If you or somebody else is routinely spending hours and hours doing a monotonous task in spreadhseets(cutting and pasting, transposing a table, matching data between different tables) you can be pretty sure there is a simple formula or function. Try googling the need as a question (e.g. "How do I transpose a table in Excel"). If it’s a bigger task worthy of weeks and months of effort, such as reconciling different files, tracking errors, it's probably time to set up a system with more advanced features in Excel (like macros) or to get out of spreadsheets altogether and invest in a dedicated software/database solution for your need. The number of solutions now geared to small business and non profits organizations is blossoming. If you are a charitable non-profit check out Tech Soup for free or low cost software and great discussions and resources.
Be neat and tidy. Put data in a properly set up table in a unique sheet and resist the urge to have supplementary calculations there. Comment using the comment feature rather than type directly in cells. Why? Data in a unique sheet are set up in a way that makes it very useful for the cool features of Excel like Data Filtering, Sorting, Moving Duplicates, and of course…Pivot Tables! Supplemental calculations below or beside a table can get incorporated into the data.
In closing, here are my top five tricks that I wish I knew earlier:
1. Formula Tracing. You can visually see where the cells are that are included in a formula, rather than getting your head around AA341 and Z3 etc. If it’s on another sheet, double click on the arrow and it will say where (just learned this one last month!)
2. Printing something you can actually read. I’ve always found this to be a real pain because you can’t just press print as in MS Word and know that you’ll have something you can easily make sense of. I find the quickest way to get something legible on just one or more pages is to select what you want printed,specify 'print selection only', and then select, 'fit all on one page', or 'all columns on one page', or whatever makes sense for the size of the data you want to print.
3. Calculating across sheets using the Shift Key. If you want to add up something across sheets (or use another type of formula like Averaging) , you don’t have to go to every sheet and select the cell. Just start your formula, and then select ALL the sheets with the data, and then only one cell in one sheet. It's great if you are tracking data monthly or annually and have a unique sheet with the same structure of data for each period. It looks something like this. .
4. Paste, change format and many other things, across all sheets. You guessed it, hold down the shift key and select ALL sheets and what you do on one sheet will happen on all of them.
5. Data Validation. If you have a huge file with lots of data that various people have built up for years, aquaint yourself with this function, and you will save a lot of error tracing. You can specify that data is entered only in a certain way under Data Validation in Data Tools. If you are getting all kinds of funky ways of spelling an organization’s name, you can also specify that the person input the name from a drop down. It looks very impressive, but is very easy by specifying ‘list’ in data validation and entering the options there, or referencing a range.