Want to Improve Your Excel Skills? Learn Access

by

It takes two for tango What if you love Excel for all the wrong reasons? What if its flexibility creates nothing more than a glorified mess? What if you start neglecting the skills that truly matter?

Every formula in Excel is an accident waiting to happen. Sometimes, a parameter is missing, sometimes it points to the wrong cell, sometimes you use absolute references instead of relative references…

Structure Is Your Friend

What can you do? First of all, learn how to structure your workbook. Each sheet should be assigned to a specific role: create a control/parameters sheet, a data sheet, a report sheet (use different tab colors for each role).

You’ll probably know  how to structure a workbook if you know how to structure your data. Unfortunately, most Excel users are not aware of this (because of that “flexibility” thing).

That’s why you should learn Access (or a similar database tool). You don’t have to be an Access power user. You just need to learn the basics: what a relational database is, how to create a table, what is and how to design simple queries. And, of course, how to connect Excel to an Access table.

Example: How to Make Excel and Access Work Together

Let me give you an example of how you could use Access and Excel for, say, a sales dashboard.

  1. Create a new Access file;
  2. Sales data is stored in an Oracle database. Ask your IT to create a view (a query) with the data you need;
  3. Create a link between your Access file and the query (via ODBC – your IT should be glad to do it for you, right?);
  4. Create a “Parameters” Excel file. Make is available somewhere so that product managers can open and edit it.
  5. Connect your Access table to each of the sheets in the “Parameters” Excel file;
  6. Now create an Output table where you will store all the data you need for your dashboard;
  7. Design and run all the queries to calculate market share, growth and other KPI. Use those queries to populate the Output table;
  8. Create a macro to run the queries in the right sequence;
  9. Create a new Excel file;
  10. Connect the Excel file to the Output table;
  11. Start designing your Excel dashboard!

I use this approach in many of my projects. While queries are not error-free, those errors are often much easier to spot than using Excel formulas.  The dashboard is easier to maintain and, since I don’t have to manage hundreds of formulas, I feel safer. Performance also improves, because Excel is not recalculating everything every time the user selects a different option.

Want to Know More? Buy the Book!

I was writing this post when I came across Michael Alexander’s new book, The Excel Analyst’s Guide to Access. I fell in love with the book the moment I read Michaels’s post. I’ve been doing this for years now, but I’m sure I will learn a lot, and you will too. I’ll write a review here as soon as the book arrives.

You must always check your Excel reports or dashboards for errors, and formulas are the first place you should look for them. Better yet: avoid them. Use VBA, use pivot tables and make Excel and Access work together. Do it, and I’m sure you’ll find more time to play with the kids.

Photo credits: Humberto Terenziani