- 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'll 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 1 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 going to 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, we'll use a shortcut, Alt H MC, 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 going to 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 FC and hit up on the white color. Hit Enter there, Control B to bolden. And for the row below, just press Control Shift and right arrow. And here we're going to make it in a light blue tint, 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 Control Shift down, Control Shift right. 

Then we're going to go to the Insert tab. And we're going to 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're also going to remove that title there. 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 going to 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 the first one, which is simply a button. So we're just going to 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 going to 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 going to work again. So I'm just going to 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 KENJI25; 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 going to go to Record macro. And let's call this something Highlight. Hit OK there. And from here, let's do the set of steps to highlight this cell, in particular. 

So let's say I go Alt H H. And we're going to color it, say, 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 bolden by pressing Control 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 Highlight. Let's make sure we link it to the Highlight there. Hit OK. And we're going to call it Highlight, too. Great. 

So from here, let's suppose I want to highlight the Canada region. So I'm just going to select it, so Control 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, say, 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 at 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'll click on that. And let's say I want it to highlight. Hit OK 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 going to format it, 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 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 going to click on Edit. And 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 Control Shift bottom here, so Control 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 use 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 going to go to Save As. So press the 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.