It’s quite likely that you use Microsoft Office Excel, or just plain Excel, and that just about every one else at your company does too, to one degree or another.
Among all of the file types that Monarch can read and write (Access databases, PDF files, text files and more), the Excel file formats get the most attention, especially since Monarch v8. The reason for that is simply Excel’s popularity, flexibility and continuing growth.
Right or wrong, Excel continues to be the go-to tool for many people.
So far in the 30 Days to Become a Better Monarch Modeler series we’ve (justifiably) focused on Monarch, but today on day 23 we’re going to shift focus a little and the reason for that is a singularly important feature of Monarch v10.
Monarch has been able to add, or append, new worksheets to existing Excel files for quite some time. But it’s the ability of Monarch v10 to add new worksheets to Excel 2007 workbooks that already contain macro code (.xlsm files) that changes the game nicely.
This might put some additional pressure on you however. While I might be guessing incorrectly, I suspect that your nitty-gritty Excel skills could be a little stronger. Our use of Monarch might be somewhat to blame for this as it effectively shelters us from a large amount of complexity, and over time we become accustomed to this.
What do you do with your Excel exports?
Monarch allows us to feed Excel with an immense amount of data and that offers tremendous benefits. But as I’ve written about many times, together Excel and Monarch are a great team. But the best teams work together; each team member compliments the weaknesses of the others.
Monarch is very good at creating simple data structures – rows and columns of related data.
One of Excel’s biggest strengths is its flexibility. You can put anything anywhere. Another is its ability to include interactive elements like checkboxes and drop down lists.
One of the most effective uses of this combination is the dashboard report. This style of Excel report can summarize vast amounts of data on wide ranging topics on a single page. When you design the mechanisms that can tap the data that your Monarch exports populate into even a single Excel file, you can create incredibly efficient reporting systems.
You may say that I’m a dreamer
Imagine that you’ve built a number of Monarch models and projects to extract a number of different reports, and that you’ve designed each of them to export to the same Excel file.
When you execute those exports Monarch does something wonderful that might be unknown to you. It automatically defines a named range for your data table in the Excel file that you can take advantage of in your formulas and you won’t have to worry about whether you’re going to miss some data by creating formulas with a fixed row or column reference. Use the named range in your formula instead.
You know by now that you can export from the Table window or the Summary window to Excel. These exports can be just a few rows and a few columns or can number in the thousands of rows.
But did you realize that you can go to the other extreme and export just a single field value to a worksheet? Why would you want to do such a thing?
This can be a great way of sending control data from Monarch to Excel. Maybe your data reflects activity over several financial periods. Send Excel a single value for the reporting period, and then use this in a formula to automatically generate the report heading. Maybe you want to send the Excel file filtered data, but also send it field averages for the unfiltered values for comparison. Having Monarch put each value on its own sheet makes your Excel work really easy.
Gluing Excel to Monarch
It’s pretty rare that having a collection of exported worksheets is the end of the work. More often than not, and rightfully so, someone wants to do something with all that data.
Are you comfortable with Excel data connection and retrieval functions? You need not master them all, but you’ll probably thank yourself for knowing how (and perhaps more importantly when) to use INDIRECT, VLOOKUP, MATCH, INDEX and probably OFFSET.
Do you know how to make use of some of Excel’s interactivity features like checkboxes, radio buttons and validation lists?
If you do, you’ll be able to create a single sheet that controls what is displayed on an Excel report and what specific data it will reflect. You’ll be able to create a single file that has all of the important data about your customers (or vendors or salespeople or widget production or …) and by connecting up some interactive tools with some functions, you’ll be able to have the report user elect to display whatever she wants:
- quarterly, monthly, and/or weekly data
- all customers or a whole report that focuses on a single customer
- small detail tables with the top 10 or 20 items (or whatever limit the user wants)
- charts, and
- warning indicators
But today’s post isn’t about dashboard techniques (hint: that’s coming soon), it’s really about these two ideas:
- How you can push Excel a little harder to take better advantage of the good work that you’ve done with your Monarch models, and more importantly
- How being aware of what you can do with Excel can (and should) affect how you build your models.
Your Task for Today
It’s a pretty encompassing topic, but since we’re just begun a new year, maybe this is a good time to resolve to further improve our Excel model building skills as they relate to Monarch work and learning how to incorporate interactivity.
Today, start by saving a new completely blank Excel file and then exporting to it from Monarch. Set your project exports to append to the file. This will make it easy to update the file with new data later on. Now with the some data in the Excel file, open it up and add some functionality that makes use of your data.
Experiment with checkboxes, radio buttons and lists. How could a well-placed spin button or scroll bar benefit an analysis of your data? What steps would you take to have a chart display different data based on a user’s selection from a drop down list?
Redefine Modern Art
Once again today it may seem that we’re a little off track. How does being able to add interactive features to a spreadsheet make one a better Monarch modeler?
I’m confident that the more you play with these “user interface” abilities in Excel, the more you’ll see how important it can be to structure your exports in certain ways, depending upon what you need to do with the export. In no time you’ll become more familiar with how to artfully combine a blank canvas with some data building blocks and beautifully excel with Monarch.
—
Continue your commitment to Become a Better Monarch Modeler with Part 24 of the series, or review Part 22.
