In case 54x20 formats is not enough for you, you can design your own table format. Episode 935 shows you how.
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey. Alright. Welcome back to the MrExcel netcast. I’m Bill Jelen.
Well, all week, we've been talking about Lisa's question of how to format every other row.
Remember, on Monday, we talked about how to use the format autoformat, ALT+O A, and applied 2 rows of green, 2 rows of white, on Tuesday, Lisa's trick of using the fill handle where we could use any 2 colors we wanted, and then, on Wednesday, conditional formatting basically chose the one color for the odd number of rows. On Thursday, we had the 54 formats in the table * 20 different colors, so a whole bunch of different options there, but what if we want to use that table functionality but we want to replicate what we had back on Monday -- 2 rows of green and 2 rows of white or something like that? The table functionality actually has those 54 built-in formats but many more formats are possible. I'll show you how to do it.
I'm going to use my shortcut key of CONTROL+T to turn on the table. Click OK. That's because I'm going to go in and create my own table style. So, all the way at the bottom of this gallery is something called NEW TABLE STYLE.
I'm going to format this. I want to go to the FIRST ROW STRIPE and say that that first row is 2 rows tall, choose FORMAT, and I'll choose a color. Let me go with this green here. Click OK, and then I’ll go to SECOND ROW STRIPE and say that that is 2 rows tall.
I'll choose FORMAT, and let me choose something else, maybe this light yellow here. Click OK. Alright. You can see the preview now.
We've done green, yellow, green, yellow.
If I wanted this to be the format that I got everywhere in this document, I could set this as the default table style. That would be what I would get for CONTROL+T. As a matter of fact, let's go ahead and do that, click OK. Alright.
Now, setting up that style doesn't change the table that I'm in because I've already declared that table, but if I come back to the gallery, I now have the CUSTOM, and I can choose CUSTOM and, very quickly, we've assigned alternating rows of green and yellow.
So, as you can imagine here, you can set up the first row stripe however you'd like, 1 row, 2 rows, 3 rows, 4 rows, up to 8, I think, and the second row stripe however you like, so you actually have absolute control here over how we do that formatting. We're not stuck with the 54 formats they built in for us. Great feature that allows us to very quickly go through and format the table.
Now, let me tell you. This table functionality, there's a couple of weird things with it.
We get the autofilter dropdowns at the top.
They're now called filter. They would turn off a couple of things that might be important to you. Custom views goes away once you use the table functionality. So, if you find the table functionality just annoying the heck out of you because it turns off something that you want, then I have a strategy.
Go in here and use the formatting, and then come back to TABLE TOOLS, DESIGN, and say CONVERT TO RANGE. When you convert to a range, it turns off all the cool table tools but it leaves behind the formatting. Now, the formatting is no longer interactive. If I insert a row, it's still going to act just like it did on Monday and Tuesday, but still this might be a very fast way to apply some formatting and then get rid of the table functionality in case some of those features are causing problems for you.
Alright. Well, hey, I want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.
Well, all week, we've been talking about Lisa's question of how to format every other row.
Remember, on Monday, we talked about how to use the format autoformat, ALT+O A, and applied 2 rows of green, 2 rows of white, on Tuesday, Lisa's trick of using the fill handle where we could use any 2 colors we wanted, and then, on Wednesday, conditional formatting basically chose the one color for the odd number of rows. On Thursday, we had the 54 formats in the table * 20 different colors, so a whole bunch of different options there, but what if we want to use that table functionality but we want to replicate what we had back on Monday -- 2 rows of green and 2 rows of white or something like that? The table functionality actually has those 54 built-in formats but many more formats are possible. I'll show you how to do it.
I'm going to use my shortcut key of CONTROL+T to turn on the table. Click OK. That's because I'm going to go in and create my own table style. So, all the way at the bottom of this gallery is something called NEW TABLE STYLE.
I'm going to format this. I want to go to the FIRST ROW STRIPE and say that that first row is 2 rows tall, choose FORMAT, and I'll choose a color. Let me go with this green here. Click OK, and then I’ll go to SECOND ROW STRIPE and say that that is 2 rows tall.
I'll choose FORMAT, and let me choose something else, maybe this light yellow here. Click OK. Alright. You can see the preview now.
We've done green, yellow, green, yellow.
If I wanted this to be the format that I got everywhere in this document, I could set this as the default table style. That would be what I would get for CONTROL+T. As a matter of fact, let's go ahead and do that, click OK. Alright.
Now, setting up that style doesn't change the table that I'm in because I've already declared that table, but if I come back to the gallery, I now have the CUSTOM, and I can choose CUSTOM and, very quickly, we've assigned alternating rows of green and yellow.
So, as you can imagine here, you can set up the first row stripe however you'd like, 1 row, 2 rows, 3 rows, 4 rows, up to 8, I think, and the second row stripe however you like, so you actually have absolute control here over how we do that formatting. We're not stuck with the 54 formats they built in for us. Great feature that allows us to very quickly go through and format the table.
Now, let me tell you. This table functionality, there's a couple of weird things with it.
We get the autofilter dropdowns at the top.
They're now called filter. They would turn off a couple of things that might be important to you. Custom views goes away once you use the table functionality. So, if you find the table functionality just annoying the heck out of you because it turns off something that you want, then I have a strategy.
Go in here and use the formatting, and then come back to TABLE TOOLS, DESIGN, and say CONVERT TO RANGE. When you convert to a range, it turns off all the cool table tools but it leaves behind the formatting. Now, the formatting is no longer interactive. If I insert a row, it's still going to act just like it did on Monday and Tuesday, but still this might be a very fast way to apply some formatting and then get rid of the table functionality in case some of those features are causing problems for you.
Alright. Well, hey, I want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.
