John Cotter
Share this content

Excel to maintain accounts

Excel to maintain accounts

I am finding it easier and quicker to maintain accounts in Excel database.

Bank statement cut and pastes straight in.

Cash books likewise.

Using sumifs to produce great Management accounts.  Pivot tables can also be used very well for reporting.

Balance sheet - again sumifs.for monthly analysis.

Coding analysis allows flexibility by having two columns Primary Code and secondary code.

Anyone else work like this?


Please login or register to join the discussion.

27th Apr 2012 17:51

Done it for years

In the days before affordable computerised bookkeeeping/accountancy I set up my own spreadsheets in Supercalc for DOS for doing clients accounts. It is very simple to create a group of sheets for expenditure, income, fixed assets etc.These sheets link into an extended trial balance where adjustments can be done and the final trial balance is linked to a sheet for the accounts.

The only downside is finding the occasional error when an amendment stops it balancing.


Thanks (0)
By slarti
to Ruddles
03rd May 2012 12:19

Why re-invent the wheel?

scalloway wrote:

In the days before affordable computerised bookkeeeping/accountancy I set up my own spreadsheets in Supercalc for DOS for doing clients accounts.


Company I worked for got our first affordable computerised book keeping/accountancy package and VisiCalc at the same time - pre MS-DOS and PCs becoming available.

Since then I have never seen the point in re-inventing the wheel when others have done all of the donkeywork, and have created proper audit trails.

Plus it is too simple for people to make mistakes in data entry in a spreadsheet, unless great lengths have been gone to, to limit the data entered into cells to the type that is appropriate.


Thanks (0)
to Manchester_man
03rd May 2012 19:23

Time rich, cash poor

As I recall in those days such software was very expensive. When I started doing accounts on my own behalf I had the time to finesse my spreadsheets but no cash to buy dedicated software. I wasn't reinventing the wheel, merely linking  a few pages of sprreadsheet to recreate a manual system that I had used in pre computer days in a CAs office.

My typical client has always been a one man band sole trader so the chances of major errors are prety small.

Thanks (0)
By slarti
to cheekychappy
04th May 2012 10:06

An accounting system for the cost of a car.

I suppose I am looking at things from a different perspective, as I was the accountant for the company.

My memory says that the whole system, computer, printer, accounting software and VisiCalc, together with a few boxes of 8" floppies cost about £3,500.00 as the boss did wonder if we should have bought a new Escort van instead

But within 2 months it had allowed me to reduce the accounting cycle from quarterly to monthly with one less member of staff and to produce a budget model that could be revised throughout in a day - given the 1 line carried forward limitation of VisiCalc and 1 page per floppy, that was pretty good.

Thanks (0)
27th Apr 2012 18:30

I would refer youto Ecclesiaties Ch1 v 9-11

It is all right untll it goes wrong, then you are faced with recreating or trawling through, you will realise why the Venitians developed double enty all those years ago


Thanks (0)
27th Apr 2012 20:50


Wasn't double entry developed in Florence rather than Venice?

Thanks (0)
to bhaveshk
28th Apr 2012 09:12

Oh! A history question - I love history...

thisistibi wrote:

Wasn't double entry developed in Florence rather than Venice?

A Venetian called Luca Pacioli, first documented it in his 1494 book, but his description was of practices used by the Florentine traders and even the Medici for more than a century prior to that.

Thanks (1)
27th Apr 2012 21:24


Incredible flexibility with Excel, both in terms of workings and easy to add a note to the accounts.

I gave up with Sage Accounts when I kept losing my custom formatting, necessary because their formats were poor and unprofessional, with updates that were all too frequent because they weren't doing their job properly.

Thanks (0)
28th Apr 2012 00:43

I think vt accounts is great. It also has a excel add in!

Thanks (0)
28th Apr 2012 09:06

VT is the Excel-based accountancy package which has had all of the wrinkles a home-grown effort will encounter programmed out of it.  I suspect the concept for VT grew out of frustrations with Sage, as a Sage user since 1990 VT has had all the silly annoying features of Sage taken out of it at source.

Excel is fine for a small, simple business and all my one-person band contractor type clients use it and there are no great problems.  But pivot tables get very clunky and unwieldy for larger businesses, and you have do a few more miles to ensure you have complete integrity back to the TB.

Incidentally this last point is a valid criticism of Sage.  I was involved in a Sage implementation out of SAP for a £400m business and the pivot tables were a bloody[***] nightmare.  Project and department heads would come up with these reports from the Sage team based on the pivot tables, but they'd have gaps and only be half of the story....


Thanks (0)
28th Apr 2012 09:38

Excel and Cloud

Excel is best for very simple businesses. I don't try anything complicated - I just ask for lists.

The cloud accounting packages that accept bank transaction downloads ensures that the correct transactions are recorded and an accountant can correct errors.

Thanks (0)
03rd May 2012 11:00

Cheaper than Excel

Compatible with Excel files and free

Libre Office Calc

And yes, it does do Pivot Tables

Thanks (0)
03rd May 2012 19:03

I use excel for doing book keeping for £0.5m t/o companies

If you know what you are doing, you can use excel to produce a fully fledged double-entry book keeping system. Producing a Trial Balance, aged debtor report, aged creditor, full nominal ledger, VAT reports etc.etc. all within one excel file. Mine is about 2Mb in size with no data. and my spreadsheet doesn't have 100 columns for different expenditure, just one column with a drop-down menu for you to choose the expenditure. Please do not ask me for a copy, as I don't hand it out. I use it as a marketing tool to win new client for my employer.

Excel is far more powerful than most people realise. Did you know that a flight simulator computer game has been written in excel?

so long as you know your macros, you can do almost anything in excel.

Thanks (1)
03rd May 2012 19:10

Accounting software?

If you are going to that level of sophistication isn't it better to use an accounts package? Even though none of them are perfect.

Thanks (1)
11th May 2012 15:58

I prefer to use accounting software .
My experience of using both Excel and Sage for maintaining accounts has led me to conclude that it is generally best to use an accountancy package over spreadsheets. The best package that I have ever used in practice was Sage Accounts Production. This was simple, reliable and fun to use (as far as you can have fun under the circumstances). All credit to Microsoft for developing Excel however. It is in my opinion only 'a' tool in the Accountants work-bag not 'The' tool. I think it best that as professionals we are using the correct software for the job, as the 'everything to everybody' Excel product at times can be a bit of a blunt instrument.


Thanks (0)
11th May 2012 16:36


I think the OP means how to record individual transactions.

When I started preparing accounts I used a mixture of Excel and Word but it took a long time.

I switched to Digita Accounts Production and it was very quick. The software would accept individual transactions if you wanted to use it.

Most of my clients use Excel because it is easier for them to use than an accounts package. I then analyse the schedules, and add sheets for journals, a journal summary and an extended trial balance. I then input the trial balance into Digita Accounts Production. I could import the spreadsheet trial balance but I have found that it is quicker to input it.

What other programs have you used other than Sage. Most people think it is the worst.


Thanks (0)
11th May 2012 16:30

Excel is a...

...poor man's VT.

For heavens sake just get VT for the price a self assessment tax return (ish) and save yourself all the hassle.

Pivot (tables) shmivot I say.


Thanks (0)
14th Jul 2012 10:12


Thanks (0)
15th Jul 2012 12:11

Double entry

Is VT double entry?

Unfortunately a lot of clients are not comfortable with double entry bookkeeping.

Thanks (0)
Share this content