graph totals

How do I display totals within the graph data table without those figures 
showing up in the graph
0
Mike1154 (1216)
2/18/2005 6:33:03 PM
excel.charting 18370 articles. 0 followers. Follow

6 Replies
576 Views

Similar Articles

[PageSpeed] 28

The data table won't display totals. However, you could show the totals 
with the X axis label:

In the data source table, insert a row between the X Axis labels, and 
the first row of data.
In the cell below the first label, enter a formula that refers to the 
cell with the label, and the cell with the total, e.g.:

   =B1 & CHAR(10) & B8

Copy this formula across to the last column of data.

Select the chart
Choose Chart>Source Data
On the Series tab, for Category (X) axis labels, select the cells that 
contain the above formula
Click OK

Mike wrote:
> How do I display totals within the graph data table without those figures 
> showing up in the graph


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
2/18/2005 6:57:05 PM
Well, a series can be added plotting the totals. Then the series can be 
formatted to be hidden (no lines, no markers, no border or fill or 
whatever. The numbers will still appear in the data table.

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

Debra Dalgleish wrote:

> The data table won't display totals. However, you could show the totals 
> with the X axis label:
> 
> In the data source table, insert a row between the X Axis labels, and 
> the first row of data.
> In the cell below the first label, enter a formula that refers to the 
> cell with the label, and the cell with the total, e.g.:
> 
>   =B1 & CHAR(10) & B8
> 
> Copy this formula across to the last column of data.
> 
> Select the chart
> Choose Chart>Source Data
> On the Series tab, for Category (X) axis labels, select the cells that 
> contain the above formula
> Click OK
> 
> Mike wrote:
> 
>> How do I display totals within the graph data table without those 
>> figures showing up in the graph
> 
> 
> 
0
2/19/2005 5:24:57 AM
True, and with your technique you'd probably want to reset the maximum 
for the y-axis too.

Jon Peltier wrote:
> Well, a series can be added plotting the totals. Then the series can be 
> formatted to be hidden (no lines, no markers, no border or fill or 
> whatever. The numbers will still appear in the data table.
> 
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
> 
> Debra Dalgleish wrote:
> 
>> The data table won't display totals. However, you could show the 
>> totals with the X axis label:
>>
>> In the data source table, insert a row between the X Axis labels, and 
>> the first row of data.
>> In the cell below the first label, enter a formula that refers to the 
>> cell with the label, and the cell with the total, e.g.:
>>
>>   =B1 & CHAR(10) & B8
>>
>> Copy this formula across to the last column of data.
>>
>> Select the chart
>> Choose Chart>Source Data
>> On the Series tab, for Category (X) axis labels, select the cells that 
>> contain the above formula
>> Click OK
>>
>> Mike wrote:
>>
>>> How do I display totals within the graph data table without those 
>>> figures showing up in the graph
>>
>>
>>
>>


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
2/19/2005 3:52:42 PM
That depends. If the main chart is stacked column, I'd make the total 
series a line series before I hid it. If they're all lines then the Y 
max would be overstated by the totals (unless the hidden series were 
also plotted on the secondary axis, and this axis were hidden).

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

Debra Dalgleish wrote:
> True, and with your technique you'd probably want to reset the maximum 
> for the y-axis too.
> 
> Jon Peltier wrote:
> 
>> Well, a series can be added plotting the totals. Then the series can 
>> be formatted to be hidden (no lines, no markers, no border or fill or 
>> whatever. The numbers will still appear in the data table.
>>
>> - Jon
>> -------
>> Jon Peltier, Microsoft Excel MVP
>> Peltier Technical Services
>> Tutorials and Custom Solutions
>> http://PeltierTech.com/
>> _______
>>
>> Debra Dalgleish wrote:
>>
>>> The data table won't display totals. However, you could show the 
>>> totals with the X axis label:
>>>
>>> In the data source table, insert a row between the X Axis labels, and 
>>> the first row of data.
>>> In the cell below the first label, enter a formula that refers to the 
>>> cell with the label, and the cell with the total, e.g.:
>>>
>>>   =B1 & CHAR(10) & B8
>>>
>>> Copy this formula across to the last column of data.
>>>
>>> Select the chart
>>> Choose Chart>Source Data
>>> On the Series tab, for Category (X) axis labels, select the cells 
>>> that contain the above formula
>>> Click OK
>>>
>>> Mike wrote:
>>>
>>>> How do I display totals within the graph data table without those 
>>>> figures showing up in the graph
>>>
>>>
>>>
>>>
>>>
> 
> 
0
2/19/2005 7:41:10 PM
Or, Mike could try one of your usual suggestions, and avoid the built-in 
data table:

For an embedded chart:

http://groups.google.ca/groups?&threadm=OYJDR7UlEHA.2504%40TK2MSFTNGP14.phx.gbl

for a chart sheet:
 
http://groups.google.ca/groups?&threadm=%23fKkv%24%24UEHA.3016%40tk2msftngp13.phx.gbl

Jon Peltier wrote:
> That depends. If the main chart is stacked column, I'd make the total 
> series a line series before I hid it. If they're all lines then the Y 
> max would be overstated by the totals (unless the hidden series were 
> also plotted on the secondary axis, and this axis were hidden).
> 
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
> 
> Debra Dalgleish wrote:
> 
>> True, and with your technique you'd probably want to reset the maximum 
>> for the y-axis too.
>>
>> Jon Peltier wrote:
>>
>>> Well, a series can be added plotting the totals. Then the series can 
>>> be formatted to be hidden (no lines, no markers, no border or fill or 
>>> whatever. The numbers will still appear in the data table.
>>>
>>> - Jon
>>> -------
>>> Jon Peltier, Microsoft Excel MVP
>>> Peltier Technical Services
>>> Tutorials and Custom Solutions
>>> http://PeltierTech.com/
>>> _______
>>>
>>> Debra Dalgleish wrote:
>>>
>>>> The data table won't display totals. However, you could show the 
>>>> totals with the X axis label:
>>>>
>>>> In the data source table, insert a row between the X Axis labels, 
>>>> and the first row of data.
>>>> In the cell below the first label, enter a formula that refers to 
>>>> the cell with the label, and the cell with the total, e.g.:
>>>>
>>>>   =B1 & CHAR(10) & B8
>>>>
>>>> Copy this formula across to the last column of data.
>>>>
>>>> Select the chart
>>>> Choose Chart>Source Data
>>>> On the Series tab, for Category (X) axis labels, select the cells 
>>>> that contain the above formula
>>>> Click OK
>>>>
>>>> Mike wrote:
>>>>
>>>>> How do I display totals within the graph data table without those 
>>>>> figures showing up in the graph
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>
>>


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
2/19/2005 9:11:53 PM
If you avoid the default data table, you can simulate a much better 
effect.  See Custom Chart Table
http://www.tushar-mehta.com/excel/newsgroups/data_table/index.htm

-- 
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <29191823-F7B7-43ED-AFA3-BAADDC9E7E75@microsoft.com>, 
Mike@discussions.microsoft.com says...
> How do I display totals within the graph data table without those figures 
> showing up in the graph
> 
0
2/20/2005 1:04:12 PM
Reply:

Similar Artilces:

Make lines on a graph appear with a button
All, I need to create a graph with 40 or so lines graphed but only one up o the graph at a time with the use of buttons. I would really appreciat a hand, I am very new to this level of Excel. Thanks -- Message posted from http://www.ExcelForum.com Select all the data, and create a line chart Select a cell in the table Choose Data>Filter>AutoFilter From the dropdown list in one of the heading cells, select an item The chart will display the data for that item geistritter < wrote: > I need to create a graph with 40 or so lines graphed but only one up on > the graph at a tim...

XY scatter graph
How do I attach dates to data points when the x&y values are not dates? Hi, One way is to use Rob Bobey's free addin, Chart Labeler. http://www.appspro.com/Utilities/ChartLabeler.htm Cheers Andy gps tech wrote: > How do I attach dates to data points when the x&y values are not dates? -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info ...

Prediction Graphs
How do i make a graph predict the next value from 3 tables with info eg Table 1 has 5$ profit Table 2 has 3$ profit Table 3 has 8$ Profit how do i make a graph which predicts the next set of values from these values? -- peabushmartin ------------------------------------------------------------------------ peabushmartin's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27399 View this thread: http://www.excelforum.com/showthread.php?threadid=469414 The mechanics of getting a chart to predict future values is easy. Chart your data in the desi...

Graph within a graph
I am looking to have a pie chart show general information and then have another pie chart show a section of the origional in greater detail. Is this possible? Have you tried using a Pie in Pie chart? "Jen Kulak" wrote: > I am looking to have a pie chart show general information and then have > another pie chart show a section of the origional in greater detail. Is this > possible? That is what I am trying to figure out how to do. I know where the option is, but for some reason I cannot figure out how to enter the data correctly. "Barb R." wrote: &g...

sales graphs
How can I create a graph that would show sales/dept on the Y axis and years or months on the X axis? use a pivot chart/table in excel. You must create a datasource forst. "irene" <anonymous@discussions.microsoft.com> wrote in message news:260801c5113c$6dd4a6d0$a601280a@phx.gbl... > How can I create a graph that would show sales/dept on > the Y axis and years or months on the X axis? ...

Resizing Graphs
I have problems with Excel when there are tons of graphs i hve to create with all the graphs being on one page. Having to resize all of them when they are all ALREADY different sizes is a pain. I know how to hold down the control key and select them and resize them all at once, but some or still larger than others unless i resize them individually. Is there a way to make them all the same height adn width some how? or is this just a dream? Must i resize all of them individually? I personally use an add-in (.xla) which includes the following code (you can pick up the size of a chart with ctrl...

how to build expression with conditins and totals
Ok... I have 3 tables (tbJOBS, tbDept1, tbDept1_DwnTime) and a query that returns: all the jobs ran on range of days ( criteria: between day1 and day2); the time it took to set up; the time it tooke to produce; and, the down time for each job. I was asked to seperate the SetUp's DownTime from Production's DownTime so I added a check box on the "tbDept1_DwnTime" table. Now, my boss can tell if the down time belongs to the setup time or the production time. This works fine. Now, I'm being asked to have the report (query), automatically substract the dow...

Creating a Line graph
I have data that I want to put in a simple line graph: The data is A B 23 200 87 500 I mark the 6 cells, select insert chart / line and get a chart with both A and B labels on the x axis. I go to "select data source" and try "switch row/column" and get A and B as series names in the legend. How do I get the label A and the values 23 and 87 on the x axis, and label B and values 200 and 500 on the y axis please? (Congratulations Microsoft for making something that was so easy and powerful in Excel 2003 an absolutely primitive disgrace...

Total field on query
Hi, I used the fomular below to sum the fields. I works fine. I need to add the table name (Student) to this fomular. Please show me how. thanks. Chi Total Score: Nz([Section 1 - Pain Intensity],0)+Nz([Section 2 - Personal Care],0)+Nz([Section 3 - Lifting],0)+Nz([Section 4 - Walking],0)+Nz([Section 5 - Sitting],0)+Nz([Section 6 - Standing],0)+Nz([Section 7 - Sleeping],0)+Nz([Section 8 - Social Life],0)+Nz([Section 9 - Traveling],0)+Nz([Section 10 - Changing Degree of Pain],0) Chi, Just include the table name at the beginning of each field in brackets, followed by an exclamation mar...

Graphs on Forms
I am having a problem getting a graph to display the variables along the right axes. The graph sits on a form which is linked to a parent query. The graph's information comes from a separate crosstab query that is linked to the form via fields from the parent query. The graph fields are day (horizontal axis), race time (Y-axis) and race distance (series field). Unfortunately MS Graph automatically plots the day field as series and the race distance on the X-axis despite the fact that in the crosstab query, I have assigned 'day' as a column heading, 'race distance' as ...

joining data points on graph
I am merging two graphs and one has 23 data points, the other has 9. The category axis will have 23 data points but the 9 points are spread out, i.e. they may be at points 1, 3, 7, 15 etc of the 23-point scale. At the moment the graph will only join points that are beside each other; how do I get them to connect to the points that have spaces between them? minihana wrote on Fri, 10 Feb 2006 11:29:04 -0800: m> I am merging two graphs and one has 23 data points, the m> other has 9. The category axis will have 23 data points but m> the 9 points are spread out, i.e. they may be...

Excluding multiple checking accounts from budget totals?
Howdy! Running Money06, and I have two checking accounts synching through Bank of America. Everything there is working well, but one thing that I dont like is that the totals for BOTH accounts are added together. I have two accounts, 'personal' and 'class', and both accounts are shown in the net balance statements, the 'spending by catagory' chart on the home page etc. I would like to keep synched with my class account, but want it excluded from all of the balances.. any suggestions? "Raichean" <Raichean@discussions.microsoft.com> wrote in mes...

Automatic Graphs/ Dynamic Graphs
I have a list of test results, one row for each participant. (Each result is in a seperate column) I need to make a graph of all of their results when I click on their names. Obvously I cannot create a graph for each one, as there could be hundreds of participants, I need it to create a graph using relevent cell references for each person on the fly. I have seen this done in the past, but am unsure on how to accomplish this myself. Thankyou for any help you can give me Dan Hi, Maybe you used one of these sites previous to get your information. http://peltiertech.com/Excel/Charts/Dy...

Drawing Graphs
Hello, I would like to create an application that displays one or more graphs on tabbed pages in a single window. These would be straightforward XY coordinate line graphs of the type you would typically see on the face of an oscilloscope. Although my experience with MFC has been sporadic and minimal, I don't want to buy some 3rd party package to do this. While I could do this the hard way (i.e., get out my copy of Prosise's book and start reading), I was wondering if there is a standard class already in existance to do this, or maybe some standard Windows functions. When...

Change graph type
Hi Is there a way to flip a graph type between, say, a bar and a pie chart just by clicking a single on-screen user button? I know it's easy by right-clicking the graph and selecting the appropriate option, but it's an on-screen option I'm wondering about. Thanks in advance Steve Sorry, to clarify. I can make the change once by using the following command button code: Private Sub CommandButton1_Click() ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.ChartArea.Select ActiveChart.ChartType = xlLine End Sub However, what I want to do is revert bac...

Negative WIP Totals in GP 7.5
I've got an odd problem in GP 7.5. The WIP totals I get from the MOP1000 table are, in some cases, greater than the amount issued. In most of these cases, the qty issued is zero (there are some cases where qtyissued is greater than 0). In a lot of these cases, the MOP1210 table has the items listed as issued to the MO. I've been able to confirm this on a number of the entries in MOP1000. It looks to me like GP is not updating the table properly. Has anyone see this before? Does anyone have a process for reconciling this data? MOP1000 is the WIP stack. MOP1210 is the PickD...

3D Graph Formatting
Hi all, I'm currently doing some work with a graph and am trying to format it to reduce the spacing between the columns. For the life of me, I can't work out how to do it. Any tips? Take a look at the Word document (zipped) for an example of the graphs. Basically, I want the orange columns closer to the green columns. Many thanks, Sam B +-------------------------------------------------------------------+ |Filename: graph.zip | |Download: http://www.excelforum.com/attachment.php?postid=3906 | +-----------------------------------...

Making a worksheet and a graph that doesn't show zero values
Hi I am trying to build a template worksheet for my research. I get a file of logged data per day. I want to be able to copy the logged data from a .CSV file to a template file. Then separate worksheets will look at the raw data and import data from certain columns into that worksheet. The data is not logged in a regular way, there can be 30 cells between logging intervals or 29 or 31 cells. I can select that every cell in a column of Worksheet 1 looks at a set cell in the "Raw Data" worksheet. This will then give me a column consisting of logged data values with zeroes ...

extracting totals from within a spreadsheet
I need to know how to take different information from within a spreadsheet --where two conditions/catogories must apply so that a third column where i have inputed hours, will total for those conditions only... can anyone help? Please spell out what you want we are not mind readers -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Jovetta" <Jovetta@discussions.microsoft.com> wrote in message news:91442C6D-03DF-4FC1-A069-AAB43FA57485@microsoft.com... >I need to know how to take different information from within a spreadsheet > --where two condi...

Deducting minutes off running total
Hi There, I am currenly trying to keep a tab of my staff's extra mins/hours worked which they can use at any stage. I currently have two columns a running total of additional hours worked and a column showing hours deducted/ taken I would like to deduct the hours taken from one cell against another e.g (CELL H40) 3hrs - (CELL G46) 45mins = 2.15 How do i get it so that my (CELL H48) shows remaining hours left to use is 2.15? I look forward to your response. Does H40 actually contain "3hrs" or does it contain the number 3 ? Similarly, does G46 contain the ...

stacking graphs
i want to analyze the relationship between 10 factors over time. most of the factors have different measurements or scales, such as milliseconds, pounds, bar, percentage of 1, etc. As far as I can tell, Excel charts can only deal with 2 different scales on the 2 Y axes. does anyone know of a software add-in or program that would allow me to stack a number of charts vertically, while each uses the same scale (such as seconds) on the horizontal X axis? Hi mark, I don't know of anything that will do this automatically. Maybe your best bet is to create a chart with a single data s...

disable right mouse click on a scatter graph?
hi everyone i want to disable right mouse click on a scatter chart. i need help thanks a lot Engin H. By default, a chart embedded in a protected worksheet is Locked. This means it updates as the data updates, but the user can't change it. If it's feasible, unlock the cells the user is allowed to change, then protect the sheet. In a protected chart sheet, the right click menu items are disabled. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Engin H. wrote: > hi everyone > i want t...

How do I plot error bars on 3D graphs?
I was wondering if anyone knew if it was possible to display standard error bars in 3-D graphs in Exel. My data set is more easily interpreted in the 3D format, but have been unable to plot the error bars. Thanks!! Hi, Sorry, no error bars with 3d charts. And because 3d charts do not allow combination charts I can not think of an easy work around for this. Could you make use of the Shadow option on standard 2d charts? Cheers Andy jho105 wrote: > I was wondering if anyone knew if it was possible to display standard error > bars in 3-D graphs in Exel. My data set is more easily...

Using Graphs in Publisher
I am trying to do a mail merge in publisher and have data for a simple graph in the document come from the data source(excel file). I can't seem to figure out how to get a merge feild into the graph data. Any one with experience with this? Hi RT (rt_stevenson@yahoo.com), in the newsgroups you posted: || I am trying to do a mail merge in publisher and have data || for a simple graph in the document come from the data || source(excel file). I can't seem to figure out how to get || a merge feild into the graph data. Any one with || experience with this? You will have to create...

Graph Resolution
I have a graph on a report that only displays all the columns if the screen resolution is set to 1280X1024. Any other resolution cuts the graph off on the right. How do I get the graph to resize so it is all displayed no matter what the screen resolution is? ...