Get Shift Done: Tips and Tricks
Google Spreadsheets can be mundane to look at, with all those numbers just sitting there staring at you in black and white. It’s easy to fall into a vortex of numbers determining what number goes where, totals, what needs to be done, dates, and so on.
How to keep it all straight? Just as with text documents, color and formatting are your friends; conditional formatting makes it easy.
Let’s say, for example, that we are tracking several trucking companies in a Google spreadsheet. We see the dates, invoice numbers, invoice amounts, and whether each invoice was paid. As it starts with, it’s a grey block of text.
But within a few minutes, we can adjust the spreadsheet to make it easier to scan. We can glance at the list of companies and know which invoices need to be paid, which ones are due now, and which ones are in process. Conditional formatting enables the cells to change format with a bold font, font color, and even highlight the entire cell, based on rules we set.
Most importantly, we want to convey which invoices haven’t been paid. Highlight the row that includes the Invoices Paid “Yes” or “No.”
Choose “Format” from the menu and select “Conditional Formatting.”
In this case, we want the cell to be highlighted if the invoice hasn’t been paid. So select “Text Contains” in the drop down and type “No” in the field.
To make it stand out, select a color that is very noticeable. In this case, we choose an orange.
Click “Done.” Watch your cells change to orange when an invoice hasn’t been paid.
Next, let’s highlight invoices that are due now.
So, again, from the Format menu select “Conditional Formatting” (or click the “Add New Rule” in the right bar, if it is still open).
In the “Format Cells If…” drop down, select “Is Between.” Enter =TODAY()-30 in the first field to start the range 30 days before today, and then =TODAY() for the range to end today. Choose a color that helps the Accounts Payables staff recognize which invoices are past due. This time we set the cell to red, the text to white, and the text to bold so that the cell is very noticeable.
The last formatting change is to show that an invoice is in process. This way we know if the invoice is highlighted as past due, but because it is in process we don’t need to worry about it.
So add another Conditional Formatting Rule, this time to change the font to bold text; we can glance at it quickly and without distracting from the highlighted cells we created earlier.
Select “Text Contains” and then type in “Yes.” Then choose the “Bold” option or “B” in the menu options.
Now you can see which invoices need to get paid today, and which ones are past due at first glance.
Get stories like this one delivered to your in-box weekly: Subscribe to the GSD newsletter.
Related Articles:
-
Google Sheets: Consolidating Data from Multiple Tabs with
SUMIF - This Hack’s the Reason Google Keyword Planner’s Not Dead Yet
GSD: Tips and Tricks is brought to you by Xero, the cloud accounting software solution for your small business. With Xero, you can log in anytime, anywhere to get a real-time view of your cash flow and manage your books. Start your free 30-day trial today.