Databases by nature are intended to hold a lot of information, often they contain entries for different instances of very similar information. This means when even a small thing changes it has the potential to be a replicated change in a number of areas. Of course there are also regular reports that are output in excel that require some additional work. If you ever find yourself doing a repetitive task (for example: changing a specific variable in a number of places to update a spreadsheet or pulling weekly reports for review) it is likely a macro can help simplify the process.
Macros can vary from the super simple to the ultra complex. Complicated macros require significant programming skills but pretty much any one can create a simple one by recording key strokes.
Here is how to create a macro that automates a simply series of keystrokes.
Let’s say I want to update a number of prices so that product margin meet a certain goal. This is something that happens a lot in different organizations.
Here I have a list of 5 items and I want to set new prices so that each item has a 45% product margin. I could manually enter a new price and use the guess and check method but instead I use a “Goal-Seek” method. I know I will have to do a goal seek on each of the items, which requires the same steps done 5 different times,
- Select the Margin Cell
- Select “What-If-Analysis” from the Data tab
- Select “Goal Seek” from the menu
- Input the goal Value of 0.45
- Select the “Price” cell as the variable to alter
- Select “OK” to start the goal seek Select “OK” to accept the result of the goal seek
- Move the cursor to the next cell and repeat
In total that is 7 steps that, even if using keyboard shortcuts, takes a few seconds to input. Instead I record a macro so that I can automate the process after doing it once. This means that the second process will only require a single shortcut key. Longer chains of repetitive tasks make Macros even more powerful.
On the view tab select the Macro drop down menu. I am going to select to turn on relative cells, this will ensure that when repeating the goal seek the system will know to look at the select variable cells based on which cell I am trying to solve for.
Next I select “Record Macro” and go about naming the macro and assigning it a shortcut key.
Once hitting ok the software is now recording each keystroke that you make. So start doing the 8 steps above noted on how to set a new margin using goal seek.
Once you have set one margin go back to the View tab and choose Macro. It will now have an option to “Stop Recording” click this and the macro will be saved.
Now to set every other margin all you have to do is go to that cell and hit the shortcut keys selected for the macro.
To see what the macro I actually doing go back to the View>Macro>View Macros menu selection. Highlight the macro and click “Edit”. This will open a Visual Basic programming screen.
For this macro the code is fairly simple and can almost be read as a book. Everything after the ‘ and in green is a note. This states the name of the macro, the shortcut keys and the description. The black part states that we take the active cell and run a goal seek, where the goal is .46. The changing cell is the cell that is offset from the active cell (0,-2) or in laymans terms, in the same row and two rows to the left. Once the goal seek is done we move so that the active cell is one lower. You can change the goal by altering it to be .55 or some value here in the editor. Thought this may be a simple explanation it shows how powerful macros can be even for just capturing a series of keystrokes that you routinely have.
Next time you find yourself hitting the same ten buttons, or doing the same thing to a hundreds of cells, try recording a macro instead.