chart purchases by customer, various items or all together

I have a worksheet which shows all the purchases made by a 
customer over an extended period of time. Can someone tell 
me how to make a "chart" which (hopefully) will show a 
line starting on left and heading northbound as it moves 
to the right (sort of like what you see when they show the 
price of gasoline on the nightly news). I have posted the 
dat here http://bbdldarts.homestead.com/excelexample.html, 
and I also put commas between columns of info. 
It would be nice if I could get a chart that breaks it 
down by product and dat, not just total products.
please help if you can

sorry that I posted it in misc also, but I didn't see this 
category originally
0
anonymous (74722)
9/16/2004 8:39:49 PM
excel.charting 18370 articles. 0 followers. Follow

1 Replies
222 Views

Similar Articles

[PageSpeed] 31

You can turn your data into a pivot table. Select the data, choose the 
Pivot Table command from the Data menu. When it presents you with a 
blank table (I assume you're using Excel 2000 or later), drag the date 
field to the rows area, drag the product code to the columns area, and 
drag the case cost to the data area. This gives you a table with a 
column of dates next to a column for each product code.

You want cumulative data, so next to the pivot table (skip a blank 
column in between), you can put a range of formulas that sum the data 
for you. Assuming the pivot table data is in B4:Fwhatever, put this 
formula into I4 (or further right if the pivot table takes up more room) 
to sum the accumulation in column B:

   =sum(B$4:B4)

Fill this across row 4 to do the math for each column, then fill these 
down as far as you have pivot table data. As you fill across columns, 
the B in the formula changes to C, D, etc. As you fill down, the $4 
stays as $4, but the plain 4 increments to 5, 6, 7, etc.

Copy the dates from the pivot table, and paste them in the column just 
left of the running sums. This gives you a nice region for the chart. 
put labels above all of the columns with sums, and omit the label over 
the date column. Select the range and make a line chart.

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

jvoortman wrote:

> I have a worksheet which shows all the purchases made by a 
> customer over an extended period of time. Can someone tell 
> me how to make a "chart" which (hopefully) will show a 
> line starting on left and heading northbound as it moves 
> to the right (sort of like what you see when they show the 
> price of gasoline on the nightly news). I have posted the 
> dat here http://bbdldarts.homestead.com/excelexample.html, 
> and I also put commas between columns of info. 
> It would be nice if I could get a chart that breaks it 
> down by product and dat, not just total products.
> please help if you can
> 
> sorry that I posted it in misc also, but I didn't see this 
> category originally

0
9/17/2004 12:07:28 AM
Reply:

Similar Artilces:

Prevent new pivot table items from being selected
I have a pivot table that contains accounts and reports sales by account. Users selected the accounts they want to see and save their spreadsheet. When new accounts are added to the accounts list, users do not want to see the new accounts. They only want to see the accounts they selected from a previous session when they refresh the pivot table. Does anyone know how to prevent the new accounts from appearing in the pivot table? Thanks! Tom, Use another column: Include, with values of Yes or No, and include it in your pivot table, showing only Yes. Then when new data is added, enter ...

Customization: disabling built in features
I would like to disable/remove some of the sections on the left hand side of CRM. For example, on the home screen I would like to remove the Invoices section since we don't use these. And on the Account level I would like to remove the Notes and Contracts sections for the same reason. Is this possible? Keith ...

Excel 2007 Chart in VB6 Program
I have another thread in the VB group and was told to post to these two groups so sorryfor the multi-post. Ill post there again just in case folks want to follow the thread. So I've had a Visual Basic 6 app (as an EXE) that has worked great for the last 9 years or so. Up until Excel 2003 I have had to make minimial changes for it to work properly. The app has an OLE control which acts as a container for many different external sources .In this control I embed an Excel workbook and display a chart sheet. I can interact with the workbook through the normal means of Excel programming objects...

Reconciling the Accrued Purchase Account
I would like to run a SQL query on POP30390 to group the Receipts based on the purchase orders and sum the debit and credit amounts for Accrued purchase account index. The difference between Debit and credit balances should give an idea of such PO / recipts to check further. Looking at POP30390 I coudnt find any means to link the Purchase orders with it. Even though Great Plains does allow Multiple orders to be received together in one receipt, but In my case most of the time purchase werent combined in Receiving process. Any help..to figure out a query. Samit ...

how do I recover charts within an excel workbook?
The workbook had many charts that were previously visible. Now, upon opening the file, the charts are no longer visible. How to I reactivate them to appear? thanks ...

Problem with Asset Allocator pie charts
Everything "seems" to be working fine with Money 2005 Premium (after restarting the program multiple times). I'm having a problem with many of the pie charts. They do not show up at all. I upgraded from Money 2004. Any ideas? I'm running Windows XP Professional if that's of any help. Thanks, Patrick I've seen them going on a new file on Win XP Pro - do you get them on a new file, or do they not appear on both a new and old one? -- Glyn Simpson, Microsoft MVP - Money http://money.mvps.org Check http://support.microsoft.com/default.aspx?scid=fh;EN-GB;...

Draw line in a chart
I have the following problem. I have a chart (date on x axis) on which I draw a line. How can I obtain the ending points of the line in order to compare, mathematically, their positions to corresponding values of chart at the same date? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements I'm sure your post makes perfect sense to you and might to other readers, but I am los...

how to chart who was working when
Hi there, I'm looking for some help in producing a chart. My organization has hired about 100 folks since 1991, each on contracts of varying lengths of time. Some peoples' contracts have ended, and some have been renewed. I set up some worksheets inputting their names, start and end dates with the intent of producing a graph that will tell me exactly how many people were working for us at one time, based upon 6 month intervals. For example: John Doe 7/29/91 - 3/31/05 Jane Doe 6/21/94 - 2/20/96 Jake Doe 8/2/01 - 4/18/03 I would like a chart that can tell me how many people w...

OL2007+Exchange: Sent items disappear if i move them to another folder...!
I'm seeing a very strange issue with my Outlook2007 (against Exchange)... Sent items disappear if i move them to another folder...! Steps: 1. Send an email. Sent email gets saved under Outlook's default "Mailbox\SentItems" folder. 2. Go to that SentItems folder, and move the mail to another folder (let's say "Foobar") under your inbox. 3. Go to "Foobar", make sure mail is still there. 4. Let Outlook Sync up with Exchange server (or hit "Send and Receive"). 5. You will notice that the mail disappears... ! I did a full text search in my ...

Pivot Chart: Stop skipping Dates on X-Axis
I have a pivot chart which pulls 2 pieces of information from one table 1 and 1 from Table 2. All three entries are included for each day. Except somedays which may have only the one piece of data from Table 2 or none at all. How do I get the table to automatically fill in all the days whether they have information or not? =?Utf-8?B?UHJpbmNl?= <Prince@discussions.microsoft.com> wrote in news:A5EF53AB-6063-434F-BFC3-43259301751D@microsoft.com: > I have a pivot chart which pulls 2 pieces of information from one > table 1 and 1 from Table 2. All three entries are included for &...

email charts
I have an excel workbook file containing many department sales charts that recieve data from a group of central data entry spreadsheets. Each department chart resides on its own sheet. Is there a way to email individual chart sheets (or snapshots of the chart sheet) to the respective departments without sending the entire parent file to all recipients? Thanks, Mnaut You could export the chart as a gif file, then email that file to the department. The following code creates a gif file from the active chart: '============================ Sub ExportChartGIF() ActiveChart.Export Filenam...

Pivot charts formatting
Hi, I want to change the formatting imposed by default in a pivot chart and I want that changed formatting to be retained permanently. For example, for my bar chart, for the different series I have selected colours / patterns but every time I refresh the pivot table / chart; excel falls back to its own colour coding. Any way to get around this? Many thanks in anticipation. Not directly. The only way to simulate this is to record a macro of the formatting changes that you make and then set it up to run whenever the PivotChart calculates. Assuming your pivot chart is on a seperate shee...

see chart from pushing button
how would I see a chart from pushing a button on the spreedsheet ? ...

Switching x-axis & y-axis on line chart
I am working with a large data spreadsheet with year, number and currency data. My problem is when I extract data to create a standard line chart, the chart is defaulting to a specific x-axis and y-axis format with data related to each axis. For visualization purposes, I would prefer to have the data that is currently presented on the x-axis to instead be presented on the y-axis. And likewise, I would like the y-axis data be presented in the x-axis. However, I have been unable to figure out how to do this and the MS Excel Help menu does not seem to address this. Can anyone help? If ...

pie-charting non-numeric data
I would like to chart and graph responses to a survey that are non-numeric in nature, such as a bar graph for "yes" and no" responses to a question. Or in particular a pie chart that shows how many people picked option "P" vs. "K", "J" or "N". For the pie chart I have a column of data cells, each containing one of the four letters. In short, how do I accomplish charting and/or graphing non-numeric responses? So you have a column of Yes/No or P/K or J/N? Construct a pivot table of this range, and you can produce counts of each value...

Excel 2003: How to make transparent columns in Excel chart?
If you create a bar plot froma given dataset you can format the columns by right clicking and choosing the desired options. In the tab that opens there is a slider which is supposed tho set the level of transparency of the column (selceted area). But so far i couldn't find a way how to use this slider. I know that there is an alternative way to get transparent bars by creating a rectangular object formating it and the use copy -> paste. But i wonder what is the slider for if you can't use it? Does anybody know have an idea? Cheers, Thomas ...

Keeping Custom Properties When Forwarding or Replying to a Message #2
We have emails saved on the file system, that is kept track of by another program. These emails have custom properties attached to them. There is no custom form, just custom properties. These fields were originally added using objPost.UserProperties.Add. We need it so that when someone opens up one of these emails through the file system, and then replies or fowards the message, these custom properties are kept in the reply. This is so the external program can track these emails. Is there a way this can be done? We could have these emails opened up using an Outlook command line that i...

Filtering data by custom fields in pivot tables from Visual Report
I have recently upgraded to MS Project 2007 and like the visual reporting feature, except I really need to filter and group the resulting pivot table in Excel by custom fields. For example, for task usage, I cannot filter my pivot table to show only the tasks that have costs > $0, thus my table ends up showing too many lines and a lot of 0's. I would also like to group by department/ function which I put in a custom text field. How do I do this? I saw related posts about saving the file as an Access database and then creating the pivot table by connecting to the datab...

How do you define a custom paper size in Excel? (i.e. 11x17)
How do you define a custom paper size in Excel? (i.e. 11x17) Hello- Excel doesn't provide for custom paper sizes, but most any size appropriate for Excel output is already provided _if_ the installed print driver supports it. Go to File>Page SetUp--Page and open the list of sizes. If your installed driver supports 11x17 it will be in the list (although it may be listed as 'Ledger' rather than by dimensions). HTH |:>) "Marcin Rembisz" wrote: > How do you define a custom paper size in Excel? (i.e. 11x17) ...

Customized Task Views Have Disappeared (Take 2)
I was having an issue similar to this original post and followed the instructions given. It worked, but as I have worked with it I have found that it will restore my customized Task views but not my customized Contact views. I can change some of the Send and Receive setting and it will restore the Contact views but not Task views. Somehow I am searching for a way to restore both Task and Contact views. Any ideas? -- Tia, Education and Documentation Specialist Sorry, i posted this in the wrong discussion group. I have posted this correctly and my question has been answered. -- ...

CComboBox shows only one item at a time !
Hi All, I am using CComboBox control and have tried both dropdown and dropddown list styles. The problem is that I have several items in the combo box but dropdown shows only 1 at a time (i.e. the arrows of v-scrollbar are stuck together).. How do I remedy this ? Thanks in advance. Ashish Got it... Thanks "Ashish" <abc@def.com> wrote in message news:eqRb$LSyDHA.2304@TK2MSFTNGP12.phx.gbl... > Hi All, > I am using CComboBox control and have tried both dropdown and dropddown list > styles. The problem is that I have several items in the combo box but > dropdown sho...

how to insert a item when ListCtrl is in "Icon" mode
I create a ListCtrl with "Icon" mode,but I found I can't use the "InsertItem()" to insert a item in a specfied position,the new item is always at the bottom of list,but I want insert some items before a specified position,so how to do it? "pango" <pango99@tom.com> skrev i en meddelelse news:0a1101c3d919$a04cbfa0$a401280a@phx.gbl... > I create a ListCtrl with "Icon" mode,but I found I can't > use the "InsertItem()" to insert a item in a specfied > position,the new item is always at the bottom of list,but > I want i...

Stack Bar Charts
I have yearly information that I would like to graph in a stacked bar chart. I have years 2005 to present Two companies I am comparing with two variables for each company each year. I would like to have the stack bar chart by year reflect the stack of Co A vs Co B on variable 1 + 2. Any help would be appreciated. Thanks. You want a clustered-stacked chart: http://peltiertech.com/WordPress/clustered-stacked-column-charts/ http://peltiertech.com/WordPress/clustered-stacked-bar-charts/ - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.com/ Sean wrote: > ...

personal income and expenditure chart
can anybody give me a personal income and expediture chart please Thanks Mike mikeyboy87 wrote: > can anybody give me a personal income and expediture chart please > Thanks Mike You can do this in Excel but why re-invent the wheel? Quicken or Money have far more tools and options. If money is an issue try checking ebay. I purchased Quicken 2004 for about $10 including shipping. It isn't the latest and greatest but it does what I need it to do. gls858 ...

retrieving deleted items
If a user deleted a bunch of emails before quitting can I retrieve those without doing a restore? They aren't in her deleted items. if you have DIR configured on that mailbox store, you can recover them by selecting the deleted items folder, and selecting Tools, Recover deleted items from the menu in Outlook... -- Susan Conkey [MVP] "Brandon Baker" <brandon@discussions.microsoft.com> wrote in message news:eW$YsTMOGHA.668@TK2MSFTNGP11.phx.gbl... > If a user deleted a bunch of emails before quitting can I retrieve those > without doing a restore? They aren'...