Autoplay Next video from Max length

Excel - Learn How to Format Every Other Row in Excel with this Quick and Easy Method - Episode 931

965 views · Published 19 January 2009 · 3:03 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Microsoft Excel Tutorial - Learn How to Format Every Other Row in Excel with this Quick and Easy Method.

Welcome to another MrExcel netcast! In this episode, we'll be discussing a common formatting issue that many Excel users face - how to format every other row in a spreadsheet. This can be especially helpful when dealing with large data sets, as it makes it easier to read and analyze the information.

Recently, I received a voicemail from Lisa who was struggling with this exact problem. Her boss had a massive spreadsheet and wanted every other row to be formatted differently. After brainstorming different solutions, I came up with a simple method that I'll be sharing with you today.

We'll be using an easy Excel 2003 method, even though we're in Excel 2007. First, select all of the data in your data set by pressing CONTROL* and then open the old AUTOFORMAT dialog box. This used to be found under the FORMAT menu, but now it's accessible by pressing ALT+O and then A.

In the AUTOFORMAT dialog box, you'll see various options. Scroll down until you find LIST 1, which will alternate rows of gray and white, or LIST 2, which will alternate 2 rows of green and 2 rows of white. This is similar to the old greenbar formatting used in high-speed IBM line printers.

Before applying the format, click on the OPTIONS button and uncheck everything except for PATTERNS. This will ensure that only the patterns are applied and not any other formatting changes. Then, select the desired LIST option and click OK. In just a few seconds, you'll see that every other row is now formatted in the chosen pattern.

One thing to keep in mind is that if you insert rows later on, it may cause issues with the formatting. But don't worry, we'll cover how to solve this in a future episode. This method is a quick and easy way to format every other row in a spreadsheet, and I hope it helps you in your Excel journey. Thanks for watching and be sure to tune in for more netcasts from MrExcel!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/

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.

Update from 2022: There is a faster way to solve this using DOSE for Excel. Watch the newer video at https://youtu.be/WbJd21TEJQQ

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!

Table of Contents:
(00:00) Introduction
(00:19) Formatting every other row
(00:29) Using the old AUTOFORMAT dialog box
(01:03) Applying the format
(01:41) Limitations of this method
(02:22) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial

This video answers these common search terms:
Applying alternating colors to rows in Excel
Applying patterns only in Excel formatting
Avoiding unwanted changes when formatting rows in Excel
Excel 2003 method for formatting rows
Format every other row in Excel
How to use the AUTOFORMAT dialog box in Excel
MrExcel netcast on formatting rows in Excel
Problems with formatting when inserting rows in Excel
Quick and easy way to format rows in Excel
Solving formatting issues in Excel
Tips for easier reading in Excel spreadsheets with alternating row colors
Using the LIST 2 option in Excel AUTOFORMAT

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152093/

Excel - Learn How to Format Every Other Row in Excel with this Quick and Easy Method - Episode 931

More from this channel