Custom Formatting For Your Sheets

Recently there have been a few community posts offering tips to custom format your sheets and make them easier to read and understand.

I want to share those tips and offer a few new ideas. Feel free to add your own Custom Formatting tips and tricks below.

@mre wrote about how you can use a custom format to show manual budget items:

@Brad.warren described how you can customize your Transaction sheet to confirm receipts and AutoCat choices:

Here are a few more tricks.

Conditional Formatting for you Amount column

Let’s say you want to make your positive amount (usually Income) transactions stand out in your Transactions sheet.

If you Amount Column in your Transactions sheet is Column E, you can set up a Conditional Format rule where you:

  • Apply to range: E2:E
  • Format Rules: Format cells if Greater than 0
  • Formatting Style: Whatever you want. Perhaps fill with a dark green background and white text. Or make it bold.

If you want the entire row with a positive amount to have the conditional formatting (not just the Amount column) then select then:

  • Apply to range: A2:E (or instead of E, the last column where you want the formatting)
  • Format rules: Format cells if Custom formula is =$E>0 (which E is the Amount Column)
  • Formatting Style: Again, whatever you want.

Do you want to colorize your Categories sheet based on the Category Type? You can add a conditional format rule like this to your Categories sheet:

  • Apply to range: A2:AK (instead of AK, use the last column of your sheet)
  • Format rules: Format cells if Custom formula is =$E2=“Income” (which E is the Type Column)
  • Formatting Style: Perhaps make all Income Types green background.

Repeat this rule by changing the word Income for Expense and selecting a different style.

Another approach to formatting numbers is to use the Custom Number Format. You can select different formats for positive, negative, zero, and text results.

@benlcollins has a good tutorial on this on his website:

These are just meant to be some examples of how to use custom formatting in Google Sheets and Tiller. The concepts here can work on any sheet although some Tiller Sheets have existing formatting so you might not want to override that.

Share your tricks below.

Jon

Hi @jono:

Here is a trick to shade transactions that are manually added with this Tiller tool but not yet reconciled.

It looks to the Metadata column of the Transactions sheet used by the manual transaction work flow. The rule shades the first six columns of a row with a manually added transaction, flagged for reconciling. When the transaction is reconciled, the shading disappears as the reconciling workflow removes the data from the Metadata column of that row.

This provides a visual cue in my Transactions sheet for transactions that I have added manually but have not yet been reconciled. It has helped me several times when I have made a mistake entering the data for the manual transaction and it doesn’t match the actual one. It is a great reminder for transactions that were expected but may be unusually delayed.

Note, when manual transactions are added that are not flagged for reconciling, the shading will not be applied to the row.

In my formula, the Metadata column is column R, and the range to which it is applied, the first six columns in the sheet, is B2:G (defaults to my last, actual row.)

Hope this is helpful!

Good one @Brad.warren. Thanks for sharing it here.

I just did this on mine, making all my “Category = ‘Transfer’” grey, all incoming funds (greater than 0) green, and all outgoing funds (less than 0) red:
cf
Makes it a lot easier now scrolling through my list, especially when reconciling with statements, etc.

4 Likes

ohhhh the grey for transfers is brilliant! doing that right this minute for mine. Thank you!

2 Likes

Clever use of custom formatting @jpfieber.
Thanks for sharing it. As you can see, others want to add it too.

Bumping this since it was linked from a recent blog. I’m a heavy AutoCat user so I find it helpful to use conditional formatting for transactions with blank categories.