“Truth is ever to be found in simplicity, and not in the multiplicity and confusion of things.” – Isaac Newton
We previously covered how to talk with your international partners about setting up an international tracking document and where to build it. Now it’s time to get down to the details. Too many spreadsheets have descended into chaos from poor formatting, imprecise inputs, and runaway data.
It’s actually quite simple: Keep it clean
Erroneous formatting is the bane of all tracking documents. For a clean and accurate data presentation, follow these guidelines:
- Avoid the temptation to create multiple lists on the same sheet when it would be more easily tracked in a separate tab.
- Avoid merging cells. Both of these formatting errors can interfere with filters and calculations formulas.
- Avoid text wrapping. Text wrapping can enlarge cells where the data doesn’t necessarily need to be viewable at-a-glance and restricts the amount of data the user can see on screen.
- Control color use. Highlighting is a good way to call attention to certain entries, but make sure you settle on a very specific color set (don’t just use any shade of yellow, use the standard yellow).
- In general, you do not need your data sheet to “look pretty.” That’s what PowerPoint is for. Keeping the formatting basic will serve you in the long run as you implement formulas and tables to help organize the data.
Keeping complicated formatting to a minimum opens new possibilities afforded by more advanced features, and keeps everyone on the same page.
Data validation is a useful tool when you want to ensure data is consistent across a range, or provide a drop-down menu for recorders to choose from. This has a number of applications.
Let’s say you want to know how many different outlets contributed to coverage in a given period. This can be accomplished using a COUNTIF formula, but the results can be inaccurate if one outlet is given different names. If there are entries for “New York Times” “NY Times” and “New York Times ” (see the hidden space after “Times?”), your data will record three separate outlets instead of just one. By using data validation, you can ensure your entries are consistent.
Another less known feature of spreadsheets is that your calculations can reference ranges in separate tabs. By creating a separate tab for calculations, you reduce the risk of a recorder pasting raw data over your carefully crafted formulas. You can also protect specific tabs within a document to ensure no one accidentally alters those formulas.
Finally, if you can make things easier for yourself, do it. Automating as much of your tracking document as possible both reduces errors and speeds up input. These capabilities are even more powerful when using cloud-based sheets. For example, you can add a column that will automatically translate the foreign headline of a story into English. Those responsible for inputting data will thank you for it.
International reporting doesn’t need to be the monster that it can sometimes appear. By taking a conservative approach to spreadsheet formatting and focusing on usability, international tracking documents can be powerful tools for delivering insights and proving the value of PR to an organization at the global level.