« Pictures on wiltonnewyork.com and geysermilton.comSlide Show on a Blog »

The basics of Spreadsheets.

07/18/11

The basics of Spreadsheets.

Permalink 06:09:00 pm by Brendan, Categories: Uncategorized

imageimage   image

Excel, Open Office Calc, Google Docs.  There are other spreadsheets of course, though the differences between them are either cosmetic, or involve things that only power users would really think about.  Excel is the Microsoft Office spreadsheet, which is the industry standard, and is part of every Office suite sold by MS.  Open Office is an alternate free office suite developed as an alternative to MS office.  And Google Docs are a "Cloud based" program, designed to run all the programs on Google's servers and let you use them from any web browser on any internet connected computer.

All three programs work very similarly.  At heart, spreadsheets are all about the cells.  Since it's tax time, one example would be to create a column, B for instance, where you would specifically record income received, or several columns for recording multiple types of income that might be seen differently when it's time to calculate your taxes.  I'm leaving Column A unmentioned, because that's almost always used to record the extra information- The month, the specific date, etc.

The real magic of spreadsheets however is functions.  You can use them to work pretty much any sort of mathematical calculation on the data in the various cells.  Indeed, in smaller countries, most of the economic analysis is done with spreadsheet programs.  But for most people, the Sum and Product functions are all you need.image

To put a function in a cell, click the cell to select it, then click the image in excel or open office, or image in google docs for the function wizard where you can work out you're full equations.Sum is the function that will add up all the numbers in a group of cells.  You can also subtract either static numbers or the contents of other cells by typing "-" in front of what you want subtracted.  The example here is excel which add the contents of cell B11 to 42, then add the contents of Cells C9 -C15.  I'm using a blank sheet to make the example, so naturally the result is 42.  If I wanted to subtract B11, or 42, or the group of cells, clicking in front of the number.

You can click any cell in the spreadsheet to add it to the numbers, to select a range, click the start, then click the end while holding down shift.  If you want to add up everything in a column, click the column's letter at the top of the screen.  If there are a few cells in a column you want excluded, hold down CTRL while clicking those cells.

The product function works the same, except in multiplies numbers together.  You can also put functions inside other functions, but that's something to pick up over time.

One other thing to remember about functions, when you cut and paste one, it will automatically adjust itself to it's new position.  For instance, if you've set up your spreadsheet so row 4 lists one type of data, and you put a function in cell B4 to sum up the totals in cells D4, F4, H4, J4, and so on however far, if you've got a similar set up on row 6, instead of repeating the setup for the function, if you select cell B4 and use Ctrl C to copy it, you can then click cell B6, and use Ctrl v to paste the function, and it will automatically adjust to sum up D6, F6, H6, J6, etc.

So, for tax season you can maintain a spreadsheet where you maintain predesigned column's and rows for specific income, expenses, deductions and other information.  When it's time to figure out the numbers for your forms, you can use the sum function to rapidly add up everything , and the product function to work out all the percentages and fractions at play.  Though if you do a bit of searching on your spreadsheets support site, you'll probably find a template that's already designed to have all the labeling and calculations worked out in advance for you.

A few other spreadsheet tips.  Odds are you've already had some advice on how you can adjust the size of columns by hovering your mouse cursor over the line between cells until it changes to a double headed arrow pointing to both sides, then clicking and dragging the mouse out to the size you want.  But if you want to resize a bunch of cells to the same size, you can select them by clicking the first then holding shift while clicking the last.  Then when you click and drag to resize one column, every selected column will resize the same way.

Also, if you consider the Sheet listing in the lower left, image multiple sheets is a very useful way to separate many different types of data, like different types of income, or specific car or medical expenses.  And because functions work across sheets, you can create a summary sheet where you set up cells to maintain the totals of the data in the other sheets.  In the sheet where you want to gather all the information, click the cell where you'll want that total, Click the Function button, and select Sum.  Then after clicking a box for numbers, click the name of the sheet where the data is in the lower left, select the cells with the data in that sheet, and either click OK, or select another number box if you want more numbers.

You can individually name each sheet by right clicking it's tab and selecting Rename from the pop up list it generates.

December 2017
Sun Mon Tue Wed Thu Fri Sat
 << <   > >>
          1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30
31            

Search

XML Feeds

free blog software