Dynamic Area Chart

[kal4000]

Is it possible to create a dynamic area chart - an area chart which updates
its values when new data is entered? I can't seem to make it work.

 

[Jon Peltier]

It works the same as with other chart types. What steps have you followed,
and what is not working the way you expected?

- Jon

 

[ShaneDevenshire]

Hi,

1. Assuming the data is vertical, when you add new data you are adding below
the previous data, and assuming you are using 2003 or 2007. Select the data
area and press Ctrl+L this opens the create table or create list dialog box.
Now as new data is added (or old data rows deleted) the chart will adjust
automatically.
2. Assume you are not using 2003 or 2007 or that your data increases to the
right. This you will need dynamic range names.

If you need this send me a request and I will send you a file showing how to
do it.
(e-mail address removed)

 

[kal4000]

I think I may have figured it out. It wasn't working because I am only
graphing 1 data point, but it couldn't chart the area for 1 point (it was
just a blank graph, but with the right axis labels). Then I added another
cell with the same value as the first and now it charts the correct area.
I'm not sure if that was the right way to "trick" it into charting it? I
have a dynamic value in my source data because the area to be charted will be
changing every week.

 

[kal4000]

Thanks. I am using dynamic range names, but I think I figured out my problem
(see above posts).

I do have another question however. On a line graph, is there a way to show
a break in the values on the y-axis (have them jump from say 60 to 1000)? I
have multiple lines graphed on the same chart, but their values are so
different that the bottom line gets lost (and I don't have a lot of space to
blow up the graph to see it).

 

[Jon Peltier]

You can fake it:

http://peltiertech.com/Excel/Charts/BrokenYAxis.html

- Jon

 

[kal4000]

Thank you! I'm going to give it a try.

You can fake it:

http://peltiertech.com/Excel/Charts/BrokenYAxis.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

 

[kal4000]

Does this work with dynamic line charts as well?

You can fake it:

http://peltiertech.com/Excel/Charts/BrokenYAxis.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

 

[kal4000]

I've been trying to get this to work for my case, and the point where I get
stuck in your directions is the paragraph where you talk about using Rob
Bovey's Chart Labeler.

1) When I double click on the Markers series, and the Patterns tab, I do not
have a black cross option.
2) I downloaded Rob Bovey's Chart Labeler, but am unsure of how to utilize it.

Further along in your directions, what do the numbers in the equation in the
green cells mean? Do these need to be changed according to my specific case?

Other than that, your directions are great and I can follow them perfectly!
Thank you.

 

[Jon Peltier]

The cross is the last marker option. You have to change the background color
to None.

The green cells are the X values of the 'Cut Off' series. This series is on
the secondary axis, which runs from X=0 on the left to X=1 on the right. The
formula to calculate the value is given in the table below the depiction of
the range. The calculations vary based on the values in columns K, L, and M.

- Jon