100% Stacked Bar chart, two bars, want percent as well as real value

I am attempting to chart the following:
Series 1 - Values over the prior 30 days
Series 2 - Values over the prior 12 months

I would like to show both data series as 100% bar charts, so users of
the information can compare percentage of the last 30 days with
percentage of the same piece of data over the last 12 months.

So, for example the two data series might look like this:

Series 1
20
25
45
65

Series 2
211
324
403
822

I want to display both the actual value, and the percentage of the
total, for each series, in the labels.  For some reason, showing
percentage is not an option.  Why is this?

0
mr.sandog (20)
10/8/2007 10:09:21 PM
excel.charting 18370 articles. 0 followers. Follow

1 Replies
592 Views

Similar Articles

[PageSpeed] 29

Although showing the percentage is not an option, you can easily do so by 
creating two "dummy" series with data labels showing those percentages.  To 
do so, create a series 3 and series 4 consisting of all zeros.  Then create 
two seperate columns called "Series 3 Labels" and "Series 4 Labels".  
Calculate the percentages for series 3 and series 4 and add them to the 
"Series 3 Labels" and "Series 4 Labels" columns.  Then use a tool such as 
John Walkenbach's Chart Tools to add those data labels to series 3 and 4.  
You can download John's tool at:

http://www.j-walk.com/ss/excel/files/charttools.htm
 
-- 
John Mansfield
http://cellmatrix.net





"mr.sandog@gmail.com" wrote:

> I am attempting to chart the following:
> Series 1 - Values over the prior 30 days
> Series 2 - Values over the prior 12 months
> 
> I would like to show both data series as 100% bar charts, so users of
> the information can compare percentage of the last 30 days with
> percentage of the same piece of data over the last 12 months.
> 
> So, for example the two data series might look like this:
> 
> Series 1
> 20
> 25
> 45
> 65
> 
> Series 2
> 211
> 324
> 403
> 822
> 
> I want to display both the actual value, and the percentage of the
> total, for each series, in the labels.  For some reason, showing
> percentage is not an option.  Why is this?
> 
> 
0
10/9/2007 6:54:02 PM
Reply:

Similar Artilces:

Clustered column chart with stacked coumns
Is there a way to have a clustered column chart that has stacked columns? Thanks Hi Dave, Check Jon's page for an example. http://peltiertech.com/Excel/ChartsHowTo/ClusterStack.html Cheers Andy Dave wrote: > Is there a way to have a clustered column chart that has stacked columns? > > Thanks -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info ...

100 Radio Buttons Per Contact
Because of the nature of our business, we have multiple monthly communications with our customers/contacts. Depending on the contacts authority/importance in their company, each contact can receive around 100 communications from us a month. Communications are all sent different ways, mail, fax, email, personal call. Is the only way to designate who should receive which communication, to put 100 radio buttons on a tab at the contact level and select the appropriate ones? There has to be a better way than 100 radio buttons on a tab at the contact level. The screen is going to look like...

Stacked bar charts #3
I am trying to create a chart with 2 stacked bars that are side by side and not on top of one another. Hi Brenda, Have a look at Jon's page http://peltiertech.com/Excel/ChartsHowTo/ClusterStack.html Cheers Andy Brenda wrote: > I am trying to create a chart with 2 stacked bars that are side by side and > not on top of one another. > -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info ...

multi stacked column chart
Using Excel 2003, I need to show monthly work orders...one stacked column should show work orders opened (for high, medium and low priority status) and another stacked column should show work orders closed (for high, medium and low priority status).....on the same chart. I can't get two stacked columns on the same chart...the six pieces of data stack in one column. I would appreciate any help you can give me. Try setting the data up similar to this: Open Closed High 4 2 Medium 6 4 Low 5 3 In step 1 of the Chart Wizard, choose the stacked column chart. In step 2 of the Chart Wi...

CPU 100%
Several weeks ago, I did a clean install of Office 2003 Pro over a clean install of Windows XP Home on a machine with an AMD 2400+ and 512 megs of RAM. While tracking down strange behavior, I noticed that Outlook sometimes jumps to near 100% CPU usage and stays there. It happens when I send a message, when I delete a message and periodically at other times for no apparent reason - perhaps tied to inactivity. Clicking on 'receive messages' does not trigger the problem. It is similar to the redline problem earlier this year with some machines after installing Office 2000 SR-3. ...

Want listbox to move when scrolling down spreadsheet
I have a calendar control on my spreadsheet which moves whenever I scroll down and I want my listboxes to behave the same way, but they're not. The Format Control property for the listboxes is set to "Move but don't size with cells", so I don't know what else to do. I'm using Excel 2002. Any help with this would be greatly appreciated. Thanks Kevin Do you have any code that moves that calendar control when you scroll/change selection? If you do, maybe you can use very similar code to keep track of the listboxes. An easier alternative... Move the listboxes to...

I need FR on menu bar
I am trying to run Financial statements from Accpac in Excel.. the instructions I have tell me to go to FR on menu bar ...then click on FR view....I don't see that icon... I'm not familiar with Accpac, but I assume that it is an add-in for Excel. When you load the add-in, it should create its own menu items. Go to the Tools menu, choose Add-Ins, and install the add-in from there. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "jim kennedy" <jim kennedy@discussions.microsoft.com> wrote in message news:AE944...

Multiple Pie Chart
How do you create a multiple pie chart? e.g. if you have 3 groups A, B and C, each which 3 different category X, Y and Z. Is it possible to put it all into one chart? I have done it but it has to be done individually and it takes time. There's no chart type that combines multiple independent pies in the same chart. Your approach (three sepaarate charts) is how I would do it. Unless you want to try one of the approaches in this article: http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=471 These look great, combining the data into a single apparent chart, but they act...

How can I make a 3d stacked chart?
I can create a 3d stacked chart with only x-y axis or I can create a 3d with x-y-z axis but I'm trying to create a stacked charet in x-y-z axis (A program number on x axis with items overdue stacked on top of items due in y axis, with past 5 weeks of data for that program on z axis. ...

Summing two columns
Hi I have values in two columns, A and B in a single worksheet. I want to multiply values in column A with the inverse order of values in column B and add them and put the results in column C. For example at C1, I want to have A1*B1, at C2, A2*B1+A1*B1, at C3, A3*B1+A2*B2+A1*B3 and at C4, A4*B1+A3*B2+A2*B3+A1*B4, etc. How can I accomplish this job with SUMPRODUCT or VBA and fill that column automatically. Any help is welcome. Thanks HI, Here is an inelegant subroutine Option Base 1 Function doit(N, rangeA As Range, rangeB As Range) Dim tempA(10), tempB(10) j = 1 For Each cell In rang...

Excel 2007 stacked column charts
When I create a stacked column chart I only get very narrow coloured lines, instead of the usual thicker vertical bars. Please advise how I can produce the bars. (This question asked two days ago, and there was an auto response to say that an answer had been posted, but the link produced only a blank screen). Hi, There seems to be something wrong with auto response, you should check the previous post. One possible solution is to right-click a column and choose Format Data Series, and in the first tab decrease the Gap Width. -- If this helps, please click the Yes button. Cheers, S...

Changing the Display Name causes users to see the old and the new Names as two mailboxes
After changing the Display name, for example, from "Jane Jackson" to "Jane Brown" users who kept Jane's old messages in their inbox, are seeing both Jane Jackson and Jane Brown as different users. sorting mail by the from field will of course cause a split between messages from the past and from the present. My question is: how can I join the old and the new names at the clients, so both Janes would as one entity? Thanks On 19 Jan 2007 05:13:14 -0800, avic2001@hotmail.com wrote: >After changing the Display name, for example, from "Jane Jackson" to >&q...

How to reduce Excel worksheet view and find horizontal scroll bar
I can't see the bottom of my Excel workbook including the tabs and the scroll bar. What can I do to fix it. Carolyn, Perhaps:- Tools|options|view check - sheet tabs and horizontal scrollbar. Mike "CarolynC" wrote: > I can't see the bottom of my Excel workbook including the tabs and the scroll > bar. What can I do to fix it. First of all.........under Tools>Options>View are sheet tabs and scroll bars enabled? If so, is the window maximized? Can you grab the Title bar and move the window up? If not, try Window>Arrange. Gord Dibben MS Excel MVP ...

If 100.0% display 100%
I have a sheet where several numbers are compared and the difference is displayed in percentage. For instance, if the first number is 1 and the second number is 2, then the difference is +1 or 100%. The numbers I have are much more complex and varied, so my percentage is actually set to display 1 decimal place. But there are some occasions when the percentage is a whole number, like 100%, and I don't want that trailing zero there. Is that possible to remove somehow, leaving numbers that are not whole with their .7 or .2 decimals? Sorry this was meant to be posted to the Excel group...

SQL Server 2008
What is the 'proper' way to allow a user to backup/dump a SQL database directly to his desktop? Thanks Blake (blake.duffey@gmail.com) writes: > What is the 'proper' way to allow a user to backup/dump a SQL database > directly to his desktop? And SQL Server is running on some central server? SQL Server needs to have access to the user's disks. That may not be a good thing. The best is probably to backup to some common area, and then the user can pick up the backup from that place. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Lin...

Shadowing of a bar chart
How do I shade a section of a bar in a bar chart? Amcbeth -- If you right-click on that segment of the bar, you'll get a list of options. Choose 'format data series' and you can change the color, click the 'shadow' box, or add other textures and features. hth "Amcbeth" wrote: > How do I shade a section of a bar in a bar chart? ...

Pivot Chart Filter
I have two Pivot charts that compare similar data and have them displayed side-by-side. When I change the filters in one chart, I want to apply the same filter for the other chart. Is there a way to have one Pivot Chart Filter Pane control two or more Pivot Charts without having to click each chart and change to filter for each one? ...

moving to a specific cell based on another cell value
I know about the go to function in excel, but is there a way to jump to a cell based on value of another cell? EX: If cell A1 = "duct" then jump to A3. If cell A1 = "fittings" then jump to A3005 Any help would be greatly appreciated! Paste the following code into the VBA module for the worksheet: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub If Range("A1").Value = "duct" Then Range("A3").Activate ElseIf Range("A1").Value = "...

Do you want to save changes?
Everytime a shared workbook is opened and then closed without changes being made to it, users are prompted by the following: This document has been modified. Do you want to save changes? Yes: Saves your changes. No: Discards your changes. Cancel Keeps this document open. Using excel 2002. Again, no changes are made and the prompt still comes up. Thanks in advance for any help. NWH Hi not sure but could be a 'feature' of shared workbooks. another reason could be the use of volatile functions: http://www.decisionmodels.com/calcsecretsi.htm -- Regards Frank Kabel...

Excel 2007 - Insert Picture Bug - Locks Up Excel and CSRSS.EXE to 100%
I had a complex workbook, lots of tabs with a company logo on the top (in the worksheet not the header). Any time I accessed the "PrintArea" "PrintPreview" or other page layout features of Excel2007 the worksheet would lock up and the inserted graphics would disappear or blink on and off as you scrolled around the Excel work sheet (if you could scroll at all before it locked up). I was able to re create this behavior on a new blank worksheet (.xls and .xlsx). Any picture from .jpg, .wmf, or .png was causing the same problem. Inserted picture displays fine until...

Different color for data sub-set for bar chart.
I have created a vertical bar chart with 2 bars for each of 5 years showing comparison data. For the first year, I would like to have the color of one of the bars different from the color in the subsequent 4 years to show special program initiation. Is there a way to do this? -- PStJ Click on the series, then click on the point, then select Selected Point from the Format menu. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Peter St. John" <PeterStJohn@discussions.microsoft.com> wrote in message news:9E005...

bar chart Axis X
how to change the axis x for 2 series start 0 at the middle.... Ex: 70-----35----0-----35----70 ______ __________ __ _______ Is it a horizontal bar chart? Then the horizontal axis is actually the Y axis (dependent variable) Double click on the axis, click on the Scale tab, and change the min, max, and major unit as needed. If you want the bars to go left and right from the Y=0 vertical line, make sure the X axis crosses the Y axis at zero (this is in the same dialog as setting the axis scale). Then double click the X axis, and on the Patterns tab for ...

Get excel to list values that occur within raw data
If I enter new information into excel eg heights of individual people. How can I get excel to look at the cells where the data is and then produce a list of the different heights measured. Eg heights: 162, 180, 145, 180, 123, 123, 180, 144, 145, 145 I need excel to list values that occur: 123, 144, 145, 162, 180 At this stage I'm not interested in the frequency. I just want excel to look at my data and say these are the values that occur in the data youve just entered. Try to use Pivot Table. Put the required range on the right and same on the main (grouping section). which yo...

What is the best chart for this....
When I want to compare how each salesperson's sales contributes to Total Sales, I can use a pie chart. But what do I use when I want to compare how each salesperson performed against the *average* of total sales? I am envisioning a vertical bar chart with a horizontal line at the average, but I need a solution that can be generated by a macro. Any advice? Thanks in advance. Patti Patti - That's how you would do it, a column chart with a horizontal line. On this web page, I give a couple techniques to achieve the horizontal line: http://peltiertech.com/Excel/Charts/AddLine...

Selecting chart elements without having to double-click on them
Hi there, I have created a chart with a secondary y-axis. I have unchecked the y-axis's tickboxes and so on so that it is effectively hidden. The problem is when I want to select it again in order to change the scale setting (for example) I can't find it. So even if I hover in that area with my mouse, Excel does not identify it, making it impossible to double-click to format the axis. I find this happens when to chart becomes complex/busy, making it difficult to select elements with the mouse. So my question is this - is there another way to select chart elements, in stead of using the...