Fat Fingers on the Space Bar – A Conditional Formatting Example

by Gregory on March 25, 2011

I had the distinct pleasure of helping a colleague this week, because he fat-fingered the space bar whilst entering data in a spreadsheet then couldn’t figure out why there were #VALUE! errors on the Pivot Report. I used some conditional formatting to solve the problem and save the day.

But to be fair, data entry errors are pernicious and can happen to anyone. And a lot of VBA programming is written to alert users of problems with input data. The example I’ll use here isn’t quite the same as the original but will illustrate the problem of having a space character entered into a cell, then having it used in a formula to generate an error.

The Problem with the Space Character

In the spreadsheet below, hours are entered by manufacturing in different buckets to determine where time was spent during the shift. Total hours equal the sum of Setup, Run and Downtime (DT) hours. The percentage of downtime is calculated in the %DT column by taking the downtime hours (DT) and dividing by the Total hours. Zeros are not a required entry.

Fat Finger the Space Bar

My fat-fingered friend happen to bump the space bar on several of his entries and I’ve re-created that in cell D3, which looks to be empty but contains a space character. This bothersome fiend is like the invisible man, you suspect something is near but you can’t see anything. Yet the evidence, in this example at least, is right in front of us by virtue of the #VALUE! error in cell E3.

By a process of elimination we can tell from the %DT formula (=D3/A3) that cell A3 appears to be okay with the value 8.0 so that leaves cell D3, which appears to be empty. However it requires closer inspection to find the space character.

If we Edit the cell (F2 in Windows Cmd+O in Mac) we can usually tell by the cursor position that there is a space, but when cell alignment is centered this becomes problematic. We could simply hit the delete key to clear the contents of the cell, but we need to detect the presence of the space character in some manner so we can fashion a solution for alerting the user to the fat-finger snafu.

Finding the Solution

Using the  formula =LEN(D3) returns 1 and confirms something unseen is there. Using the formula =CODE(D3) returns 32, which is the numeric code for the space character. And using the formula =ISTEXT(D3) returns TRUE, which is, in essence, why the #VALUE! error is returned by the formula.

My solution is to encode the data range in the DT column with Conditional Formatting so that when the space bar is inadvertently pressed, leaving behind an invisible space character, the cell background becomes a rose color to get the attention of the user. Of course I need to inform the user of this fact, so I’ll put a comment in the column header for posterity.

Detect the Space Character with Conditional Formatting

To select the data range I activate cell D2, type D2:D10000 into the name box and press enter.

Select range with name box

Choose Home > Conditional Formatting > New Rule then select the Rule Type: Use a formula to determine which cells to format. Next type the formula =ISTEXT(D2) into the Format values where this formula is true box.

Click the Format button to bring up the Format Cells dialog box, click the Fill tab, then select a background color of your choice and click OK. The New Formatting Rule dialog box should look something like this:

Conditional Formatting for space character

When you are satisfied click the OK button for the changes to take effect.

Please notice that the formula uses the topmost cell in the range with a relative reference. This allows all cells in the range to have this conditional formatting feature.

Conditional Formatting Space Character

This solves our data entry problem with fat-fingers on the space bar.

Revisit Your Conditional Formatting

Once Conditional Formatting is in place we tend to forget all about it until there needs to be some changes to the conditional part or the range needs to be expanded. The first question that arises is “what was the range I formatted with conditional formatting?”

To locate the original range, select any cell that you know has the conditional formatting and use the keyboard shortcut Ctrl+G then click the Special button on the Go To dialog box. Now click the radio button for Conditional formats on the Go To Special dialog box then select the radio button for Same and click OK.

Go To Special dialog box

Now that the entire conditional formatting range is selected, choose Home > Conditional Formatting > Manage Rules to bring up the Conditional Formatting Rules Manager where you can change the range or edit the formatting rule or add a new rule.

Conditional Formatting Rules Manager

You can also delete the original formatting rule should you have skinny-fingers and never unintentionally tap the space bar.

Related Posts Plugin for WordPress, Blogger...
Khushnood Viccaji March 25, 2011 at 11:37 pm

This is something I face frequently in my spreadsheets too.
I use Data Validation to prevent such entries in the first place.
Only, instead of =ISTEXT (in the conditional formatting formula), you have to use =ISNUMBER (in the data validation dialog) to validate the cell entry, and display a warning message if the user enters a non-numeric value.
In the Data Validation dialog, from the Validation Criteria drop-down list, select “Custom”.
A formula box is displayed, in which you can enter the ISNUMBER formula.

One more thing:
Data Validation does *not* prevent copy-pasting non-numeric values, so it would be a good idea to use the Conditional Formatting tip *along with* the Data Validation trick !

Gregory March 26, 2011 at 12:47 am

@Khushnood I’ve never used the “custom” option with Data Validation but will do so in the future based on your recommendation. I like your solution better than mine. However I believe copying data into the range destroys any Conditional Formatting as well as the Data Validation, like you mentioned.

Another option, and I use this for dates, is to run some VBA code after a user clicks a button to run a report and check for data validation errors. If the code finds any errors it warns the user and doesn’t run the report. I usually tell the user what cells are causing the problem, but all of this takes considerable time.

Thanks again for the great comment.

Khushnood Viccaji March 26, 2011 at 1:52 am

Hi Gregory… glad you liked my solution ! I have learnt most of my Excel through e-zines and blogs written by people like you 🙂

There are very good tips on Debra Dalgleish’s site, where you can read more on Data Validation (and lots more Excel stuff)
http://www.contextures.com/tiptech.html

This is a link on using the Custom options in Data Validation:
http://www.contextures.com/xlDataVal07.html

Khushnood Viccaji March 26, 2011 at 1:54 am

Hi Gregory… glad you liked my solution ! I have learnt most of my Excel through e-zines and blogs written by people like you 🙂

There are very good tips on Debra Dalgleish’s site, where you can read more on Data Validation (and lots more Excel stuff)
http://www.contextures.com/tiptech.html

This is a link on using the Custom options in Data Validation:
http://www.contextures.com/xlDataVal07.html

And finally, you’re right about the copy-paste option over-writing Conditional Formatting…
But, if users paste values (instead of Paste ALL), then Data Validation is by-passed, but the Conditional formatting stays intact !

Gregory March 26, 2011 at 7:49 am

@Khushnood Right you are about pasting values keeping the Conditional Formatting. I too like what Debra is doing over at the Contextures site, her blog has been been listed as a Favorite Link on my blog since I started. Many Thanks for the link to customizing Data Validation.

Comments on this entry are closed.

Previous post:

Next post: