Problem with Stacking Column Chart

I want to do a stacking column chart which compares two 
values on the same column. Let's say Jan 03 in red and Jan 
02 in blue. 03 value is 500, and 02 value is 800. I want 
it to show red column up to 500, and then blue column up 
to 800 (so it is just showing blue for 300 worth). The 
only thing I can get a stacked column to do is add them 
together. In other words, it puts the red up to 500, and 
then blue up to 1300. That's not what I want. I have 
messed around with 3D columns as well, and can get it to 
show it okay, but then if I have another column where the 
numbers may be reversed, it doesn't work. In other words, 
if 02 value was 500 and 03 was 800. Then the colors would 
be swapped. I have seen this done somewhere before, but 
can't remember where. It would be a lot easier to explain 
with a picture which I have on an excel file if anyone 
wants to look at it. Thanks.
0
anonymous (74722)
1/22/2004 3:22:59 PM
excel.charting 18370 articles. 0 followers. Follow

3 Replies
501 Views

Similar Articles

[PageSpeed] 41

Mike -

You can do this with a clustered column chart, if the larger number is 
the first series. Make the chart, double click either series, and on the 
Options tab, change the overlap to 100%.

Or make a stacked column, and chart the difference between 2003 and 2002 
as the second series; the 300 will stack on top of the 500.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

Mike wrote:

> I want to do a stacking column chart which compares two 
> values on the same column. Let's say Jan 03 in red and Jan 
> 02 in blue. 03 value is 500, and 02 value is 800. I want 
> it to show red column up to 500, and then blue column up 
> to 800 (so it is just showing blue for 300 worth). The 
> only thing I can get a stacked column to do is add them 
> together. In other words, it puts the red up to 500, and 
> then blue up to 1300. That's not what I want. I have 
> messed around with 3D columns as well, and can get it to 
> show it okay, but then if I have another column where the 
> numbers may be reversed, it doesn't work. In other words, 
> if 02 value was 500 and 03 was 800. Then the colors would 
> be swapped. I have seen this done somewhere before, but 
> can't remember where. It would be a lot easier to explain 
> with a picture which I have on an excel file if anyone 
> wants to look at it. Thanks.

0
1/22/2004 6:28:24 PM
Thanks anyways, but this doesn't solve the problem really. 
If the year 2003 was higher than 2002 for all months, then 
overlapping would work. But if one month is higher in 
2002, and then the next month is higher in 2003, it blocks 
some of the data. You can only have one series on top of 
the other. So you have to pick one.

>-----Original Message-----
>Mike -
>
>You can do this with a clustered column chart, if the 
larger number is 
>the first series. Make the chart, double click either 
series, and on the 
>Options tab, change the overlap to 100%.
>
>Or make a stacked column, and chart the difference 
between 2003 and 2002 
>as the second series; the 300 will stack on top of the 
500.
>
>- Jon
>-------
>Jon Peltier, Microsoft Excel MVP
>Peltier Technical Services
>http://PeltierTech.com/Excel/Charts/
>_______
>
>Mike wrote:
>
>> I want to do a stacking column chart which compares two 
>> values on the same column. Let's say Jan 03 in red and 
Jan 
>> 02 in blue. 03 value is 500, and 02 value is 800. I 
want 
>> it to show red column up to 500, and then blue column 
up 
>> to 800 (so it is just showing blue for 300 worth). The 
>> only thing I can get a stacked column to do is add them 
>> together. In other words, it puts the red up to 500, 
and 
>> then blue up to 1300. That's not what I want. I have 
>> messed around with 3D columns as well, and can get it 
to 
>> show it okay, but then if I have another column where 
the 
>> numbers may be reversed, it doesn't work. In other 
words, 
>> if 02 value was 500 and 03 was 800. Then the colors 
would 
>> be swapped. I have seen this done somewhere before, but 
>> can't remember where. It would be a lot easier to 
explain 
>> with a picture which I have on an excel file if anyone 
>> wants to look at it. Thanks.
>
>.
>
0
anonymous (74722)
1/22/2004 7:53:38 PM
So do you always want to show the smaller one in front, even if it's a 
different year? Personally, I'd prefer side by side (clustered) columns, 
but try this:

Col A: Year
Col B: data for 2003
Col C: data for 2004
Col D: Bottom if 2003
Col E: Bottom if 2004
Col F: Top if 2003
Col G: Top if 2004
Col H: Value if it's a tie

Suppose the first data is in row 2, with meaningful headers in row 1 
(except that A1 is blank, of course). Put these formulas into the 
indicated cells:

D2: =IF(B2<C2,B2,0)
E2: =IF(B2<C2,0,C2)
F2: =if(B2<C2,0,B2-C2)
G2: =if(B2<C2,C2-B2,0)
H2: =if(B2=C2,B2,0)

Fill these formulas down as far as you need it. Make a stacked column 
chart from columns A and D-H: select the part of column A you need, 
including the blank at the top, then hold Ctrl while selecting the same 
part of D-H, including the headers at the top, then start the chart wizard.

Format the data from D and F the same way (for 2003), E and G the same 
(for 2004), and H another way. You'll have duplicate legend entries for 
each year. Select the legend, then select the text of the legend entry 
(not the colored box or you'll hose the series), and press Delete.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

Mike wrote:

> Thanks anyways, but this doesn't solve the problem really. 
> If the year 2003 was higher than 2002 for all months, then 
> overlapping would work. But if one month is higher in 
> 2002, and then the next month is higher in 2003, it blocks 
> some of the data. You can only have one series on top of 
> the other. So you have to pick one.
> 
> 
>>-----Original Message-----
>>Mike -
>>
>>You can do this with a clustered column chart, if the 
> 
> larger number is 
> 
>>the first series. Make the chart, double click either 
> 
> series, and on the 
> 
>>Options tab, change the overlap to 100%.
>>
>>Or make a stacked column, and chart the difference 
> 
> between 2003 and 2002 
> 
>>as the second series; the 300 will stack on top of the 
> 
> 500.
> 
>>- Jon
>>-------
>>Jon Peltier, Microsoft Excel MVP
>>Peltier Technical Services
>>http://PeltierTech.com/Excel/Charts/
>>_______
>>
>>Mike wrote:
>>
>>
>>>I want to do a stacking column chart which compares two 
>>>values on the same column. Let's say Jan 03 in red and 
> 
> Jan 
> 
>>>02 in blue. 03 value is 500, and 02 value is 800. I 
> 
> want 
> 
>>>it to show red column up to 500, and then blue column 
> 
> up 
> 
>>>to 800 (so it is just showing blue for 300 worth). The 
>>>only thing I can get a stacked column to do is add them 
>>>together. In other words, it puts the red up to 500, 
> 
> and 
> 
>>>then blue up to 1300. That's not what I want. I have 
>>>messed around with 3D columns as well, and can get it 
> 
> to 
> 
>>>show it okay, but then if I have another column where 
> 
> the 
> 
>>>numbers may be reversed, it doesn't work. In other 
> 
> words, 
> 
>>>if 02 value was 500 and 03 was 800. Then the colors 
> 
> would 
> 
>>>be swapped. I have seen this done somewhere before, but 
>>>can't remember where. It would be a lot easier to 
> 
> explain 
> 
>>>with a picture which I have on an excel file if anyone 
>>>wants to look at it. Thanks.
>>
>>.
>>

0
1/23/2004 9:53:28 PM
Reply:

Similar Artilces:

Problems installing Office 2004
I used a trial version of Office 2004 in my MacBook until it expired. I few days ago I bought I licensed Office 2004 - Student and teacher edition and tried to install it in the MacBook. I deleted the previous version by dragging all Office folder to trash. I inserted the CD but could not install using the installer since it did not appear active, so I copied the whole Office folder to the Applications. After this I tried to open an Office application, but no one opened. I tried to remove Office using the removal tool but that also did not open. Any suggestions of what should I do? ASPer...

Plot area of embedded charts unexplainably shrinks in Excel 2003 S
I am working on a large spreadsheet full of numerical data. It contains several dozen bar and line graphs embedded across several worksheets. I have a decent understanding of Excel and Excel graphs but do not use VBA. The problem I have with Excel is that it sometimes – and unpredictably – (vertically) shrinks the plot areas of the embedded graphs. This can happen between opening the document and printing it. Other times, the plot area would shrink if I click on the plot area with my mouse, and then click off on to a cell in the same worksheet. The chart area remains the same siz...

HTML spreadsheet problem
Hi, I have created a spreadsheet with Excel and have saved it to a HTML format to be browsed on the web. All works fine but the formatting has not been taken to the HTML version, and I have some fields that have capital letters in the HTML version that are small characters in the Excel format? Not really sure how this is possible? I have the same problem in reverse: internet pages opened in Excel are all fouled up. I think its due to the filters, the components that turn one sort of file into another. Sometimes, updating your filters will work. First, you might want to make sure that you...

Adding Named Formulas to a Chart
Guys I've been trying to figure out how to add a named formula to series' field. Do i just add teh name of the formula in quotes, singl quotes, or what? thanks -- Message posted from http://www.ExcelForum.com Maurice, like this: =workbook.xls!rangename -- DDM "DDM's Microsoft Office Tips and Tricks" Visit us at www.ddmcomputing.com "maurices5000 >" <<maurices5000.178a3w@excelforum-nospam.com> wrote in message news:maurices5000.178a3w@excelforum-nospam.com... > Guys I've been trying to figure out how to add a named formula to a > ...

Control can't be edited; it's bound to a replication system column 'TableName'.
I have an Access 2007 accdb database. It hast a table in it with field names TableName and FieldName and a bunch of others. This table has had a small number of records added to it (37) over the life of the database. Suddenly, the two fields TableName and FieldName cannot be edited. That is even if the table is open as a table and you try to type a new record. When you do that an error message is briefly displayed in the left hand end of the Access main window's status bar. For example if you try to input anything into the TableName field you will be blocked and you will briefl...

OWA logon problem #4
Hello all, here's my problem: Exchange 2003 with SP1 on Win2003 SP1 on a two-node-cluster. If the Clusterresource HTTP-Server restartet (after boot, crash or failover) nobody can logon to OWA because HTTP-Error 500 (Internal Server Error). After an administrator logged on via OWA (yes, he can !) all the other users can use OWA because they can log on now. Any idea ? Regards Ralf ...

Preventing Column Cell Values from Printing
I have a summary worksheet with several columns of information. There is one particular column (E25:F39) where I don't always want to print the values. Is there some simple way to toggle this column's values on and off just before hitting the print command? I was thinking of using an adjacent tick box outside the print area, which when ticked would print the column values, and vice-versa. I'm looking for a formula that would examine the tick box, and if the tick (letter 'a' in Marlett font) is not present (i.e. cell value = ""), then the values in column (E25:...

Adding a new data series to an existing chart
I have a stacked bar chart that shows monthly sales by customer type. I want to add an additional data series to show the monthly forecast, to be displayed as a point or line against the existing stacked-bar data. Any ideas? Select the data you want to plot. Drag onto the chart. XL will pop up with a dialog box asking for some information. Provide it and you will be all set. -- Regards, Tushar Mehta MS MVP Excel 2000-2004 www.tushar-mehta.com Excel, PowerPoint, and VBA tutorials and add-ins Custom Productivity Solutions leveraging MS Office In article <1418C2D7-4DF6-4945-A08A...

How do I force all capital in a column of lower case names?
I read the help, but it doesn't tell me where to enter the formula. If you do not have a blank column next to the mentioned column, then insert one. Enter the formula in a cell adjacent to the cell you want to change, and copy down. Then copy this column, go to the original column, select a cel, right click and click on Paste Special, tick values. Delete the helper column. Iow, with you lower case data in Column E, click on Column F, if not empty, and insert a column. F is then an empty column. Say your data starts in E2, then in F2 enter =UPPER(E2). Move cursor to the botto...

How to find a value with multi-column, multi-record list
I have a spreadsheet with $costs arranged according to values in both rows and columns. How do I create drop down lists for both the rows and columns and then return the cell value? eg if I have 5 rows (2-6) and 6 columns (B-G) with costs arranged in each cell within this group how do I create drop downs for each selection, 2-6 and B-G, and return the value to a selected cell. In a typical table of this nature there would be descriptive row and column headers. To do a lookup on this table you would then look for the intersection of a specific row header and a specific column ...

Problem with a date and concatenation
I got some data out of one of our in house databases and it comes out as 01/022206, for example..a missing second "/" (we need to FIX that damn databse too) I have thousands of these rows, and I need to do some analyses by date, so I need a correct date. So I was going to use text to columns> fixed width and separate the 01/02 from the 2006, then I would concatenate with (replacing the actual contents with the cell name of course) ="01/02"&"/"&"2006" But when I do the text to columns, I get the 2006 but I I don't get 01/02.. I get a...

Column Width Behavior
I use pivot tables alot - and I am working with a pivot table where I've set the column width to 9. When I pivot new information in, the column width automatically adjusts to fit, then I have to go and reset the columns back to 9. Is there anyway to override this "adust to fit" behavior and just let the pivot table fit as much text as it can into the column width I specify? Kirk, Unselect the 'Autoformat Table' option in the Options section of the Pivot Table. HTH, Gary Brown "Kirk P." wrote: > I use pivot tables alot - and I am working with a pi...

Move gridlines in a chart so that they are above the numbers
I have done a chart in excel and the gridlines are set so that the y axis numbers are inbetween the gridlines. When I choose minor gridlines I get double the gridlines. I just want a gridline above each number in my y axis Double click the axis that intersects the gridlines, and on the Scale tab, uncheck Axis Crosses Between Categories. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Tara Medway" <Tara Medway@discussions.microsoft.com> wrote in message news:47400C54-74B4-4E67-93AD-AE8B3F09BE47@microsoft.com...

"Changed by" Column is empty
Outlook 2003/Exchange 2000 Hi, in my mailbox, the "Changed by" column remains empty throughout all folders. When I change to my Team Mailbox, the "Changed by" column has the desired information. Even when I move a mail from the Team Mailbox to my mailbox, the "Changed by" column remains empty. Is this a bug or I am doing something wrong. Thank you very much. Christoph ...

MX/DNS problem in Exchange
One of my users (on SBS 2003) has intermittent problems sending mail to a particular user elsewhere. After some research, I found out that the recipient's mail mail servers were moved from one domain hosting provider to a new domain provider, but they left their website (xxx) & domain hosting (.com) with the existing domain provider. So... the recipient's domain, abc.com correctly resolves to an IP address at the old provider, 123.com. I can nslookup or ping abc.com, and it correctly resolves to the old IP address. However, the MX records (as tested externally via a websit...

CFileDialog drives me insane. Handle Problem ?
Hi, i want to communicate some strange Problems i have with CFileDialog. Hope someone can push in the right direction. 1. Problem: CFileDialog FileDlg(true); INT_PTR nResponse = FileDlg.DoModal(); if (nResponse == IDOK){}else{return;} LPSTR file = FileDlg.GetFileName().GetBuffer(); LPSTR folderPath = FileDlg.GetPathName().GetBuffer(); ULONGLONG fSize = GetFileSizeInByte(fileUrl.GetBuffer()); long GetFileSizeInByte(const char* sFileName) { std::ifstream f; f.open(sFileName, std::ios_base::binary | std::ios_base::in); if (f.eof()) { return 0; } if (!f.good()) { return -1; } ...

Prevent auto recoloring of chart when using Pivot Table data
I have crated a Pivot tabel with data I want to show in a Chart. My problem is when I refresh data the colours of the "data series" changes. For exaple I want one of my series "OK" always be shown in green colour, but after a refresh of pivot all colours changes. This problem is not only colouring. If I have made changes to my chart so that one series ar plotted on a secondary axis, and I change it to another chart type (e.g. the secondary should be shown as a line instead of column) this also changes when refreshing the data. Most irritating! Anyone that knows how to p...

Problem with CHKDSK
Hi When I run CHKDSK it gets a pass with /i but it hangs at the reboot with /f and /x and it says "CHKDSK is verifying indexes (Stage 2) 0 percent complete" and I have to reboot. I have a Seagate disk drive. I downloaded and ran Seatools for windows and got a pass on Long Drive Self Test. So I can assume that the disk is OK. The utility is read only it does not do any repair. I tried to do an Avast anti-virus scan but it stops before completing the scan and the computer has to be rebooted. Disk cleanup also freezes the computer Any idea of what I could do...

Browser Problems in Windows XP
I am having problems getting Microsoft Explorer to work in installations of Windows XP. In some installations it works just fine and in some it will begin to go to the web site and then the window closes with no error. Has anyone experienced this problem before? I can log in with all the user accounts from any windows 98 computer using the same browser version. Thanks for any help. Eric Davison wrote: > I am having problems getting Microsoft Explorer to work in > installations of Windows XP. In some installations it > works just fine and in some it will begin to go to the ...

Stationery Problem with ImageMaps OL2K / 2K2
Good Evening All, Thanks in advance for your responses. I'm developing a pretty simple stationery for a customer. Basically it's a three row table with images used as headers and footers. The footer image has 7 e-mail addresses on it and I setup a simple inline imagemap to facilitate hyperlinking. All good so far. Note that I developed the page in Notepad, and tested it using Outlook 2000 SR-1. I've sent the e-mail friends and colleagues who also use Outlook, Thunderbird, Eudora, Opera, and others. They everybody was able to utilize the imagemap links... except the custom...

Suppress Chart Legend Series
Is there a way to suppress a series in the chart legend if the series at a particular time equals zero? Thanks - Not that I can think of. Bernard "Alex" <anonymous@discussions.microsoft.com> wrote in message news:22f201c427aa$af9c0da0$a301280a@phx.gbl... > Is there a way to suppress a series in the chart legend if > the series at a particular time equals zero? > > Thanks - > Alex - You can hide a legend entry. Select the legend, then select the text of the legend entry, then press Delete. This cannot be done conditionally based on the values in the ser...

why is the chart displayed only when the chart area is clicked on?
We used the chart wizard to create a chart. The chart area is greyed out and the chart is displayed only when the chart area is clicked on. We tried to create the same chart using a new workbook and we don't have this problem. Hi, Use the menu Tools > Options. On the View tab in the Objects section make sure Show All is selected rather than place holders. Cheers Andy Vivien wrote: > We used the chart wizard to create a chart. The chart area is greyed out and > the chart is displayed only when the chart area is clicked on. We tried to > create the same chart using a...

Windows Live ID Problems
I have been using Money 2006 for the last 2 years. Since the online updates stopped as of 9/1/08, I was prompted to download a new version. I downloaded and installed MS Money Plus 2008. When I attempt to log in using my Windows Live ID and password I get the following error: "The email address or password is incorrect. Please try again." I never had this problem with the older version. My Windows Live ID and password work fine on this website and when I go to the main passport website. I am using Windows XP Home Edition, and have been for the last 5+ years. I have re-...

problems compiling xsd in .Net 2003
Hi, I have an .xsd document (Inc_B.xsd) that "includes" two more from the same folder: <xs:include schemaLocation="Inc.xsd" /> <xs:include schemaLocation="Inc_A.xsd" /> They all have the same targetNamespace="http://tempuri.org/Inc.xsd" xmlns:ir="http://tempuri.org/Inc.xsd" xmlns:xs="http://www.w3.org/2001/XMLSchema" When I try to use xsd utility from .Net command prompt to compile Inc_B.xsd - I receive the following error: Could not find file '\Inc_B folder\Inc.xsd'. I need to assign 'Inc_B.xsd'...

problems printing from MS Office Picture Manager
Operating System is Vista. When running MS Office Picture Manager (Office 2007) drop down option pulls up other printers (which work and MS XPS doc writer or install new printer. Install printer works up to and including print test page which prints normally. Like I said it pulls up the other printers and works with everything but MS office picture manager. Is there a way to associate the printer with MSOPM Have you tried right-clicking on any picture and then selecting "open with" method? This would allow you to associate all pictures to open in MSOPM when you cli...