8 Rules To Use A Spreadsheet For Marketing Reporting

When you want to get your Marketing Dashboard out of spreadsheets, it helps to adhere to a few simple rules.

• Here is my take on using spreadsheets the right way so you don’t build up “digital debt” in your marketing and sales function.
• Split data and reporting - Put your data on one sheet, and your reporting on another sheet. This allows usage of for example Pivots and Colors
• Keep your data clean - Now that your data is on a separate sheet, keep it clean. Have only one header row, don't merge cells, and only use alphanumeric data (no checkboxes or other fancy ways to represent data...do that in your “report” sheet).
• Apply “Format: Alternating Colors” as your only color formatting. It ads a simple header color and banded rows. It’s all the formatting you need for your data sheet.
• Put data in the rows, not the columns - This one is best explained with an example:

Bad

Okay

Good

Best

Make sure your columns in your datasheet are unique (and limit duplication of data, ask yourself, do you REALLY need that extra column?

• Try to use only use conditional formatting, no static formatting
• Use lookup fields for data in columns that represent dimensions with a limited number of values (for example paid/earned/organic, male/female, Active/Inactive/Pending).
• For columns with data that have more than say 10 values, use declarative formatting and enforce the format if possible (make sure a date column only has real date values, vs. for example strings with the wrong characters).

These rules apply for Excel, Google Sheets, and other data stores. When you follow these it becomes very easy to do complex reporting quick by using tools like pivot tables, or queries.