Lisa asks how to shade every other row in the worksheet. While there are several solutions, Episode 931 shows you how to use the old AutoFormat dialog to solve this problem.
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:
Welcome back to the MrExcel netcast. I'm Bill Jelen. I got a voicemail a few days ago from Lisa.
Lisa said, hey, I need to format every other row in the spreadsheet.
My boss has this huge spreadsheet.
He wants every other row formatted to make it easier to read, and while I was sitting there thinking about all of the different ways to solve this problem and trying to figure out which one would be the easiest to describe, I came up with this method initially.
Now, we're in Excel 2007, but we’re going to use a very easy Excel 2003 method.
I'm going to choose all of the data in my data set -- so that's CONTROL* to choose all of the contiguous data there -- and I'm going to use the old AUTOFORMAT dialog box.
Now, that used to be on the FORMAT menu and then AUTOFORMAT.
It was ALT+O to open FORMAT and then A to open AUTOFORMAT.
They've taken this out of the Excel 2007 ribbon but the old shortcut key will still get us there.
So, we hit ALT+O and then A, and we get the old AUTOFORMAT dialog box.
Now, we end up with several different options here, and if we scroll down, you see this one here, LIST 1, is alternating rows of gray and white.
LIST 2 is 2 rows of green, 2 rows of white.
This is reminiscent of the old greenbar formatting that we used to use back in the high speed IBM line printer, 132 characters wide, and it would alternate green and white to make things easier to read.
Okay, now, before I choose this though, I don't want to just apply that whole format because it's going to apply dark green to the headings.
It's going to change my fonts.
It's going to change my column widths.
Really, it's going to do a lot more than I wanted to do.
So, I click the OPTIONS piece and I uncheck everything except for PATTERNS.
All I want to do is apply the patterns, and I'll choose LIST 2, we’ll click OK, and, very quickly, you can see that we've now set up an alternating green and white -- first 2 rows in green, next 2 rows in white, next 2 rows in green, next 2 rows in white -- and we're good to go.
Fast, fast way to go.
Not difficult to master at all.
You know, just choose FORMAT, AUTOFORMAT, and you’re good.
Now, the one downside to this is that if we later insert a few rows, it's going to cause problems in the formatting.
We'll talk about how to solve that Wednesday this week a couple of episodes from now, but a quick, quick way to go.
Alright.
Hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Lisa said, hey, I need to format every other row in the spreadsheet.
My boss has this huge spreadsheet.
He wants every other row formatted to make it easier to read, and while I was sitting there thinking about all of the different ways to solve this problem and trying to figure out which one would be the easiest to describe, I came up with this method initially.
Now, we're in Excel 2007, but we’re going to use a very easy Excel 2003 method.
I'm going to choose all of the data in my data set -- so that's CONTROL* to choose all of the contiguous data there -- and I'm going to use the old AUTOFORMAT dialog box.
Now, that used to be on the FORMAT menu and then AUTOFORMAT.
It was ALT+O to open FORMAT and then A to open AUTOFORMAT.
They've taken this out of the Excel 2007 ribbon but the old shortcut key will still get us there.
So, we hit ALT+O and then A, and we get the old AUTOFORMAT dialog box.
Now, we end up with several different options here, and if we scroll down, you see this one here, LIST 1, is alternating rows of gray and white.
LIST 2 is 2 rows of green, 2 rows of white.
This is reminiscent of the old greenbar formatting that we used to use back in the high speed IBM line printer, 132 characters wide, and it would alternate green and white to make things easier to read.
Okay, now, before I choose this though, I don't want to just apply that whole format because it's going to apply dark green to the headings.
It's going to change my fonts.
It's going to change my column widths.
Really, it's going to do a lot more than I wanted to do.
So, I click the OPTIONS piece and I uncheck everything except for PATTERNS.
All I want to do is apply the patterns, and I'll choose LIST 2, we’ll click OK, and, very quickly, you can see that we've now set up an alternating green and white -- first 2 rows in green, next 2 rows in white, next 2 rows in green, next 2 rows in white -- and we're good to go.
Fast, fast way to go.
Not difficult to master at all.
You know, just choose FORMAT, AUTOFORMAT, and you’re good.
Now, the one downside to this is that if we later insert a few rows, it's going to cause problems in the formatting.
We'll talk about how to solve that Wednesday this week a couple of episodes from now, but a quick, quick way to go.
Alright.
Hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
