Advanced Excel Tricks

The Goal Seek, Solver and Scenarios playlist gives you advanced Excel tricks and tools to do “what-if” analyses with your practice data.  You can tweak one or more variables and see how the rest of your spreadsheet changes with these useful tools.  Watch how to structure your spreadsheets carefully so that by changing just a few cells you can modify the entire spreadsheet.  Goal Seek is the tool I use most often.  Goal Seek is the simplest of the advanced Excel tricks and the place I’d start.  Once you’re familiar with Goal Seek, you can use the other tools if you need to change more than one variable at a time and set constraints on those variables.

Arrays are definitely an advanced Excel topic.  There are certain kinds of calculations that you can only do with an array.  Arrays are also a great way to deal with a ton of data very quickly.  The key to arrays is that instead of hitting Enter to finish a formula, you choose Ctrl+Shift+Enter instead.  Arrays are designed to perform multiple calculations in one formula, potentially saving you space on a complicated spreadsheet.  Arrays can also make your formulas and your spreadsheet more complex for users who aren’t familiar with advanced Excel tricks.  Be sure to document in the spreadsheet how the formulas work and what the formulas are intended to do.  It’s easy to accidentally break an array formula if you aren’t familiar with the Ctrl+Shift+Enter trick.

After watching the Arrays Part 1 and Arrays Part 2 playlists you’ll be ready to use arrays with your practice’s data.  Start slowly, document your formulas, double check the answers to the array formulas with traditional Excel formulas (you might need several helper columns for some formulas) and you’re on your way to more powerful Excel spreadsheets.