1) Round numbers: use “decrease decimal point” or “format” to automatically round numbers to the greatest number of decimal points that are truly useful (so 0.15, not 0.15121215 and 32%, not 32.42%).
2) Set units: use the “format” feature to make percentages into actual percentages (ending in %), to make dollar figures into actual dollar figures (starting with $), and so on. This makes it easier to interpret figures at a glance.
3) Use formulas: anything that can be calculated using the “formulas” feature should be (don’t do calculations by hand). Using formulas means numbers are automatically updated if anything changes, reduces human error, and makes it possible to check how a calculation is done.
4) Distinguish inputs and outputs: use a different color for numbers that are automatically-calculated outputs than for those that are inputs (e.g., entered by hand). This makes it easy to see what can be varied (and what is calculated and should not be manually edited).
5) Bold totals and key figures: make totals and key figures stand out by using bold (or color coding) to draw the eye to the most important parts of the sheet.
6) Don’t repeat yourself: If you’re repeating a header more than once in a way that means the same thing each time (e.g., “Total Revenue” appears once in every column), or you find that have another form of repetition, you should restructure your tables to remove the repetition.
7) Use clear names: every column and row should have a name that makes it crystal clear what it actually means. Ambiguous names can lead to confusion and mistakes, especially if others have to read your spreadsheets or if you may return to a spreadsheet you made a long time ago.
8) Center-align column headers and numbers: spreadsheets look neater and are a bit easier to read when the column headers and numbers are aligned in the center of columns rather than using the default (left or right) alignment.
9) Order columns and rows based on importance: put the most important stuff first (to the left and to the top) so that it’s easier to see what’s important immediately.
10) Bonus tips:
- Freeze the header row so that sorting works better and so that it’s clearer what the header is.
- For columns (or rows) where the bigger numbers are more important than smaller ones, use conditional formatting (with a 3-color scale for numbers that can be both positive and negative or a 2-color scale for ones that are all positive) to make the important numbers pop out.
- Give spreadsheets really clear names so that they are easy to search for (and so that anyone who sees them can quickly understand what each spreadsheet contains)
This piece was first written on May 5, 2023, and first appeared on this site on May 7, 2023.
Comments