Results 1 to 5 of 5

Thread: Preparing Trial Balance Using Pivot Table

  1. #1
    Junior Member Zaigham's Avatar
    Join Date
    Mar 2013
    Posts
    13
    Rep Power
    0

    Preparing Trial Balance Using Pivot Table

    Hi everybody,

    Is it possible to prepare a trial balance from a given data table by using Pivot table? For example I have hundreds of records under the fields as described below:
    Month, Formation, Act_Code, Debit and Credit.

    Can I get the results as mentioned below:
    Trial Balance of a selected formation, upto a specific period i.e., Formation A's trial balance for April 2013. The table is required as below:
    1. Act_code,
    2. Op Balance Dr/(Cr) (Sum of figures upto March 2013),
    3. DEBIT (Sum of figures incorporated during the month April),
    4. CREDIT (Sum of figures incorporated during the month April),
    5. DEBIT (Progressive net figures upto April 2013), and
    6. CREDIT (Progressive net figures upto April 2013).

    I have prepared the above table by using various excel formulas but I want to get this table by using "Pivot Table". Is it doable?

    Regards
    Zaigham

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Can you post a sample with the input data and expected output data? If the file size is over limit, try uploading it to a fileshare site, and post the link here.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Junior Member Zaigham's Avatar
    Join Date
    Mar 2013
    Posts
    13
    Rep Power
    0
    Quote Originally Posted by Excel Fox View Post
    Can you post a sample with the input data and expected output data? If the file size is over limit, try uploading it to a fileshare site, and post the link here.
    Sample file is attached. I have pasted the values instead of formulas. However, this output I want through pivot table.
    Thanks a lot.

    Regards
    Zaigham
    Attached Files Attached Files

  4. #4
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Most of the subtotal shown doesn't make sense. Here's the closest I could reach.
    Attached Files Attached Files
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  5. #5
    Junior Member Zaigham's Avatar
    Join Date
    Mar 2013
    Posts
    13
    Rep Power
    0
    Hi,


    Sub totals are not important, so I have removed them from file. By using Pivot table I get sum of expense of only 01 criteria, whereas I want 03 different projections at a time i.e.

    1. Sum of expense up to Feb in 01 Col. (Dr.+Cr) or may be separately as Opening Balance.
    2. Sum of expense only for March. (For expense incorporated during the month)
    3. Sum of expense up to March (as year to date) net of which come whether debit or credit.

    Hope I have explained more clearly.

    Regards
    Zaigham
    Attached Files Attached Files

Similar Threads

  1. Create a Pivot table
    By NITIN SHETTY in forum Excel Help
    Replies: 3
    Last Post: 01-26-2013, 11:01 AM
  2. Lookup and Count Using Pivot Table
    By RobExcel in forum Excel Help
    Replies: 2
    Last Post: 12-21-2012, 11:08 AM
  3. Pivot Table - Volume by Day and Half Hour
    By Ian Herndon in forum Excel Help
    Replies: 9
    Last Post: 07-07-2012, 10:04 PM
  4. Pivot Table Count No of Items per Category
    By Howardc in forum Excel Help
    Replies: 2
    Last Post: 07-04-2012, 10:49 PM
  5. Filter more than one pivot table at one time
    By larryt1940 in forum Excel Help
    Replies: 8
    Last Post: 05-04-2012, 06:45 PM

Posting Permissions

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