Creating mixed reference formulas; converting to a table; applying conditional formatting

- [Instructor] When writing certain kinds of forms in Excel you may need to use what's called a mixed address, sometimes called a mixed reference. We're going to build a multiplication table in this worksheet. I want a 20 by 20 grid; so starting in cell A2, I'm going to put in the number one and then point to that fill handle on the lower right-hand corner, hold down the control key, and drag that fill handle downward to row 21. If you're holding down control and you drag an entry, Excel will automatically increment it by one provided you let go of the mouse first, as I just did.

I can do the same thing across starting in B1, but I could also copy this and transpose it. So I'll press Ctrl + C and go to cell B1 and (mumbles) paste special and the keystroke shortcut Ctrl + Alt + V. Remember, Ctrl + V is paste, Ctrl + Alt + V is paste special; and we use transpose, click Okay. So, and let's readjust those column widths. Click in the upper left corner; just drag any column to be a bit narrower so I can see the numbers a bit better.

And now what we need is a series of formulas. This is going to be a giant multiplication table. Every cell in here is going to be the product of the number at the top of the column and the number on the left-hand side, the row indicator. So, let's highlight all of these. And even though only the active cell is highlighted, if we write a formula for that upper left corner, and I'll zoom in on it a bit and scroll leftward so I can see everything; if I write a formula here and press Ctrl + Enter, the same relative formula will go in all of these cells.

So what formula do we need? Example here, we want B1 times A2. So we could certainly start by doing that; and, of course, it makes no difference which one comes first, B1 times A2. But thinking ahead when we're imagining every cell in this highlighted area, all of our calculations will involve row one; so the row one reference needs to be absolute. They're not always coming out of column B, but they're always coming out of row one. So on the left portion of our formula here, we can click behind the B1, in front of the B, between the two, or hide highlight.

We can do any of those four things. Then press the function key F4; and if we don't see what we want just yet, we keep pressing F4. Now if you've never done this, you wouldn't quite know what to do; but as we press F4 and see the different variations here, we want to make sure that row number one does not change in this part of our formula. We want the dollar sign in front of the one, but not in front of the B. In the other portion of the formula, we want to make sure the dollar sign is always in front of the A. And, of course, you can type the dollar sign; F4 often is a bit faster, just press it repeatedly and here's the look that we want.

That part of the formula should always refer to column A, but not necessarily row two. I'll press Ctrl + Enter and not Enter, and we'll get a bunch of correct answers. I'll zoom back a little bit. Now, it's a bit dry; let's add a little bit of color to it. One thing we could do is put a diagonal in it that highlights, for example, cells that are the product of the same number on top as on the left. In other words, the perfect squares: the 9, 16, 25, 36, et cetera. So let's use conditional formatting for this.

Highlight all the cells outside of our headings. Go to conditional formatting and establish what's called a new rule. Use a formula to determine which cells to format. And at the moment, the active cell that's highlighted right now, you can see is B2. So our formula for B2 only would be equal B1 equal A2. In other words, whenever those two are equal, we want this cell to be highlighted differently. So here too we need to adjust the opposite addressing.

For the A2 portion of the address, we want to press the function key F4 so that we see $A2; and for the B1 portion of this, press the F4 key repeatedly so that we see B$1. So =B$1=$A2; establish a format here. A yellow background would certainly stand out; click Okay. Click Okay, and we see what's happened. Now, if we convert this to a table, not everybody's familiar with that concept, the table concept is primarily, at least for a lot of people, a formatting feature; but it also gives you some other benefits, some data handling benefits as well.

Let's say we highlight all this data here. We're going to convert it into a table. We could go to the Insert tab; we could press Ctrl + T or Ctrl + L. We can also on the home tab go to Format as Table. Let's pick one of these styles here, for example, that one; and the table does have headers. In other words, that row one there is something we want to have at the top; click Okay. Now what looks a little bit off is that top row, partially because of filter button, so let's turn those off. And also cell A1 looks a little strange.

Just click there, type a space and Enter. Now we could readjust the column widths; click in the upper left corner, double-click any column boundary, until we see that look, not bad. But what happened to the yellow? It seems to have disappeared. When you do convert data to a table and you indicate that you have headers, that top row, which more often than not is text, is converted to text anyway. So we've got our numbers converted to text here. So our formula no longer works. So rather than trying to convert those numbers somehow, let's rewrite the formula.

This is the conditional formatting area where the formula has been applied. Let's go back to conditional formatting and manage the rule. There it is right there; you can see it. We can edit the rule right here, and we want to change that so that our formula reads equal value; in other words, we want to take that reference that comes out of row one and turn it into a value, in other words make it a number. So =VALUE(B$1)=A2. I'm going to highlight that and copy it with Ctrl + C and display it larger in a moment.

Click Okay, click Okay, and the yellow cells come back. So that formula that's being used there now is, as I paste it here, =VALUE(B$1)=$A2; and that's working properly. We'll just leave that there for reference. Now, another thing we could do and, again, we could stop at some point here with regard to the visual features; but another bit of interest possibly, just in terms of the values here, could be by way of conditional formatting, let's highlight all the cells greater than 99.

In other words, those that are 100 and above; and let's do it again maybe. Highlight Cell Rules, this time greater than 199, those that are 200 and above. Different scheme here; there we go, something like that. But look what happens, the yellow got displaced in these locations. Once again, got a solution because we've got control over the order of these features in conditional formatting. Conditional formatting, manage rules, we can make this rule that applies yellow here; we can move it up, in other words, make it be the dominant rule that overrides the others.

So we've seen a number of different techniques here, the last few, of course, not truly necessary; but we can easily build a table like this. And you can imagine some variations on this, smaller tables possibly, larger tables, maybe using squares, maybe using simple addition; and along the way, by converting this into a table and then possibly using some conditional formatting features, we've got a more interesting and visual display. One more thing I'd make a change here on probably is to take these entries in the top row here and make them right aligned. Not truly necessary, but one of the many different things we can do to make this more appealing visually.

So lots of different techniques here, primarily focused on that initial idea of using mixed reference formulas.