automate charting #2

To continue the analogy: 

The four columns of numbers represent the different activities of the
fruit eg.  in stock, on order, sitting on the shelf, sitting by the
counter. The first two columns of numbers are in thousands, and so get
plotted against the left y-axis. The last two columns of numbers are
in tens, and so get plotted the right y-axis.

The month column is plotted along the x-axis.

Is the answer still a PivotTable with an associated PivotChart?

Rgds,
Ooi
0
ooievaar6 (5)
1/9/2004 8:55:44 AM
excel.charting 18370 articles. 0 followers. Follow

5 Replies
358 Views

Similar Articles

[PageSpeed] 38

Ooi -

Use a Pivot Table, with a Pivot Chart or a Regular Chart.

Put headers on your data (which is already ideally set up for pivot 
table analysis, by the way, good job):

Fruit	Date	Stock	Order	Shelf	Counter

Now make a pivot table from the range, with Fruit in the page field, 
Date in the Column field, and Stock, Order, Shelf, and Counter in the 
Data field. The table looks like this:

Fruit	apples			

		Date			
Data		Jan-03	Feb-03	Mar-03	Apr-03
Sum of Stock	1000	2000	1010	2010
Sum of Order	2000	3000	2020	5000
Sum of Shelf	3	4	1	6
Sum of Counter	4	4	1	6

Change the selected fruit in the Page field dropdown and the table and 
chart will update.

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

ooievaar6 wrote:

> To continue the analogy: 
> 
> The four columns of numbers represent the different activities of the
> fruit eg.  in stock, on order, sitting on the shelf, sitting by the
> counter. The first two columns of numbers are in thousands, and so get
> plotted against the left y-axis. The last two columns of numbers are
> in tens, and so get plotted the right y-axis.
> 
> The month column is plotted along the x-axis.
> 
> Is the answer still a PivotTable with an associated PivotChart?
> 
> Rgds,
> Ooi

0
1/9/2004 3:18:35 PM
Jon et al.

Thanks for introducing Pivot Table/Chart!

I got to the stage you described, producing a default "composite?" bar
chart for Stock, Order, Shelf, and Counter plotted on x-axis, and Date
plotted on y-axis.

Try as I may, I couldn't get the chart I originally wanted, i.e. for
each Fruit, a 2-axis line chart showing Date on the x-axis, Stock and
Order on left y-axis, and Shelf and Counter on right y-axis.

I also tried sorting my original data by Date, somehow that produced
an even more "nested" pivot table. Perhaps I need to "flip" (swapping
rows with columns) the original data? If so, how can I do that? Or did
I just miss something obvious?

Another question a little ahead: once successful, how would I go about
making static (or linked) "dumps" of each of the charts, ie. to get a
bird's eye view of all the charts in eg. one spreedsheet?

-Ooi


Jon Peltier <jonpeltierNOSPAM@yahoo.com> wrote in message news:<OXIdRPs1DHA.3496@TK2MSFTNGP11.phx.gbl>...
> Ooi -
> 
> Use a Pivot Table, with a Pivot Chart or a Regular Chart.
> 
> Put headers on your data (which is already ideally set up for pivot 
> table analysis, by the way, good job):
> 
> Fruit	Date	Stock	Order	Shelf	Counter
> 
> Now make a pivot table from the range, with Fruit in the page field, 
> Date in the Column field, and Stock, Order, Shelf, and Counter in the 
> Data field. The table looks like this:
> 
> Fruit	apples			
> 
> 		Date			
> Data		Jan-03	Feb-03	Mar-03	Apr-03
> Sum of Stock	1000	2000	1010	2010
> Sum of Order	2000	3000	2020	5000
> Sum of Shelf	3	4	1	6
> Sum of Counter	4	4	1	6
> 
> Change the selected fruit in the Page field dropdown and the table and 
> chart will update.
> 
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> http://PeltierTech.com/Excel/Charts/
0
ooievaar6 (5)
1/11/2004 3:14:11 PM
I apologize if this is a double posting, I got an error message after
hitting "Post message".



Jon et al,

Thanks for introducing Pivot Table/Chart.

I got to the stage you described, producing a default "composite?" bar
chart with Stock,Order,Shelf,Counter displayed on x-axis and Date on
the y-axis.

Try as I may, I couldn't get the original chart I wanted, ie. a 2-axis
line chart for each Fruit whre Date is displayed along x-axis, while
Stock and Order is displayed against the left y-axis, and Shelf and
Counter are displayed against the right y-axis.

I also tried sorting the original data by Date, but this created an
even more "nested" pivot table. Do I need to "flip" (swapping rows
with columns) the original data, and if so how can I do this? Or did I
just miss something obvious?

Another question a little ahead: Once successful, how can I make a
static (or linked) "dump" of all the charts, so I can get  a bird's
eye view of them eg. in a single spreadsheet?

-Ooi





Jon Peltier <jonpeltierNOSPAM@yahoo.com> wrote in message news:<OXIdRPs1DHA.3496@TK2MSFTNGP11.phx.gbl>...
> Ooi -
> 
> Use a Pivot Table, with a Pivot Chart or a Regular Chart.
> 
> Put headers on your data (which is already ideally set up for pivot 
> table analysis, by the way, good job):
> 
> Fruit	Date	Stock	Order	Shelf	Counter
> 
> Now make a pivot table from the range, with Fruit in the page field, 
> Date in the Column field, and Stock, Order, Shelf, and Counter in the 
> Data field. The table looks like this:
> 
> Fruit	apples			
> 
> 		Date			
> Data		Jan-03	Feb-03	Mar-03	Apr-03
> Sum of Stock	1000	2000	1010	2010
> Sum of Order	2000	3000	2020	5000
> Sum of Shelf	3	4	1	6
> Sum of Counter	4	4	1	6
> 
> Change the selected fruit in the Page field dropdown and the table and 
> chart will update.
> 
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> http://PeltierTech.com/Excel/Charts/
> _______
>
0
ooievaar6 (5)
1/11/2004 3:31:13 PM
Did your pivot table look like what I posted? Make a line chart of the 
pivot table data, with the series in rows. The chart has two lines up 
where you can see them, and two along the X axis. Double click one of 
the low lines, and on the Axis tab, select Secondary. Select the other 
low line and press the F4 key to repeat the last action.

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

ooievaar6 wrote:
> Jon et al.
> 
> Thanks for introducing Pivot Table/Chart!
> 
> I got to the stage you described, producing a default "composite?" bar
> chart for Stock, Order, Shelf, and Counter plotted on x-axis, and Date
> plotted on y-axis.
> 
> Try as I may, I couldn't get the chart I originally wanted, i.e. for
> each Fruit, a 2-axis line chart showing Date on the x-axis, Stock and
> Order on left y-axis, and Shelf and Counter on right y-axis.
> 
> I also tried sorting my original data by Date, somehow that produced
> an even more "nested" pivot table. Perhaps I need to "flip" (swapping
> rows with columns) the original data? If so, how can I do that? Or did
> I just miss something obvious?
> 
> Another question a little ahead: once successful, how would I go about
> making static (or linked) "dumps" of each of the charts, ie. to get a
> bird's eye view of all the charts in eg. one spreedsheet?
> 
> -Ooi
> 
> 
> Jon Peltier <jonpeltierNOSPAM@yahoo.com> wrote in message news:<OXIdRPs1DHA.3496@TK2MSFTNGP11.phx.gbl>...
> 
>>Ooi -
>>
>>Use a Pivot Table, with a Pivot Chart or a Regular Chart.
>>
>>Put headers on your data (which is already ideally set up for pivot 
>>table analysis, by the way, good job):
>>
>>Fruit	Date	Stock	Order	Shelf	Counter
>>
>>Now make a pivot table from the range, with Fruit in the page field, 
>>Date in the Column field, and Stock, Order, Shelf, and Counter in the 
>>Data field. The table looks like this:
>>
>>Fruit	apples			
>>
>>		Date			
>>Data		Jan-03	Feb-03	Mar-03	Apr-03
>>Sum of Stock	1000	2000	1010	2010
>>Sum of Order	2000	3000	2020	5000
>>Sum of Shelf	3	4	1	6
>>Sum of Counter	4	4	1	6
>>
>>Change the selected fruit in the Page field dropdown and the table and 
>>chart will update.
>>
>>- Jon
>>-------
>>Jon Peltier, Microsoft Excel MVP
>>Peltier Technical Services
>>http://PeltierTech.com/Excel/Charts/

0
1/12/2004 5:25:46 PM
Jon,

Yes, my pivot table looked like what you posted and yes I now get the
chart I wanted.

Here's what happened:

I had a read of Pivot Chart tips & troubleshooting at your website,
and clicked on a cell away from the Pivot Table and created an empty
chart.

My error was in highlighting all 5 columns (for apples) and drag+drop
them into the empty chart. This always created a chart with Date
plotted along the y-axis.

After your answer, I had another go, and eventually found that to set
the data range, I needed to highlight only from the 2nd column
onwards, ignoring the first column containing "Data", "Sum of stock",
"Sum of order", "Sum of shelf", "Sum of counter".  The Date is now
plotted along the x-axis, but I had to "manually" label the "Series
1",etc. This isn't really a problem, as I would only have to do it
once. To get a 2-axis chart, I just change the Chart Type.

I can live with the current solution, but would like to know the
difference of the two methods above. Is the second chart-type also
called a Pivot Table? The first chart-type had a toggle/pivot in it,
while the second chart-type is updating based on a toggle/pivot
setting at the Pivot Table.  Is it possible to get the first
chart-type with the Date plotted along the x-axis?

How about getting a "dump" of all charts into a single spreadsheet?

-Ooi.

 
Jon Peltier <jonpeltierNOSPAM@yahoo.com> wrote in message news:<#zKOKET2DHA.1532@TK2MSFTNGP10.phx.gbl>...
> Did your pivot table look like what I posted? Make a line chart of the 
> pivot table data, with the series in rows. The chart has two lines up 
> where you can see them, and two along the X axis. Double click one of 
> the low lines, and on the Axis tab, select Secondary. Select the other 
> low line and press the F4 key to repeat the last action.
>
0
ooievaar6 (5)
1/13/2004 12:19:20 AM
Reply:

Similar Artilces:

Gradients in Column Chart
I can't see a way to choose my own gradients in an Excel 2007 column chart. Am I missing something or is that functionality gone? Try the following:- 1. Click on one of the columns. This will highlight all of them. 2. Right click / Format Data Series (window of same name should launch) / Fill - in here there is an option called Gradient fill. Is this what you want? If my comments have helped please hit Yes. Thanks. "Erin Dicks" wrote: > I can't see a way to choose my own gradients in an Excel 2007 column chart. > Am I missing somet...

CRM and Fax automation question
Hi, I'm looking for a good or better fax server that can be easily and seamlessly integrate with MS CRM 3.0 or 4.0. I'm thinking of RightFAX or GFIFax... or even if I install these fax server in my environment, do I still need to do programming and customizations, such as post callout when a new Fax activity is created? Please advise :) You immediate response is appreciated. "Benjamin Fallar III" <fallar@pldtdsl.net> wrote in message news:3484C69D-B193-4B48-86A4-A4529D9DE4C6@microsoft.com... > I'm thinking of RightFAX or GFIFax... or even if I install ...

FRx WebPort #2
Could someone please help me! I have set up an image with FRx and WebPort on it. I have finally gotten it to work but when I log into WebPort there is no Repository in there! Also I notice that the Administrator password, continues to be blank. Any thoughts? Thanks Debra ...

Charts #15
I know how to produce group and a stacked bar graphs, but I do not know how to combine the two designs into one graph. For example, Item A and B on the x axis each have 4 bars grouped together. Now, I'd like to have additional information stacked withing each of these 4 bars. Any insight would be greatly appreciated! -- mariab ------------------------------------------------------------------------ mariab's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=31118 View this thread: http://www.excelforum.com/showthread.php?threadid=507850 Here are some lin...

Automation
Hello Everyone, Recently our IT group installed service pack 3 on our computers. I wrote some code in word that automatically attaches it self to an outlook email and sends it out. I'de like to disable two windows that i didn't get before they installed service pack 3 there are not titles so here is a description of the boxes: 1 - A program is trying to access email addresses you have store in outlook. do you want to allow this? then it asks for "allow access for" (1,5,10 minutes) 2 - then i get a similar one that just asks you if you're sure you want to send ...

Hide Rows #2
Hi, I've been working on this for a few hours and am finding this one to b a difficult one. How can I write a formula or some code that will hide rows based o values? Example: If in Row 1, cells F1 and J1 contained zero, then hide the whole row (This would need to be done for multiple rows i.e all rows where F an J columns contained zeros) Please help. Thank You -- Message posted from http://www.ExcelForum.com You can't do it with formulas, you need VBA code. This example will do it as the worksheet changes, not a macro to run to hide all rows at once: right-click the sheet...

Oranizational chart
Hello. I've created an organization chart. The problem is that when I enter much text in a box, the text disappears (when I edit again the box, the text remaining no longer exists). Do anyone know how to make it allow more text in the boxes? Thanks in advance. ...

DLL Created using vb.net 2.0... Could not create object.
Hi all, I created a simple DLL for use with the begin tender hook in the POS. I get a message when the event fires saying "Attempt to execute COM object 'blah.blah' failed. Could not create object." Has anyone else experienced this or know why and can you point me in the right direction? Probably has something to do with how I created my DLL...just a guess. KJ It's probably because you haven't gotten your dll registered properly for COM. I've done it with VS2003, but VS2005 may have changed something. You used to use REGASM to register the assembly ...

Excel Charts #20
I have workbook with different worksheet in it. each tab has a chart and a data table. i would like to create one more tab and have the master chart and underneath all baby charts where the information is being combined into master chart. Please I need help to show me a step by step directions how would i do this. I have created all charts including master now I need to put them all on one sheet. You can e-mail me at nk1519@aol.com Thank you, nk ...

Print area #2
I have many workbooks that have from 10 to 60 worksheets. All pages have the same set up in them. It's just that some data changes. I have a template set up and now want to set the print area for all the pages. When i select all sheets the print area menu greys out. How can I do this without setting it for each worksheet idividually? smarty, here is one way using a macro, will set the print area on all sheets in the workbook Sub Set_Print_Area() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.PageSetup.PrintArea = "$A$1:$C$25" '******Change to your range*...

how to switch the primary and secondary Y axis to opposite chart s
Using Excel 2007, evertyime I refresh pivot table data, my charts lose almost all of the formatting (ex. axis will disappear and chart type will revert from lines to columns). Most annoying problem I have when this happens is the primary Y axis moves from the left side to the right side of the chart, and when I re-introduce the secondary Y axis, it will appear on the left. How can I easily move the primary Y axis back to the left side of the chart or switch the two axes? I've tried choosing Layout - Axes to hide and re-display the axes but this did not work. Format the primary cate...

Provisioning/Automation tools for Exchange??
I have been asked to find some tools possible of automoating mailbox moves to specific databases/storage groups based on the department of a user for instance. Let's say User1 is part of HR and has transferred to Marketing. All the HR mailboxes reside on SG1 DB1 and all Marketing reside on SG1 DB2. Is there any software anyone knows of that can read the department attribute from AD and then move the mailbox according to defined rules you put in place. Any and all help appreciated! Mike In a couple of situations, organizations I have worked in have talked about having something li...

auto complete #2
Does anyone know how to delete an email address in outlook 2002 that "pops up" when you type the persons last name? They are using outlook 2002 with Word as their email editor. When they open outlook and click in the TO: field, and start typing an internet email, outlook prompts with the auto complete feature, with two choices. One has the correct email and one does not. How can I delete one? The email address is not comming from contacts. thanks Ok, I figured out I can simply hit the delete key. I guess it was just too simple! -----Original Message----- >Does a...

=PROPER #2
Using Excel 2003. Have downloaded info and it was downloaded in all caps. Have tried to use the =PROPER formulat but it doesn't work. If I go to a separate cell in the spreadsheet referencing a specific cell it works. How can I change in one worksheet all caps to PROPER ? I don't understand why =PROPER wouldn't work! You can copy this code to your personal.xls file and run it Sub TextProper() Dim ttt As String Dim c As Object For Each c In Selection.Cells ttt = c.Value c.Formula = Application.Proper(ttt) Next End Sub Have a great day...

Address Lists #2
I upgraded to Exchange 2003 and removed the last exchange 5.5 from the site. But I'm still seeing all the old address book views. How do I get rid of them. Thanks for your help! ...

chart #4
All, With VBA I'm making a chart sheet. This chart sheet is than viewed by the user. I have protected this sheet, so he can make no changes. I have also set some settings for the graph options: Application.ShowChartTipNames = False Application.ShowChartTipValues = False Now I notice that you can still select the different parts of the graph. Is it possible to switch this off? I also would like to add a button on this chart sheet. I tried this with: ActiveSheet.Buttons.Add(869189.25, 125211.75, 395520.75, 289965).Select ActiveChart.Shapes("Button 1").Select ...

DNS Reverse lookup #2
Hi, One help, please: - I have a domain test.local (W2k3) and in it a Exchange 2003 Std (machine mail.test.local); - I configured the users send email as @test.com; - My MX is pointed to Exchange Server; - I have a public IP from DSL service; - when I send an email the recipient use Reverse DNS lookup and the result is mail.test.local and not test.com How can I solve that problem? Thanks. Luiz Luiz wrote: > Hi, > > One help, please: > > - I have a domain test.local (W2k3) and in it a Exchange 2003 Std (machine > mail.test.local); > - I configured the users send e...

Is it Possible to Automate a Graph?
I have a sheet that has data in weekly fashion in the first and second rows. (Row 1, week: Row 2, data.) Is it possible to have a graph populate off of the information in rows 1 and 2 that automatically includes data for new weeks as they are entered in to the sheet? For example, I want to create a graph for weeks 1 through 3 now, and want the chart to automatically update if I add data for week 4. Row 1 Week 1 2 3 Data 12.5 17 18 Thanks for the assist! -Adam Use a defined name using the offset function for your series --...

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...

Chart size relative to contents
2 questions: 1.) I have a chart which plots circles in an X Y scatter. Based on the size of the circles within the chart, the circles appear as an ellipse. I can resize the chart manually to bring the circles back to shape - I was wondering if there was some quick VBA to make the chart area relative to the X and Y axis length. 2.) I use these charts as a template, so the data changes. If I know the extreme X and Y values, is there a method to control the axis length with that information - while still taking advantage of the "automatic" scaling feature? (this will be i...

Word crashes or hangs constantly #2
Version: v.X Operating System: Mac OS X 10.4 (Tiger) Processor: Power PC Hello, Just today, I started having severe problems with my Word program. It is constantly either crashing or hanging (not responding and I must kill it with Force Quit). I did not do any software upgrades or other changes that may have caused this problem. I read up a bit and realized I was way down in terms of recent upgrades, so I installed the latest and am running now at 10.1.9. This behavior has repeated on various (large) fies I have opened, so it isn't confined to just one doc file. I do not seem to be havi...

WinMail Could not be started 0x800C0155,2 error
error message is: Win Mail could not be started 0x800C0155,2 MSOE.DLL could not be initiated I am unable to open an email from websites (contact us or email link). I get the error msg above. It seems this started when updates to Vista were downloaded. Don't know which ones. It may also be related to Adobe Reader updates, since I seem to be having problems with opening a pdf files from websites. I get an error msg that relates to Adobe Reader (no problems opening pdf files I have saved in my computer). I cannot seem to find a fix anywhere for this. Some posts suggest ...

Way to automate adding pictures in a series in Excel?
I'm putting a column of pictures of people next to a column with their names. If I have the pictures together in a folder, with the photo file names numbered with the corresponding row numbers that the pictures go in, is there a way to record a macro so that (for instance) 28.jpg will go into the picture column in row #28, then 29.jpg will go into the picture column in row #29, etc. In other words, insert a whole series of pictures (filenames the same as their appropriate row numbers) in their correct places, by initiating one macro? If anyone has any idea of how that could be done, I wou...

Column Reference #2
Hi, On Row 3 - I have text from columns "B3:J3". I am constanly adding and clrearing the rightmost cell, so that the last column is J, P, K, ...etc. Is there any way to create a function in Cell A1 so that it will give me the letter of the rightmost column with text (whichever is nonempty). - (I am using this with the indirect function to do a vlookup) So if the text spans from column B to column AJ - then Cell A1 = AJ If text spans to column K, Cell A1= K Any help would be greatly appreciated. One way: =LEFT(ADDRESS(1,COUNTA(B3:IV3)+1,4,TRUE), 1+(COUNTA(B3:IV3)>25)...

Display percent and value on stacked column chart
I'm displaying cumulative $ totals from 3 sales groups on a 100% stacked column chart. We've recently exceeded 100% of our total $ goal so the chart isn't able to display the amount above 100%. If I switch to a standard stacked column chart, I can't choose the display percentage option. I downloaded the Rob Bovey's Chart Labeler but I can't get it to display what I want. Perfect world would be displying the dollar value of each sales group in the column segments and showing percent of total goal on the y-axis. I would even be okay with showing the dollar valu...