I’ve recently discovered array formulas and the wonderous ctrl-shift-enter function. The versality of excel expanded tremendously after I learned these things. For instance, you can use an array formula to calculate the average of cells fitting multiple criteria. You can also count cells fitting multiple criteria, and a number of other things. OMG, array formulas have helped me so much!
Tonight I’m going to sit down and finally learn myself to do a pivot table. I hear pivot tables are even better than array formulas.
Other cool things: the “CONCATENATE” function. You use this if you want to combine two columns together. For example, let’s say in one column you have area codes and in the next column, you have the 7-digit telephone number. You would use “concatenate” to merge these columns so that you have 10 digits.
You use the “text to column” thingy under Data to do the opposite, i.e. break one column into two.
Lots of people don’t know that you can make a cell reference “fixed” by using a $ sign in the formula.
I use the find and replace function a lot. Say you have a column where each cell has a similar formula, but with different, non-sequential cell references. You realize you meant to type “AVERAGE” instead of “SUM” in the formulas. Just highlight the column, press ctrl-F, then “replace all” SUM with AVERAGE. This will save you from having to retype formulas individually. By being a little clever, you can also insert and delete things using this technique.
Freezing panes can be a sanity-saver if you’ve got a huge spreadsheet.