A reader was asking about whether we know of a method or formula to highlight every other row and column in Excel. Today, we’ll learn how to apply different color schemes to alternate rows or columns in Excel. How do i shade every other row in excel 2010 generated on lbartman.com show printable version!!! Hide the show to save images bellow, right click on shown image then save as.png. Alternate Row Shading Using Conditional Formatting. Category: Formatting| One way to make your data legible is to apply cell shading to every other row in a range. Excel's Conditional Formatting feature (available in Excel or later) makes this a simple task. Select the range that you want to format; Choose Format, Conditional Formatting. ![]() There are several ways to alternate row colors, but today, we will focus on two main ways to do that: using conditional formats and using table designs. Using conditional formatting: • At first we need to select the rows that should be shaded using conditional formatting. • Now go to the home ribbon, click on the drop down box of conditional formatting and select new rule. • In select a rule type, hit use a formula to determine which cells to format. • In the format values where formula is true, enter the formula =mod(row(),2)=0. • Then click on the format, go to fill and set a background color (I assigned it to green). • The formula describes when modulus of row (1 to n) divided by two = zero fill the background color green. • Modulus is the remainder value in a division. • So from the selected rows 2,4,6 will have the background color green. • Thus alternate rows shading with conditional formatting can be done. Fill alternates using Using Table style: • We can also use table style format instead of using condition formatting. • To do so, select the cells that has to be formatted and click on Format as table in the home ribbon and select anyone of the needed table format. • If u already have a header in the selected cells, check on the My table has headers. • In the table style options, we can check or uncheck on the options provided. I sometimes wonder whether Microsoft spends so much on its programming budget it has little left over to spend on designers. Using outlook on a mac. This thought often crosses my mind when working with Office sample files, templates and styles. Take, for example, the auto-formats supplied with pre-2007 versions of Excel. Who designed these things? Not only are they ugly, they also don’t address basic formatting needs such as coloring every other row in order to make a table more readable. Yes, there’s one auto-format option which gives you this – sort of – but it comes with unattractive headings and footer formatting and it’s inflexible. The solution? Do the job yourself, using conditional formatting. Conditional formatting lets you apply formatting based on the value contained in a cell or based on the result of a formula. Here’s how you can use it to apply shading to alternate rows: • Select the range of cells you wish to format. • Click Format -> Conditional Formatting. • In the drop-down box, select Formula Is. • In the box type: =MOD(ROW(),2)=0 • Click the Format button and then click the Patterns tab. • Select a color for the shaded rows and click OK twice. There you have it: a table with banded colors. If you find you don’t like your chosen color, simply select the range once more, open the Conditional Formatting box and choose another color. The real beauty of this method is that even if you delete a row in your table, the banding will be maintained automatically. Formatting in Excel 2007 If you’re using Excel 2007, you have a whole range of new formatting tools at your disposal, including the alternate row shading offered through the Format As Table options. I’ll be writing more about these options in the future. Nevertheless, you can still roll your own conditional formats if you choose: from the Home tab, click Conditional Formatting -> New Rule -> Use A Formula To Determine Which Cells To Format and then use the same formula described in Step 4 above to create banding.
0 Comments
Leave a Reply. |