An Ode to the Underappreciated Spreadsheet
Spreadsheets get a raw deal. We are so dependent on tools like Excel and Google Sheets for managing budgets and P&Ls that it’s easy to fall into the trap of seeing spreadsheets only as applications for managing money, or at the very least, for working with numbers.
But the structure and features of spreadsheets make them so useful for a wider range of purposes, from project planning to writing. Breaking information or text into cells helps you break your work into bite-size chunks so you can find different ways of structuring it. The ability to sort and filter cells makes it easy to find, categorize, or reorganize lists or content. And yes, it’s nice to be able to do quick calculations when you are working with numbers.
Spreadsheets can be useful writing tools because they can help you organize resources and ideas. Here are a few ways you can use spreadsheets in content creation.
Structure a document. When you’re putting together a major document, PowerPoint deck, or book, the hardest part is often figuring out the structure. If you have seen (or authored) a document or deck that has the structure you need, consider mapping the structure out in a spreadsheet. I once drafted a book proposal by mapping out the structure of a book I admired, using column A to capture the title of each chapter and column B for each subsection within each chapter. Then I wrote my proposal in column C, mirroring the structure of the original book.
Create an asset file. If there’s a certain type of information you need again and again — like quotes you can use in speeches, or examples for presentations and reports — a spreadsheet can be a useful way of organizing that information in a way that makes it easy to find again. For example, I created a spreadsheet of digital lifestyle anecdotes, labeled by subtype (“money,” “family,” “business,” etc.). Because I can search or sort by those subtypes, I can easily refer back to those quotes when I need a story to illustrate a speech or blog post.
Build an idea file. Jotting down new project or story ideas in a plain text file is better than nothing, but makes it hard to organize and prioritize — let alone track those ideas through to completion. I now keep all my blog post and story ideas in a Google Sheet, with columns for “subject” (the broad topic area each idea relates to), intended outlet (e.g., HBR), and status (idea, pitched, assigned, complete). While some people maintain this kind of file using a project management tool like Trello, I’ve found a spreadsheet gives me more options for customizing my setup and organizing my ideas in different ways. Since it’s a pain to open my spreadsheet every time I have a new idea, though, I also maintain an Evernote notebook titled “story ideas”; when I have an idea, I immediately add it to that notebook (one note for each idea), and this If This Then That recipe simply adds my idea to that Google Sheet as a new row.
Planning and Organizing
In a world full of purpose-built task managers and productivity tools, it can be tempting to use a different application for each aspect of productivity or information management. But spreadsheets are often more flexible and powerful than specific productivity apps, because you don’t have to install a new application for each use case — and you can tailor them to your personal requirements. Here are a few of my favorite ways to use them:
Plan a project. You don’t necessarily need a dedicated project management tool to plan a project, track milestones, or capture dependencies. I create many of my project plans in a spreadsheet, with one column for dates (with a row for each week) and a column for each team or team member. That way I can map out what each person needs to accomplish in a given week, and keep the whole project on track. I often add an additional column to estimate my own hours at each step along the way, so I know how much time to block off in my schedule for each week of the project.
Set priorities. Whether I’m looking ahead at the week, the quarter, or the year, I find that a spreadsheet is the best way of capturing all my tasks or projects, estimating the time each one requires, and then prioritizing my to-dos based on the time I actually have available. It’s easier for me to see the big picture in a spreadsheet than in a task manager, and once I decide what I am going to prioritize, I can transfer actionable items to a task manager (without clogging it up with all the stuff I won’t actually have time to tackle). You can create your own spreadsheets for each type of prioritization process, or get the spreadsheets I use as part of the new Getting the Right Work Done tool kit.
Create a directory. AppSheet is a web service that lets you create mobile apps from spreadsheets, and it’s a great way to create a flexible contact directory for your team or organization. I created a mobile app for my kids’ school by getting parents to fill in a contact form I created with Google Forms; that form populates the Google Sheet that drives the directory, so we can offer a mobile app that not only lets parents search for a specific family but also allows them to see a list of kids by homeroom — or even a map of families so we can plan carpools. You can do the same thing for your organization, or even for your own personal contact list.
Working with Data
Surprise! Yes, spreadsheets are also useful in working with data. In addition to the obvious — organizing and calculating data, and creating basic charts — here are a couple of my favorite ways to use spreadsheets when working with data.
Surface interesting data. I do most of my serious data analysis in Tableau, but when I first dig into tables to summarize survey results, I use a spreadsheet to see which segmentations yield the most-interesting variations. For example, if I have a survey where results are segmented by age, gender, ethnicity, and social media usage (my usual columns), I want to see whether any of those segments differ significantly from the overall average. So I create what I call “Brent columns” — named for Brent Peppiatt, the colleague who showed me how to set up conditional formatting for a set of columns. If the responses for a particular segment (e.g., 18-to-25-year-olds) give a response that’s more than 10% higher or lower than the average, my conditional formatting turns that cell green. It’s a great way to quickly scan hundreds of response cells to see which ones are worth zeroing in on. (Excel has a button to do this quickly, but there is an art to making it work right, showing the right ranges in the right colors to actually help you interpret your data.)
Plan your infographics. If you’re producing a set of data-driven infographics for publication, you probably won’t want to release graphics you created in a spreadsheet. (I usually rely on Tableau or Infogr.am for infographic design.) But a spreadsheet is still essential for getting your data ready for visualization, particularly if someone else will do the actual graphic design. Create a separate worksheet for each graphic you need, and put the data you will be visualizing in a simple, clear table. Then create a rough mockup of your desired design using your spreadsheet’s built-in chart creation tools. Annotate the table and mockup with a few lines at the top of each worksheet, explaining what this graphic is intended to show, and what the key point of the chart needs to be. That makes it a lot easier for a designer to get their hands on the data (as opposed to wading through a huge spreadsheet) and it makes your communications goals clear.
Getting More from Your Spreadsheets
Once you start using spreadsheets for more than just financial data, you may find it helpful to have a few different spreadsheet applications in your tool kit. I use Excel for any spreadsheet I’m working on solo, or if I need advanced filtering, conditional formatting, or chart options. I use Google Sheets when I need to share a spreadsheet with other people, when I want to connect it to other web services (like my Evernote-powered ideas file), or when I want other people to add data to it from a form (like my support request system) — or when I want to be able to access it on the go.
There are also a few spreadsheet features that many people overlook or underuse but that will make spreadsheets more useful to you.
Learning how to use filters. Showing or hiding specific cells depending on whether they contain a specific word or value makes spreadsheets a lot more useful for quickly zooming in on specific information (as in a file of quotes or examples), and can speed up the process of cleaning up a workbook so that it’s more usable (by making it easy to just show and copy the rows you care about).
Conditional formatting. Making cells change color based on their content or value can take a little more work to nail down, but it is essential for anyone who routinely works with enormous spreadsheets of data they need to scan quickly.
Cell annotation. Most spreadsheet applications allow you to annotate cells; this lets you attach comments to a specific cell so you can explain what it means (or how to use it).
Invest a little time in learning to get more from your spreadsheets, and you’ll likely find yourself where I am now: turning to my spreadsheet applications as a way of dramatically simplifying and accelerating a wide range of business tasks.