Let's have a look at a more complex example involving formulas and calculations when recording. Let's imagine every day, week, month, I get sent some data. Sometimes the list is a little bigger or smaller, but it's always in this format. So I've got an order, a region, the city where it was placed, a category, a product, quantity, and a unit price. What I need to do is add a column to calculate quantity times price, and include a grand total at the bottom of this new totals column. And then finally, apply some formatting to tidy things up. I can see this is gonna take a while to do and I don't want to make a mistake, so I'm gonna make a list of steps and importantly, the way we do each step. So here's my list of steps. And as you are making your list of these steps, you need to think about how that is gonna be recorded. And if I was not sure about that, I might want to do some testing on just that aspect before I recorded the whole thing. For example, when I press the enter key, I'd wanna make sure that the cursor stays where it is, rather than moves down a cell. So, I'd turn that off in options before I started doing my recording. We are gonna start recording and then we're gonna talk about these different challenges as we go through.

So first things first then, let's start recording. So that's Alt-L-R and we're gonna give our macro a name and we're gonna call it totals and formatting.

I am not gonna worry about a shortcut key, but I am gonna make sure that I'm storing this macro in this workbook,.

Click okay.

And now we're recording and we just need to go through the steps that I've got in my list that I pre-prepared in the order and making sure that I'm doing the steps appropriately. So let's go and have a look at that list again, just remind ourselves what the first steps are. So it says that I should be using Control-G to go to I4. Now the reason for this is if we think about what we could have done instead is I'm currently over in column H here, and I could of course use the cursor keys to move across one or I could use all kinds of different keys, but of course I won't know where a particular one was starting. So good idea to just save ourselves the minimum amount of steps and I'm gonna do control-G and go to I4.

Then we're gonna have our label. So let's have a look at our list and see what we're gonna call it. We're gonna call it order total.

And now we're gonna move down a cell to I5. Now whenever you are moving in Excel, when you're recording you need to think about do I need to have relative references turned on or off? And in this case it doesn't matter because at the minute we've already hard-coded ourselves going to I4, and actually we can still continue to hard-code ourselves going to I5. So I can use to do the down arrow key. I don't need relative references on at this point. The next thing we need to do is write our formula. So obviously this is just gonna be equal to our quantity multiplied by our unit price.

And there's our answer. Now, if I'd stopped recording now and went to have a go and look at the code to see what I'd got recorded, we would see that Excel uses a different formula format for recording formulas in VBA as opposed to how we write them in Excel. So we wrote equals G five times H five, but actually if we were to look at the recorded code we would see that actually it recorded a relative reference. So what it recorded us doing was multiplying the cell that's two cells to the left by the cell that is one cell to the left. So this is good news for us, right, because it means that whenever I'm entering formulas in Excel in this way I know this is ex, how Excel works, and this is perfect. Okay, so I've done one formula obviously the next job is to copy that down the column. Now to do that I, I'm gonna use column H as a traveling column. So I'm gonna move across to column H and then we're gonna go down that column. Let's go across to our instructions and see what I've made a note of. So I've done my formula my next instruction is to copy I5. Then I can use my left arrow key, and then I can use control and down arrow key to get down to the bottom of the column. So let's go and do those steps next. So I'm gonna do Control-C to copy, arrow key across once Control-arrow down key to jump down to the bottom and let's come back to our list of instructions and see what's next. So I've got myself down to H248 and I've put myself an instruction that says I need to turn on relative references, references at this point. And that's because I need to move down, sorry, move across one column to the right because obviously now I need to go across the right, I don't need to recall myself selecting the G248.

Absolutely. So I need to make sure that relative references are turned on. So let's go back to Excel and go to Alt-L.

And then U for turning on relative references. Now I can move across one, and then I'm going to use I'm gonna turn relative references off again because I want to go back to using absolute references. So that is Alt-L-U again.

Now I'm going to use control and shift and up to get to the top and then I can use enter to paste in my formulas.

Okay, so that's that column completed. Let's go back to our list of instructions to see what's next. So now I've pasted in my copied formula, it says that I need to do Control-G and go to H4. So the top of column H is where I need to get to next. Then I'm gonna use control and down arrow to come to the bottom of the column. Then I'm gonna turn on relative references 'cause I'm gonna move down a couple of cells and that's where I'm gonna have my label for total sales. So let's get down to those steps. So let's go again, go back to Excel, let's do control and G to go to H4, which is the top of column H. And I'm gonna use control and down arrow. And then I'm gonna turn on relative references which is Alt-L-U, and then I'm gonna move down two cells. And then we are gonna go and enter in our label. So let's come back to here.

And it says we are gonna use total sales as our label.

So let's type that one in.

And I'm gonna make that bold.

I still have relative references turned on, which is good because I need to now move across one column to the right to get to where I want to do my top total. And we're just gonna do our normal sum function. And then we're gonna have a little chat about how we're gonna edit it slightly to make this work with a recorded macro situation. So we're just gonna do equal sum open bracket And then I'm gonna come up to my I248 cell and then Control-shift-up arrow to select to the top, shift-down. And then what we're gonna do is we're just gonna edit this formula slightly. Now, normally this would be fine, I5 to I248, but remember we said that this is going to record us doing this relative style of referencing. What I need to tell Excel is that in this recorded situation, every time you run this macro, I want to record myself selecting something from I5 down to some flexible amount at the bottom. And we know how to fix stuff in formulas because we've done it before, right? We use dollar signs to do this. So I'm just gonna use my cursor keys to come across and edit the cell I5, F4 to go and fix that.

And then I'm gonna come back to the end of the sum function, close my bracket, and press enter. So there's our total. I need to think about while I'm here. I'm gonna apply some formatting. So I'm gonna bold this, yeah. And then I'm also gonna add some borders. So I'm gonna go to Alt-H and then B for borders And then I'm gonna have a top and bottom border. So let's go for all, that's C.

Let's go back to our list and see how we're getting on.

So we've done our total sales, we've typed in our sum function, we've applied the formatting but now it's telling us that I'm gonna go back to using Control-G to jump right up to the top of the table. Now when I'm doing the formatting you'll see what I've done is I'm applying the formatting to the whole of the table first and then I'm gonna select just the top header row and apply some formatting to just that. So again, just gotta think about the order that you're going through to save yourself doing the, you know the least amount of steps. So let's go to the top. So I'll go back to Excel Control and G and I'm going to go to B4.

And we're gonna do Control-Shift-Right Control-Shift-Down to go and select our table. I'm then gonna apply some formatting. So let's add some borders, so Alt-H-B and let's do all borders, which is A and then I'm gonna apply a thick board around the outside which is Alt-H-B, and then T for thick. The next step is, I need to do something to the header row. So I'm gonna do Control-G again, I'm gonna go to B4.

And then Control-Shift-Right to select the top row. And then let's go and apply those thick borders around the outside of that selection. So that's Alt-H-B, T. And then let's go and add a formatting to the top row as well. So let's go with a highlight color. So Alt-H-H and let's go and have a light blue.

Let's go back to my little list instructions and see how I'm doing. I've done that. The last step then is just simply to go and tidy up and go and select B4 so that we don't have a selection left after I've finished recording my macro. So back to Excel and then control and G, B4, enter and then I need to stop recording. So that is Alt-L-R. So now what I've created is a very flexible macro which allows me to do formulas and calculations on a piece of data, which can change in size.