Most People's Formatting Sucks

In today’s email:

1. Conditional formatting basics

2. Custom formula conditions

3. Using dates to conditionally format

SPONSOR

My daily podcast (400+ episodes and running) is written on Notion, the happier workspace. I’ve been using Notion to organize these daily writings since December 2023. Try Notion now.

MAIN ARTICLE

Formatting basics

We all know the despair that comes from a bland spreadsheet, right? It’s the classic black and white monotony that screams Office Space, but not in a good way.

I’m building a simple training guide for a race I’m doing in a couple months, and of course I throw all the info into a spreadsheet to get started.

I grabbed a guide off the internet and copied it into a sheet.

The first, easiest thing to do is just turn it into an Excel table instead of the raw data.

CTRL + T is the keyboard shortcut for this, and right away, we’re given some alternating colors and coherency that we lacked out of the gate.

Custom formula conditions

If we want to add a dynamic quality to our formatting, though, we need to use conditional formatting.

At its most basic, this gives Excel the authority to format a cell or range of cells according to certain criteria.

This can be as simple as numerical values or as complicated as we dare to get with custom formulas.

You can see all of the options from the Home menu on the Ribbon. Towards the middle of the screen is the Conditional Formatting drop down menu.

For example, we can highlight any cell where the mileage value is greater than 20 by selecting the range of cells and then choosing the Greater Than… highlight rule from the Conditional Formatting menu.

There are a ton of options built-in to provide quick formatting that add visual appeal and readability to our datasets.

But if we want to go further, we can define custom formulas for our formatting too.

Using dates to conditionally format

Let’s look at a simple example where we highlight the current week’s values.

I’ve made adjustments to my original workout plan and added dates to each week leading up to race day.

Now, I want the row of the current week to be highlighted as I get closer to race day.

Today is February 4th, and you can see that the whole Feb 3rd week is highlighted below.

For this to work, we need to do a few things:

Select the entire data set

I’ve selected all the values, excluding the headers, in my table. So, A2:J25.

Select Conditional Formatting - New rule. This pops open yet another menu. The bottom option is the one we want: Use a formula to determine which cells to format.

Dynamic Date Formula

Here’s the fun part. We need to compare the week of the row to the week of today’s date. This is the formula that does it:

=WEEKNUM($A2)=WEEKNUM(TODAY())

Excel thought of everything, right? WEEKNUM takes a date and then returns a number value. So if it’s the first week in the month, it returns 1, etc.

All we’ve got to do is compare the date in column A with TODAY() which gives us today’s date.

The other important detail is the dollar sign in front of A2 in the formula.

Because we selected the entire table’s range, we need to make sure that the conditional formatting only checks the first column. This is what lets the entire row be highlighted based on only the first column’s value.

Custom Formatting Options

By clicking the Format… button in our formatting rule dialog box, we can edit any aspect of the format we want.

This will open up a familiar format cells box where we can change the number format, font style, border and fill values.

Once you’ve settled on formatting, click Apply and Ok to apply it to the table.

Edit Existing Custom Formatting

What if you want to change the format?

Head back to Conditional Formatting, and this time select Manage Rules.

What if you don’t see anything? Depending on where your active cell is, you may not see the rule you just created. If you’re not in the table, it’s not going to automatically show you the table’s formatting.

Simply change the rules to show from the drop down menu.

From here you can double click into any of the rules you’ve defined and change them however you need to.

Will Formatting Apply to New Table Rows?

Yes, yes it will.

Even though in the conditional rule manager, it shows the range as $A$2:$A$25, Excel is smart enough to know this is a table and will adjust that range if you add new rows to the table.

Data Visualization

Want to dive into some more beginner data visualization topics? I made this video for exactly that. Check it out now.

NEXT STEPS

Whenever you’re ready, here’s how I can help:

1. Consulting (limited availability)
   Grab a slot on my calendar for help with a spreadsheet OR newsletter project.

2. Business tech stack (FREE)
   My recommendations for software and tools I use to create content and run my online business.

3. Sponsor Got Sheet
   Got Sheet teaches business operators, teachers and entrepreneurs how to get good at spreadsheets. Interested in sponsoring an issue or a series of Got Sheet newsletters? Reach out to get more information.

4. Personal budget tool
   As a Got Sheet subscriber, I want you to have a 75% discount on the personal budget I built 10+ years ago and am still using to this day.

5. YouTube

   If you aren’t subscribed yet, come on over to my YouTube channel where I make all my spreadsheet, coding and productivity tutorials

HAPPY SPREADSHEETING!

Enjoyed this issue?
Subscribe below or Leave a testimonial

Cheers, Eamonn
Connect on LinkedIn, YouTube & Twitter.