Graphing by time in Excel

I have a data set (~2000 rows) with the following form:

+---------------------+-----+-----+-----+-----+-----+ | Datetime            | n1  | n2  | n3  | n4  | tot | +---------------------+-----+-----+-----+-----+-----+ | 2008-08-02 12:25:00 | 20  | 5   | 2   | 3   | 30  | +---------------------+-----+-----+-----+-----+-----+ 

I'd like to graph this data using a Stacked Area chart in Excel 2003. Where I'm having difficulty is I'd like to present the chart on a scale of each hour. Could someone please provide the steps for producing a Stacked Area chart using time scale?

Replay

Here are the steps you need to follow. I'm using a Pivot Chart, as suggested by Longhorn213. While you have the table selected:

  1. Select Data -> PivotTable and PivotChart Report ...
  2. Select the Radio Button for PivotChart report (with PivotTable report)
  3. Hit the Finish button You should now have two new tabs in the spreadsheet. One will have an empty chart and the other will have an empty pivot table. Select the sheet with the empty pivot table. Then:
  4. Drag the Date field from the Pivot Table Field List to the area in the pivot table marked Drop Row Fields Here You should now see EVERY time stamp listed in the row.
  5. Right click on the Date header in the Row and select Group and Show Detail -> Group
  6. Highlight that you want to group by Hours and then hit the OK button Note that if your data spans several days/months, you MUST select both Days and Months in addition to Hours to further order the data.
  7. Drag the n1 field into the area marked Drop Data Items Here
  8. Drag the n2 field over top of the n1 data and drop it This will cause a new row header called Data to appear beside the Date header.
  9. Drag the new Data header to the area marked Total and drop it there You now have the basic structure of the table that you want.
  10. Drag the n3 and n4 fields into the data area, completing the table you want Now you can switch back over to the new chart sheet that was created. You should see the default type of chart with all your data.
  11. Right click on the Chart Area and select Chart Type...
  12. Select the type of chart you want

And we're done! I'll check back later and clarify anything you need.

You can use Pivot Tables for it.

With Pivot Tables you have the data in one tab and then create a pivot table and summarize them by time. I would refer to the Help with Excel for more on Pivot Tables.

Category: microsoft excel Time: 2008-08-27 Views: 2

Related post

  • Dynamic time in Excel spreadsheet column 2012-03-09

    I am trying to automate time in Excel. I have two columns that show time advancing in minutes and seconds. I want to be able to automate so that if the time changes in one row, it will automatically correct in the times that follow that row. Any advi

  • Formula to add minutes to date+time in Excel 2013-09-24

    If I have a cell formatted as date+time in Excel, how can I add to this in terms of minutes? It would need to work whether I was adding 20 minutes, or 2,500 minutes. So, for example if I had this spreadsheet, what formula could I put in B5 to get a r

  • Stacked area graph of time/duration values 2014-02-07

    How can I create a stacked area graph of time/duration values? I have a set of times for each date which correspond to each leg of a journey. https://docs.google.com/spreadsheet/ccc?key=0AlOsQm5RC6U5dFBVazA2eGItcFZ6YjVGR3hTNU0wT2c&usp=sharing If thes

  • Can't insert current date and time into Excel 2013 2015-03-05

    Ctrl ; is not working to insert the current date and time into Excel 2013. How can I fix that? UPDATE Glitch disappeared when Excel restart, so nobody knows the reason and I awarded just "direct" answer. --------------Solutions------------- How

  • Converting Time from Excel to seconds 2016-01-28

    I have a function in c# to convert Time in Excel cell's, in the hh:mm:ss format, to seconds. But when I get cell's with more than 10.000hours, it doesn't work. Any ideas why it does not work with bigger hours? String time = (Convert.ToInt64(Convert.T

  • Generate meaningful graph for times of day in Excel? 2009-10-04

    I have an excel spreadsheet, specifically it's a list of hardware devices with various details dumped out of our system. Each row has a 'last modified' time/date value in the format of 09/09/2006 10:37 etc I want to be able to create a graph where I

  • calculate positive and negative times in excel 2003 (new) 2010-01-10

    Possible Duplicate: calculate time differences in excel 2003 i have a goal of 2h47m00s minutes per month. if i attend let say 1h20m00s and when my excel cell format is [Red]-[m]:ss;[Blue][m]:ss it gives me in red color 87 minutes time difference befo

  • How to get Average for time in Excel 2007? 2010-01-30

    I have these values 01:15 05:00 01:31 02:00 02:21 02:39 03:29 08:00 I highlighted all these cells and went to format cells -> custom -> and choose mm:ss I then tried to use the built in average function in Excel 2007 =AVERAGE(D31:D38) The result is

  • Is there a way to unhide multiple sheets at the same time in Excel? 2010-05-13

    I have a number of sheets that I want to unhide quickly in Excel. Do you know if it is possible to unhide multiple sheets at the same time? --------------Solutions------------- If you are able to have macros in your workbook I would go for this optio

  • Inserting static current time in Excel 2010-08-13

    I have a time log spreadsheet. I have a new sheet for each day. In each sheet, I have a transactional record of how my time was spent. When I start or end a task, I usually type in the time ("11:00 AM" for example). Is there a shortcut to insert

  • How to convert 24/12/2010 7:24:56 AM into a time in Excel 2007? 2011-01-25

    In a CSV file I have data in this format 24/12/2010 7:24:56 AM in older versions of excel just opening the file it recognizes it as time. In 2007 it doesn't....and I'm not quite sure how to get Excel to recognize it as a date time. --------------Solu

  • Convert "Mon Aug 01 09:08:25 CDT 2011" to a usable date/time using Excel 2011-08-03

    I have a giant spreadsheet with computer names and the date stamp that they last reported into our PGP server that I need to convert to a usable date/time, so I can sort the column by the time the computer last reported. The format of the field now i

  • Is it possible to export a graph as graphic to Excel? 2012-07-05

    It is straightforward to export lists to Excel with Export. Can I export a graphic image, too? This does not work: g = CompleteGraph[4]; fnOut = "Output1.xls"; Export[fnOut, {"Sheet1" -> g}] Maybe I need to transform g in some way?

  • working with elapsed time in Excel 2010 2013-01-22

    I have an excel spreadsheet in which employee times have been recorded with text and numbers, as in 7 hours 6 minutes. I need to convert the existing data to h:mm so I can then deduct lunch breaks automatically. Any advice appreciated! --------------

  • Calculating time in Excel between AM and PM 2013-02-19

    I have an Excel spreadsheet that calculates the difference between two times. The formula that I'm using, for example, is as follows: Cell A1 12:00 PM Cell B1 12:30 PM Cell C1 =minute(B1-A1) and the result is 30 which is what I'm looking for, but whe

  • Drawing sample points versus time in Excel 2013-06-12

    I have a set of data as follow: As you can see the first column has either R or P, the second column is a measured value, and third one is obviously time. I need to present this in a chart (I believe it is called sample points versus time). Here is a

  • How to increment rows each time an Excel VBA Macro is run 2013-07-09

    I have recorded a macro to get a filtered set of 6 numbers and paste them in row 1.I want to get another set of 6 numbers in row 2 when I next run the macro and a third set in row three and so on when I run it for the third time and so on.how to do i

  • How to save and close an excel spreadsheet after idle time in Excel 2010 2013-10-03

    I have Excel 2010. I am experiencing situations where people leave work for the day and leave an Excel workbook on a shared file server open exclusively; as a result, other users are unable to edit the workbook. How can I mitigate this problem? -----

  • Calculating units of time in excel in order to multiply 2013-11-20

    I would like a formula that can multiply a unit of time, e,g if I enter 1hr and 20 min as 1.20 and multiply by10 i should have 13 hrs 20min displayed as 13.20 but what I would get currently is 11. --------------Solutions------------- Enter 1hr 20 min

iOS development

Android development

Python development

JAVA development

Development language

PHP development

Ruby development

search

Front-end development

Database

development tools

Open Platform

Javascript development

.NET development

cloud computing

server

Copyright (C) avrocks.com, All Rights Reserved.

processed in 0.979 (s). 13 q(s)