Cash Book in Excel

[Ernlife]

Thanks. Really how can one create a CashBook in Excel and use Pivot Table to analysis the data into a Trial Balance Format.

 

[Hui]

Ernlife


Firstly, Welcome to the Chandoo.org Forums


I'm not an accountant/book-keeper but I'd set up a simple table with the following fields (Columns)

<Strong>Date, Account (Code or Name), Description, Type (Credit/Debit), Amount</Strong>

Add data


I would add a Named Formula for the data area

Data: =Offset($A$1,,,Counta($A:$A),Counta($1:$1))


Then add a Pivot Table, Insert Pivot Table) on another page and link it to Named Formula, Data.


I'm sure any accountants can assist in refining the above.

You could add a number of extra fields like Entered By, Entry Date if required.

 

[Jack]

Hui the silence is deafening after this one. No one wants to admit to being an accountant!


I like your dynamic data formula for Data buried in this answer - that is an important concept and worthy of it's own post one day I think.

 

[Ernlife]

Thanks Hui.Will put it to work.