The Power of Excel
June 22, 2011 Leave a comment
Pretty much any project or program manager worth his salt is likely going to have a good familiarity with Microsoft Excel. The staple software from Microsoft’s flagship MS Office suite has been around in one guise or another since the 1980s. The software itself has come a long way since its early days as strictly a spreadsheet tool. Nowadays, Excel has numerous capabilities including charting, function call capabilities, and advanced features that allow for automation and repetitive tasks in the form of macros.
Despite good working knowledge of Excel, most actually do not take advantage over some of the more advanced capabilities that exist in the tool, specifically when it comes to the developer level options that are part of the full MS Studio suite. These features can add some really advanced capabilities to what a project manager can do when it comes to the reports they draft with Excel.
Now depending on your skill level with Excel and whatever rudimentary programming knowledge you might possess, these features can seem daunting to use. But Excel provides some key shortcuts and intuitive ways to become familiar with some of these utilities and often will allow for automatic code generation.
For those looking to explore further into some of the really neat and effective mechanisms that Excel has available, continue to read below so as to gain insight into how to set up Excel and take advantage of these options.
Enable the Developer Tab
If you are using Excel prior to the 2007 release, this option will actually already be enabled. But for 2007, it has to be actively added. If you click on the Office Button, select the ‘Excel Options‘ in the drop-down window. This will bring up the options menu. Within the ‘Popular‘ selection, you will see a check box on the right of the window that says ‘Show Developer Tab in the Ribbon‘. Click this option and the Developer tab will become enabled in your Excel version from that point forward.
If you click the Developer tab once it is enabled, you will see links to the various mechanisms that allow you to perform some of the more advanced options in Excel. The Visual Basic Editor, the Macro Recorder and various other options are available.
Become Familiar with the Macro Recorder
The Macro Recorder is listed in the menu once the Developer tab is enabled and selected. This is an invaluable tool when first becoming familiar with some of the more advanced features of Excel. Basically, what the Macro Recorder does is monitor your clicks and usage of Excel during a recorder enabled session and then autogenerate the code that will be required to automate that particular task.
So for example, if you are performing a particular calculation for a cell in Excel and you realize you are going to reuse that calculation on various other cells in the form, you can use the Macro Recorder to record you performing the routine once. After that, you can save that Macro and then call it anytime you need to perform a similar operation on a cell. Furthermore, that Macro can be made part of a script that will perform that routine iteratively for any number of cells, thereby reducing the manual work.
Get Some Advanced Training
Various outfits actually provided advanced training for those individuals who wish to become more familiar with the high level features of Excel. Additionally, there are several excellent books and a video series that are available on the subject that can be purchased. Two of the key ones I used for my own training are:
- Excel Programming – Your Visual Blueprint for Creating Interactive Spreadsheets (Available from Amazon)
- Excel VBA and Macros with MrExcel (Available from Amazon)
The Excel Programming book gives a good synopsis of some of the more involved programming attributes that Excel can perform. Note that if you are a complete novice, this book may need to be complemented with some ancillary material on VB Programming, although you can actually get quite familiar with VB through the book itself.
The Excel VBA and Macros is actually a video series which gives an outstanding overview of all advanced capabilities of Excel, including advanced charting, data source access and programming. I highly recommend this video series to anyone wishing to really get into the nitty-gritty of Excel.
Get to Know Mr. Excel
The aforementioned video series in the above link was actually created by Bill Jelen who often goes by the moniker ‘Mr. Excel‘. Bill actually keeps a running website up at the following URL:
This website is probably the best resource you will find on the web when it comes to some of the more advanced tasks for Excel. Not only does it have a community of contributors, but it has a great question and answer section whereby you can post a question regarding some advanced mechanism you are trying to institute in Excel, and one of the contributors (possibly even Bill himself) will likely give you a good solution. You can also perform searches on specific areas of interest to see how you can perform certain operations and become familiar with some of the subtleties of Excel.
One final thing to note: the Visual Basic capabilities that are inherent to Excel are actually available across all the MS Office Suite of applications. So as you become familiar with its function, you can potentially utilize your knowledge for other apps, like Word, PowerPoint and Project. As such, becoming familiar with these advanced features will benefit you beyond just advancing your Excel skills.