What's up, everybody? I've got a really quick little Excel formatting hack for you today. And this is going to be most useful for people who don't generally tend to like to put their data into an Excel table. I know there are a lot of you out there. So this is going to make your life slightly easier when it comes to applying formatting automatically.
So let's take a look. Now in the worksheet here, I have a small data set, and notice that this isn't in a table, I've just applied formatting. So we have some shading going on in the header row, and I've also applied borders around the outside. Now, I generally tend to apply borders when I've turned off gridlines in the spreadsheet.
If you take a look, we don't have the gridlines showing. And if you're wondering where that little setting is, if you go to view in the show group here, there is your gridlines checkbox. So if you put a check in it, you get your gridlines back Or you can deselect it and they disappear. And this is quite nice because it gives our worksheet a nice blank clean look.
Now if you are somebody who likes to turn off grid lines, it is nice to still be able to have effectively borders around the outside of our data so it's not blending into the background. Now, this data set is going to grow, meaning that I'm going to be adding more records onto the bottom. And I want the formatting to travel down as I'm adding new rows.
I don't want to have to reapply these borders each time I add a new row into the table. Now, we can achieve this by utilising conditional formatting. Now, the first thing you need to do is make your selection. So, what we're going to do is we're going to select all of the data in our table, but we're going to extend past that.
So, I'm going to apply this formatting to, let's say, 140 rows. That should accommodate my next few months worth of data. Now, another little tip here. If you ever want to jump to the top of your spreadsheet or your data, but you want to keep your selection, press Ctrl Backspace. That's going to allow you to do that.
If you press Ctrl Up Arrow, it's going to deselect everything. So we want to keep it selected, so Ctrl Backspace. Now we've made our selection, let's go to Home, and let's go into Conditional Formatting, and we're going to create a new rule. Now I'm going to create a rule based on a formula. And the formula that we're going to use is we're going to check if the cell is not equal to blank.
Which sounds a little bit strange. But the first cell that we need to select is the first cell in our data. So that's going to be cell B4. Now remember, when you're working in this formatting rule window, whenever you select a cell, it's going to put it in as absolute referencing, meaning we have those dollar symbols in front of the column and the row.
Now in this case, and in most cases when you're using conditional formatting, We don't want this to be an absolute reference. We need it to be a mixed reference. Meaning we want to have the column locked, in this case column B, but we want to keep the row unlocked. So we need to press the F4 key twice so that we just have the dollar symbol in front of the column.
And what we're going to say here is we're going to check if cell B4 is not equal to That's the symbols for not equal to, blank. So two quote marks. Now if that's true, we want to apply borders. Because basically what we're saying here is, if the cell isn't blank, apply the border. Meaning we've added something into the cell, we want a border around the outside.
So let's click on Format. We're going to go to the Border tab and we're going to choose Outline. Click on OK and OK again. So now what we should find is that if we add someone onto the bottom, and I'm just going to add myself, it's automatically going to add that border for me. And again, if I press Enter and start typing the next record, it's going to add the borders for me.
So this is a really nice little efficiency tip when it comes to automatically applying formatting. .
