Learn Macros in 7 Minutes (Microsoft Excel)

so this video covers macros which allow

you to automate repetitive tasks in

Microsoft Excel to save you time before

we get started let me mention if you

want to follow along a link to the

sample file is in the video description

so to illustrate macros imagine this

scenario you work in a bank and each

week you get a report from your database

that provide you updates on consumer

loans and it shows things like the

amount of the loan the term or length

the interest rate the income of the

consumer and the purpose for the loan

and so on now the problem is when you

get the file from the database each week

you've got to make lots of changes to it

to get it ready for reporting and those

are things we want to automate using a

macro so before we can create a macro

though we've got to add the Developer

tab to the ribbon and Microsoft Excel

the way to do that is to go to the file

menu and then at the bottom click on

options you should see a window that

appears and you want to click customize

ribbon and on the right hand side you'll

be able to check developer that adds a

tab to the ribbon in Microsoft Excel

now the next thing we want to do is

record a macro that's going to get

everything started so let's give our

macro a name we'll call it loans report

and then we'll give it a description so

if we pull this file back up later we

remember what this macro does can now

you should see the stop recording button

that means that Excel is now tracking

every mouse click you perform the first

thing I want to do is change the font I

don't like it the way it is so we're

going to go ahead and change it to

Calibri now what I want to do is format

the header row so that stands out

so I'm going to make the text

and then I'll give it a blue background

with white text and now what I want to

do is take all the columns that are in

dollars and convert those to the

currency format so that's the loan

amount the installment the annual income

the total payment and the last payment

amount now what I'm also going to do is

delete the home ownership column that's

not a column that we use on a regular

basis that will go ahead and delete that

because we don't need that in our

reporting now the next thing I need to

do is to create a new column called debt

to income so this is your monthly

payment divided by your monthly income

it's going to help us determine how

risky a borrower might be so I'm going

to take my monthly installment the

amount I pay or the consumer pays each

month for the loan and then divide that

by the annual income divided by 12 so

whatever they make on a monthly basis

now right now it's showing us as a

dollar value we've got to change that so

it's a percentage the debt to income

ratio should be a percentage and I'll

take that formula drag and drop it all

the way down and then I want to sort my

data from the highest debt to income

ratio to the lowest

and now you see that the values are

sorted in column C there the next thing

I'd like to do is to change a term in

the loan status column rather than it's

saying delinquent I'd like it to say

charged off so I'll use control F and

then click the replace tab and replace

and change the language now to make that

charged off term stand out even more I'm

going to go to the Home tab and use

conditional formatting I want anytime I

see charged off for it to appear in red

so go to conditional formatting

highlight cell rules and then equal to

and I'll go ahead and typed in charged

off when that appears

the final thing I want to do is take my

columns and autofit them so they're the

proper width and now what I'm going to

do is go ahead and stop recording and

take a look at my macro so I click on

the macros button and I should see loans

report my macro appear and it tells me

what it does I'm gonna click Edit and

the code appears so this code captures

every one of your mouse clicks and just

by looking at it you might be able to

tell what some of it does and so look at

this line with selection dot font this

is the start of a section of code that

changed our the font of our table to

Calibri and down below there's another

section we changed our header row to

bold and there was another section a

little below that where we changed

certain columns into the currency format

now you don't have to make adjustments

to the code but one thing that I do

suggest is to add comments to it this is

an actually code that's gonna run it's

really just for you to remember what the

code performs the way to write a comment

is to use a single apostrophe and just

write out what the next section of code

does so in this case change the font of

the report

and then I'm gonna go down and then

after the end width after that section

of code I'm going to create some returns

and then in the next comment I'm going

to say format the header row and then

after the end with a little farther down

I'm gonna say format or change the

appropriate columns and at the currency

for minute if you're just adding notes

for yourself so if you pull the code up

or pull the file up a few months from

now or years from now you remember what

it does now what I've got to do now is

save the file but I've actually got to

save it as a different file format

because it's got a macro inside of it so

instead of saving this as an Excel

workbook I have to save it as an Excel

macro enabled workbook that allows the

macros to be saved in the file now I can

close my code window and we'll practice

this to make sure it works so we'll go

to the week 2 tab and then we'll click

the macros button and then we'll run our

macro to see if it works there we go it

looks like we're good to go

hopefully this helped and demonstrate

how powerful macros can be even this

example we only automated a few tasks

but imagine if you have 50 a hundred or

200 tasks to automate it can make your

life a lot easier