Microsoft Excel 2016 - Recording a Macro

you hi guys this is Mark Davis at optimum technology transfer welcome to you all in this video I'm going to take a look at recording macros in Excel recording VBA in Microsoft Excel this happened to be 2016 but this kind of functionality has been around a long long time what do we use macros for we use macros to automate repetitive tasks so I've got a sample data table here in fact I've got several sample data tables and what I do is I add sales figures for various countries and various categories of product that we sell let's say for example I do that on a weekly basis so I've got my heading here sales data for week commencing whatever yeah for example so as I say I do that on a regular basis and at the moment I'm doing it rather to say the least we're Boreas lee very very manually indeed and I think I wish there was a way I could do this so much quicker we can do that with macros what I'd like to achieve is a format something like this and apply that kind of format consistently across my various other unformatted worksheets for example yeah so go from something like this to something like that now we don't know quite enough about Excel VBA the moment to be able to do this completely from scratch and that's where recording does come in it's a wonderful wonderful wonderful learning tool so I'm going to click the Developer tab if by the way guys you don't have the Developer tab displayed click file click options click customize ribbon and do make sure that the developer tick box is ticked or checked click OK I can then with the Developer tab I can click the record macro button over here in the code group on the left hand side so click record macro type apply custom formats or of course whatever you choose to call it you're not actually allowed to have spaces in either range names in Excel or indeed here of course in macro names click on tab into the shortcut key box I'm going to pop an uppercase capitals EDD in there so ultimately I'll be able to run this particular macro nice quick shortcut key ctrl shift and said tap down a couple of times to the description box and here kind of free text as it were applies custom table format for example and click on OK and then I would go through the the various kind of formatting processes so let's say for example I'd like to widen column B so it looks something like that I'd like to widen these columns nice and consistent nice and neat nice and tidy I'd like to number format these cells I go back home click the number format dialog box launcher click number thousand separator pick ok already starting to look a little bit neater a little bit tidy I've played around with the column width I do like to have slightly or perhaps a bit more sometimes higher rows gives us spreadsheets a little bit more kind of breathing space as it were if you are playing around with the role Heights I do think it really is a good idea once you've done so again select multiple columns select multiple rows for consistency and then align middle looks neat it looks tidy up what else I'm going to perhaps enter across selection here so all of course I'm doing is going through manually at least at the moment yeah a various set of formatting processes to apply certain formats to my spreadsheet maybe up to the same or something similar with these guys with these cells for example here so center across selection once again for example heading this here they're going to be right aligned I'd like these headings this heading these cells with this heading or to be bold for example to get a color I'd like to apply there go on to my recent color to have a teal color and I've got there for example this kind of made your heading we've got there at the top this main heading it's going to be a bit bigger its sub heading as it were that I've got here various countries yeah not quite as big and then these headings 11 points eyes of the world let's just pop them up to 12 points eyes for example I'm quite fond of my borders as well so what I'm going to do is two borders more borders and I'm going to use one of my recent colors this kind of orange color that I got here to apply a double underline to the bottom of the selection and click OK something kind of similar here again borders more borders use a consistent kind of coloring for your borders just a single underline this time there we go there for the product category heading and the various EU countries that we've got there against something very very similar indeed to get some really nice consistency going and I'll do that I'll just do one more borders you like using borders to kind of separate out sections of a spreadsheet can be quite effective I think back to borders and more borders again it's going to be that same orange color but it's going to be over on the right side of the selection click ok and then just click away and that's the formatting that I'd like to achieve for any subsequent sales data tables what I'm going to do going back to the developer tab under stop recording so at any point in the future we commencing week commencing further on down the line I don't have to go through that terribly manual laborious process ever again I just click into my table somewhere in fact you need to do that and press control shift and said and there we go it's done and then it's the following week yeah same product categories assuming here same various countries I'll change my heading week commencing whatever there's my unfun formatted table control shift and Zed and there we go that's it done and that's what recording macros is all about macros for automation of repetitive tasks and that's it for this particular video do search optimum technology transfer guys for other videos that i've created and put here on youtube but as I say that's it from me mark davis at optimum technology transfer take care and bye bye you

Channel: Optimum

Views: 207,683

Keywords: Excel, Microsoft Excel, Video, Tip, Tips, Trick, Tricks, Tutorial, Excel 2016, Microsoft Office, Makro, Macro, Record, Creating, Create, Records, Help, Videos, Microsoft Excel 2016, Recording, Beginner, Start, Starter, Basics, Basic, Optimum, Excel Training, Train, Macro Training

Id: MUeL0nHEqkI

Length: 6min 59sec (419 seconds)

Published: Tue Mar 01 2016