MAKING A WORKING TRIAL BALANCE IN EXCEL FROM IMPORTED DATA

  by Jerry Osterman, June 2015

ContentSteps: These procedures only require the use of 2 Excel funtions: IF and SUMIF.  No macros are needed.  Help with these functions is available within Excel.

MAKING A WORKING TRIAL BALANCE IN EXCEL FROM IMPORTED DATA

1 Import a trial balance into Excel from QuickBooks or some other program.

2 Change 2-column trial balances into a single column of debits and credits by doing this:
    In the empty column to the right, put in and drag down this formula:
                                 =IF(B3>0,B3,C3*-1)
   (where B is the original debit column and C is the original credit column and row 3 is the first row)
    Then hide columns B and C so that only the combined column remains (Column D in this example).

     Credits are negative in this one-column trial balance.

 

3 Add 4 new columns to the right for:
                 Adj#   Debit    Credit      Final

 

4 In these 4 new columns, put in adjusting entry data – entry number, debit amount or credit
   amount.


5 In the "Final" column, manually put in and drag down the necessary sum formulas.


6 Copy the sheet into a new sheet.


7 In this new sheet, highlight and make a table of the whole working trial balance including headers.


8 Sort by adjusting entry number, but don’t sort blanks – that eliminates non-entry rows.


9 Insert a column to the right of the “adj #” column for a text explanation of the reason
   for the entry.


10 Eliminate any unnecessary columns of the table. Now you have a page of adjusting entries.


                 That’s all; you are done with your trial balance and list of adjusting entries!

 

   SETTING UP ACCOUNT GROUPINGS FOR FINANCIAL STATEMENTS (OR RATIOS)

    Several rows probably need to be grouped together in Excel for financial statement or ratio analysis groupings.

    The SUMIF function in Excel computes totals for items that have the same account grouping number,

     so first we set up a table of account grouping numbers (need not be an actual Excel table),

     then we assign grouping numbers to the trial balance accounts.  

    This may seem complicated the first time, but just carefully follow these instructions:                                                                 

   

    1 Make an account groupings table a few columns to the right of your final balance column, lets say Col K, L and M:  

 

         --------------Trial Balance--------------------           -------------------Table of desired account groupings-------------------     

     

                             Col H             Col I                           Col K                          Col L                                        Col M        

                           Final Bal        Assigned grp#           Group   #                description                             Total Amount

         row 3         $10,500                                             1                            Cash                                      (SUMIF function)

         row 4             7,500                                             2                            Accounts receivable                           

         row 5             1,500                                             3                            Other current assets

         row 6             2,000                                             4                            Accounts payable      

         row 75           2 500                                             15                             etc.

 

     2 Assign these grouping numbers to all final balances.  Lets say your final balances are in column H and

            you assign grouping numbers in the next column, column I.                                                                   

  

    3 Make the third column in your account groupings table under “Total Amount” a  SUMIF function column like this:                                                           

            =sumif(the Col I range containing the group numbers,the lookup number, the Col H range containing final balances)                                          

            Make the range settings absolute by pressing F-4 or by manually adding the $ signs to column and row settings 

            (because you are going to be dragging the SUMIF cell.)   This example assumes that your trial balance is in rows 3 to75

             and your account grouping table group numbers are in rows 3 to 15.

                                    

                                  for this example:      =SUMIF($I$3:$I$15,K3,$H$3:$H$75)          

                                                      

 

    4 Drag the SUMIF function down column M so that all of the account groups in your table have totals.                                                              

 

             That's all; you are done with account groupings that can be linked to financial statements or ratios analysis!


Last Updated: June 25, 2015