Today’s topic may seem like an unnecessarily basic one, but I can tell you that nine out 10 shared spreadsheets I receive from team members are unusable for analysis because they can’t be sorted. I’m not referring to fancy formulas, just simple spreadsheets, so I’m going to share with you how to set up a spreadsheet, the common errors I see and how to fix them. These tips apply largely to nonfinancial spreadsheets such as keeping track of people and things over time. Follow these rules and your colleagues will thank you.
A spreadsheet is a simple grid designed to organize information and perform accurate and consistent calculations, essentially eliminating errors that happen when you’re using a calculator. If set up correctly, you will be able to add data and find new insights for a set of related information for years. Working with spreadsheets is like speaking a language, as long as you adhere to the basic form, others can understand your data and contribute to it.
Your spreadsheet should consist of columns and rows. Row 1 should always be used for headings and each one should be sortable. I see a lot of people use the first several rows for titles and labels and finally get to the actual column headings. Do not do this because it makes it more difficult to see how many rows of items are in the sheet. Say you’re tracking how many people have registered for an event you are managing. You can easily scroll to the bottom and subtract one from the last row number, but if you’ve used the first eight rows for labels, it’s easy to overestimate your total attendees.
Before you start your headings, think about what you want to do with this dataset and plan ahead. You may not need to do a mail merge today, but if you keep each heading to one type of information, you’ll be able to do that easily in the future. Use conventional names for your column headings, ones that you can duplicate across projects or match the headings found in the data source you most frequently use. For instance, First Name, Last Name, Title, Company and Street Address are all common. Keep your headings as short as possible while still conveying a clear meaning. Each heading should include a single piece of data. Make sure there are no spaces before your entries because a space will put that item at the top of the column when you sort. Fill in your rows with your data.
Do not merge cells or use colors. Do not group data into blocks with subheadings. Stick to the simple array to keep the spreadsheet sortable.
A pivot table is the quickest way to analyze your data, but it is also a simple way to check for inconsistencies. Because I work with thousands of customer records, running a pivot table for each column is a huge timesaver. If you’re not familiar with pivot tables, they are part of Excel and are used to calculate, summarize and analyze data.
To use a pivot table to check your data, select a column by clicking on the column letter at the top of your spreadsheet, click the “Insert” tab at the top of your screen and then “PivotTable.” A dialogue box will appear where you can change the default options, but in this case, just click OK. Take a moment to name this tab based on the column you’re analyzing for easy reference later. The column name will appear in the right panel. Drag it down to the Rows box. Now you can do a quick scan to see if any are misspelled or can otherwise be corrected. You must go back to your spreadsheet tab to make corrections. Repeat this process for each column. Once your spreadsheet is cleaned up, save it.
Finally, here are two tips I learned years ago when I was hired to manage the subscriptions for a large magazine that have saved me hours of work. Never work on the original spreadsheet. Instead copy it. This way, if you make a mistake you will be able to go back to the original. Second, don’t delete data because you may need it in the future. Say you have a number of records that represent cancellations of a product; instead of deleting them, cut and paste them to a new tab labeled cancellations. If any of those records become active again, you’ll have the data.