Embed data in graph

  • Thread starter Thread starter wrace
  • Start date Start date
W

wrace

Hello all,

I am using excel with phstat add-in to create the mean, median and mode for
some data. The mean, median, and mode occupy 6 rows and two columns in the
spreadsheet. These 6 rows/2 columns are what I need to embed as a legend or
a note into a graph. The graph is from the same spreadsheet but is of some
supporting data. How do I do this?

I've tried clicking in the chart area, then in the formula bar entering =
and the group of cells I want, but this only seems to work if you are trying
to display one cell. I need to display all 12 cells.

Thanks for your help.

wrace
 
You want a picture of this range of cells? Select & copy the range,
select the chart, then hold down Shift while you select Paste Picture
from the Edit menu. Or select the range, hold down Shift while you
select Copy Picture from the Edit menu (appearance - as shown on screen
and format - picture are my favorite options), select the chart, and
paste. This technique gives you a picture which is not linked to the
cells, so if the data changes, the picture no longer reflects their content.

If you want a linked picture, you'll need to use the Camera tool. It
used to be prominently featured on the Excel toolbars, back in about
Excel 95, but you can still find it. On the Tools menu, pick Customize.
On the Commands tab, select the Tools category on the left, and scroll
most of the way down the right, find the Camera button, and drag it onto
your commandbars. Select a range, click on the camera button, then click
where you want the dynamic image to go.

The camera tool is a little flaky with charts. It isn't easy to paste
the "photographed" image into an embedded chart, because the first click
that activates the chart is commandeered to paste the image, but I've
found two ways. First way: move the chart temporarily to a chart sheet,
camera-copy the range, change to the chart sheet, and click on the
chart. Second way (requires knowledge of VBA): select and camera-copy
the range, switch the the VB Editor, run this command from the Immediate
window:

activesheet.chartobjects(1).activate

then click in the chart.

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

I do have one problem still. On one worksheet I have the data displayed,
then a chart next to the data. I've copied a small calculation section of
the data to embed in the graph. This worked ok except the embedded data
doesn't display unless the graph/chart is selected. Once I click into the
spreadsheet part, the embedded data disappears from the chart.

Any ideas?

Wayne
 
Excel has a "drawing layer" floating above the worksheet. This layer
contains all the objects you can put into a worksheet, such as chart
objects, drawing objects (shapes), pictures, what have you.

I think the image you pasted is not in the chart, in the drawing layer,
but under the chart. When you paste, the chart has to be active in order
that the pasting happens in the chart. That's why I described those
tricks to get the pasted range "in" the chart. If that's too big a pain,
you can select the chart, then using the Draw menu on the Drawing
toolbar, use the Send To Back button on the Order sub menu. The table
won't be "in" the chart, but it will be visible over the chart.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Thanks for your reply Jon. For some reason I've still not been able to make
it work. I tried selecting the chart and the picture itself and used the
"send to back" command from the draw menu. In fact I tried all four "order"
commands and the picture still disappears when the chart is not selected.

The strange thing is, I have the same type of set-up on another tab with
another graph. The picture embedded in that graph stays visible whether the
chart is selected or not.

Wayne
 
Wayne -

Sounds to me that the "other" chart has the picture embedded in the
chart. The one you're still having issues with does not have the picture
embedded in it.

Did you mean you selected the chart "and" the picture, or the chart "or"
the picture, before sending to back? If you select both objects, their
relative stacking order would not change.

Did you try selecting and copying the picture, then selecting the chart
and pasting?

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