Disappearing chart legend

I have an Excel chart. Originally it was a column chart with two data
series, comparing averages from two different data populations.
Recently I added a third series which put a dotted horizontal line
across the column chart to show the overall average.

When I added that horizontal line I named it "Overall Average = [x]"
and that name immediately showed up in the chart legend at the bottom.
Which was great.

The way this project works is: All of the data for the chart is in a
separate worksheet called "Data." I wipe out all the data and save the
workbook to be used as a template. The data is supplied by an Access
database. I copy all the data from Access and paste it into the
template's "Data" worksheet. And, voila!, we have pretty charts.

The problem is that when I paste the data into the worksheet the third
line of my legend, the one that says "Overall Average =[x]", suddenly
vanishes. The legend lines for the other two series remain.

What's happening here? How can I get it to keep all three lines of the
legend?

Thanks.
0
evancater (2)
4/13/2011 8:18:35 PM
excel 39879 articles. 2 followers. Follow

2 Replies
468 Views

Similar Articles

[PageSpeed] 25

On Apr 13, 4:18=A0pm, Evan Cater <evanca...@gmail.com> wrote:
[snip]
> The problem is that when I paste the data into the worksheet the third
> line of my legend, the one that says "Overall Average =3D[x]", suddenly
> vanishes. The legend lines for the other two series remain.
>
> What's happening here? How can I get it to keep all three lines of the
> legend?

If you look at the formula for the data series that should
be named "Overall Average =3D[x]" is there an entry in the
name part of the formula.

=3DSERIES(Sheet1!$C$3,Sheet1!$C$4:$C$6,Sheet1!$H$4:$H$6,5)

If your paste has stomped on this formula you might see some
kind of error thing there. #Ref or something.

If there is a valid formula, then look at where it points. Does that
cell contain the name as it is supposed to be? Maybe your
cut-and-paste operation stomped on it.
Socks
0
puppet_sock (108)
4/14/2011 6:09:15 PM
No, the series is fine. The first argument in the SERIES formula (the
one that defines the chart Name) is pointing to the cell that says
"Overall Average =3D[x]".

Turns out the problem is that when new data is pasted in, Excel
repaints the legend and it puts so much space between lines that my
third line is forced off the screen. Any way to get it to retain my
formatting so that it all fits on the screen?


On Apr 14, 1:09=A0pm, Puppet_Sock <puppet_s...@hotmail.com> wrote:
> On Apr 13, 4:18=A0pm, Evan Cater <evanca...@gmail.com> wrote:
> [snip]
>
> > The problem is that when I paste the data into the worksheet the third
> > line of my legend, the one that says "Overall Average =3D[x]", suddenly
> > vanishes. The legend lines for the other two series remain.
>
> > What's happening here? How can I get it to keep all three lines of the
> > legend?
>
> If you look at the formula for the data series that should
> be named "Overall Average =3D[x]" is there an entry in the
> name part of the formula.
>
> =3DSERIES(Sheet1!$C$3,Sheet1!$C$4:$C$6,Sheet1!$H$4:$H$6,5)
>
> If your paste has stomped on this formula you might see some
> kind of error thing there. #Ref or something.
>
> If there is a valid formula, then look at where it points. Does that
> cell contain the name as it is supposed to be? Maybe your
> cut-and-paste operation stomped on it.
> Socks

0
evancater (2)
4/14/2011 7:52:33 PM
Reply:

Similar Artilces:

Legend changes after closing file
I have a chart that contains a 2 series bar chart, a line chart and an xy chart (for the purpose of making a verticle line to divide two sections of the chart). In my legend i want to show both series for the bar chart and the line chart - not the xy chart. When I delete the xy chart from the legend it appears fine. Once I save my file, exit and re-open the file my legend is missing one series of the bar chart and in puts the xy chart in place of it. I've tried fixing it several times. Any suggestions? btw I'm using 2007 "Ashley" wrote: > I have a chart that c...

User Form Date Field disappeared!
I have an Excel workbook that I have been using for 6-7 years, initially in Excel 2003; and more recently in Excel 2007 (where it is saved as an .xlsm file). There is a user form to enter data, and one of the pieces of data is a Date. I could either enter the date directly, or select the different sections (M/D/Y) and use up/down arrows to change the date. I don't recall how I set this up initially. I copy this file back and forth between two different machines -- a Windows XP machine; and a Windows 7 machine. Today I opened it on the Windows 7 machine, and the Date field ...

Elements in Chart Object move around between saves
I have a line chart embedded in a worksheet. This chart has a title and legend at top and 9 text boxes that display data from cells using a formula entered in the formula bar. I carefully arrange all of these items in the chart object and then save and close the file. When I re-open the file, one or more of these objects is no longer in the location where I placed them during my previous session - they've moved around inside the chart object. Has anyone seen this problem before? Is there a way to keep this from happening? ...

how can I limit my chart
I wish to limit my chart to only the data I entered into the spreadsheet. How can I suppress the use of the row numbers as the x-axis on a line chart? I want the leftmost selected column to be my horizontal axis, relative to which every other column is displayed. I also need to control the scale of the vertical axis, but "help" doesn't help with that either. How can I do this? Richard If the first column contains text information, select any cell in the range you want charted, then click the Chart Wizard button. XL should use the first column as the x-axis values. ...

chart formatting #2
since excel already has conditional formatting for cells, it should also have conditional formatting for charts. like turning a portion of a line red if it falls, then the next blue if it rises. just a suggestion. Hi see: http://peltiertech.com/Excel/Charts/format.html#CondChart and http://peltiertech.com/Excel/Charts/format.html#CondChart2 -- Regards Frank Kabel Frankfurt, Germany "Pringles." <Pringles@discussions.microsoft.com> schrieb im Newsbeitrag news:C0975DF9-CBA2-41A2-BB20-0BA647A40846@microsoft.com... > since excel already has conditional formatting for cells...

Area Chart and Different Colors
I have a series of yearly data from the past 100 years. I would like to have certain ranges of data points to be certain colors. I can't seem to figure out how to change specific data points on an Area Chat that is using a Logarithmic scaling feature. Hi, You can't figure it out because you can't do it. Area charts don't allow the picking of data points. If this helps, please click the Yes button. Cheers, Shane Devenshire "SHalper" wrote: > I have a series of yearly data from the past 100 years. I would like > to have certain ranges of data points to ...

Chart resizing during macro run
I have a VBA macro that updates a chart with a new data range every 2 minutes, and then copies the chart to a Word document for access via html/web displaying. Over time, the actual chart gets smaller and smaller in height (everything is scaled down to adjust) in the chart area. This happened with a Win98 Excel version, and still does with a new installation of Win XP Pro/Excel 2002. Is there anything I can do to stop this automatic resizing? Thanks. You could set the chart height during the macro: Sheets("Sheet1").ChartObjects("Chart 1").Height = 250 Mike Gre...

Scaling the x-axis on a line chart
I am having trouble scaling the x-axis so that the intervals are equal. The source data is not in equal intervals, it goes 0.0,0.2,0.4,0.45,0.5,0.6,0.7,0.8,0.9 (I think). What can I do to make the interval on the graph 0.2? I'm using XP. Don't make a line chart. Make an XY chart, using the markers and lines option. Then you can double click the X axis and set any appropriate tick spacing. An XY chart treats X and Y as continuously varying numerical values. A line chart treats Y that way, but treats X as non-numerical text labels. - Jon ------- Jon Peltier, Microsoft Excel M...

table
with a vba macro I populated a table, the size of this table is variable in each execute this macro. and with this table I create pivot table, Database functions, but How to use all resources with all data? suggestions and examples thanks Marina Hi Marina Create a dynamic named range for your data. Insert>Name>Define>Mydata Refers to =OFFSET($A$1,0,0,COUNTA(A:A),10) The above example would create a range which contained as many rows as are present in column A, and the would be 10 columns wide. Change the formula as appropriate to your data location. Then in the Pivot Tab...

Chart sizes with window frame
I am trying to resize graphs using the 'chart sizes with window frame' within Tools/Options menu, and get unexpected results. Can someone please tell me how best to resize to get graph as desired. Thanks, assuming the chart is a new sheet, unclick the "size with window" option (under View) and size the chart by changing the margins and\or page layout under File | Page setup. Mary wrote: > I am trying to resize graphs using the 'chart sizes with window frame' > within Tools/Options menu, and get unexpected results. > > Can someone please tell me how ...

Help setting scale limits on chart
I have a chart based on data in an xls sheet. Both the x and y data data change when calculations are made. So I don't want the x axis variable to be fixed, per se, but I'd like the scale limit dialog to allow me to put in the cell on the sheet to set the lower and upper limit. So when I place the cell address into the dialog for min and max I get an error I tried both the address (i.e. N2, or like this =N2) both were errored out. Is there no way to do this?? I guess I need to build a macro instead that always selects the same cells and have the macro built the chart that way? I did...

Why do series names on charts disappear when re-opening excel?
I have set up some bar charts in excel to display the series names and values of each series. However, when I close the application and re-open it, the series names are gone. I have checked the chart options, and the series name box is still checked. How do I fix this? Thanks ...

BUG?: Excel scatter chart: will not plot x-values
I am trying to make a simple scatter plot. There are two columns of data. Excel will not plot values on the x- axis, only categories. I have entered the ranges for x and y values correctly, and the chart wizard shows it; yet, the chart comes out with the x-axis showing only the points numbered in the order they are entered in the spreadsheet. Oddly enough, if only one y-value is included, it plots properly; the x-axis correctly shows the range of x-values entered, and the single data point shows up at its proper xy location. as soon as I expand the y-range to more points, the x-axis r...

% completed in a Gantt Chart
Is there a way to show the percentage completed for a given task? I would like the bars in my Gantt chart to show how much of the task is completed, either by manual input of a % completed column, or based on calendar days. For example, on a given task's bar, 75% of the bar would be blue, the remaining 25% green, indicating the % completed. On Fri, 4 Jan 2008 10:56:01 -0800, Michael B. <MichaelB@discussions.microsoft.com> wrote: >Is there a way to show the percentage completed for a given task? I would >like the bars in my Gantt chart to show how much of the task is comp...

Multiple charts with same serie names
I want to have the same color in all four of my line charts per series. All charts have the same series name. Is there some type of VBA code that I can run a macro to achieve this ? example: boat = green line mnotorcycle = yellow line auto = red line Hi, You could avoid VBA, maybe, by choosing Tools, Options, Color and setting the first three colors of the Chart fills aor Chart lines area depending on the type of charts you are using. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Jennifer" wrote: > I want to have the same color in all four of m...

Displaying hidden data on a chart
I have a number of data series in a table on sheet 1, and this data is plotted as an x-y scatter in a chart on sheet 2. I want to hide a column in the table, but when i do this the related data series disappears from the chart. Is there a way to hide a column whilst still having it plotted on the chart? At the moment I have just set the column width to 1 pixel, which is a bit messy. Thanks Hi, Yes, select the chart and then via the menu Toosl > Options > Chart. unchecked the Plot visible cells only. Cheers Andy Nick Terry wrote: > I have a number of data series in a tabl...

Visio Org Chart being moved over as an object in powerpoint
I have an org that has dotted lines drawn around groups of boxes with arrows on each end and when I move or should I say copy to powerpoint one set of dotted lines become solid and I have no idea why? Can someone tell me. I suspect it's the fact that you may have copied it as an emf/wmf. Visio tries to ease the pain (since dotted lines make very large files) and converted them to straight. al "Angie" <Angie@discussions.microsoft.com> wrote in message news:6D15E003-C042-478D-8113-2453AD2855D7@microsoft.com... >I have an org that has dotted lines drawn around g...

Charting two averages
I am trying to create a chart for real estate showing the length of a task vs the final results. There are three variables representing each trial: Group ID Duration Results For each group there is a variable number of trials. It was a simple task to use a pivot table to create a bar graph of the average duration for each group, sorted in ascending order, and the pivot table included the average results in the table as well. What I want to end up with is a bar graph with the group IDs on the X axis, sorted by the average duration, charted on the Y axis. To this graph I want to add a...

expanding chart plot area
I need to be able to expand my plot area to cover several months and when I print it it will cover several pages, I did this last year but cannot remember how I did it. Every time I try and expand the chart to spread out the information it will not go any farther than the 1 page. I have tried page set up - no help. I am a beginner and not very good at this Create the chart as desired. Then, select the rows under the chart, then File | Print... | select the 'Selection' option and click the Preview button to ensure you will get what you want. -- Regards, Tushar Mehta, MS MVP ...

Charting oone number, displaying another
Hello everyone, I have a line-combo graph that is graphing one number, but I need the number in another call to be the number displayed on a chart. IS this possible? If so, how? Use either of the add-ins: the misnamed XY Chartlabeler from www.appspro.com or Chart Tools from www.j-walk.com -- Regards, Tushar Mehta, MS MVP -- Excel www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article <84c201c3e9b9$d6c67de0$a101280a@phx.gbl>, anonymous@discussions.microsoft.com says... > Hello everyone, > > I have a line-co...

copy and paste excel charts as same size
I have a workbook that I need to make many charts in. And most of these charts are the same from page to page, just with data from a different sheet &/or series. I want to copy and paste one chart several times and then update the data series. However, when I paste, the chart always comes out about 75% of the size of the original. How can I copy and paste and keep the sizing in tact? I'm using Excel 2002 SP3. Thanks, Laurie A couple ways worked for me. You didn't specify that you were pasting copies of the chart onto the same worksheet and then scrolling down or over... The t...

Pivot Chart in Excel 2007
Any ideas where the "top 10" feature is hidden in Excel 2007 Pivot Charts? I looked everywhere, consulted the help, but "Value Filters" is just not in any menu, nor is "Filter" for values. Many thanks. In the PivotChart Filter Pane, click the down-arrow next to one of the Category fileds or Series fields, then select 'Value Filters' from the drop-down menu. The Top 10 option is down the bottom. Ed Ferrero www.edferrero.com > Any ideas where the "top 10" feature is hidden in Excel 2007 Pivot > Charts? I looked everywhere, consulted th...

Column chart with additional "average" column
I created a vertical column chart with 10 columns of varying heights of data. On my spreadsheet I have a cell containing the average of the data. I need to add an additional vertical column showing this average and make it stand out from the other columns somehow (ex: color and/or height of column.) Is there a way to do this? Hi, Maybe you could have the average column as the 11th column. Extend the existing data series but don't give it a value. Create another series with data for only the 11th item. You can then format the second series so make the average stand out. Or mayb...

When I copy a picture and paste it into Publisher it disappears
I have tried to copy a picture from my documents and paste it into Publisher and the picture disappears. What version of Publisher? What happens if you go to... Insert / Picture / From File? -- John Inzer "Cathy" <Cathy@discussions.microsoft.com> wrote in message news:EF2CA50C-78D9-47DB-8799-E9C3C9171038@microsoft.com... >I have tried to copy a picture from my documents and paste it into >Publisher > and the picture disappears. I am using Office Profession Edition 2003. I tried another picture and so far it is still there. Thanks. "John Inzer"...

mail messages disappearing out smtp Queue
Hi, I've exchange2003 and domain @domain.com yesterday everything worked fine but this morning i discovered something. There were no messages in the queue. Normally there are always messages. When i send to test@domain.com it comes in the smtp queue and disappears again. while the test account doesn't exists. I also doesn't recieve a error mail. Can anyone help me? Use Message Tracking Center to find out what happened to those messages. -- Bharat Suneja MVP - Exchange www.zenprise.com NEW blog location: www.exchangepedia.com/blog ----------------------------------------------...