🏆 Transactions sheet: Tip to Show count of Uncategorized transactions in A1 or show Tiller logo if there are none, plus highlight Uncategorized rows

Google Sheets Show & Tell
,
1

Not sure if this has been posted before (I hadn’t come across it in my last month of browsing):

I was finding that it was a little visually difficult to track all Uncategorized transactions from the Transactions sheet, so I made a few changes that make it a lot easier to now know exactly 1) How many transactions are Uncategorized and 2) Visually see those exact rows.

Screenshot 2023-12-17 at 4.58.16 PM
Screenshot 2023-12-17 at 4.58.16 PM1252Ă—604 69.5 KB

Screenshot 2023-12-17 at 5.14.04 PM
Screenshot 2023-12-17 at 5.14.04 PM1208Ă—606 73.8 KB

To get a real time number of Uncategorized transactions, replace the formula in A1 of the Transactions sheet with the following:

=if((countif(Transactions!D:D,"") > 0), (countif(Transactions!D:D,"")),(image("https://storage.googleapis.com/assets.templates.tillermoney.com/Tiller_Icon_Reverse%402x.png")))

This will show the number of Uncategorized transactions in cell A1 (and replace the Tiller logo). If there are no Uncategorized transactions, then the Tiller logo will be displayed. Note: If your Categories column is different than D, then you need to replace all instances of “D” in the above formula with the Categories column in your sheet.

Additionally, if you’d like to style cell A1 when there is 1 or more uncategorized transactions (to make it stand out more), you can do so by creating a Conditional Formatting rule (Format → Conditional Formatting → Add A Rule), where if the “Value is greater than 0” it gets applied. Make sure cell $A$1 is specified in the “Apply to Range” field. I chose a red cell color and white text color with a slightly larger text size.

Next, to highlight any row that has an Uncategorized transaction, select the blank space to the left of “A” (column) and above “1” (row) to select all cells, then go to Format → Conditional Formatting → Add A Rule → Make sure the entire range of your sheet is included in the “Apply to Range” field, select “Custom Formula” for the “Format Rules If” section, and add the following formula:

=$D1=""

Note: If your Categories column is different than D, then you need to replace “D” in the above formula with the Categories column in your sheet.

Then, select a Fill Color in the “Formatting Style” section (I chose a light red). Finally, Hit “Done”.

Screenshot 2023-12-17 at 4.56.42 PM
Screenshot 2023-12-17 at 4.56.42 PM652Ă—1180 39.4 KB

Now, whenever you have an Uncategorized transaction(s), the number will appear in the A1 cell, and the uncategorized transaction rows will highlight with the color/font choices you selected on the Conditional Formatting page. As you apply Categories to the Uncategorized rows, the shading will disappear for said rows and the number in A1 will adjust accordingly until there are no uncategorized rows left. When, there are none left, the Tiller logo reappears in A1.

I’ve found this to be helpful, I hope you do, too.

9 Likes
2

Giving it a whirl, thanks.

1 Like
3

This looks great! Nice little utility.

*maybe my favorite formatting hack!

2 Likes
4

Thanks a bunch for sharing. I’ve been wanting to do this in my own sheet but haven’t put the energy towards it.

1 Like
5

Thank you! Just getting everything set up and this is very helpful.

1 Like
6

Thanks for sharing, @mu3484343. That logo was not as helpful as your red uncategorized counter. Great repurposing of the space.

1 Like
7

This is really helpful!

I’ll add a small addendum:

In your example screenshot you have cell A1 in red when something is uncategorized. To create this effect, you can add another conditional formatting rule, where if the “Value is greater than 0” it gets applied.

1 Like
8

Good idea, added that!

1 Like
9

Is it me or does this formula also count the entire column D, which will also count all the blank cells after the transaction table? For me I get 1045274 as a result.

I revised to use only the rows that contained transactions (taking out your reference to the image and adding “All Uncategorized!”:

=IF(COUNTIF(INDIRECT(CONCAT(“D2:D”,H1)),“”)>0,COUNTIF(INDIRECT(CONCAT(“D2:D”,H1)),“”),“All categorized”)

Where cell H1 contains the formula: =DCOUNTA(D:D,1,D:D)

10

It does. I delete any blank rows as it takes less computer memory and performance is better.
Google Sheets entire spreadsheet limit is 10 million cells.

11

Ahhh. Yeah, Excel won’t allow that. Perhaps my modification will be helpful for Excel users then.

12

That’s a good idea. Like Mark, I delete all of my rows at the bottom of the Transactions sheet, so this wasn’t an issue for me. However, if anyone does have blank cells at the bottom, I’m glad you posted a solution here.

13

In the spirit of better performance, I’ve limited the checking to the first 99 transactions, assuming my uncategorized transactions will be the most recent.

=if((countifs(INDIRECT("$D$2:$D$100"),"") > 0), (countifs(INDIRECT("$D$2:$D$100"),"")),(image("https://storage.googleapis.com/assets.templates.tillermoney.com/Tiller_Icon_Reverse%402x.png")))

I’m not saying I’ve experienced poor performance with this change, just a general practice - it all adds up.

Similarly with conditional formatting, which tends to be performance hungry across thousands of entries.

3 Likes
14

My sheets had been running slowly for some time. I finally realized that I had ~50k empty rows at the bottom of my Transactions sheet when I went to go archive all of my 2022 transactions. I deleted them and my performance issues instantly went away. Runs like a champ now. Recommend to everyone to delete empty rows.

And this is a nice little utility. I’ve been using the sentence on my Savings Budget sheet to confirm everything is categorized but this is more immediately helpful.

2 Likes
15

I love this, helps with performance for me. Thank you @Mark.S

16

Simpler and faster:

=let(x, countif(Transactions!D:D, ""), if(x, x, image("https://storage.googleapis.com/assets.templates.tillermoney.com/Tiller_Icon_Reverse%402x.png")))

1 Like
17

I am having a problem with another spreadsheet that is looking at a1 and not working properly. Can someone provide the original formula that was in a1 before it was replaced?

18

Original A1 cell formula:

=image("https://storage.googleapis.com/assets.templates.tillermoney.com/Tiller_Icon_Reverse%402x.png")
19

I did the count stated with over 800 , it couns down but how do I clear out as all but a few are filled?

20

Realized the issue, had to delete all the blank lines, seems to work fine now, thanks