# Data Bar - You cannot use relative references

#### tobermory

##### New Member
I'm trying to replicate the colour bars in table two, in table one. It's important for the presentation I'm doing I use numbers, not percentages. Basically the data bar represents how the proportion of how many people responded to answer in their group.

Table two is easy enough, just just zero as minimum, 1 as maximum.

The first table, requires a small formula, a sum of the total responses in the group. I can do this, however it wont permit relative references. I get a 'You cannot use relative references in Conditional Formatting criteria for color scales, data bars, and icon set' message.

Is there a cheap trick around this? I'm looking to around 20 tables in this format, having to manually formula every one would take forever.

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### pjmorris

##### Well-known Member
I managed to do it by formating each column top cell and using the formula =sum(\$B\$2:\$D\$2) to obtain the maximum. I then copied the three cells, highlighted the remainder of the table and pasted formats. Excel then adjusted the formula as required. It does lead to a lot of conditional formats and amending them might be quite tedious (or delete the lot and start again!). This was my finished table:

Book1
ABCD
1GroupYesMaybeNo
2Group 127203
3Group 2101116
4Group 31314
5Group 427628
6Group 530228
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D3:D6Other TypeDataBarNO
B3:B6Other TypeDataBarNO
C3:C6Other TypeDataBarNO
D2Other TypeDataBarNO
B2Other TypeDataBarNO
C2Other TypeDataBarNO

HTH

#### GraH

##### Well-known Member
Hi,

I was thinking one can maybe "fool" CF.

Then apply the CF using this name in the formula

At first glance it works, but looking closer the relative portion is off. The CF in blue is based on the portion of each answers to the total. You don't have the same visual at the left.

Sorry, can't do better then @pjmorris.

Last edited:

#### pjmorris

##### Well-known Member
Hi GraH, I think your approach does work. I defined =RowSum just as you did yours and got the following visual:

I think yours only appears wrong as you have 100 in Group 1 - No, rather than 3 in the OPs example.

Or am I missing something?

For info, this is the dialogue when I setup the red colour having started by selecting the entire column:

HTH

#### GraH

##### Well-known Member

Hi Peter,

I used some random values for testing purposes. And I did it like you explained.

Maybe like this it's more clear why I still think it is wrong.

In G-I range, the relative proportion is right. Somehow CF is considering the values in the other (previous?) rows. As you can imagine, I was not expecting this. So I'm missing the clue here.

#### pjmorris

##### Well-known Member
This is really strange. I've done a test and completely agree with you. The only difference between the two tables below are the numbers in the top row:

and

There ought not be any difference between the two rows and certainly shouldn't be a change in Group2 when Group1 is changed.

Most odd.

#### Fluff

##### MrExcel MVP, Moderator
It appears that everything is based on the sum of the 1st row B2:D2, whilst the defined name by be relative CF seems to be treating it as absolute, possibly because you can't use relative references.

Last edited:

#### GraH

##### Well-known Member
It appears that everything is based on the sum of the 1st row B2:D2, whilst the defined name by be relative CF seems to be treating it as absolute, possibly because you can't use relative references.

View attachment 34368
That actually makes a lot of sense the way you see it @Fluff.

Replies
0
Views
442
Replies
1
Views
78
Replies
9
Views
347
Replies
1
Views
4K
Replies
3
Views
2K

1,141,060
Messages
5,704,037
Members
421,323
Latest member
Exidous

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back