Macro tutorial in Excel
In this tutorial I will teach you about using macros in excel. In several Microsoft applications a Macro is a VBA code which is saved inside a document used for automatic manipulation of a document.
The manipulation includes the following:
Styling and formatting.
Data manipulation and management.
Connection with other data sources like database or text files.
I will teach you macro with examples. For instance we have data file with some numbers and we want to format it well along with totals in the bottom of each row.
By using macros, a VBA code is recorded at the back end. Excel provides facilities of creating a macro without doing any coding.
We will create a macro by going to the view tab and Record macro.
Assign the macro name without spaces and then click OK.
Once you will do that, every change you make, every action you take is recorded. This record mode is displayed under the macro section that macro is in record mode the other place is the bottom right corner with the stop icon.
Now that we are recording the macro, we will add calculations and formatting.
Apply the Sum, Average, Min, Max and Median values.
Apply the calculations on the entire data.
Now, highlight all the calculation cells and drag the length of all our data rows to apply the calculations to each row.
Once this is done, each of the row will display its own summary. row should display their respective summaries.
Now we will apply some more calculations.
=AVERAGE(B2:K21) (Averages of row does not equal to all averages of individual records).
=MEDIAN(B2:K21) (Same as above)
When the calculations are done, we will apply formatting by applying number formatting, total formatting and applying colors.
Now we have done making all the changes and formatting, we will stop recording the macro.
In order to use the macro for future use, we will delete the current records and when we will import the data, we will just apply this template and get it formatted in no time.
Before we apply this macro we should know that Macros can be malicious.
VBA code is actually powerful and anyone can use it to control outside the document. For example, it can delete files or delete the copy of windows while running this code. Install a script etc. Make sure you have and run macros from trusted sources.
In order to implement macro on the data, we will open the excel macro enabled template.
When this is done, as per your enabled security settings, you will see a warning across the top of the workbook which says that macros are disabled.
We will import our data with the same number of rows and column and then on the view tab click on view macros.
Open the macro you recorded and then click on Run.
The cursor will jump around for few moments and then the result will be exactly the same you recorded.