hide the bar spaces in the chart if thre is a no value in the selected range

i have attached a excel which has a chart inserted by selecting the range which has values as well as null, is there a way we can hide the space utilised by Sep, nov, dec as there is no value for these month. Also i have tried with hiding the cell which does't have values, it is working fine, is there any other simplest way...?
Testdata.xlsx

Thanks,
Uday kumar

Hi, You can achieve this by creating named range.

Press Ctrl+F3 and add a new name ChrRange. In the refer to tab, type this formula

Formula:

Please Login or Register  to view this content.

Then in the select data for chart, use =Sheet1!ChrRange.

Hope this helps

Can you please elaberate the second option please.

This is working fine for me, but the problem here is each time i should go to select range and update this formula ro refresh the data, is there a way to auto refresh each time when the data is modified.


Thanks,
Uday Kumar

Ya because of the indirect, it is happening so.

You can try this one. It works fine.
Create a named range ChrLabel =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$B$2:$B$13),1)
ChrA =offset(ChrLabel,0,1)
ChrB =offset(ChrLabel,0,2)
ChrC =offset(ChrLabel,0,3)
ChrD =offset(ChrLabel,0,4)

Do not select any of the data. Insert Chart. Add a new series =Sheet1!ChrA. Similarly add the other series
Edit the horizontal axis part(Label) =Sheet1!ChrLabel

This will automatically refresh the data. You need not re-enter the range.

Excellent!!! its working fine.......

@ Shanthuday

Welcome to the forum.

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.