3 Reasons to Use Macros for Your Excel Dashboards

By Michael Alexander

Macros are very useful for your Excel dashboards and reports. A macro is essentially a set of instructions or code that you create to tell Excel to execute any number of actions. In Excel, macros can be written or recorded. The key word here is recorded.

The first step in using macros is admitting you have a problem. Actually, you may have several problems:

  • Problem 1: Repetitive tasks. As each new month rolls around, you have to make the donuts — that is, crank out those reports. You have to import that data. You have to update those pivot tables. You have to delete those columns, and so on. Wouldn’t it be nice if you could fire up a macro and have those more redundant parts of your dashboard processes done automatically?

  • Problem 2: You’re making mistakes. When you go hand-to-hand combat with Excel, you’re bound to make mistakes. When you’re repeatedly applying formulas, sorting, and moving things around manually, there’s always that risk of catastrophe. Add to that the looming deadlines and constant change requests, and your error rate goes up.

    Why not calmly record a macro, ensure that everything is running correctly, and then forget it? The macro is sure to perform every action the same way every time you run it, reducing the chance of errors.

  • Problem 3: Awkward navigation. Remember that you’re creating these dashboards and reports for an audience that probably has a limited knowledge of Excel. If your reports are a bit too difficult to use and navigate, you’ll find that you’ll slowly lose support for your cause. It’s always helpful to make your dashboard more user-friendly.

Here are some ideas for macros that make things easier for everyone:

  • A macro that formats and prints a worksheet or range of worksheets at the touch of a button

  • Macros that navigate a multisheet worksheet with a navigation page or with a go-to button for each sheet in your workbook

  • A macro that saves the open document in a specified location and then closes the application at the touch of a button

Obviously, you can perform each of these examples in Excel without the aid of a macro. However, your audience will appreciate these little touches that help make perusal of your dashboard a bit more pleasant.