Link to home
Start Free TrialLog in
Avatar of developingprogrammer
developingprogrammer

asked on

excel line chart not showing gaps

hey guys, got a simple question. i've got a line chart which is supposed to show 8 weeks of data. however i've only got 3 weeks. i would like to leave the remaining 5 weeks blank.

i've already chosen the "Show empty cells as: Gaps" option - but excel still connects the line as a 0 point.

i've also tried a =na() but it still doesn't work.

how can i show the full 8 weeks for the x axis but leave the line chart stopping halfway at 3 weeks?

i can think of a few hacks e.g. putting in another graph showing 0 data value for 8 weeks and making it the same colour as the x axis, but then it would show up in the legend and that's something i don't want.

help guys!!!! thanks in advance!!!

edit:

omg guys, my hack failed!!!!! i tried to set the "Active Done Per Week" to 0 all the way - the chart didn't plot out, then i tried to set it to 10 all the way, didn't plot out too as well. help!!!!!

btw my x axis is set to 8 weeks dates already

edit edit:

omg omg omg, this is super weird now. check out weird 1 and weird 2 - x axis has 8 date values, my data series has 8 data values, but once i delete my line chart everything screws up. why why why guys?!!!! helpppp!!!!

edit edit edit:

here's the file to play with guys. thanks!!!!
chart.png
chart-settings.png
chart-data.png
chart-data-empty-cells.png
chart-hack-failed.png
chart-hack-failed-data.png
weird-1.png
weird-2.png
testing.xls
SOLUTION
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Deleted...
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi I deleted my earlier comment as ssaqibh had the answer. However I see now it does introduce a few other issues. I rebuilt the chart in the WBS Scatter sheet in the attached file, based on the Data sheet drawn from your Performance sheet. This has a number of tricks in it that achieve the functionality and look I think you wanted. Let me know if that helps

Cheers...Terry
testing-1.xls
Avatar of developingprogrammer
developingprogrammer

ASKER

inserting phantom 0 value bar charts allow us to continue using the line chart