MrExcel's Learn Excel #931 - Greenbar Formatting

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on Jan 19, 2009.
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!
maxresdefault.jpg


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.
 

Forum statistics

Threads
1,213,521
Messages
6,114,104
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top