Friday, September 2, 2011

Fiscal Fridays: A crash course in spreadsheets

So this is really just a side-post, since my main budget post was already so long. I’m covering some basic info on creating Microsoft Excel or Google Spreadsheet formulae. I’ve noticed that a lot of people aren’t very familiar with Excel spreadsheets, so here are a few helpful terms to get you started:

Cell – Each box is known as an individual cell. It’s named according to the row and column it falls in; for example, the top-left cell in a sheet is A1, since it’s in the A column and the 1 row.
Column – Vertical sections in a spreadsheet named by letters A-Z, with A starting on the left. After Z comes AA, AB, AC, etc. to infinity.
Row – Horizontal sections in a spreadsheet named by numbers. The top row is always 1, and the numbers go on as high as you need them; pretty much forever.
Formula – A mathematical representation within a cell, denoted by the =SUM( ) text, with cell names and mathematical functions (plus, minus, multiply, divide) within the parenthesis.

Now, on to creating formulae!

First, go to the cell where you want your formula. In my budget, I have a formula for “total bills” and one where I subtract the bills amount from the income amount. Formula cells (blank at the moment) are highlighted below.

You type a formula by starting with this basic function: =SUM( ). That tells the software that you’re doing math, and the cells that you are mathing will go in the parenthesis. For example, if I’m adding cell A1 and cell A2, I’d type =SUM(A1+A2) in the box. When you hit “enter,” the formula will disappear and a number, the sum, will appear. (If that’s not working, right-click the cell, choose Format Cells, go to the Number tab and select General. Should fix it.) Below is an example.

You can use =SUM(A1:A5) to add a series of cells; in this case, it’s A1, A2, A3, A4 and A5. So in your total bills cell, add all the expenses by using the colon function. Don’t worry about including blank cells; they won’t change your total.

My total is currently 0 because I don’t have actual numbers listed in there. But if there were numbers there, the total would appear. Next, you want to subtract your bills from your income. To do this, use the same function, only with a minus sign instead of a plus sign. For my spreadsheet, the formula is =SUM(B1-B26). The SUM part doesn’t change, even though you’re subtracting.

So there you have it! If I had real numbers plugged in, it might look something like this:

These aren’t actual numbers for me; I just plugged in some randoms to give you an idea. Now, go read the REAL Fiscal Fridays post :)

No comments:

Post a Comment

Hello, awesome commenter! I love feedback and try to respond to all comments (especially ones with questions) if I can find an email or blog address.

Thaaaaaanks for reading!