It’s the end of a week of action and you have a giant list of sign in sheets, petition signatories, donors, event attenders and new volunteers. Right? Okay, maybe you have half a sign in sheet splattered with coffee, three tired organizers who remember the room, and a dozen different sign up forms with contact information. Sound familiar?
Guess what? You’re not alone! The truth is, we all struggle with this stuff. If it was easy, I wouldn’t be blogging! Today i want to introduce you to a simple tool that will make organizing data in Excel easier AND prettier. It’s called “Format as Table.” I think you’ll love it (or your money back!)
Here’s a screenshot of some made-up data. Let’s say you want to organize the data based on “Relationship Holder” (or some other column that is unique to YOUR group!).
Well, you could go all the way to the right of the toolbar and find the Sort Button, OR you could Format as Table and quickly sort (or filter) by anything. Follow these painless steps:
- Click in any cell
- Use the keystroke combo CTRL + T
- Confirm “My Data has Header”
- Click Ok
What happens? Excel will select your data for you (see the dotted line? magic!). If Excel guessed right about what data should be included in your table, click Ok. If you accidentally forgot to include a header row (meaning First, Last, Email, Relationship Holder, etc), uncheck the box. You can add titles to the columns later.
Low and behold: A Table!!!! So, what makes a table special? Here are a few things:
- Excel will stylize your data and make it more aesthetically pleasing (blue!) — and easier to differentiate between different rows. You can change the colors and styles at the top of your screen
- You can sort OR filter your data by clicking the little triangles in the header row
- Click CTRL + Q to open up a quick menu of table tools at your finger tips
- Tables make it REALLY EASY to clean up your data and look for common outliers. Do you want to see just the people who DID or DIDN’T include an email address? Easy — just go to the email column and filter for the blanks. Do you want to find the people who typed Pennsylvania instead of PA? They will stick out like a sore thumb when you click the “triangle” button on your State column and see all of the options.
- You can make quick Totals OR Subtotals by adding a Total Row to the bottom of your table. Be careful, though! If you have hidden or filtered data, the Total formula will still count it. If you want your sums to reflect your filter criteria, make sure to use the subtotal function.
There’s a lot more to know about Tables and how to make them your best friend, but first, give a try with some of your own data, and let me know how it works. If you get stuck, write to me through my advice column, The Spreadsheet Whisperer, and you might get to be featured on the blog!
For those of you ready for another helping of table-y goodness, here’s a juicy listicle!