Learn Excel MACROS in 10 Minutes Using Real World Examples

if you work in any of these roles odds are you'll  come across some boring and repetitive tasks from   time to time now to help you eliminate those  in this video you learn to automate them in a   click of a button like this using macros let's  go so here's the scenario we're working with   let's suppose that every week we get a data set  like this one which you can actually download   for free in the video description and as you  can see we have the week one financials by   country and our goal here is to format it and  make it look nice so that we can send it to the   management team suppose by the end of the day now  because this task requires the same steps every   week we can actually automate it using a macro  so to enable the macros we're just going to go   up over here to any part of the ribbon right  click there and click on customize the ribbon   from here you should get a pop-up like this  and we want to go down to the developer   area here make sure that's ticked if you  haven't already and from there we'll hit OK   so that's going to open up this new tab  called the developer up top here so make   sure you click inside of it what we're gonna  do is record a macro so let's click on that   from here let's name it something like format  hit OK there and this is going to record a set   of steps that we take and so every time you want  to run it you can just hit run so first let's   format this so we'll press shift right arrow  here all the way to the end and to center it   would use a shortcut alt h m c which is to merge  and center over here then let's say we also want   to highlight it in dark blue alt H H for that  and I'm just gonna hit on the dark blue over   here then we want to change the font color to  something like a white here so we'll go alt h f c   and here on the white color hit enter there Ctrl  B to bold in and for the row below just press Ctrl   shift and right arrow and here we're gonna make  it in a light blue say so alt H H again and this   time we're just going to select a light blue like  so so suppose we're happy with this and on top of   that let's also add a chart down on the bottom so  they can see it more visually what that's going to   look like so we'll select all of this area here  by going to Ctrl shift down Ctrl shift right   then we're gonna go to the insert Tab and we're  gonna insert a chart let's say we go for a combo   chart to see both the revenue and the profit  so combo here this is the first one that we   want and let's say we move this down over here we  also going to remove that title layer so just hit   delete and there you go now to stop recording  that macro we'll go back to the developer tab   and from here we're gonna go to stop recording  and there you go now it's recorded that set   of steps let's suppose we're now in week two  so go to control page down over here and you   can see all of the unformatted data so now to  actually make it work we just have to hit macro   and you can see that under macro we have the macro  name the format and we're just going to hit run   and just like that you can see it's fully  formatted and we also have the chart right below   now to make this a bit faster instead of having  to go through the developer tab and hitting   macro we can actually just add a button on the  side so on insert here you can see that we have   a ton of different options but we want to go  with that first one which is simply a button   so we're just gonna put it say over here like so  and let's make sure that we link it to the format   so every time we click that button it's gonna  do the format macro that we recorded earlier   we'll hit OK there and let's rename this something  like macro actually let's rename it format there   just like so and now let me just delete this chart  so we can see if it's gonna work again so I'm just   gonna click on format and now we only need to  press that button and it's going to do the exact   same action as you can see here and even though  you can automate tasks on Excel there's more   powerful tools like power bi which allows you to  create awesome visuals in a fraction of the time   to learn about it I recommend checking out  Financial Edge which provides certified   online courses and is also sponsoring this  video their data visualization and power bi   course goes from the basics of data cleaning and  progressively moves to harder topics like creating   power bi charts customizing visuals and finally  creating some awesome dashboards in power bi   and if you're interested in other areas of  business or Finance they've also got courses   on investment banking private Equity trading  and more so if you're interested in checking   them out go to the link in the description  below where you can get 25 off using Code   kanji 25 all right back to the video on top of  that what if we want to highlight the country   we're sending things to so for instance if we're  sending this to the Canada country manager then   we would like Canada to be emphasized for that  we can also create another macro so just click   on any random cell here we're gonna go to record  macro and let's call this something like highlight   hit OK there and from here let's do the set of  steps to highlight on this cell in particular so   let's say I go alt H and we're going to color it  say um in a light gray Also let's add some borders   to it so alt h b and then from here D is going to  be the shortcut so now you can see that we have   the top and the bottom border and let's also bold  them by pressing Ctrl B so once we've done all of   this we'll go back to the developer tab and hit  stop recording and again let's make this a button   as well so we'll put it right over here and let's  say we call this one something like highlight   let's make sure we link it to the Highlight there  hit OK and we're gonna call it highlight two great so from here let's suppose I want to  highlight the Canada region so I'm just going   to select it so Ctrl shift right and all I need to  do is click on highlight and you can see how it's   been able to emphasize that and it doesn't have  to be for all the rows it can just be safe for   the country itself I can just click on highlight  and you can see how it's been highlighted there as   well so now whenever you send this new file  to each respective manager you just need to   select a particular area and hit highlight for  now we've only looked up buttons to make the   macros work but they also work with something  like a shape or an image so here I've got the   logo of my company let me just right click on  it and you can see here that it says to assign   a macro so I click on that and let's say I want  it to highlight hit okay there and so every time   I have a cell selected let's say I have this  one here selected and I click on my logo you   can see that that's kind of formatted like so  one scenario that we haven't considered is what   happens when the data set is not of equal size  so in this case you can see it goes all the way   to row 11 but if you go control page down you  can see that we've added three more rows over   here so we want to find out if the macro is still  going to work so if it's dynamic in other words   now to do so let's go to the developer tab as  we don't actually have the button here anymore   click on Macros there and we're going to want  the format one first so we're just going to hit   on run and you can see that if we look at the  chart over here it's actually accounting for   Portugal Belgium and Austria now if you're curious  to see why that I why that is why it's dynamic in   this scenario we can just go click inside macros  here and then for the format one which is the   one that we just used we're gonna click on edit  that's going to show us the steps that we took   click on that and bear with me I know this looks  a bit daunting this is basically a VBA which is   the programming tool within Excel so you can  see that here we have all of the steps that we   actually took so on the horizontal alignment and  the vertical alignment here you can see that it's   saying like Ctrl shift bottom here so Ctrl shift  down and now the reason why it's been working for   us is because it's seen as something Dynamic here  if we actually had a range like say I don't know   B2 to B10 then it wouldn't quite work because it  would be limited to that but because we used a   shortcut it's able to detect that all the way down  to the bottom dynamically let's get out of that by   pressing X there and one final thing to note about  Excel files when they have macros is that you   can't save it as a regular file instead we're just  gonna go to save as so press the F F12 key there   and here towards the bottom when it says save as  type we don't want a regular Excel workbook but   we want the next thing which is the Excel  macro enabled workbook click on that and   then just hit save from there for more on Excel  check out this video over here going over Excel   shortcuts hit the like and that subscribe  button and I'll catch you in the next one

Channel: Kenji Explains

Views: 415,697

Keywords: macro, excel macro, vba, excel vba, vba code, automate anything, automate anything on excel, use macros to automate anything on excel, macro automation, macro recording, macro button, macro shortcuts, excel macro shortcuts, macros tutorial, excel macros and vba, excel macros coding, excel macros vba tutorial, macros real world example, eliminate repetitive tasks using macros, use excel macros in real life, learn macros in less than 10 minutes, free excel macros tutorial

Id: IZY0SpSpYTM

Length: 9min 12sec (552 seconds)

Published: Sun Oct 09 2022