Debt payoff spreadsheets: formatting

Last week I discussed the formulas in the debt payment calculator spreadsheets, but before jumping back into the why-should and how-to’s of various types of formatting I wanted to take a little time to point out the formatting that I did on them.

I did a fair amount of formatting on these spreadsheets, and you might want to take a look at it to see what works for you, and what doesn’t. One thing that may not be obvious, but which is actually pretty important, is the fact that I moved all of the input/output worksheets to the beginning, and all the purely-calculation worksheets to the end. If you’re showing someone a spreadsheet and they have to page through irrelevant data, they’re likely to get lost.

Similarly, I tried to keep or reduce things as close as reasonably possible to amounts that could be seen on a single screen. Screen sizes and resolutions vary, of course.

Other than those two things, I did different kinds of formatting on different kinds of worksheets, depending on what I was trying to convey.

On the first two worksheets, “Debts” and “Income”, I used formatting to mark various sections in different ways.

  • I used a blue background to highlight the cells that could accept input.
  • I used number formatting to make it clear what was supposed to be dollars, what was percentages, and what was dates.
  • I used bold text to mark the column headers/labels.
  • I used italic text to indicate instructions
  • I merged cells to make the instructions easier to read

On the third worksheet, “Payments”, I used formatting to make it easier to read, and also to protect important formulas.

  • I used different shades of the same color to highlight groups of information. Since each debt has four columns associated with it, the first column was a dark color, the second one was a lighter version of the same color, the third was lighter still, and the fourth was the lightest shade of that color. This makes it easy to track which column you’re in, which is important since there are so many rows. Then by choosing a different starting color for the next debt, I made it easy to track which debt you’re looking at as well.
  • I used number formatting to make it clear what was supposed to be dollars, what was percentages, and what was dates.
  • I used bold, italic text to mark individual column headers.
  • I used bold text and merged cells to mark shared headers.
  • Because this is an “output” sheet, designed to let the user see the results, I hid row 1. This hid some information that wouldn’t add to the understanding of the results, and protected a critical formula from being accidentally deleted.

On the fourth worksheet, “Summary”, I again used formatting to make it easier to read.

  • I used alternating rows of background color to make it easier to track across the screen.
  • I used number formatting to make it clear what was supposed to be dollars, and what was dates.
  • I used bold text and a darker background to mark column headers.
  • I used a darker background to set off the two columns with significantly different sorts of information from the rest.
  • I used borders to make the columns easier to see, and to highlight the fact that the information below the table is not the same as the information in it.
  • I used bold, italic text to mark the information below the table as relevant, keeping the result and the label in the same formatting so that they’re clearly together.
  • I used text alignment to move the label for the bottom information to the right of its cell, to keep it as close as possible to the value it’s labeling.

On the fifth through eighth worksheets I didn’t do much formatting, as they’re not designed to be looked at — they’re there to enable calculations, not to provide easy-to-read results. I mostly just froze panes to keep the headers visible, and hid rows/columns that had confusing information. Even if you don’t expect anyone to look at these pages it can be good to hide stuff you don’t want anyone to mess with.