Practice, Practice, Practice
– I find that sorting and filtering seem simple, but are often an overlooked skill set. Know these functions inside and out including multi-level sorting. Getting your data organized is one of the biggest parts of the battle.
– If statements are really handy, although I honestly don't really use them at work. I feel like they help me to understand the way Excel thinks though.
– Keyboard shortcuts save you a ton of time and effort. Our teacher taught us to imagine that you had 50,000 rows of data when you were learning how to do something. So we always had to think to ourselves, could I do this like this with all of those rows or would I need a better way. I'm not sure if that makes sense or not, but consider the following shortcuts: Double clicking the little black square at the bottom of the cell (the one that you click and drag to fill formulas) will fill the formulas all the way down the column to where Excel deems a logical stopping point. Ctrl, Shift and an arrow will select all of the cells in that direction. Ctrl, Home will take you to the top of your worksheet; or Ctrl, End to the end.
– I find it really useful to name a range of data by selecting it and typing a name in the box at the top left of the screen. This is especially helpful when referencing the data with a Vlookup function because it saves the need for using absolute values.
– Also, Vlookups are a must know.
– You also need to make sure you know the difference between an absolute and relative cell reference.
– Some of the time and date functions are pretty handy. I use Ctrl, ; or Ctrl, : for a time or a date stamp sometimes.
– I tend to think up something that I would like to do in Excel and then Google what formulas to use. I've learned a ton that way.
– Oh yeah and you should gain at least a little familiarity with pivot tables. They can seem scary if you've never worked with them.