Excel Tips

Almost all the reports I generate for business users are in Excel. Sadly, I've become expert in a tool that I don't even like.

If you are generating reports in Excel, and finding it a frustrating struggle, an excellent resource is Dennis Taylor's "Excel Weekly Tips" series at Lynda.com. Some of the material on this page comes from there. Other material is items I've worked out during my own struggles.

Macros

Because large companies typically lock down their systems to prevent easy usage of macros, I've moved away from VBA, and concentrated on formulas. You can do many useful actions with VBA, but you can't necessarily easily ship them. I recently created a simple macro workbook (.xlsm) to generate Adobe report suite IDs according to the formula used by the company I was working at. It replaced a manual process of creating and recording a new RSID with a form and a button. The form consisted of data validation drop-down menus and one free-form text entry. The form data was processed by a macro when a button was clicked. I uploaded it to SharePoint and sent a link to the guy who was assigned that task. He downloaded it, but couldn't figure out how to make it work. That, I believe, is the outcome of the high level of macro security that has been the default of Excel for years now. He was a business user who used Excel on a daily basis. But not with macros. Macros used to be bread-and-butter for Excel users. That's no longer the case.

You can use macros without embedding them in the current workbook. This is useful if your macros are to be used during the report preparation, but are not to be used once it is complete. For example, I've written macros to manage worksheets when updating a monthly workbook. Last month's sheets are deleted, new ones created and named. These types of macros can be saved to your Personal workbook (personal.xlsb). They're available to you, and you only.

Exporting Macros

You can export macros, and them import them into another workbook. This is a useful option. I recommend it, especially if you are a consultant and may one day find yourself "moving on" to a new assignment. If you've put much work into your macros, you don't want to have to rewrite them at your next position. Or, ever.

To export your macros:

• Go to the View tab and click Unhide
• Unhide your personal workbook
• ALT-F11 to open the Visual Basic Editor
• Click the module that has your macros in Personal workbook
• From the File menu, choose Export

This creates a file with extension .bas. It's a plain text file. You can open it in any editor and view it. In appearance, it's pretty much the same as it appeared in the Visual Basic Editor.

You can import the file's contents into another installation of Excel by following the same procedure, and choosing the Import option on the File menu.

Templates

If you use the same sets of formats, with minor variations, for your reports -- or, if you can see how you could do so -- then creating a template file is a good idea.

Creating a Template

For example, I create almost all of my reports on Adobe Analytics data with Adobe Report Builder, the Excel Add-In. Therefore, I created a simple template for this purpose. The template contains 2 sheets, a Coversheet and a Data sheet. The Data sheet looks like this.