A Guide to Use Excel Color Alternate Rows: Easy & Fast

August 4, 2023 1.2K views

In Excel, efficient data organization is vital, and the Excel color alternate rows feature plays a pivotal role in achieving just that. However, many users need help with how to use it effectively. If you've ever wondered how to easily and quickly apply color alternate rows in Excel, this article will present simple and practical methods to master this feature. As a bonus, we'll also introduce WPS Office as an excellent alternative for seamless productivity.

2 Easy Ways to Use Color Alternate Rows in Excel:

Excel's color alternate rows feature provides a visually appealing way to present data, making it easier to read and comprehend. Let's explore two straightforward methods to apply Excel color alternate rows in your worksheets.

Highlight Every Other Row Using Banded Rows:

When you want to format your data with alternating row colors quickly, Excel's banded rows feature comes to the rescue. When you're working with tables and want to make your data stand out amidst a barrage of other data streams, this is the method you want to use to do so.

Step 1: Open your Excel worksheet and select the range of cells to apply the alternating row colors, then navigate to the "Home" tab on the Excel ribbon.

Step 2: Look for the "Format as Table" option in the "Styles" group and choose a table style that suits your preferences. This action will automatically apply alternating row colors to the selected range.

By default, Excel uses a light gray shade for alternate rows, but you can customize the colors. This simple method lets you quickly achieve Excel color alternate rows without the table’s complex formulas or conditional formatting.


Consider a sales data table with columns for "Product Name," "Quantity Sold," and "Revenue." By applying banded rows, you can easily distinguish one product's data from another, making it more convenient to analyze and comprehend the information.

Alternate Row Color Shading Using Conditional Formatting:

Conditional formatting in Excel is a powerful tool that allows you to apply formatting based on specific conditions. This method is suitable when you want more control over the colors and must format cells based on specific criteria.

Step 1: Narrow down the cells where you want to apply Excel color alternate rows and select that range. Then go to the "Home" tab on the Excel ribbon.

Step 2: Click "Conditional Formatting" in the "Styles" group and choose "New Rule" from the drop-down menu.

Step 3: Navigate to the "New Formatting Rule" box, and then position your cursor over the options which says: "Use a formula to determine which cells to format."

Step 4: Enter the following formula: `=MOD(ROW(), 2) = 1`, then click the "Format" button to choose the desired fill color for the odd rows. Press "OK" to apply the conditional formatting.

Step 5: Repeat Steps 2 to 4, but this time, use the formula `=MOD(ROW(), 2) = 0` to format the even rows with a different color.

Step 6: Step back to appreciate your end result or rework the steps to change the colors or whichever rows you intend to change in accordance to the data collected.


Suppose you have a budget spreadsheet with expense categories listed in one column and corresponding expenses in the next column. You can use conditional formatting to shade alternate rows, making it easier to differentiate between different expense categories and track your budget efficiently.

Benefits of Using Color Alternate Rows Formatting in Excel:

Utilizing color alternate rows formatting in Excel offers numerous benefits that contribute to better data presentation and analysis. Let's explore the advantages of employing this formatting technique:

  • Enhanced Readability:

Excel Color alternate rows provide a clear visual distinction between different rows, reducing eye strain and making it easier to scan and comprehend data. This enhanced readability is particularly helpful when dealing with large datasets.

  • Improved Data Visualization:

By applying different shades to alternate rows, you create a pattern that visually organizes your data. You can ensure that the person poring over your data is able to identify various outliers, trends, and patterns far more easily, making for better data visualization and analysis.

  • Easy Data Comparison:

When data is displayed with Excel color alternate rows, it becomes simpler to compare information across different rows. Users can easily spot differences between data points, facilitating quick decision-making.

  • Highlighting Important Rows:

Color alternate rows can be used to highlight specific rows that require attention or hold significant information. When working on tables or reports where certain rows require emphasis, this feature becomes especially indispensable.

  • Professional Presentation:

Employing color alternate rows formatting in Excel adds a touch of professionalism to your documents and reports. The visual appeal of well-organized data enhances the overall presentation of your work.

  • Reduced Errors:

With shade color alternate rows, it's less likely for users to lose their place or make errors when reading through rows of data. The clear distinction between rows reduces the risk of data misinterpretation.

Shortcut to Apply Color Alternate Rows in Excel:

To efficiently apply color alternate rows in Excel, you can use the following shortcut:

Shortcut: Alt + H + H

By pressing these keys simultaneously, you can quickly implement Excel color alternate rows in your selected range of cells. This time-saving shortcut ensures that you can efficiently format your data without navigating through multiple menu options.

Best Alternative - WPS Office:

What is WPS Office?

WPS Office is a comprehensive office suite that rivals Microsoft Office, providing users with a powerful set of tools for word processing, spreadsheet management, and presentation creation. As an alternative to Microsoft Office, WPS Office ensures seamless compatibility, allowing users to open, edit, and save files in formats such as DOCX, XLSX, and PPTX without any compatibility issues.

Clear Guidance and Problem-Solving:

WPS Office emphasizes user-friendliness, offering a user interface that is intuitive and easy to navigate. Whether you are a seasoned Excel user or a beginner, WPS Office provides clear guidance, ensuring that you can efficiently work with Excel color alternate rows and other advanced features.

Use Color Alternate Rows Formula in WPS Office:

WPS Office enables you to apply color alternate rows in your spreadsheets easily. Here's a step-by-step guide to get you started:

Step 1: Launch WPS Office and open the spreadsheet where you want to implement shade color alternate rows.

Step 2: Select the range of cells where you wish to apply the color alternate rows.

Step 3: Go to the "Home" tab on the WPS Office ribbon and locate the "Conditional Formatting" or “Format as Table” options in the toolbar, and follow the steps provided for Excel color alternate rows methods. WPS Office will apply alternating row colors to the selected range as required.



WPS Office- Free All-in-One Office Suite
  • Use Word, Excel, and PPT for FREE, No Ads.

  • Edit PDF files with the powerful PDF toolkit.

  • Microsoft-like interface. Easy to learn. 100% Compatibility.

  • Boost your productivity with WPS's abundant free Word, Excel, PPT, and CV templates.

5,820,008 User


Algirdas Jasaitis

Suitable for New Users:

WPS Office caters to users of all levels, making it particularly suitable for those new to spreadsheet software. With a user-friendly interface and simplified procedures, users can easily grasp how to utilize color alternate rows and other formatting options to enhance data visualization and analysis.


1. What are the benefits of using Excel color alternate rows?

Color alternate rows offer enhanced data visualization, making it easier to read and analyze information. By applying distinct colors to alternate rows, users can quickly identify patterns and trends within their data, leading to more efficient decision-making and improved data presentation.

2. What is the shortcut to apply Excel color alternate rows?

To swiftly apply color alternate rows in Excel, use the shortcut Alt + H + H. This time-saving combination allows users to format their data seamlessly without navigating through multiple menus.

3. How can you apply color alternate rows formula based on group in Excel?

Excel's conditional formatting enables users to apply Excel color alternate rows based on specific grouping or criteria. By creating a formula that considers the grouping identifiers, users can tailor the alternate row colors to their specific needs, resulting in a more organized and visually appealing spreadsheet.

4. Is WPS Office compatible with Microsoft Office for collaborative work?

Absolutely! WPS Office ensures seamless compatibility with Microsoft Office, enabling smooth collaboration and file sharing between users of both office suites. You can confidently work with others, regardless of which office suite they use.

The Bottom Line

Mastering the art of Excel color alternate rows empowers users to present data more effectively, providing clear visual cues that aid in data analysis and interpretation. Whether you choose to apply this formatting using Excel's banded rows or conditional formatting, the result is an organized and visually appealing spreadsheet.

So, embrace the power of Excel color alternate rows, seize the potential of WPS Office, and unlock a world of efficient data organization and visualization in your Excel journey.

15 years of office industry experience, tech lover and copywriter. Follow me for product reviews, comparisons, and recommendations for new apps and software.