A slightly "different" excel question

[OctoberLouise]

Hello all!

I am new around here and am hoping for a bit of inspiration!

I have a project that I am currently working on and am a bit stuck... I have a spreadsheet which has lots of rows and columns, I need to add data to it regularly. One column and around ten rows per month. It has been going for almost 3 years and it is only going to keep on growing. I am basically trying to find a way of scaling it down a bit without compromising the quality of the data and make it a bit more user friendly / look better.

The data itself is fairly simple and all on just one worksheet. Column A is "date", column B is "unique ID number", column C is "client name", column D is "client address", column E is "cost". Each month, payments will be made and I will add a column representing the current month and put any payments made in the relevant clients row (hence why I add one column per month). Each time there is a new client, I will add their details on a new row (hence why I add around ten rows per month).

Because it has worked for me for so long, I have become very used to it and to me it is fine. However, other people will need to use it soon and I have had comments that it is too large, a bit of a pain to follow and needs some tweaking. The only idea I have come up with so far is to create a new worksheet and use it as a "finalised accounts" sheet - so any clients for whom payments have finished can be moved to this separate sheet. Otherwise, I'm kind of stumped! I can see that it is large and only going to get larger, but am stuck for ideas on how I can improve it!

Any tips will be gratefully received! Thank you

 

[Smitty]

Welcome to the Board!

Instead of adding a column for each month, I'd stick with the date entry you have and just add a new row for each payment (think of this as storing transactional data like a database). Then you can use a Pivot Table to summarize the data.

As for ease of use, you could consider using Excel's Data Form.

HTH,

 

[Caribeiro77]

Welcome to the board, can you post some examples os what you have so far, and desired result, e.g when do you want to move a client row to the new sheet?
Post some examples....you can use Mr Excel HTML maker ( see how to on my signature)

 

[OctoberLouise]

Thank you both, some good tips. A pivot table sounds good, I will try that out. Sorry it's taken so long to reply, just been installing and testing the HTML maker! Ok, I've made a sample spreadsheet (see below). The real one is the same format, but obviously with lots more data. I forgot to mention in my OP, there are two columns on the end, one for any additional costs incurred, and one showing the total amount outstanding. I was thinking of moving the clients to a separate worksheet once the amount outstanding reaches zero. After X amount of years they can be deleted completely anyway. The desired result is just to make it 'look' better / more user friendly. Just to add, I normally have the individual month columns 'hidden' so you don't have to constantly scroll left to right. Ooh, I've just thought, maybe I could do a 'look up unique ID' sheet using a VLOOKUP to quickly check the amount outstanding by typing in the client's unique ID...?

 

[OctoberLouise]

Excel 2012
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Date	Unique ID	Name	Address	Cost	Apr '13	May '13	Jun '13	Jul '13	Aug '13	Sep '13	Oct '13	Nov '13	Extra costs incurred	Amount outstanding
2	02/04/2013	12300	J Smith	Sample x x x x x x x x x	£1,250	£50	£50	£0	£0	£0	£0	£0	£0	£500	£1,650
3	12/04/2013	12301	D Reeves	Sample x x x x x x x x x	£1,000	£40	£40	£40	£40	£40	£40	£40	£40	£0	£680
4	15/05/2013	12302	F Brown	Sample x x x x x x x x x	£1,100			£44	£44	£44	£44	£44	£44	£0	£836
5	20/05/2013	12303	G Hurst	Sample x x x x x x x x x	£1,250			£50	£50	£50	£50	£50	£50	£0	£950
6	02/06/2013	12304	L Mills	Sample x x x x x x x x x	£1,300			£52	£0	£0	£0	£0	£0	£700	£1,948
7	22/06/2013	12305	R Stevens	Sample x x x x x x x x x	£1,500				£60	£60	£60	£60	£60	£0	£1,200
8	01/07/2013	12306	Y Tyrell	Sample x x x x x x x x x	£1,450				£58	£0	£0	£0	£0	£650	£2,042
9	18/07/2013	12307	K Payton	Sample x x x x x x x x x	£1,200					£48	£48	£48	£48	£0	£1,008
10	15/08/2013	12308	D Bradford	Sample x x x x x x x x x	£1,500						£60	£60	£60	£0	£1,320
11	28/08/2013	12309	B Sissons	Sample x x x x x x x x x	£1,000						£40	£40	£40	£0	£880
12	17/09/2013	12310	K Thomas	Sample x x x x x x x x x	£1,300							£52	£52	£0	£1,196
13	23/09/2013	12311	S Masters	Sample x x x x x x x x x	£1,450							£58	£58	£0	£1,334
14	02/10/2013	12312	P White	Sample x x x x x x x x x	£1,200							£48	£48	£0	£1,104
15
16															Total Outstanding
17															£16,148
Sheet1

Cell Formulas
Range	Formula
O2	=(E2-F2-G2-H2-I2-J2-K2-L2-M2)+N2
O17	=SUM(O2:O14)

 

[OctoberLouise]

BUT the main goal is to (somehow) try to make the main sheet 'better'...

 

[Smitty]

From a data perspective, I'd probably use multiple lines entered by date. The initial "cost" would be entered as a positive or negative value, and the subsequent payments as the opposite. That way a pivot table can calculate the amounts paid vs. owed. The way you have it set up now a pivot table wouldn't do much good, and you'll constantly have to add columns. If you enter on a transactional basis you won't have that problem.

 

[OctoberLouise]

Thank you Smitty, I'll give that a go