|
|
Post by solicitorious on Apr 16, 2016 20:31:34 GMT
How did everyone learn their spreadsheet skills? Anyone any tips on learning the skills to be able to produce an effective tracker (I know people kindly offered example spreadsheet but the skills to adapt these or produce your own would be great - teach a man to fish and all) Just get started simply, to begin with, and learn by your mistakes! There's plenty of help either in Google Docs itself, or on the web. Just Google what you're trying to do, or what a keyword does. Include the word Excel, and a whole raft of examples will appear, most of which are almost perfectly transferable to Google Docs spreadsheets. Failing which, ask here I suppose, if you're really stuck.  Lesson 1 (to get you going) Use Google to get an understanding of what the following functions do, and play around with them in a spreadsheet: $ (as part of a cell reference) IF MAX (and MIN) SUM COUNT SUMIF COUNTIF SUMPRODUCT Lesson 2Find out what "Conditional Formatting" is, and try and use it in a spreadsheet to automatically highlight cells of interest, based on their content.
|
|
|
|
Post by uncletone on Apr 16, 2016 21:27:02 GMT
And of all these, the greatest is "IF". |
|
duck
Member of DD Central
Posts: 2,882 
Likes: 6,974
|
Post by duck on Apr 17, 2016 4:56:51 GMT
All platforms are slightly different in the way they present downloadable/cut'n'pastable information.
It is sensible to have a look at this information first and see the common threads. That way you can build a spreadsheet that you can then use for different platforms without going back to the beginning when you need a new spreadsheet.
When learning don't get over ambitious. Use formulas that you can easily manually check are giving you the correct answer. When you know the spreadsheet is working correctly/as you want it to expand the sheet by cutting and pasting. If you always want to refer to a specific column or row the $ symbol is invaluable since it will lock the reference to cell/column/line and no amount of cutting and pasting will destroy that reference (I've seen engineers manually changing 100's of cells when a simple $ sign is all that is needed!).
As solicitorious has said 'on line' help is very good, I've never failed to find something to help even when I've been trying to do something very obscure.
|
|
SteveT
Member of DD Central
Posts: 6,875
Likes: 7,924
|
Post by SteveT on Apr 17, 2016 8:09:23 GMT
Excel's "pivot tables" are incredibly useful for totting up different transactions types / different borrowers / etc and are extremely simple to use. Simply highlight the entire columns that contain your source data, typically from a transaction statement download (including column titles in the first row). Then "Insert - Pivot Table" and choose to put it on a new sheet. Tick the field names (column titles) you want to use in the table and then drag and drop them into the appropriate area in the set-up wizard. For example, to sum all transactions of the same type, drag your [transaction type] column title into the "Rows" area and your [amount] column title into the "Values" area, changing this from "count of" to "sum of". Voila, you have a table that totals all your transactions by type, which you can then tweak further as you want. If you drag your [date] column title into the "Filters" field you can also limit the table to specific time periods (eg. a certain month). The best thing is that, when you next update the extract data (use Paste Special/Values" to overwrite the data, leaving your column titles unchanged), you simply right-click/Refresh the pivot table to update it without having to recreate it.
|
|
