Home |
Search |
Today's Posts |
#1
|
|||
|
|||
having trouble creating chart to show monthly totals
Hi
I'm using office XP. I keep a single spreadsheet were I record orders received. Its a very simple sheet with one order per row. In col B I place the date the order was received (in the format DD/MM/YY) I'd like to make a chart (pie or column) which displays the total number of orders per month so that I can see at a glance when were busy and quieter periods. I've tried using the wizard but all I ever get is a mess and not what I expect to see at all. I suspect I may need to separate my data by month? But is there a way for Excel to work that out for itself from a single column of dates? Maybe I'm expecting far too much or I haven't laid out my data correctly. I'm assuming Excel can distinguish the dates by month and then calculate the total number of orders received within each month. Your guidance would be much appreciated. Many thanks for your time. Ian |
#3
|
|||
|
|||
You are organizing your data in the correct manner. What you want can
be done as follows. Add a new column to your data table. Say column C is available. Then in C1 enter the literal 'OrderMonth'. In C2 enter =MONTH(A2). Copy C2 as far down column C as needed. Now, create a PivotTable (Data | PivotTable and PivotChart Report...) -- keep in mind MS has changed the name of this menu item over time. For the row field use the new field we created. For the data field use either field. By default XL will create a 'Sum of xxxx' field. Double click that label and in the resulting dialog box change the Sum to Count. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... Hi I'm using office XP. I keep a single spreadsheet were I record orders received. Its a very simple sheet with one order per row. In col B I place the date the order was received (in the format DD/MM/YY) I'd like to make a chart (pie or column) which displays the total number of orders per month so that I can see at a glance when were busy and quieter periods. I've tried using the wizard but all I ever get is a mess and not what I expect to see at all. I suspect I may need to separate my data by month? But is there a way for Excel to work that out for itself from a single column of dates? Maybe I'm expecting far too much or I haven't laid out my data correctly. I'm assuming Excel can distinguish the dates by month and then calculate the total number of orders received within each month. Your guidance would be much appreciated. Many thanks for your time. Ian |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Urgent Chart Questions | Excel Discussion (Misc queries) | |||
Creating a High-Low-Close chart with multiple series? | Excel Discussion (Misc queries) | |||
creating a chart | Charts and Charting in Excel | |||
hide column but show chart | Charts and Charting in Excel | |||
creating an x,y chart | Charts and Charting in Excel |