Jump to content


Photo

Alternating row colors in Excel

color background color conditional formatting advanced filters INT ROW MOD tables table styles

  • Please log in to reply
4 replies to this topic

#1 MigMig

MigMig

    Newbie

  • Members
  • Pip
  • 5 posts

Posted 21 July 2015 - 04:10 AM

How do I alternate color my worksheet just like in accounting columnar paper?



#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 888 posts

Posted 21 July 2015 - 09:37 AM

Hi MigMig,

 

The easiest way to do this is to create a Table within Excel. Tables automatically color every alternate row differently, and you can customize a table's style to change these colors however you wish.

 

You can learn how to create a Table in the video lesson: Expert Skills Lesson 1-11 Convert a range into a table and add a total row.

You can see how to customize Table styles in the video lesson: Expert Skills Lesson 1-13 Create a custom table style.

 

I have also attached an example workbook showing how a Table is displayed:

 

Attached File  TableExample.xlsx   10.04KB   159 downloads

TableExample.png

 

I hope this is what you were looking for, but please feel free to reply if you need any further assistance.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#3 MigMig

MigMig

    Newbie

  • Members
  • Pip
  • 5 posts

Posted 21 July 2015 - 12:57 PM

Is there anything else other than creating a table to accomplish this task?



#4 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 888 posts

Posted 21 July 2015 - 01:57 PM

Hi MigMig,

 

You could do this by formatting the cells manually, but it would take a very long time.

 

Note that you can convert a Table back into an ordinary Range again and the formatting will remain, so you aren't required to keep the table if you don't want it.

 

You can see how to convert a Table back into an ordinary Range in the video lesson: Expert Skills Lesson 1-12 Format a table using table styles and convert a table into a range.

 

As usual, please feel free to reply if you have any further questions.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#5 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 888 posts

Posted 21 July 2015 - 05:53 PM

One more thing, you could also do this by using Conditional Formatting to format every other row.

The basics of conditional formatting are explained in the video lesson: Essential Skills Lesson 4-15 Use simple conditional formatting.

 

To shade every other row, you'll need to create a formula-driven conditional format. You can see a walkthrough of creating one of these in: Essential Skills Lesson 4-18 Create a formula driven conditional format.

 

You could use either one of the following formulas to shade every other row:

=ROW()/2=INT(ROW()/2)

=MOD(ROW(),2)=0

 

The INT, ROW and MOD functions are all explained in the video lesson: Expert Skills Lesson 1-7 Apply an advanced filter with function-driven criteria.

 

I have attached another example workbook showing this approach in action:

 

Attached File  AlternateRowColored.xlsx   7.7KB   182 downloads

AlternateRowColored.png


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users