- [Instructor] When you convert data to a table you get lots of visual features, and probably what stands out most obviously is what we call Banded Rows. I've got a list of data here, I'm gonna click on it and double-click the bottom edge of a cell to remind myself how many rows are in this list. It goes down to row 675. I'm gonna double-click the top edge of that cell. Now the list might grow, it might shrink, but if you convert data to a table you've got lots of advantages. We can do this one of four ways. We can press Ctrl + L or press Ctrl + T.
Think of L for a list, T for table. On the Home tab there's a option right here, Format as Table, it's in the Styles group. The description there probably doesn't do it justice in terms of if you were unfamiliar with the feature would this entice you to use it? Quickly convert a range of cells to a table with its own style. You'll also find this feature on the Insert tab. And here as you slide over Table a better description, Create a table to organize and analyze related data. Tables make it easy to sort, filter, and format data within a sheet.
So I'm gonna choose this, remember there are four ways, Control + L, Control + T, Home tab, or here on the Insert tab. All of these lead us into this dialog box. My table has headers, give that a quick look, make sure it does. Excel nearly always figures out the extent of your data, give it a quick look, click OK. And immediately we see, not only this Banded Row look, but we have a contextual tab up above. Notice how we have Banded Rows. We could uncheck that and try Banded Columns, sometimes that's a better view of the data.
Off to the right we've got Table Styles, we can slide over the choices here, see some of those. Click the drop arrow, 61 choices. I'm sure you'll find at least one you like in there. Now you'll also see here at the bottom New Table Style, and it's a bit cumbersome to come up with your own design style. Now let's cut to the chase here, make it simpler. Suppose we go back to Banded Rows, and by the way, using Banded Rows and Banded Columns together, not a great idea. Banded Rows, let's say you like this idea, but you don't want every other row.
You might even want the flexibility to say I want every fifth row, or every 10th row, or every fourth row, whatever, to be banded. We can start the process here by either simply turning off Banded Rows, another option could be you could pick a Table Style, even with Banded Rows turned on, that doesn't show them. So that's another approach. Some of these up here, for example, are not showing Banded Rows, but let's say it's more direct to simply uncheck Banded Rows. Now we can use Conditional Formatting here and we can even tie this to a cell.
We can possibly use a drop down arrow. Let's begin with this idea. We're gonna have a number out here, let's say initially it's gonna be in cell N1. Right now I'll just put in the number four. Now let's establish Conditional Formatting for this table. I'm gonna drag across columns A through J, that means we're selecting the entire column, so if the list grows or shrinks the feature we're about to apply here would apply to those new cells and those new rows. Conditional Formatting, none of the existing rules applies here, we're going to create a New Rule.
Now although this might seem a little bit awkward and certainly not straightforward, we need only do this once. Use a formula to determine which cells to format. Format values where this formula is true. I'm going to be using a function that you might not have heard of, it's called MOD, M-O-D. And think of it as calculating the remainder in a division problem. And you might be saying, why would I ever use that? What we're going to be doing is imagine each cell, one by one, we are going to look at its row number and divide it by four, and if the remainder is zero, that means it's evenly divisible by four, then we will apply a format.
So we type =, and I will display this larger on the worksheet momentarily. You don't, as you're typing this, need to capitalize everything, but I want it to be large and clear, MOD, we're going to apply this to the row number. Now when you use the ROW function, R-O-W, you can then follow it with an open ( and a close ), that will suffice to refer to all cells. And that looks kind of funny right now, kind of looks like a squished zero, but we're saying in effect, this is for any cell, we're looking at its row, comma, and we're gonna be using that value in cell N1.
I'll click on it, it's an absolute reference, we want to keep it that way. Now whenever you divide a row number by that number up there, it currently is four, when the remainder is zero, remember that's what the MOD function does, it takes a value, in other words a row number, we'll be dividing it by, in this case four, we don't see the division symbol in there, but that's what MOD does. When the remainder is zero that's when we want to apply the format. And the format can be anything we want, but let's say the most obvious visual difference would be a Fill Color.
So I'll use bright yellow in the example here, click OK, click OK, and we see what's happening. And if we change that four to a three we see what's happening there, or we change it to a five, and so on. So we're overriding the Banded Row capability of a table here by selecting our own interval here. Now, to make this even simpler, and maybe it's a situation where you want to change at different times or different people wanna have this ability to change, you could certainly continue to use this feature by way of a number, but you could also go to the Developer tab in the ribbon.
Now some of you might be trying this right now and saying to yourself, well I don't see a Developer tab in the ribbon. If you right-click any of the tabs in the ribbon, any tab, right-click, you can go to Customize the Ribbon. And then along the right hand side here you'll see various choices, and perhaps you'll see some that are different and maybe you won't see all the ones I'm seeing here, and you might see some additional ones, but you will see a Developer tab. And if there is no checkmark in front of it you want to check it, and that would be the case if you're not seeing it, so make sure it's checked, and then OK.
And then it will appear here. If you then click the Developer tab and go over to Insert you'll see some choices here. Now we could be using a Spin Button, we could be using a Scroll Bar, I'm gonna use the Scroll Bar, and maybe the other one eventually. And simply click and drag out here. We can reposition this a bit later. Now it's still active, I'm going to right-click it and go to Format Control. Let's say that as we use this Scroll Bar we will only use values between zero and 10.
And of course, you could change it to be 12 or 15 or whatever the upper limit. Incremental change is one, that means every time we'll be clicking those arrows later we want the number to go up or down by one. And then let's link this to the cell N1. I'll simply click on it. So every time we make a change by clicking the arrows or moving the slider bar cell N1 will change. Click OK. And then click outside of this. We'll come back later and maybe resize that. So we can drag this to the right this way and you see this changing that way, or just click the arrow.
As we move down here, every seven, every five, and what happens if we get down to one? Everything is highlighted, you probably wouldn't want that, and zero is for no highlighting. And sometimes you want that. So for a lot of people that's just gonna be whatever choice you want. I think in this case too it's probably easier to use the arrow on either side, but you certainly can drag if you wanna change it more quickly. If you wanna come back and change the look of the slider bar hold down the Control key, click it, you could have it overlap that cell, doesn't need to show necessarily, nothing wrong with it showing either.
If you wanna resize this, so it looks like it's fitting into the cells just exactly as you drag a corner you can make a different size, but if you hold down the Alt key it's as if it has like a magnetic attraction to the cell boundaries, you could do that. And then in the upper right corner, maybe the same thing. Not truly necessary. So there's one example of it, and we'll use this. And we could easily change this. Now I applied this to a table, but we could just as easily do the same kind of thing for other data. So over here, this data is not a table, maybe we don't want it to be a table.
I'll drag across the columns this way, and maybe I'll assign this to cell P1, maybe I should put that number in there first just to test it out, so I'll put a five in there. And then let's scroll to the right, imagine we're only working with this data for the moment, drag across these cells here, and as before, we'll go to the Home tab, Conditional Formatting, New Rule, Use a formula to determine which cells to format, =MOD(ROW(),P1, that's an absolute address, we want it to stay that way, )=0.
And Format here, use a different color here, maybe the light green, click OK, click OK, you see what's happening there. And the actual formula, I'll paste it right here, there it is. Make that column wider too so we can see it better. That's the Conditional Formatting formula that we're seeing for these cells right here. So I have not set up a Scroll Bar or a Spin Button in this case, but I certainly could, or simply change this number. Change it to four, you see what's happening.
So another option here, as I suggested earlier, Developer, on that same location under Insert, under Form Controls here, here's Spin Button. Now this could be vertical, it could be horizontal, some people are seeing it like this, so you can change this later of course. Just as with the previous kind of button, we right-click, go to Format Control, we want this to be linked to that cell P1. And here too, probably Minimum value is zero, Maximum value 10, Incremental change one.
So click outside of it, drop arrow, one, two, three, four, we see it like this. Now back to this table idea. If you want to move this or change it in any way hold down the Ctrl key and click it. I'm gonna move this off to the right a little bit, leave it there for the moment. This is exposed, nothing wrong with that. Now the issue with tables is, if you have worked with them you know that when you add information on the right side of the table it automatically becomes part of the table, if you add information at the bottom of the table it automatically becomes part of the table.
So what if I want to put in a new entry here called New Salary? Watch what happens when I press Enter, this becomes part of the table, although it's not exact obvious, but you can see by the way the formatting has been copied over. However, it didn't copy the Conditional Formatting rule. So in a case like this you'd click column K, with the right mouse button point anywhere along the right edge, hold down the right mouse button, drag into column L, let go of the right mouse button, Copy Here as Formats Only.
And that copies the Conditional Formatting rule as well. So now I see how the yellow's applied there. So you would have to take that extra step. As we click a cell here and then double-click the bottom edge and you go down to the bottom you see what's happening here. Now this is throughout the entire worksheet, and so that's not wrong, but on the other hand if you didn't want this to happen, let's say when you initially highlighted the data instead of dragging across the column letters you could have highlighted, for example, just the data from A1 down to the lower right hand corner and that would be something like L1000, some number bigger where you knew the list wouldn't grow any larger than that.
So getting back to this idea. I think you can see how it has some visual appeal, you want control over this. One other change that you might wanna consider making, I'm gonna change this number to a five, and I should do it ideally by way of the arrow to the right, so let me do it that way. Here's the left arrow, now it's a five. Now that is every fifth row, but this is the fourth record, and this is the ninth record, and this is the 14th record. Nothing wrong with that, and it does remind us that this is in sync with the row numbers, and I think that's probably more important perhaps.
But if you somehow said, I wanna make sure that it's every fifth record exactly, then we'd have to rewrite this formula slightly. In other words, highlight these cells here and let's go back by way of the Home tab to Conditional Formatting here, and Manage these Rules, and change them slightly. Now you might see the rule here twice, because now we are out to an additional column. We can get rid of the first one here, this isn't truly necessary, but wouldn't hurt. Let's modify the second one, it's called Edit the Rule, and there we see it, there's the rule right there.
We are referring to cell N1, but if we want to make sure that the count actually begins in row 2, we'll modify this formula by putting in a minus one. So I'll make that change and click OK. And now we see how the counting actually begins here. So this is every fifth row, but that's row 6, row 11, row 16. I think most people probably would not want to do that, so let me jump back there first by highlighting these cells, and then back into Conditional Formatting, let's Manage the Rule, and all I'm doing here really is selecting it, so we can see that formula a bit better.
That's probably difficult for you to see, so I'll copy it, and then off to the right display it right here. And let me scroll rightward too, so you can see that even better, there we go. So we made a change in the formula, let me zoom in on that also, holding down Ctrl, using the mouse wheels. So there's the Conditional Formatting rule that's in effect right now for the data from columns A through L. So a technique for putting in Banded Rows you want it to be flexible, this technique would work. You don't necessarily need to use the Scroll Bar or Spin Button, but in some cases that makes it more visually appealing.
So this is still in effect, it's still working, and I think most people would probably cover that up, the actual cell. But we saw how it's all set up. It's an ideal feature when you wanna be able to read lists more easily.
Note: Because this is an ongoing series, viewers will not receive a certificate of completion.
Skill Level Intermediate
Q: Why can't I earn a Certificate of Completion for this course?
A: We publish a new tutorial or tutorials for this course on a regular basis. We are unable to offer a Certificate of Completion because it is an ever-evolving course that is not designed to be completed. Check back often for new movies.