Up next in 10
How To Create and Use Macros in Excel _ Tutorial
783 views
Feb 19, 2024
The video "How To Create and Use Macros in Excel: Tutorial" offers a comprehensive guide on creating and utilizing macros in Microsoft Excel. It likely covers various aspects, such as recording macros to automate repetitive tasks, editing and debugging recorded macros, assigning macros to buttons or keyboard shortcuts, and managing macro security settings. The tutorial may include step-by-step instructions and practical examples to demonstrate how to create and apply macros effectively in Excel. By providing clear guidance, the video aims to help users streamline their workflow, save time, and improve productivity by automating tasks in Excel. Overall, it serves as a valuable resource for individuals seeking to enhance their proficiency in Excel macros.
View Video Transcript
0:00
In this video, I'm going to show you how to create and use macros in Excel
0:06
So first let's understand what macros are, how they work, and why they're extremely useful
0:12
for certain tasks. So now let's move to the laptop where I'll show you
0:17
So here in Excel, I've created a sample data set where over here you have the names of salesmen
0:25
and these three columns have their sales for three months. So let's say you're given this data regularly
0:33
and every three months you need to find the top three salesmen from this data
0:38
So this is a task which is really repetitive and you need to follow the same steps every month
0:45
Then for these repetitive tasks, macros become really useful. So essentially, macros are a set of actions which can be recorded, saved and then executed many times
0:57
So if you get the same type of data and you need to use it to get the same outcome
1:04
Over here, it's to find the top three salesmen, then macros can be really useful to automate this entire process
1:11
So how it works is that when you record a macro, you're recording all of the mouse clicks
1:17
So the next time you get that type of data and run the macro, it'll execute all of the clicks on the new data
1:24
and by doing this, you saved yourself the time of following the same steps again
1:30
So now on this data, I'll show you how to record a macro and execute it
1:35
So let's get started. So now I am in Excel and I'll show you how to record a macro
1:42
So like I mentioned earlier I use this data to find the top three salesmen from this list So to record a macro the first thing you have to do is click on view that written over here So before we start recording the macro
1:59
one thing that you should do is place the cursor where you want it to start
2:03
So I'll place it in B2 because I'll start from here. And now to record the macro, just click on macros, which is on the right
2:15
and then click on Record Macro. record macro. So here you can enter the name
2:22
After that, you can create a shortcut. So every time you enter that shortcut, it'll run the macro
2:29
So I'll call it Control Shift R. And then you can decide where you want to store the macro or add a description if you want
2:40
And click on OK. So now it started recording the macro and all of the mouse clicks
2:46
will be recorded. So now the first thing I'll do is create a new column over here
2:52
which will add up all of the sales for the three months. So I'll call it total sales
3:00
And then I'll just sum up the values in these three columns
3:07
And then I'll just copy this for all the other roles. So now, as you can see, I've got the total sales for all the salesmen
3:16
in this column. Now the next thing I have to do is find the top three salesmen
3:21
and I can do this using conditional formatting. So I'll go to the home page again
3:28
After that I'll select all these values and click on conditional formatting
3:33
which is over here. So since I want to find the top three values
3:39
which they haven given over here then I have to click on more rules Over here select only top or bottom values and change this to three
3:51
Then I can just change the color of the cell. And now I'll click on okay
3:58
So as you can see, it's highlighted the top three values for me and my job is done
4:04
So now to make this data look better, I'll quickly format it
4:10
And now let's say this is how we want to submit it
4:17
Then we'll have to stop recording the macro now. So I'll go back to view, then click on macros and click on stop recording
4:28
So now it has stopped recording and gotten saved. So let's see if it works
4:34
So now I've created a new sheet where I've copied the old data which we had
4:39
which we had and let's run the macro and see what it does
4:44
So before running the macro, there's one thing which you'll have to check
4:48
and that is that the starting point for this data is same as that
4:52
for the macro which you recorded. So as you can see, my data starts from cell B2
4:59
which is same as before. So to run it, you have to follow the same steps
5:04
click on view, then macros, then click on view, then click on view
5:09
View macros, select the macro which you want to run, and click on run
5:17
As you can see, it's run the macro and it's the same as before
5:21
So this is the beauty of macros when you need to do the same tasks repeatedly
5:26
So now, no matter what data I enter, as long as it's in the same format, this will do the job
5:32
for me So even if I put different names or values over here it automatically do the job for me and give it to me in this format So now there are a couple of more things related to macros which I want you to know
5:47
So on a new sheet, I've put the same data, but there's one difference
5:52
Earlier, it started from the cell B2, but now it's starting from E3
5:58
So the starting position has changed. And now, if I try to run the macro, it won't run
6:05
I'll quickly show to you. So here I run the macro and this is what it's given to me
6:11
It hasn't worked. So to solve this problem, we need to use relative references
6:18
But before recording the macro, had I selected this option, which is use relative references
6:25
then I could have applied the macro onto this data and it would have worked
6:30
Now I'll show you one more thing that you can do, that's insert a button and assign that to a macro
6:38
So first I'll insert the button by going to the insert page that's over here, then click on shapes and insert any shape you want
6:49
Then you can type in anything. So now I've created this button and to assign it to a macro I'll just have to write it to
7:00
right click and click on a sign macro. Then select the macro and click on OK
7:09
So now let's see if it works. I'll just click on this
7:13
So as you can see, the macro has worked and it has given us the top three salesmen
7:19
So this is basically how it works. And if you want to know how to use conditional formatting in Excel, then check out this
7:27
video over here. That's it for today. Goodbye
#Business & Industrial
#Computers & Electronics