Help me become a spreadsheet power user

November 19, 2011 2:54 PM

How do I become a spreadsheet power user? And do I have to get Excel to do it? (Or can I stick with LibreOffice/Google Docs?)

I'd like to use spreadsheets to create personal budgets, reading lists, sleeping logs, and the like. Especially when it comes to the budget, I feel there must be stuff I'm missing out on, both presentation-wise and math-wise. How can I learn more?

posted by Busoni to computers & internet (10 answers total)

The first thing I tell people about using Excel is that if you're using it to make lists, you're doing it wrong (or more accurately, you're not using 99% of the software).

For example, for budget purposes, the first step would be the simplest, start adding and subtracting values. You have a current balance, you enter a transaction and then it automatically calculates your new balance. The second transaction takes the result of the previous operation and adds or subtracts (really, you're just adding a negative) the amount of the second. In this way, you never actually do the math and if you ever go back and change an old value, the change cascades.

Next, you're going to want to assign each transaction a date. And if you ever have something out or order, well, that's OK, Excel can sort for you, but that's not really the point because then you're just "list making" again. Create a new sheet in your workbook (those are the Excel terms, I'm not sure if LibreOffice calls them the same thing or not, but I'm sure the functionality exists). Now you're going to learn how to reference other cells to calculate income and expenses over any given date range. To make it look pretty, type in "From" in A1 and "Through" B1, let A2 and B2 be the dates themselves, in A4 type in "Income" and in B4 "Expenses". You know how to add a range of cells, but spend some time googling and see if you can figure out how to selectively add one cell's value if certain conditions are met. In this case, for the Income cell, A5, you'll want to add the transaction value if it's a positive amount and the value in the cell beside it (the date) is less than or equal to B2 and greater than or equal A2 (in other words, on or between the dates that you can change whenever you need to in the income/expense sheet). You'll do the same for the Expense cell, B5 except you'll want a negative amount as one of the conditions to include the transaction.

You can get more complicated with categories and subcategories. Pivot tables will also come in handy quickly. As for presentation, Excel is kind of boring, I prefer R, but the learning curve is much more steep. But whatever data you want to tease out of Excel, you can graph. I'm a huge fan of conditionally formatting expenses, such as a 20% increase. For example, if the electric bill deviates from the average of the last 3 payments by more than 20%, highlight it in bold, maybe that's something to look into. Earn some more money this week by working overtime? Smiley face, that's a reminder to buy yourself something nice, be happy. Maybe you want the overall balance number to shift from red to green as you move away from $0 and closer to your savings goal?

That's just budget, but this should give you some ideas.
posted by Brian Puccio at 3:21 PM on November 19, 2011 [1 favorite]

It's been a while, but I'd check out sites like Contextures, Debra Dagleish, and sites they link to. Lots of stuff for power users there.
posted by dfriedman at 3:36 PM on November 19, 2011

Pivot tables are probably one of the most powerful uses of Excel and Google spreadsheets, simply because you can now arrange data, tables, calculations and charts any which way if you've got a consistently formatted stream of data.

Look up an intro to pivot tables and start learning how to make them.

It can ultimately save you hours and hours of time and it's also fun to learn.
posted by The ____ of Justice at 5:59 PM on November 19, 2011 [1 favorite]

In your situation I would totally get the ASAP Utilities excel add-in. It adds a menu item that basically creates a ton of fancy excel shortcuts for you. It won't make you an excel wizard, it might even keep you from learning stuff as fast as you might by honest effort. But it will get you a lot further a lot faster in doing what you want to do, which I assume is the goal. It's free for home users, $49 for business users.
posted by stupidsexyFlanders at 8:12 PM on November 19, 2011 [1 favorite]

OpenOffice Calc does all of the math that Excel does - and I do some pretty complicated number crunching with Calc (averages, medians, even getting correlations).

It also does v-lookup, which is a pretty awesome means of running calculations between worksheets.

I don't know about pivot tables, as I don't use them in Excel, but I much prefer using Calc to excel. I've created all the tables for my relational databases in Calc - and it does all my figuring for my research.
posted by jb at 8:21 PM on November 19, 2011 [1 favorite]

The equivalent of Excel's pivot tables is OpenOffice Calc's data pilot. It's pretty awesome.
posted by nemp at 11:43 PM on November 19, 2011

I use Excel for my budgeting and tracking by having a running data sheet that records Transaction Amount - Month - Category and then I have a front summary page that takes that pulls that data in a table with Months as the column heading and Categories as the row headings. I could do the same thing with a pivot table, but for this purpose, I prefer my own formatting. Anyway, I mention this because Array formulas are awesome for this. They allow you to compile data using multiple criteria easily (Grocery spend in March). If you know your vlookups and your pivot tables, the next big game changer (for me) was array formulas.

Google doc works ok. I've got a few budget sheets running on it. If you keep things simple, it'll do just fine. It's just not as good at handling a lot of data or complicated calculations quickly.

Also, I recommend that you separate your data from your presentation. Data should just be ugly lists with tons of rows and categories. You should have another sheet that pulls it all together and makes it pretty. In my experience, when you try to make your data pretty (adding colors, skipping rows for formatting, etc) you quickly lose the ability to use the full range of Excel's abilities.
posted by oryelle at 8:10 AM on November 20, 2011 [1 favorite]

Also, I haven't used OpenOffice for a couple years, but when I did, it worked fine. It did all of the Excel like things I needed.
posted by oryelle at 8:28 AM on November 20, 2011

As far as Excel vs other spreadsheet apps, I would first say stick with whatever app you have at your disposal. If it is Excel, that is great, but working with Numbers, OO Calc or Google Docs will take care of most of your needs. While there are a lot of great little utilities for Excel that you may not find with other softwares, for what you have described in your post it does not sound that you need all that much sophistication. I would stick with the free stuff for now.

These other apps do just fine for probably more than what most people need. Some also offer some distinct advantages such as being able access your Google doc from any computer with an internet connection. Also, MS products, being the bloatware that they are, can be just too confusing when all you want is a simple answer. Limiting the array of menu options is a good way to get used to the spreadsheet world without wasting time on features that may be above your skillset at the present time. You can always upgrade later on, but for now K.I.S.S. is the good mantra.
posted by lampshade at 10:46 AM on November 20, 2011

I love spreadsheets, and I learned the most by having real scenarios to use. Recently, I used an existing mortgage spreadsheet, copied it to multiple sheets in a workbook, and tried different scenarios of points and rate. You can build a payroll spreadsheet and show the effect of various pay rates and raises. I used a spreadsheet to show someone the difference between the old bracket method figuring taxes and the current tiered version.

Learn how to make graphs. There are some terrific sites with very cool info-graphics, try to replicate them. It's useful to know which chart type displays info best. Make your spreadsheets nice looking.

Excel tutorials. And your local adult ed. program may have a good class.
posted by theora55 at 4:09 PM on November 20, 2011