Column chart with both value x and y-axis

I was wondering if it is possible to define the x and y axes from two
columns of data for a column chart. For example, I want the x-axis to
be "Days Past Due" and the y-axis to be "Quantity" for a bar chart.
When I try to do it now, it plots both sets of data as separate bar
series and I want it similar to XY scatter but with colums instead.
Thanks for any ideas or suggestions you may have.


-- 
chris46521
------------------------------------------------------------------------
chris46521's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=35909
View this thread: http://www.excelforum.com/showthread.php?threadid=556987

0
6/29/2006 4:21:36 PM
excel.charting 18370 articles. 0 followers. Follow

4 Replies
480 Views

Similar Articles

[PageSpeed] 46

But that's not what a column chart is good at. Columns cannot be located in 
this way, and if they could be, you'd still have to worry about overlapping 
and so forth. Why do you need columns? If you want a connection to the axis, 
you can use error bars, using a custom measurement of -100% of the value. 
But I think even that little bit of decoration will not add value to the 
chart.

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


"chris46521" <chris46521.2a69bo_1151598308.2898@excelforum-nospam.com> wrote 
in message news:chris46521.2a69bo_1151598308.2898@excelforum-nospam.com...
>
> I was wondering if it is possible to define the x and y axes from two
> columns of data for a column chart. For example, I want the x-axis to
> be "Days Past Due" and the y-axis to be "Quantity" for a bar chart.
> When I try to do it now, it plots both sets of data as separate bar
> series and I want it similar to XY scatter but with colums instead.
> Thanks for any ideas or suggestions you may have.
>
> -- 
> chris46521


0
jonxlmvpNO (4558)
6/30/2006 4:27:35 AM
Thank you for responding. Management wants to see information presented
in the form of columns and that is why I wanted to know if a column
chart could be used similar to an XY scatter. Is there a VBA or a
plug-in that could accomplish this?
Thanks, Chris

Jon Peltier Wrote: 
> But that's not what a column chart is good at. Columns cannot be located
> in
> this way, and if they could be, you'd still have to worry about
> overlapping
> and so forth. Why do you need columns? If you want a connection to the
> axis,
> you can use error bars, using a custom measurement of -100% of the
> value.
> But I think even that little bit of decoration will not add value to
> the
> chart.
> 
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> http://PeltierTech.com
> _______
> 
> 
> "chris46521" <chris46521.2a69bo_1151598308.2898@excelforum-nospam.com>
> wrote
> in message
> news:chris46521.2a69bo_1151598308.2898@excelforum-nospam.com...
> >
> > I was wondering if it is possible to define the x and y axes from
> two
> > columns of data for a column chart. For example, I want the x-axis
> to
> > be "Days Past Due" and the y-axis to be "Quantity" for a bar chart.
> > When I try to do it now, it plots both sets of data as separate bar
> > series and I want it similar to XY scatter but with colums instead.
> > Thanks for any ideas or suggestions you may have.
> >
> > --
> > chris46521


-- 
chris46521
------------------------------------------------------------------------
chris46521's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=35909
View this thread: http://www.excelforum.com/showthread.php?threadid=556987

0
6/30/2006 4:21:24 PM
I looked at your original question again. Here's what to do.

1. Delete the label above the days past due data (you need a blank cell to 
make it easier), and make sure there's a label above the Quantity data.
2. Select a single cell in the data range.
3. Create a column chart.

This gives you almost what you want. If there are gaps in the Days Past Due 
data, there will not be gaps in the chart. To get these gaps, go to Chart 
Options on the Chart menu, and on the Axes tab, choose Time Scale under 
Category Axis. This gives you some funny looking dates along the X axis. 
Double click the axis, and on the number tab, choose General.

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


"chris46521" <chris46521.2a83zn_1151684702.4757@excelforum-nospam.com> wrote 
in message news:chris46521.2a83zn_1151684702.4757@excelforum-nospam.com...
>
> Thank you for responding. Management wants to see information presented
> in the form of columns and that is why I wanted to know if a column
> chart could be used similar to an XY scatter. Is there a VBA or a
> plug-in that could accomplish this?
> Thanks, Chris
>
> Jon Peltier Wrote:
>> But that's not what a column chart is good at. Columns cannot be located
>> in
>> this way, and if they could be, you'd still have to worry about
>> overlapping
>> and so forth. Why do you need columns? If you want a connection to the
>> axis,
>> you can use error bars, using a custom measurement of -100% of the
>> value.
>> But I think even that little bit of decoration will not add value to
>> the
>> chart.
>>
>> - Jon
>> -------
>> Jon Peltier, Microsoft Excel MVP
>> Tutorials and Custom Solutions
>> http://PeltierTech.com
>> _______
>>
>>
>> "chris46521" <chris46521.2a69bo_1151598308.2898@excelforum-nospam.com>
>> wrote
>> in message
>> news:chris46521.2a69bo_1151598308.2898@excelforum-nospam.com...
>> >
>> > I was wondering if it is possible to define the x and y axes from
>> two
>> > columns of data for a column chart. For example, I want the x-axis
>> to
>> > be "Days Past Due" and the y-axis to be "Quantity" for a bar chart.
>> > When I try to do it now, it plots both sets of data as separate bar
>> > series and I want it similar to XY scatter but with colums instead.
>> > Thanks for any ideas or suggestions you may have.
>> >
>> > --
>> > chris46521
>
>
> -- 
> chris46521
> ------------------------------------------------------------------------
> chris46521's Profile: 
> http://www.excelforum.com/member.php?action=getinfo&userid=35909
> View this thread: http://www.excelforum.com/showthread.php?threadid=556987
> 


0
jonxlmvpNO (4558)
7/2/2006 1:47:41 AM
That worked perfectly. Thank you so much for your help!

Jon Peltier Wrote: 
> I looked at your original question again. Here's what to do.
> 
> 1. Delete the label above the days past due data (you need a blank cell
> to
> make it easier), and make sure there's a label above the Quantity
> data.
> 2. Select a single cell in the data range.
> 3. Create a column chart.
> 
> This gives you almost what you want. If there are gaps in the Days Past
> Due
> data, there will not be gaps in the chart. To get these gaps, go to
> Chart
> Options on the Chart menu, and on the Axes tab, choose Time Scale
> under
> Category Axis. This gives you some funny looking dates along the X
> axis.
> Double click the axis, and on the number tab, choose General.
> 
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> http://PeltierTech.com
> _______
> 
> 
> "chris46521" <chris46521.2a83zn_1151684702.4757@excelforum-nospam.com>
> wrote
> in message
> news:chris46521.2a83zn_1151684702.4757@excelforum-nospam.com...
> >
> > Thank you for responding. Management wants to see information
> presented
> > in the form of columns and that is why I wanted to know if a column
> > chart could be used similar to an XY scatter. Is there a VBA or a
> > plug-in that could accomplish this?
> > Thanks, Chris
> >
> > Jon Peltier Wrote:
> >> But that's not what a column chart is good at. Columns cannot be
> located
> >> in
> >> this way, and if they could be, you'd still have to worry about
> >> overlapping
> >> and so forth. Why do you need columns? If you want a connection to
> the
> >> axis,
> >> you can use error bars, using a custom measurement of -100% of the
> >> value.
> >> But I think even that little bit of decoration will not add value
> to
> >> the
> >> chart.
> >>
> >> - Jon
> >> -------
> >> Jon Peltier, Microsoft Excel MVP
> >> Tutorials and Custom Solutions
> >> http://PeltierTech.com
> >> _______
> >>
> >>
> >> "chris46521"
> <chris46521.2a69bo_1151598308.2898@excelforum-nospam.com>
> >> wrote
> >> in message
> >> news:chris46521.2a69bo_1151598308.2898@excelforum-nospam.com...
> >> >
> >> > I was wondering if it is possible to define the x and y axes from
> >> two
> >> > columns of data for a column chart. For example, I want the
> x-axis
> >> to
> >> > be "Days Past Due" and the y-axis to be "Quantity" for a bar
> chart.
> >> > When I try to do it now, it plots both sets of data as separate
> bar
> >> > series and I want it similar to XY scatter but with colums
> instead.
> >> > Thanks for any ideas or suggestions you may have.
> >> >
> >> > --
> >> > chris46521
> >
> >
> > --
> > chris46521
> >
> ------------------------------------------------------------------------
> > chris46521's Profile:
> > http://www.excelforum.com/member.php?action=getinfo&userid=35909
> > View this thread:
> http://www.excelforum.com/showthread.php?threadid=556987
> >


-- 
chris46521
------------------------------------------------------------------------
chris46521's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=35909
View this thread: http://www.excelforum.com/showthread.php?threadid=556987

0
7/3/2006 2:34:35 PM
Reply:

Similar Artilces:

In Macro: return column value of active cell
Using the VB tools in Excel 2003, am trying to return the column value of the active cell. Any suggestions? Hi Jim col_no = ActiveCell.column Frank Jim wrote: > Using the VB tools in Excel 2003, am trying to return the > column value of the active cell. Any suggestions? Jim, In the immediate window ?ActiveCell.Column -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Jim" <anonymous@discussions.microsoft.com> wrote in message news:74d001c3e771$35a5e0d0$a001280a@phx.g...

Can I make different colour gridlines in a single chart in Excel?
One particular horizontal gridline in my Excel chart marks a significant limit. This gridline should be highlighted. The gridlines above and below this can remain black. The best bet is to introduce an extra series, with X values at (or beyond) the min and max limits for your chart, and Y values at your particular value. -- David Biddulph "cambium" <cambium@discussions.microsoft.com> wrote in message news:06958E33-1015-4C68-A6D8-A5AA0B07FA37@microsoft.com... > One particular horizontal gridline in my Excel chart marks a significant > limit. This gridline shoul...

can i somehow put two cells into one column or split a column
Data>text to columns to split =A1&B1 in a third cell with concatenate 2 (or more) -- Regards, Peo Sjoblom (No private emails please) "Chrono" <Chrono@discussions.microsoft.com> wrote in message news:E211310C-11BC-423A-92AF-5F0863940E4D@microsoft.com... > ...

Multiple charts one one report
I have created a report which takes data through a select query. Now when i've put 8 charts on the report which also takes the data from the same dynaset of the query. now when i preview report i need to enter the filtering parameter 17 times. How to resolve this ? One way was that i've converted that select query into make table query and routed the data from the temp table to the report and charts but in this i get a deletion confirmation message every time i preview report. i've disable messages from tools/options/edit/find.. Any ideas ???????? Try using a form to enter the i...

Pivot Chart #6
In Pivot Chart Wizard, How do you change the "Series in" from Rows to Columns and from Columns to Rows. Thanks. Those buttons aren't available in a pivot chart. You can change the series by dragging the field buttons between the category (Row) and series (Column) areas. Anna wrote: > In Pivot Chart Wizard, How do you change the "Series in" > from Rows to Columns and from Columns to Rows. Thanks. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html ...

Line Chart #7
I am using Excel 2000. I have a line chart that charts 4 sets of data, by month, for 1 year. I am using the 4th type sub chart type of line chart. My data points are OK except for the last point which is always down at "0" - the bottom of my chart. This causes, what I call a tail to hang down from the previous point to the bottom of my chart. I understand why this happens because there is no data for the next point. But I do not like the "tail" hanging down. If I only hi-lite the cells that have data, my chart looks like it only has 2 or 3 months. I can not ke...

Display Formula Values
I have a number of formulas that are displaying the result using the value that is displayed in a referenced cell rather than what was actually input. For example, if my formula in cell A1 is B1*C1 and my value in C1 is .075 and the cell is formatted to display a percentage with no decimal places my formula in B1 uses 8% instead of 7.5%. How can I address this across the entire workbook rather than having to change the format in each of the necessary cells. Thanks for your help. What formula do you have in B1? -- Regards, RD --------------------------------------------------------...

Another prob with combination chart
I am using Excel 2003 I have a "custom line - column plotted on 2 axis. curently I have one vertical column and one line. But I want to add a second line to the graph. When I do this I loose the first line and only the 2nd line appears. Can't I have 2 lines from the 2nd axis on a graph with 1 column from the 1st axis? What am I doing wrong? I would appreciate any help someone can give me. aurora I'll try to answer both your posts. You're going to need to create a custom chart. First, create a chart with all the series (past data, goal, etc) and make it the type o...

excel 3 column merge
I have an excel file with 4,000 rows and 15 columns. I need to merge three columns so I created a new column for the merged data. Entered formula =M1&","&N1&"/"&O1 and that worked as intended for first row. How do I apply formula to remaining 3,999 rows? One way: Copy the first cell. Select the remaining cells in which the formulae are to appear (i.e., in a column). Paste. In article <5EA9FC86-5D1D-4F5E-A523-22A55DE58519@microsoft.com>, flaglernet <flaglernet@discussions.microsoft.com> wrote: > I have an excel file wit...

Scatterplot Duplicate Values Charting Help (URGENT!!!) GMAIL invite offer inside
Sorry to be so rude in the title but I'm making a scatterplot in Exce and I have XY values like (1,4), (1,3.5), and (1,4). When I plot the with the scatterplot wizard, the (1,4)s are indistinguishable. I'd lik Excel to differentiate them. Any help would be GREATLY appreciated. I'l throw in a gmail invite for anyone who can do this for me. . I've bee using Photoshop to paste numbers next to the points and this is reall tedious. THANKS -- Message posted from http://www.ExcelForum.com Hi! Excel will do a brilliant job of putting (1,4) in the same place ever time it encounters...

Column Selection using external programs
Hi, I am trying to use matlab to extract data from an excel worsheet. however, the data is contained from columns "A" to like "FZ". Is there a way to run a loop to increment the "A" column by column? Thanks. -- olitong ------------------------------------------------------------------------ olitong's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29805 View this thread: http://www.excelforum.com/showthread.php?threadid=495230 ...

Returning a colored value in If function
Hello all, =CONCATENATE(IF(C9>1.15*D8,"SELL"," "),IF(AND((D8*1.15>=C9),(D8*1.05<=C9)),"HOLD"," "),IF(AND((D8*1.05>C9),(D8*0.95<=C9)),"APPROACHING BUY"," "),IF(C9<0.95*D8,"BUY"," ")) How can i return a bold red "Sell" for this function? formula cannot do that but conditional formatting will. "ExcelQuestion" wrote: > Hello all, > > =CONCATENATE(IF(C9>1.15*D8,"SELL"," > "),IF(AND((D8*1.15>=C9),(D8*1.05<=C9)),"HOLD",&quo...

assign a value to a cell
is it possible to assign a cell or range of cells to have a value of 1 no matter what you type into that cell Yes, using VB code; however, it is unclear what you want to do if the user tries to delete the value. The following code assume the user cannot delete the value... Right click the tab at the bottom of the worksheet, select View Code, and copy/paste the following code into the code window that appeared... ' ************* Start of Code ************* Private Sub Worksheet_Change(ByVal Target As Range) Const RangeAddress As String = "B3:C5" If Not Intersect(Target,...

Family Tree Charting templates?
Are there templates available on Excel (or Word or Powerpoint) to easily chart family trees? Powerpoint has a default template (Choose Format>Desig Templates...>Other Layouts) called "Organization Chart" You could customise it to represent a family tree. Hope this helps. Regards, Leni -- LeninVM ----------------------------------------------------------------------- LeninVMS's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=668 View this thread: http://www.excelforum.com/showthread.php?threadid=18529 ...

updating values
on a form frmSetPrintOptions, i have a combobox: cboPresetOption the data source for the form includes the table [tbeFixtureSchedulePrintOptions] on the change event of cboPresetOption, i want to update the field in the datasource table [PresetOption], that is not displayed on the form, with a value from the table [tblFixtureSchedulePrintOptions], in the field [PresetOption] note: table [tblFixtureSchedulePrintOptions] is also the row source for cboPresetOption, and has only (1) record and the (2) tables are similarily named: one starting with tbE, and the other tbL to ...

Gantt chart report
I received a PDF file of a Gantt chart. It shows an * after each date in the early start column. Does this mean the date is constrained? I think that's something you see in Primavera, but I haven't seen this in Project - unless a user manually typed in the date w/ an asterisk into a text field. Are you sure you're not looking at a PDF of a Primavera schedule? I'd check with whomever sent you the original schedule. - Andrew Lavinsky Blog: http://blogs.catapultsystems.com/epm > I received a PDF file of a Gantt chart. It shows an * after each > ...

Fix column width with when sychronizing a list to a Sharepoint sit
When I synchronize my Excel file with a Sharepoint web site, the column widths change (i.e. get wider) in an attempt to allow all of the text (wrappred) to be visable. This repeats itself with every saving iteration which makes the file to big to print and I then have to manually reset the column widths. Is there a way I can keep the column width the same when synchronizing? Or, alternatively, is there a way I can easily restore the column widths without have to manually resize them? Thanks, Bob. Click on the Data. then, get the External Data Toolbar, if it didn't pop up on its own...

How to find/replace data in column
Hi, In Excel I have a column of 0's as text and I want to change the whole column to 1 but still as text. How can I do this? I tried a find/replace, but the 1 was entered as a number not as text. Thanks Rock Thank you Gord... The column I wish to change is actually K. Does this mean the A1 in the formula should in fact be K1 ? Rock Gord Dibben wrote: > Rock > > In an adjacent column. > > =IF(A1="0",TEXT(A1,"1")) > > Drag/copy down. > > > Gord Dibben Excel MVP > > > On Wed, 16 Nov 2005 08:26:57 +1100, Rock <194...

retreiving Status bar values
hi to all i am beginner to MFC but not C++, i am writing small programs to learn MFC better but currently i am stuck up in a place where i need to access the staus bar values of a window I have 2 applications running, one is mine and from my program i am not able to access the other application's window's status bar values i have used findwindow API that returns the handle to the other window(other application), then what should i do?, can i use CStatusBar functions here? i will be grateful if anyone can send a snippet that can do this. -- thanks a lot in advance bye ...

Non-Numeric values in X axis of a scatter chart
Hi, I'm trying to figure out how to create a scatter chart with non-numeri values for the x axis. I'm trying to setup a scatter chart showin availability of servers, so for the Y axis I want to show a percentag (between 1-100%), and for the X axis, I want to show the name of th service (mail, news, etc). However, it seems that with a scatter char I can only do numeric values on both axis. Can it be done? Thx -- penas ----------------------------------------------------------------------- penasm's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1583 View ...

Limitation in number of charts
A friend of mine has run into a problem when adding charts to a workbook. Right now he has about 60 charts in the workbook, and when he tries to add another one, he gets in error telling him, that he exceeds "the maiximum munber of FONTS in a workbook". He has enough memory so that is not the problem, and as far as I have been able to find, that is the only limitation concerning the number of charts (btw he needs about 350 charts in the workbook). The only other linitations I have found deals with limitations for a single chart. Does anyone know if the details in different charts a...

conditional color formatting for line chart background color
Does anyone know how to or even if it's possible in RS2008 to change the background color of a chart based on the data point values being charted....for ex If the data point value is > 80, then the background color for the that y axis range should be "RED", if data point is between 61 & 79, the backgrouund color for that data range should be "YELLOW" & if the data point is <60, the background color of that data range should be "GREEN".. THis would essentially create a "RED-YELLOW-GREEN" banding appreance for the backgr...

Keeping Chart Series Formats whilst sorting
Hi, I have created a bubble chart where I have set specific colours to each series. However if I then sort the source table the formating doesn't follow and they all change. Any way to fix the format so this doesn't happen and I keep consistent colours irrespective of sorting the source data ? regards Greg -- Fullam ------------------------------------------------------------------------ Fullam's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=33363 View this thread: http://www.excelforum.com/showthread.php?threadid=538391 ...

how do I create a quadrant chart in Excel or PowerPoint?
I'm trying to set up a quadrant chart, where a data point represents both the x-axis and y-axis: Ex: 100 l l 50 l l <*> data point = (25,5) 0 + - - - - - - - - - - - - - - -- 5 10 15 Tavo - In Excel, arrange data with X values in a column on the left and Y values in an adjacent column on the right. Select the X and Y values. Click the ChartWizard, and select the XY (Scatter) chart type. - Mike www.mikemiddleton.com "Tavo" <Tavo@discussions.microsoft.com> wrote in message news:019A0E2A-A325-4443-AD97-...

Receiving error inserting charts in PowerPoint and Word.
When I try to insert a chart in PowerPont and Word, I receive an error that "Some chart types cannot be combined with other chart types. Please select a different chart type." It doesn't matter what type of chart I choose. It is independent of user profile on the computers, and the Office diagnostics does not fix it. I have tried repairing the installation, but it is on several machines, not just one. I have also tried opening Excel before Powerpoint, as suggested on several websites, but that doesn't help either. ...