When you work a lot with Word and/or Excel, there are undoubtedly small actions that you perform every time. You can easily create macros for this. We show you how to do that.
Recording a macro can be seen as recording a movie: you literally record what you are doing and can play it back later. It is useful to use macros if you perform a task a lot. Creating macros may sound complicated, but it's incredibly simple. Also read: 3 functions in Word you didn't know yet.
They can be used in all Office programs, although they are most useful in Word and Excel. We therefore show you how to create macros in these two Office programs.
To create a macro, you first need to know where to find them in Word and Excel. You will find the option to create macros under the tab Image. Click on the black arrow below macros, on the right side of the taskbar, and there you will see the option Record macro.
Record Macro in Word
To create a macro in Word, open the document in which you want to make a specific change. For example, it may be that your texts are not always in the same font after you have copied a piece of text from a website. To get the text in the same format you can always select all text and then click Clear formatting and then select the desired format. However, you can also do all these actions at the touch of a button, by means of a macro.
If you clicked on record macro, a window will open. Here you can give the macro a name and a description. The name of the macro cannot contain spaces. If you want to use several words, you can connect them with, for example, a dash. For example, you name the macro format_clear and then click on the hammer next to 'Knob'.
A window will appear allowing you to add the macro to the toolbar Quick access. Select the macro and click Add to start recording. Now perform the action you want to automate. In this case you select the whole text with CTRL + A and then click the ´Clear All Formatting´ button (the A with the eraser) to delete the entire formatting. Then set the desired font and size of the text. When you have done this, click on Stop recording and the macro is created.
A new button has now been added at the top of the screen next to the 'undo' and 'redo' buttons. On the icon you see a green diamond surrounded by three squares, which are connected by lines. Pressing this icon will run the macro you just recorded. For every macro you create afterwards, a new button is created that remains the same.
02 Record macro in Excel
Macros in Excel work much the same. In this example, we'll create a macro that sorts all the data in the worksheet. For example, make a list of people with a first and last name. You can sort these in alphabetical order and to avoid having to perform the same action over and over, we create a macro for that. click on Image / Macros / Record Macro and give the macro a name, for example SortNames.
In Excel, you cannot assign a button, only key combinations. Hold down the Shift key and choose a number or letter (the key combination becomes Ctrl+Shift+number/letter). With this double combination there is less chance that the combination is already forgiven). click on OK before recording.
Now select the cells with the names and then in the ribbon on the tab Facts and then on To sort. In the window that now opens, enter the values on which to sort, for example first by last name and then by first name. Then press OK and then on Image / Macros / Stop recording. You have now created a macro in Excel. By using the Shortcut key you perform the action every time.
03 Developer tab
In the latest versions of Office, you can also use the tab Developers add to the toolbar. Here you get additional options for creating macros. To add the tab go to File >Options and you choose left Customize ribbon. On the right side you will see all the tabs, and only the box next to Developers should be unchecked. If you do check it, the tab will be added to the ribbon. You have to add the tab in each Office program separately.
Especially in Excel it is recommended to add this tab. You cannot add a button in the program if you add a macro via the tab Image. In the tab Developers do you have that option. On the left you see the option to record a macro and that then works just the same as we explained above. In the middle you can click on Insert. Choose a button here and then you can draw the button in the worksheet. Once you have done that, a window opens with an overview of all macros and you can choose the macro you want to use and assign it to the button. Select the macro and press OK. The button is then created and you can still change the text of the button to Sort names for example. Now if you press the button the names will be sorted.
