Accounts spreadsheet help, please

[Gerri Attrick]

Spreadsheets give me hives.

Currently, I use a calculator to work out income and expenditure balances, but I'm sure there must be a formula for this - a one time set-and-forget thing - which would make it easier.

I have four columns:
Item - e.g. Amazon Payment, Fussy Librarian promo cost
Income
Expenditure
Balance.

Actually, there's a fifth, where I record a dollar price (I work in pounds sterling) if I've had to pay out in that currency (that's mainly promos and covers - my designer is in Australia)
I use it only to check on price increases (more rarely, decreases!) since my last promo/cover and would be prepared to forgo it if it messed with any formula.

I've tried Googling for an answer, but either can't find one to match my situation - or can't understand the answer when I do. (Did I mention that I'm a techno-numpty as well as being allergic to spreadsheets?) 

So, how do you record your accounts? Any help would be much appreciated. Thank you.

[123mlh]

So what you want is the total amount for each of those columns?

In Microsoft Excel you can write =SUM(A:A) to sum all the values in a specific column where the A:A part is the letter at the top of the Column. Just don't do it in that column or it won't work.
Or you can write =SUM(A1:A35) to sum a range of cells. Where the first value, A1, is the column and row of where you want to start and the other one, A35, is the column and row of where you want to end.
Or you can go to the bottom of a range of cells and click on a cell and then use that little summation sign that looks like a big E and has Auto Sum next to it. It's on the top right of the Home tab in my version. That will highlight all the cells above that it thinks you want to sum and then just hit enter if it's the right range.

Or you can click on the letter at the top of the column and then look in the bottom right corner where it says SUM to see the sum of all the values in that column.

[Lynn]

Maybe you'll find something here that will help?

https://support.office.com/en-us/article/excel-for-windows-training-9bc05390-e94c-46af-a5b3-d7c22f6990bb

Here's a basic one that might be what you're looking for.

https://support.office.com/en-us/article/overview-of-formulas-in-excel-ecfdc708-9162-49e8-b993-c311f47ca173?wt.mc_id=otc_excel

and

https://support.office.com/en-us/article/sum-function-043e1c7d-7726-4e80-8f32-07b23e057f89?ui=en-US&rs=en-US&ad=US

[Gerri Attrick]

Thank you.

I'm probably being particularly thick here, but how do you indicate a minus amount?

If my income balance is (say) £100 and I then spend £35 on a promo, I need to subtract that line/cell/amount from -and in - the balance column. Is there a way to do that? That is, one (or more) rows of the balance column may be additions, but some will be subtractions.

I'm not explaining this very well, sorry. Unfortunately I can't attach a ss to show what I mean. *Sigh*

[cecilia_writer]

I think what you need to do is to click into the balance column (e.g. column D) for the first entry and type something like this: =B2-C2
where column B has the income in it and C has the expenditure, and row 2 is the first row where there's any data (apart from headings).
Then you should be able to drag this formula down the balance column and it *should* automatically adjust itself to the different rows as it reaches them, so to speak. If you have the totals there already for the other columns it will apply the formula to them as well.
Hope this makes sense!

[Gerri Attrick]

Thanks, Sheila. I'll give that a try. I wasn't aware that you could drag it down all the cells in a given column so I hope that works.

[123mlh]

Thanks, Sheila. I'll give that a try. I wasn't aware that you could drag it down all the cells in a given column so I hope that works.

If dragging doesn't work, click on the cell with the formula you want to use, hold down Ctrl and C at the same time (this is Copy), highlight all of the cells where you want to put that formula, and then hold down Ctrl and V (which is Paste) at the same time, and hit Enter. As Cecilia said, the formula should auto adjust as you copy it to other cells.

And, as she mentioned, for subtraction you use the negative sign. So =A1-B1 subtracts the value in Cell B1 from the value in Cell A1. For adding just two cells you can use the + sign the same way.