Highlighting trailing spaces with Data Validation

In part 2 of our trilogy about trailing spaces (part 1 is just here) we look at how to identify trailing spaces if you have them in your data sets. As discussed in part 1, they're hard to spot, as we can't see spaces with the naked eye at the end of a cell. In this post, we'll look at how to identify them using Conditional Formatting.

First, I have some "raw" data, which contains some trailing spaces which I can't currently see:

Now, click on Conditional Formatting on the Home tab:

 

In the drop-down, choose Highlight Cells Rules then More Rules:

Then, in the Conditional Formatting dialogue, choose the second option: Format only cells that contain then specific text, ending with and then insert a space in the text box:

Then click the Format button and format these highlighted cells as you like. For me, the finished data then looks like this:

If you'd like to have a go at this yourself, you can download an Excel file with the completed example, and a raw data set for you to try yourself. Click here to download the file.