The best things in life are either free, difficult to attain or well hidden. In their respective order, I offer laughter, friendship and the Developer tab in Excel 2010. The Developer tab arguably contains the most powerful tools in Excel, which is arguably the most powerful application in the Microsoft Office Suite. And yet, to the unsuspecting (or untrained) consumer, it is nowhere to be found. The Developer tab is the place where you can put your programming skills to use and make the best things in Excel automated and easy to find, and the worst things, like repetitive tasks, disappear forever. To get the most out of your Developer tab, do yourself a favor and acquire essential VBA and Macro knowledge.
Before we get too excited, we need to answer question number one: ahem . . . where’s the Developer tab again?
Go Ahead, Be an Enabler
To enable the Developer tab in Excel 2007 or 2010, follow these short steps. If you’re working on a PC, go to File then choose Options. Mac users should click on Excel in the title bar and select Preferences.
The respective dialogue boxes will launch. In Windows, find the option to Customize Ribbon:
For Mac users, locate the Ribbon icon in the bottom-right corner:
All that remains is to find the unchecked Developer box, and check it. Windows:
Click OK and you will now see the Developer tab displayed on the Ribbon:
So that may not have been as difficult as I insinuated, but it’s not exactly intuitive either, which pretty much sums up my experience with many of the incredible tools Excel has to offer: only a few, nonsensical clicks away. In Excel, it’s just a matter of knowing what to do, which segues us into the next section of this tutorial…
The tools within the Developer tab are displayed differently on Mac and Windows versions of Excel, but both share capabilities. In one form or another, you can expect to find the following:
- Code (Visual Basic on Mac)
- Controls (Form Controls on Mac)
- XML (unfortunately, you will need to download a file converter to convert XML files on a Mac; you can find one here)
- Modify (go to File and choose Properties on Mac)
The most important elements here are the VBA Editor and Macros. VBA is a programming language that allows competent users to create commands not previously included in the user interface, or simply to relieve themselves of repetitive tasks (the most prevalent usage). Macros are the VBA’s eyes and ears; these are the actual saved, written pieces of code. Use the Record Macro button to create a new macro. If you have multiple workbooks, and multiple worksheets within each workbook, a simple, handy macro would allow you to automate formatting changes, instead of going through them one-by-one. A more complex example would involve A-Z data analysis, using statistical characteristics to create and appropriately format applicable tables and/or charts.
The uses of VBA are nearly infinite. You can program object models (in your web browser, these would be your windows, tabs, etc.), methods (such as window control buttons, i.e. the red, yellow and green dots on Mac windows) and collections (the plural of objects).
Just like it sounds. Add-Ins allows you to “add in” new features into Excel. This means, of course, that they do not come standard, with the exception of the Solver and Analysis ToolPak add-ins. But downloading and installing add-ins is not difficult. Many are available from Microsoft, and others can be obtained from third-parties:
Downloadable Add-Ins: These can be downloaded and installed from Miscrosoft, and may appear as a new tab in your Ribbon; removing the tab requires uninstalling the add-in.
Custom Add-Ins: These are your third-party add-ins provided by developers. Formats include VBA, XLL (XML Linking Language) and COM (Component Object Model).
Controls are kind of like the poor man’s VBA, but you can run macros through Controls and even use them to write/record a new macro. Examples of Form Controls include:
- Labels: Upon selection of a cell, it literally launches a label (instructions, captions, etc.))
- Button: Displays a button that, when clicked, performs an action or runs a macro
- Group Box: Contains (usually) related options and an ability to choose between them. Think ethnicities, varieties, age groups, etc.
- Check Box: A list with entries and corresponding check boxes:
A check box can exist in three states: selected (on), cleared (off), and mixed (multiple selections)
- Scroll Bar: Self-explanatory. Allows the user to scroll through a range of values.
- Option Button: When you have mutually exclusive options, an Option Button is what you’ll need and is usually in the form of a Group Box. An Option Button shares the state characteristics of Check Boxes: selected, cleared and mixed.
- Spin Button: Featuring an up-arrow and down-arrow for increasing or decreasing a value:
This, of course, contains all of your XML options for coding in Excel: refreshing data, importing and exporting XML files, writing XML, etc. The main use for XML is to transmit, interpret and validate data between applications and databases, and subsequently there are a vast number that support XML. But as with VBA and Macros, steepen your learning curve with a professional boost and learn XML the right way.
This includes only the Document Panel, and is simply a place to store, view or change properties or information concerning the workbook: Company, Name, Author, Keywords, etc.
You ought to feel pretty great about yourself. You made out good with an entire life’s happiness (free, friendly advice on how to find and understand the Developer tab) for the price of reading a blog post. The trick now will be learning how to use all of its fabulous tools, for which I might recommend an advanced Excel training course. Better yourself and your skills. Don’t let all that power go to waste.