I'm going to explain all about R1C1 style in Excel in this screencast and why it's important. Most of us are already familiar with something known as A1 style. A1 style Excel is referring to column and row. For example, I could refer to cell E1, E1 there this is known as A1 style because it's a letter followed by a number. If we want to make that absolute, we can always put in the dollar signs. You guys know all about that. For example, in A1 style, if I wanted to sum those two numbers up there, I could just point to B2 plus B3 and that's A1 style and the result in cell B4 is the sum of those two numbers. Also in A1 style, if I wanted to create a column of values here where I'm going to take x plus this value b up here, I could just do equals 2. In this case, D4 plus b has a reference of E1. Now, ordinarily, you might want to name that cell in the name box using b but I'm just going to use that absolute reference E1 with a dollar sign, and then we can drag this formula down. Because E1 is an absolute reference, we're always adding the value b of 5 to our x value. You guys are quite familiar with A1 style. One more thing I want to show that we can do using A1 style that's particularly difficult to do and this is one reason why R1C1 style is useful is in something like a multiplication table. In each of these cells of this table, we want to multiply the value up here. The row heading by the value in this column over here to make a multiplication table. For example, this value would be 3 times 4 and this value here would be 5 times 4. This is tricky to do in A1 style because you have to use mixed references in this column, in column C, I always want to use row 13, so I have to press F4 twice to make C relative and the row absolute and then I want to multiply by this 1 over here. But that's always going to be in column B and it's a relative row. When I drag this formula down, I don't want it to always be row 14, so I'm going to press F4 three times, so I get absolute column, relative row. This is tricky because it's in mixed formatting, mixed cell addressing modes. When I drag this over and drag it down, you see that it's always using in the first position here, it's always using row 13 and in the second position it's always using column B because we use those mixed cell addresses. It's quite difficult to write this type of code using A1 style. It's doable, but now let me introduce the R1C1 style. Let's go up to the File menu, select "Options" and then in formulas, the very first thing here is R1C1 reference style in working with formulas. Let's go ahead and click that and we click, "Okay". By default, Excel is actually storing everything in R1C1 style. People over the years have just gotten used to using the column headings as a, b, c, and so on. But if I look here at the top, you see that now the columns are all numbers as well as the rows, because now we're in R1C1 style. Let's just click in a couple of these cells. For example, this first one, that's R1C1. Let's go here to this cell, that's R2, meaning row 2, C8, column 8. Let's take a look at how some of these formulas have been changed here. Remember this cell, we're just summing the two cells above it. You see that Excel has added these square brackets. Square brackets indicate a relative reference. What this means is we're taking the value that's in 2 rows above. That's what the negative 2 in brackets means. The cell that's 2 rows down in number, which means above in Excel, same column. So C without anything just means the same column. Then we're adding that. We added it to the cell, it's just one cell above, and in R1C1 style, that's our negative one and square brackets, same column. That's how relative referencing works. Relative referencing works with these square brackets with numbers. Now let's take a look at how absolute references work. Remember in these formulas, we had used cell E1, which is now R1C5. That's an absolute reference. Absolute references that we denote with dollar signs end up being just numbers without square brackets. Here we added a relative reference so that same row, 1 cell over negative 1, and square brackets. That's what that means. It's a relative reference. But then absolute references are without square brackets. R1C5 is always going to be R1C5, regardless of where that formula is placed because it's an absolute reference. We denoted that with $2 signs when we did this in A1 style. If I look down the column here, you see again that cell up here, that value for b is always R1C5. Now, let's take a look at our multiplication table. This is where the real value and utility of R1C1 style, at least in Excel, comes into play. Because if we look at this formula, it's R13C times RC2. These are mixed addresses. The first one meaning row 13, it's always going to be row 13, same column. The second element here, it's always going to be the same row. That's a relative reference, but then C2 is an absolute reference. So it's always going to be relative row, the current row, but column 2. R133 times RC2 remember that because if we take a look at any of these R1 C1 style formulas in this multiplication table. They're all the same. Row 13, same column, that's what this first part is saying. Same row, RC2 column 2. That's the utility of the R1C1 style formulas. It's nice because let's just delete this. I'm going to actually turn this back to A1 style. Let's go back into formulas deselect the R1C1 referenced. We're going to go into Visual Basic. So let's go into the Visual Basic Editor and add in a test sub in cells C14 to G18. I'm going to put in a single formula in Visual Basic Editor. We can write this range, C14 to G18 now that's in the A1 style. I'm going to say formula R1C1. That's how we can refer to this in VBA. We're going to put it in R1C1 formula into that range and this is going to be equal to, and it's just a single formula and that formula that we had in the multiplication table, remember all of those cells were exactly the same. That formula was R13C times RC2. When I run this, just run that it places into all of those cells the same formula in R1C1 style. But take a look because in A1 style, each of those are different in one quick formula like that, because every one of those cells has the same exact R1C1 style formula, it's very easy to do. This is the utility of using R1C1 style. I'm going to explain a little bit more about this in this module and go through quite a few examples of why this is important when working with VBA.
