If you are someone who often spend time cleaning and processing raw and dirty data in spreadsheets you know how repetitive some tasks can be like filtering the data, sorting it, trimming unwanted spaces or writing same function several time. I mean you surely can just work your way out with Excel without ever using Macros but they are really a great tool to save your time when you're dealing with huge datasets. Consider Macros as a tool or a medium which will help you reach your goal more efficiently.
What is a Macro ?
A macro is like recording a set of instruction to automate your task. To understand simply it's more like recording your mouse clicks and keystrokes which can then be run as many times as you want with just a click. After you create a macro, you can edit it to make minor changes to the way it works.
Don't worry you don't have to write code for it, but you surely can write VBA scripts for it if you want more precise control and logic in your macros, Check out this article by Microsoft about VBA scripts and it's usage in Office Apps.
But fear not, I'm not going to scare you with coding Marcos because we are going to use the inbuilt function to record Macros. So, let's begin :
The Macro function is located in the "Developer" tab but to access that you first have to enable it in Settings. To do that Open a blank spreadsheet and go to FILE > OPTIONS.
The 'Excel options' windows will now pop-up on your screen, you have to click on Customize Ribbon and then on the right hand size there will be list with a header of Main Tabs, In that you have check the Developer option like shown below :
If you have followed me correctly you can now see the Developer tab on your Ribbon i.e where other tabs like Home, Insert, etc. are located.
It's time to create some Macros. For the purpose of this tutorial I'm using a table that I've copied from Wikipedia. If you want to follow with me, you can copy the table from here.
Here's the copied list I pasted in my sheet :
We are going to create a macro to calculate the sum of Revenue and to do that click the Developer tab, on the top left side You'll see an option to "Record Macro", click that and it will start recording your steps :
In Cell E12 I've wrote a SUM function :
Click stop recording.
To play our Macro we have to go to the "View" Tab and In the Extreme right side, you'll see a "Macros", When you press it, it'll show a drop-down list and from there you have to select "View Macros":
It'll show all your macros that you have created for that Workbook, you can press "RUN" to see your Macro in action.
I'm now going to create button, so that when we press that button it'll show the sum automatically without me going to the view tab and running my macro from there. To do that I'm using a rectangle shape which you'll find in the "Insert" tab :
Fill that shape with the color of your liking and to add a text on it Right click > Edit text :
To fill color :
Now we'll have to assign this button the Macro we created earlier and for that Right click > Assign Macro.
From here choose the macro you want to assign and that's it.
Now when you press the button, it will show you the SUM of the Revenue in Cell E12 :
You have successfully created a Macro and assigned it to a button element on your Excel sheet, I mean how cool is that and How easy it was to do that. I hope that you have found this post useful. Never stop learning.