Microsoft Excel has been used by millions of people, for millions of years. (well not that long but you get the idea!). A lot of people just get by with knowing exactly everything that they need to know in order to do their job – but nothing more.
They can format cells and add up columns. Of course they can filter. They may even be able to do simple functions like adding a range of cells.
Most people probably don’t realise that if they are doing an action in Excel that is repetitive, they can do it with a Macro. Most people have heard of Macros in Excel but are too afraid to touch it. After all it’s code right? Wrong! Well it is code but you don’t have to know the code in order to create and use Macros. All you do is press record, perform your routine and press stop. Then, every time you want that action performed you just select the Macro and voila! You’re action is done. Simples.
Now that I’ve convinced you to give it a try, take a look at this quick tutorial.
What is an Excel Macro?
A Macro in Excel is a sequence of commands that are recorded using the built-in Macro Recorder facility. You could record a macro to format certain parts of a worksheet in a specific way. You would switch on the Macro Recorder, perform the series of formatting actions, and then turn off the Macro Recorder. The Macro can then be run when-ever you need it to run.
Just select the Macro to run it. (You can even call the macro from within another macro, such as, to perform different formatting tasks in one go)
Excel Macros are recorded or written in VBA (Visual Basic for Applications), a programming language developed by Microsoft. (You can use VBA in Word, PowerPoint, Outlook, and Access, as well some 3rd party programs. (If you use a program with a Macro facility it will be VBA.)
Create a new Excel Macro
Firstly, you need to make sure you have the Developer Tab in the top ribbon (2010 and 13 edition):
- Click the File tab
- Click the Options at the left to enter into Excel Option window
- Click the Customize Ribbon at the left
- At the right, select the Main Tabs from Customise The Ribbon drop down box
- Tick the Developer item
- Click the OK button to finish customising.
Now you can go to the Developer Tab and click Record Macro.
In the dialog box type a Macro name (and assign a shortcut key like a ninja).
Macro names must start with a letter, after which they can be a choice of a mixture of letters, numbers, and underscores. They can’t contain spaces, symbols, or punctuation marks. Type a short description – make sure it accurately describe the Macro action:
Eg – Name: “Red text”. Description “Make all selected text red”.
You can store the Macro within the current workbook and also in a new workbook. For Macros that you will use all the time, in lots of different workbooks, you can even store it in a special place on your computer and have it readily accessible every time you open Excel. Enter Personal Macro Workbook.
The Personal Macro Workbook is Excel’s central repository for macros you create. Excel creates the following file when you choose to store a Macro as a Personal Macro Workbook:
If you want a disposable Macro – select the new workbook option, use the Macro, then close the new workbook when you’ve finished with it.
When you press OK you will then be able to start recording the actions. When finished just press Stop Recording.
So lets create a handy Macro that will delete all blank rows.
- Press Control + Home (Moves cursor to Cell A1)
- Select Home > Insert > Sheet Columns. (Don’t worry we’ll remove it)
- Press End, Down Arrow, Right Arrow, End, Up Arrow.
- Press CTRL + Shift – Home, Shift – Right – Arrow, CTRL + Shift – Right – Arrow.
The whole data range is now properly selected.
- Press Control + G, then ALT + S).
Click Blanks>OK. All the blanks are now highlighted grey.
- Select Delete>Delete Cells>Shift Cells Up>OK and the blanks vanish.
- Press Control + home, then
- Select Delete > Delete Sheet Columns to remove the extra column we inserted.
- Press Stop Recording.
Here’s one to conditional format all cells that have N/A in them:
Macro – GreyItalicNA
- Select the top left corner to select all cells.
- Go to Home > Conditional Formatting > Highlight Cells > Text That Contains
- Type N/A in the left field
- In the right field > drop down > custom format
Font Style – Italic, colour – Grey > OK
You can do the same for highlighting cells with ‘Y’ to green font colour with green fill. Also a red ‘N’ will be helpful.
So now every time we want to do these repetitive actions we can go to Developer > Macros and select the Macro and it will perform the action.
Create a button and assign a Macro to it
This creates a visible button on the worksheet which is assigned to a Macro.
- Go to Developer tab, click Insert and Button
- Drag in the area you want the button to be
- The Assign Macro dialog box appears – select your Macro and press ok
- Right click, select Format Control
- Now you can colour the text and format it to your liking (change size etc)
You can be snazzy and create a shape (like a yellow star with a black outline, and assign a macro to the shape!