If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Series of dates in one column, hours for each date in another
I added column headers above your table, selected Subtotals from the
Data menu, and Excel generated this for me: Date Hours 1-Mar-04 4 1-Mar-04 3 1-Mar-04 0.5 1-Mar-04 0.5 1-Mar-04 Total 8 2-Mar-04 4.5 2-Mar-04 Total 4.5 3-Mar-04 3 3-Mar-04 2 3-Mar-04 Total 5 Grand Total 17.5 I clicked on the boxes in the level 2 outline in the left hand margin, and the non-subtotal rows were hidden: Date Hours 1-Mar-04 Total 8 2-Mar-04 Total 4.5 3-Mar-04 Total 5 Grand Total 17.5 Select all but the Grand Total line, and Excel will chart just the subtotals (by default, a chart only shows visible cells). You could also get a summary table like this using a Pivot Table. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ Novice wrote: Hi all, I have an Excel worksheet with a series of dates in one column. Then in another separate column I have the number of hours worked on those dates. However, I have multiple date entries for some dates - for example: it was necessary to create four entries for March 1, 2004, since there were four separate activities performed on that day. Each of those activities have a number of hours associated with them (that can be found in the aforementioned "number of hours worked on those dates"). Some days may only have entry associated with them (if for instance only one activity was done). Is there a way to get the chart function in Excel to display the dates on the y axis and then put the cumulative hours for each date above those dates? So for example, I have (in the excel Worksheet): March 1, 2004 | 4 | March 1, 2004 | 3 | March 1, 2004 |.5 | March 1, 2004 |.5 | March 2, 2004 |4.5| March 3, 2004 | 3 | March 3, 2004 | 2 | This is the chart that I would like to be generated: 8 ___ | | 5 | | ____ 4.5| |___| | | | | | 1 2 3 I realize my ascii art has much to be desired, but hopefully I have got across what I would like to do. Thanks, Novice |
#2
|
|||
|
|||
Series of dates in one column, hours for each date in another
If the subtotals are in the way, you could do a pivot table instead.
Select the data, choose Pivot Table Reports (or whatever your version of Excel calls it) from the Data menu. When laying out the table, put the Date field in the rows area, and Sum of Time in the Data area. The pivot table can be located elsewhere on the sheet or on a new sheet, and it doesn't disrupt the original data range. You can make a pivot chart from the data. You can also make a regular chart, which doesn't update automatically if the pivot table changes size, but which gives you more formatting capabilities. Select a blank chart away from the pivot table and start the chart wizard. In step 2, use the Series tab to set up the chart's series. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ Novice wrote: That worked great. I didn't know about the subtotals feature or the layers feature. Is there a quick and easy way to remove the subtotals once I have generated the chart? They clutter up the data - making it more difficult to read when they are present. Thanks, Novice |
Thread Tools | |
Display Modes | |
|
|