Suppose we receive a daily report of data that we are required to turn into a specific type of chart with various features and customizations.
The catch with the data is that some days there are more rows of data than other days.
We want to make the process of chart creation completely dynamic, so we don’t have to create the chart from scratch every time we receive a new day’s file.
Starting the Macro Recorder
To record a macro, click either the “Record Macro” button located in the lower-left corner of the Excel window…
… or select View (tab) -> Macros -> Record Macro.
Adding the Developer Ribbon
If you want to record macros the way the “cool kids” do it, you can activate a special ribbon known as the Developer ribbon. This ribbon contains the various tools for creating and editing macros.
To activate the Developer ribbon:
- Right-click on any visible tab and select “Customize the Ribbon…”.
- In the Excel Options dialog box, place a check next to the “Developer” entry on the right panel of options, then click OK.
You now have access to all the cool toys.
Recording a Macro
PRO TIP: Just like the “record a song” analogy earlier, you want to think through all the steps you are going to perform before creating the recording. Any unwanted steps performed during the recording process will become part of the playback procedure. If you happen to make a mistake during the recording process, you can always delete the macro and try again. (Do you think Lady Gaga sang “Bad Romance” perfectly the first time?)
Using one of the methods described above, start the macro recorder. The following dialog box appears.
- Each macro requires a name. The name cannot contain any spaces and must be unique to this file. We will use “Create_A_Report” as our macro name.
- You can assign a shortcut key to the macro for easier execution, such as CTRL-r.
- You can store the macro in the file or the Personal Macro Workbook.
- You can add a description to the macro for documentation purposes.
Click OK to start the recorder.
Every click and everything typed, edited, or deleted will be recorded.
Notice the icon in the lower-left of the Excel window has changed from a “record” button to a “stop” button.
Let’s perform the following steps to creator our chart that will be reproduced by the “Create_A_Report” macro:
- Click in cell A3 to establish the upper-left corner of the data. NOTE: Do NOT highlight the data range. If you do, you will force the macro to always select the defined range. If later you have data that goes beyond that range, it will be ignored. If later you have less data, you will end up selecting too many cells and have empty areas in your report.
- To properly select the data range so it will be dynamic during future executions, press CTRL-Shift-Down arrow then press CTRL-Shift-Right Arrow.
- To create the chart, select Insert (tab) -> Charts (group) -> Combo Chart.
A new combo chart has been inserted into the file.
WARNING: Do NOT click away from the chart and reselect it. If you do, the chart’s name (ex: “Chart2”) will be hard-coded into the macro which will cause a failure later since the next chart will be a different name like “Chart3”. If you leave the chart selected, the macro code will refer to it as the “active chart” without any specific name.
- Perform any cosmetic changes to the chart you like, such as changing bar colors, add data labels, customize the legend, customize the title, etc.
- Click the “Stop” button (bottom-left) to stop the recorder.