Hi, this is Wayne again with a topic “Automatically Highlight Blank Cells and Errors in Excel”.
In this article, we’re going to look at how to make it so that any blank cells in excel are highlighted so that your attention is drawn to them, and this also works for errors in excel. If you’ve ever used a spreadsheet. Similar to this one. With lots of data – and maybe it’s a spreadsheet, that you would expect to have data in every single cell like in this case here, even though some of these don’t have discounts the word, none is there, so there shouldn’t be too many blank cells, but it can Be hard to find those blank cells. So let’s look at how to set this up so that they’re highlighted what we’re going to do is select all of the data now an easy way to do. That is just to turn the data into a table and there’s a couple of different ways you could do that insert table is one, but as long as you’ve selected a cell inside of the data somewhere, you can hold ctrl and tap t, and that will enable You to create a table out of the data surrounding the cell that you picked i’ll click. Ok, this is now a table and it’s selected and i’m going to go here to the home tab and you can see in the styles group there’s an option for conditional formatting i’ll, just click there and i’m going to go down to new rule. If you’re not familiar with excel conditional formatting, you should definitely watch my other tutorials on that topic. You’Ll learn a lot and you’ll see how powerful and useful these can be, but for now i’m just going to click new rule and i’m going to switch it from the default of format.
All cells based on their values to this format, only cells that contain and then i’ll, go down here to this drop down list, i’ll click and i’ll choose blanks format, only cells with blanks. Next, we need to indicate how we want those cells that are blank to be formatted, so i could click here on format and i could change some of these font options. But remember: the cells in question are going to be blank cells, so the font color, the font size and all of that won’t really do much, because there won’t be text in that cell. We could work with the border if we want to, but in most cases fill is what you’re going to want to pick and then just choose a highlight, color that you want to use. I’M going to pick this red color here, i’ll click! Ok and now, when i click ok again, it applies the rule. Now, let’s see what happens if there’s a cell with no data in it, it’s automatically highlighted in red. In my case now you might be wondering about these cells here.
Why aren’t they lining up in red? The reason? Why is because these cells aren’t completely blank? They have a zero in them. Let’S say i delete that tap enter. It turns red, so these cells here, the reason they have a dash – is because of the particular number formatting that was chosen accounting, but i could switch those to general and you’ll see that there is a zero in that cell. So using this technique it should be a lot easier to identify any parts of your spreadsheet that have no data at all. Now, let’s look at how to do a similar thing, but with errors in your spreadsheet, i’ve already got my data as a table. Now i need to make sure i’ve selected the table there’s a couple of ways.
I could do that. I could just hold ctrl and tap a and that selects all of the data. That’S in the actual table.
So that’s one option. The other option is to go here in the upper left corner. I could click on this name box the drop down arrow and switch to table one.
Now the entire table is selected, so it doesn’t matter which way you choose, but as long as the table data is selected or all of the data you want to examine, you don’t actually have to use tables with this, but as long as the data is selected, We go to conditional formatting, we choose new rule format, only cells that contain change cell value to errors instead of blanks this time so errors we want to format, let’s say with dark purple: i click, ok, click, ok, again and now, as i browse down the page, I can see there’s an error here in my spreadsheet. It’S a value error. It looks like this is a formula that refers back to itself.
That’S probably the problem, and it was easy to spot this error thanks to this technique that we did of conditional formatting and setting up a new special rule that identifies only specific cells that contain something either blanks or errors. You can do a similar thing for specific text like if there’s a certain word that you want to flag or a certain value or date. I hope you found this tutorial to be helpful. If you did please like follow and subscribe and when you do click the bell so you’ll be notified.
When i post another video, if you’d like to support my channel, you can do that through my patreon account or by buying channel merch and you’ll, see information about that in the description below this video .