+ Reply to Thread
Results 1 to 4 of 4

Data select on a large table to create data for charting

  1. #1
    Registered User
    Join Date
    02-01-2016
    Location
    Canada
    MS-Off Ver
    2003
    Posts
    2

    Data select on a large table to create data for charting

    I have a "large" spreadsheet where I keep track of my natural gas billings. It goes back to 1983. The data is setup to follow the billing data so fields are not adjacent. What I want to do is select from this large table sub sets of data, i.e. month and gas usage so that I can review changes made to the house that would impact heating. I would want to create a table that shows a particular month and usage over the years. I guess in the end I would have 12 charts. The charts need to pickup new data as it gets added to the master spreadsheet dynamically.
    I had thought of doing a SQL select to get the data for each month ( date format mm/dd/yyyy ) but I could not find any suggestions. Is there a better way to extract month and usage by month and create the input for a chart? I understand in Excel I can use MONTH get the data for Jan. etc.. My background is programming on the mainframes, COBOL, PLI, DB2, IMS etc. so my solutions are driven old thought processes that may not work with Excel.

    Looking for some direction and possible solutions that I can understand :-).



    Billing Date Billed Amt Water H Cust Chrg Delv Chg Suply Chg TranChg TranRate Qty M3 3rd Party rate PEF Adj M3 Current Reading

    Prev Reading Usage Recal supply C harge BBP billed YTD Enbrige rate Recalc Enb usage Delta Site restoration Y/E Value Y/E Gas Usage

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: Data select on a large table to create data for charting

    Use a pivot table - select all the columns of data that you have, then insert a pivot table. Use the date as the row field, and any data fields of interest as the data fields, set to sum. Then right-click the dates in the row field, and group the dates by month. When you want to look at the values that go into any single month's value, double-click the value in the pivot table and Excel will create a new sheet that will show all the values that went into creating that number.
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Registered User
    Join Date
    02-01-2016
    Location
    Canada
    MS-Off Ver
    2003
    Posts
    2

    Re: Data select on a large table to create data for charting

    Thanks for the info Bernie. I am using the 2003 ver.. I am not familiar with the pivot table. Can you point me to a beginners how to use it?
    I created a pivot table with Date as the row field and Qty in the data field. Working slowly from there.

  4. #4
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: Data select on a large table to create data for charting


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 8
    Last Post: 02-19-2015, 05:07 PM
  2. [SOLVED] How to merge two data sets to create a single large data set.
    By Econocrat in forum Excel General
    Replies: 5
    Last Post: 10-06-2012, 04:02 PM
  3. simple charting through Pivot table data
    By lavi in forum Excel General
    Replies: 1
    Last Post: 05-13-2010, 04:45 PM
  4. Macro to create pivot table from large data file
    By johnson748r in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-27-2007, 02:36 PM
  5. Including data in data table but not charting as a series or point
    By pepper76 in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 08-01-2006, 10:25 PM
  6. SELECT large amount of data in a worksheet
    By OTS in forum Excel General
    Replies: 3
    Last Post: 08-13-2005, 12:05 PM
  7. in charting, how do i select data ranges from multiple sheets, sa.
    By michael in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 03-06-2005, 11:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1