Save Custom Functions from Excel VBA in Add-in Files
How to Edit Macros in Excel's Visual Basic Editor
How to Record New Macros in Excel 2013

3 Reasons to Use Macros for Your Excel Dashboards

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.

blog comments powered by Disqus
How to Assign Macros to the Ribbon and the Quick Access Toolbar in Excel 2013
Use the Option Button Control for Your Excel Dashboards
Excel Dashboards: How to Record Your First Macro
Use the Check Box Control for Your Excel Dashboards
Excel Dashboards and Reports: How to Run Your Macros