Change the Color of Tabs in Excel

One of the nice features of Excel is the ability to change tab colors. This helps provide a visual guidance to your audience.  Grouping tabs together by color helps your readers better understand your data.

To change a tab color, right mouse click on sheet and select “Tab color” option to change the worksheet tab colors. 

So the next time you have a large worksheet, try grouping your tabs by color.

Data Validation

Everything we do with Excel starts with data. So today I wanted to examine some cell contents a little closer.

There are times when we look at a cell and what we see is not what really is in the cell.

Take a look at the below example.

Value	Test	Formula
A	FALSE	=ISBLANK(A2)
B	FALSE	=ISBLANK(A3)
1	FALSE	=ISBLANK(A4)
2	FALSE	=ISBLANK(A5)
	FALSE	=ISBLANK(A6)
	TRUE	=ISBLANK(A7)

I want to test the values in my left column (column A) to see if there is a value in the cell or if the cell is empty. I can use the formula =ISBLANK to accomplish this.

As you can see the first 4 rows all have values in them and the formula returns False indicating that. However in the last two cells I get different results. Both cells appear to be empty but in reality only one of them is as indicated by the TRUE result.

Now there are a couple of reasons why a seemingly empty cell would return FALSE for the =isblank function.

First is that there is a value in the cell. Perhaps the font color of the text in the cell matches the background color of the cell giving the appearance of an empty cell.

The second and more common reason is that there is a space value in the cell (someone has hit the space bar and then the enter key. This also can occur when importing values from other data sources such as an Access database.

Take another look at the data. This time I want to do a VLookup for the letter B in my column A and return the value in column B. However my VLookup formula does not work. The reason is again due to a blank space after the B in cell A3. The lookup is searching for “B” and the value in the cell is “B “.

Value	Test	Vlookup	Formula
A	FALSE	#N/A	=VLOOKUP("B",A1:B7,2,FALSE)
B	FALSE
1	FALSE
2	FALSE
	FALSE
	TRUE

This vlookup error is quite common. Almost weekly I am asked to trouble shoot a coworkers worksheet because their vlookup does not work. 9 out of 10 times the problem lies with blank spaces to the right of the value(s) they are using in the look up.

Now you can avoid this common error by using the =Trim function to strip out blanks from both sides of your data.

A third but common occurrence is when a number is represented as a text. Again if we look at the data in column A, I show two numbers (1 and 2). However in this instance, 1 is not a number but a text. The formula ISText fails for the number 2 since 2 is a number in my data.

Value	Blank Test	Vlookup	Formula	Text Value	Formula
A	FALSE	#N/A	=VLOOKUP("B",A1:B7,2,FALSE)	TRUE
B	FALSE			TRUE
1	FALSE			TRUE	=ISTEXT(A4)
2	FALSE			FALSE	=ISTEXT(A5)
	FALSE			TRUE
	TRUE			FALSE

So what does all this mean? Well just because it looks like a duck and quacks like a duck, it does not mean it is a duck. So when you have a formula that you are sure should work. Start by taking a closer look at your data to ensure that what you see is what you really have.

Fuel Gauge Redo

Earlier this week I had a reader comment on my Fuel Gauge dashboard graphic. Not familiar with what a fuel gauge chart is, click here see my earlier post.

Basically a Fuel Gauge graphic represents a percentage from 0 to 100. The concept is based on a pie chart. Here is an example of what one looks like.

My reader liked the concept but wanted to add a twist to it. He wanted the gauge to represent numbers from 70 to 100 and couldn't figure out how to convert it.

Well as a former coder I often say it’s only 1s and 0s. Anything is possible. So how do we convert the above fuel gauge to the one that works from 70 – 100?

Well first we need to understand the concept of the gauge.

Where my gauge would show 0, his will show 70, my 25 would be his 77.5, my 50 would be his 85, etc…

To convert the gauge I need to setup a matrix and it starts with a basic question. What is the difference between milestones on his gauge?

your #	77.50	My #	25
Your Starting #	70.00
	7.50	7.5/25 =	0.3000

I randomly choose one of his milestones (70, 77.5, 85, 92.5 or 100)

I then choose the next lower milestone and subtract the two.

In this case I chose 77.50 as the starting milestone and 70 and the previous milestone. The difference between these two numbers is 7.5. If I chose 85 and 77.5 I would again get 7.5 when I subtract them. So 7.5 is my first key for converting my gauge.

Next I take the first milestone of 77.50 and identify what that would be on my gauge (25). I then divide the 7.5 by 25 and get a conversion factor of 0.30 (7.5/25=0.30).

Now I can create my conversion matrix based on the 0.30 number. I create a column and run numbers from 0 to 100. I then start with the readers low score (70) adding 0.30 to each subsequent number. In the below example I show the first 11 numbers of my scale from 0 to 100. The readers scale increases 0.30 in each row.

Column P	Column Q
Your Scale	My Scale
70.00	0
70.30	1
70.60	2
70.90	3
71.20	4
71.50	5
71.80	6
72.10	7
72.40	8
72.70	9
73.00	10

Another way to get this number is to subtract the users high and low value and multiply that by 0.01.  (100-70)*0.01 = 0.30

Now that I have my matrix setup I just need to do a vlookup to convert the percentage entered by a user.

=VLOOKUP([Number Entered By User],P:Q,2,TRUE). This formula is shown in cell D3 of the converted Versions tab in the spreadsheet that you can download at the end of this post.

I need to use TRUE in my vlookup instead of false since with true, if an exact match is not found, the next largest value that is less than lookup_value is returned.

The vlookup result converts the user’s number into my gauge number from 0 to 100 and the needle points correctly.

Confused? Download the converted example and play with it.