Have you ever found yourself staring at a spreadsheet and wondering how to get anything useful out of it? Spend enough time doing that and you break out your calculator to get answers. Don't be ashamed, we've all done it.. Here are a couple tricks that may save the day for you.
Golden Rule: If you get nothing else out of this, know this golden rule. Don't hard code numbers if you can help it. Use formulas. Don't hide rows or columns either. Ok, that was two golden rules. Trust me, you're better than that!
Good spreadsheet etiquette will get you far in life. Use formulas, think long term, and keep in mind that someone else will likely have to follow your work. The formula helper in Excel is decent if you need help with these.
Easy Stuff: If you are keyboard oriented, use these tricks:
Control C to copy.
Control V to paste.
Control and an arrow key will get you to the edge of a range or sheet quickly.
Control and the home key will get you to cell A1.
F2 in a cell to see and edit formulas.
F4 repeats last command or action.
Clever Stuff: You will literally use these all the time:
VLOOKUP: Say you have a list of clients with their respective net worth. VLOOKUP will give you the answer to, "What is Ms. Moneybag's net worth?" Formula: =VLOOKUP(A1,C1:D20,2,0) Translation: Give me the value from the list that corresponds with the Ms. Moneybag in cell A1, in the client net worth list, A1:B20 - second column, giving exact matches only - no guessing please.
Say you don't like typing ranges like C1:D20. Grab the range you want with your mouse, think of a name that makes sense (NetWorth in this example), and type it in the name box just above the spreadsheet in the upper left hand corner. Don't use spaces in the name. From then on, you can refer to this range by the name. This turns that ugly formula above into something almost pleasant, =VLOOKUP(A1,NetWorth,2,0).
SUMIF: If you want to add up all the values for something like sales in a zip code, this is your formula. Formula: =SUMIF(C1:C20, A1, D1:D20) Translation: I'm looking to get the total of sales for the zip code in A1 that is in C1:C20, and has amounts in D1:D20). If you want to as a more complicated question like total sales in a zip code of a specific product, you can use this formula's big brother, SUMIFS. That's a story for another day. Another option is a pivot table.
If you have lots of data that you need to quickly understand, try using a pivot table. It's a quick and dirty way to get you good information fast. Here's a trick - make your data a table first, and any new information you add will automatically update in your pivot table. There are entire books written about pivot tables, and they're not for everyone. It's easy to make a simple one that will help with 80% of what you need without any help.
When you blow the minds of your friends with your slick excel skills, let me know. I'll tell you where you can build a statue in my honor.