It seems like I’ve been using Excel for as long as I’ve been using computers, and I like to think of myself as a power user. I’m familiar with how to create pivot tables, how to write VBA functions, graphs, formulas, pretty much most things you can do in a spreadsheet. I often turn to Excel in my day to day tasks as a database administrator. It’s very handy to quickly compare how much a database grows over time.
To get myself up to speed with the 2013 version I recently picked up a copy of Microsoft Excel 2013 Inside Out, written by Mark Dodge and Craig Stinson and published by Microsoft Press. And I’m glad I did. This is an excellent book on Excel, whether you’re already familiar with it or brand new to spreadsheets.
This is a thick book, coming in at over 1100 pages. There are 32 chapters organized into 10 sections, from getting to know the Excel environment to formatting and editing worksheets to working with charts and graphs and finally automating Excel with macros and custom VBA code.
My favorite section is Creating Formulas and Performing Data Analysis. It starts out by explaining the basics of functions; how to write them, operator precedence, the difference between relative and absolute cell references, etc. Where it really takes of is where it describes each built in function. They’re separated by purpose for easy reference; mathematical,text, logical, information, and lookup. For example you’ll see the difference between SUM, SUMIF, and SUMIFS. All the statistical and lineal regression functions are explained. The book includes sample Excel files where you can see how the functions work with real data.
Another favorite is Chapter 24, An Introduction to PowerPivot.Microsoft PowerPivot for Excel 2013 is an add-in that allows users to build their own data models for analysis for self-service BI. This chapter is only meant to be an introduction, but it walks you through how to set up your data models and work with the data, and there’s even a section on how to query with DAX (Data eXpression Language).
Don’t skip the first few chapters, even if you’re an experienced Excel user. You might miss some good tips. For example I wasn’t aware before that you could have a workbook open every time you open Excel by saving it to C:\Users\<your name>\AppData\Roaming\Microsoft\Excel\XLStart. Maybe it was just my imagination but when I did this my workbook seemed to open quicker, before all the Excel add-ins. Another helpful section is the discussion of what was removed in 2013 and, even better, what was added in 2007 and 2010, good information to know if you’re upgrading from older versions.
Also don’t neglect the back of the book. Appendix A maps the old Excel 2003 menus to their new location on the ribbon, a great help if you haven’t worked with the ribbon before. And Appendix B is a list of Excel keyboard shortcuts. There are definitely some time savers here.
I recommend Microsoft Excel 2013 Inside Out for any Excel user, either power user or newcomer. I know I’ll be referencing this book often.