Making alternate color odd rows in excel, but have header first row in another color

[Sossity]

I have spent about two hours trying to find an answer to this, maybe it has never been done before.

what I would like to do is have alternating color in rows, and have the header row in another separate color, nowhere online is this explained, it is all just alternating rows.

I have done the formulas for alternating rows, but then, I cannot change the color of the very first top row of which I make as my header.

so it would go like this; in spreadsheet; the whole row one; in a medium pink, 2and row; white; 3rd row light pink; 4th row white, and so on.

[Richard Buttrey]

Have you considered the 'Format as Table' in the Styles area of the Ribbon? This allows various pyjama stripe combinations.

What formula have you used? And I assume you're trying to use that in a conditional format for the rows. The usual way is to use the

Please Login or Register  to view this content.

structure. And if you don't want that to apply to the header row then just exclude the header row from the conditional format.

[Sossity]

how do I exclude the header row from the conditional format?? yeah, I have messed with the format as table styles, but it did not really work or get what I wanted.

So I have done the conditional format, but just have not found a way to exclude the header row.

[Richard Buttrey]

Well perhaps stating the obvious but where are you applying the CF and why apply it to the header row.

There are a couple of ways to set CFs. I prefer to create it on the first row of the data to which I want it applied and then when it comes to the 'Apply To' input box simply set the relevant range.

The other way is to set the CF in a cell and then copy and Special Paste formats to the range required.

Upload your workbook for a more definitive answer.

[Sossity]

Screen Shot 2017-04-02 at 4.02.51 AM.png

I tried to upload a screenshot of how I want it too look, dont know how to upload the workbook. The first darker colored row is the header, and this would have text and headings. The rest would have the data.

I am a bit new to excel, so things are not obvious to me, I need things spelled out and explained.

thanks so far for the help.

Alternating Colors.xlsx here is the workbook.

[Aardigspook]

To apply the CF only to the second to nth row, select just those rows before applying the Conditional Formatting (CF).
Let's say your table is in A1:Z100, with the header row in row 1. Select the range A2:Z100, then click 'Conditional Formatting' → 'New Rule' → 'Use a formula to determine which cells to format'. In the 'Format values where this formula is true' box, enter this formula:

Please Login or Register  to view this content.

Click the 'Format' box and choose your preferred light-pink colour.

That should do what you want.

Edit: with the file you uploaded, the only change you need to the instructions above is to select A2:Z1000 as the range.

[Richard Buttrey]

In A2 use the Formula Is option with the formula

Please Login or Register  to view this content.

and set the light blue colour.

and apply it to $A$2:$Z$100 or whatever

See attached.

[Sossity]

In A2 use the Formula Is option with the formula

Please Login or Register  to view this content.

and set the light blue color.

and apply it to $A$2:$Z$100 or whatever

See attached.

Thank you, my only issue no is if I insert a new row below the first row to enter in new data, (I like having my rows of info in ascending order, from the most recent on top, oldest at the bottom), it throws off the whole patterning. The patterning seems to only work if one is just inputting text data onwards down in the rows, if one inserts any new rows in the middle of the spreadsheet somewhere, it throws the whole patterning off.

[Richard Buttrey]

I don't see that behaviour.

The CF only uses the row number not any refence to any cell values text or otherwise.

Are you sure the CF when you set it up was applied to the whole range? Adding a new row in the middle of the data and resorting the data should not change anything. The rows will still be pyjama striped. Upload the workbook if you think this is still a problem.

[JohnTopley]

See atached

used

=MOD(ROW(),2)=1

[Aardigspook]

See atached
used
=MOD(ROW(),2)=1

For instructions on setting it up yourself, see my previous post (#6).

[Sossity]

To apply the CF only to the second to nth row, select just those rows before applying the Conditional Formatting (CF).
Let's say your table is in A1:Z100, with the header row in row 1. Select the range A2:Z100, then click 'Conditional Formatting' → 'New Rule' → 'Use a formula to determine which cells to format'. In the 'Format values where this formula is true' box, enter this formula:

Please Login or Register  to view this content.

Click the 'Format' box and choose your preferred light-pink colour.

That should do what you want.

Edit: with the file you uploaded, the only change you need to the instructions above is to select A2:Z1000 as the range.

Ok, I think I see where or what went wrong, how do I select this range A2:Z100, this might be simple to all here, but I am new to this, so I need to actually see how this part is done, it might be tricky for me to drag my pointer all the way across the whole range. I looked it up online, and all just show how to select only a few cells.

[Richard Buttrey]

Ok, I think I see where or what went wrong, how do I select this range A2:Z100, this might be simple to all here, but I am new to this, so I need to actually see how this part is done, it might be tricky for me to drag my pointer all the way across the whole range. I looked it up online, and all just show how to select only a few cells.

You can either select the range first before applying the CF, in which case the 'Applies to' section of the CF will already be filled in, or select a single cell and manually edit the 'Applies to' section.

[AliGW]

I must be missing something, but it took me two clicks to get this (no CF required):

Excel 2016 (Windows) 32 bit

	A	B	C	D	E	F	G
1	Column1	Column2	Column3	Column4	Column5	Column6	Column7
2
3
4
5
6
7
8
9

Sheet: Sheet1

[Aardigspook]

Ok, I think I see where or what went wrong, how do I select this range A2:Z100, this might be simple to all here, but I am new to this, so I need to actually see how this part is done, it might be tricky for me to drag my pointer all the way across the whole range. I looked it up online, and all just show how to select only a few cells.

You can either select the range first before applying the CF, in which case the 'Applies to' section of the CF will already be filled in, or select a single cell and manually edit the 'Applies to' section.

First method - select the range first then do the CF:
Option 1: left-click once in cell A2, then use the scroll bars to move until you can see Z1000, hold down Shift then left-click in Z1000.
Option 2: use the box to the left of the formula bar. Click in the box and type 'A2:Z1000' (without the inverted commas) then press Enter.
Regardless of which method you use, the range will then be selected, which will be visible by the cells being surrounded by a black border and shaded a light blue (this border and shading are just to let you see the selection - they aren't permanent).
You can then apply the CF using the instructions above (CF → New Rule → Use a formula... → =MOD(ROW(),2)=1 → choose colour).

Second method - do the CF to one cell then expand the range
Select just cell A2 (left-click it once or use the arrow keys to move to it).
Apply the CF using the instructions above (CF → New Rule → Use a formula... → =MOD(ROW(),2)=1 → choose colour).
Now, with A2 still selected, click 'Conditional Formatting' then 'Manage Rules'.
You'll see the rule you just did. On the right-hand side is a box labelled 'Applies to' in which you'll see '=$A$2'.
Click in that box and change it to read '=$A$2:$Z$1000'. Click OK.

Hope that helps.


@AliGW:
The OP said in post#3 that he didn't want a table.

[Sossity]

I don't see that behaviour.

The CF only uses the row number not any refence to any cell values text or otherwise.

Are you sure the CF when you set it up was applied to the whole range? Adding a new row in the middle of the data and resorting the data should not change anything. The rows will still be pyjama striped. Upload the workbook if you think this is still a problem.

I would attach the file, but I don't know how to upload the spreadsheet

I will try to describe the problem, I tired to mimic what you did, but when I try to insert a new row just below the colored header (that is set to a separate color from the rest of the stripes), the row below gets duplicated like the header, it does not keep to the pattern. In your spreadhseet it does; when I insert a new row just below the colored header row, the rest of the patterning is retained, it does not just make another colored header row.

[Richard Buttrey]

[U]


@AliGW:
The OP said in post#3 that he didn't want a table.

Maybe a subject for the Water Cooler but I always wonder why people don't want to use this or that. Pivot tables, Tables, VBA, CFs, array formualae...etc.

No doubt unfamiliarity with and comfort zones come into play. But presumably people come here to develop their skills so why mandate not this/that?

[Sossity]

First method - select the range first then do the CF:
Option 1: left-click once in cell A2, then use the scroll bars to move until you can see Z1000, hold down Shift then left-click in Z1000.
Option 2: use the box to the left of the formula bar. Click in the box and type 'A2:Z1000' (without the inverted commas) then press Enter.
Regardless of which method you use, the range will then be selected, which will be visible by the cells being surrounded by a black border and shaded a light blue (this border and shading are just to let you see the selection - they aren't permanent).
You can then apply the CF using the instructions above (CF → New Rule → Use a formula... → =MOD(ROW(),2)=1 → choose colour).

Second method - do the CF to one cell then expand the range
Select just cell A2 (left-click it once or use the arrow keys to move to it).
Apply the CF using the instructions above (CF → New Rule → Use a formula... → =MOD(ROW(),2)=1 → choose colour).
Now, with A2 still selected, click 'Conditional Formatting' then 'Manage Rules'.
You'll see the rule you just did. On the right-hand side is a box labelled 'Applies to' in which you'll see '=$A$2'.
Click in that box and change it to read '=$A$2:$Z$1000'. Click OK.

Hope that helps.


@AliGW:
The OP said in post#3 that he didn't want a table.

Thanks that clears things up a bit more, is there a way to add into in the conditional formatting to set the header to another color from the rest of the striping/alternating colors?

[Aardigspook]

I would attach the file, but I don't know how to upload the spreadsheet

Click 'Go Advanced' under the reply box then scroll down to 'Manage Attachments'.

I will try to describe the problem, I tired to mimic what you did, but when I try to insert a new row just below the colored header (that is set to a separate color from the rest of the stripes), the row below gets duplicated like the header, it does not keep to the pattern. In your spreadhseet it does; when I insert a new row just below the colored header row, the rest of the patterning is retained, it does not just make another colored header row.

By default, when you enter a new row, Excel applies the formatting from the row above. When you insert it, you'll see a little button pop up with a paintbrush symbol on it. Hover your mouse over it and a little down arrow will appear, plus a label saying 'Insert Options'. Left-click and you'll get three options: 'Format Same as Above', 'Format Same as Below' and 'Clear Formatting'. Choose '...Below' to have the CF-striping applied to the row you insert below the header.

Edit: here's a pic of the 'Insert Options' button

[Aardigspook]

Thanks that clears things up a bit more, is there a way to add into in the conditional formatting to set the header to another color from the rest of the striping/alternating colors?

If you only apply the CF to the range A2:Z1000 then you can just format the first row however you want - but you'll then run into the problem you've mentioned of rows inserted below the header being formatted like it. My previous answer describes how to get round that, but there is another option:
Do two CF rules.
First do the rule previously described (=MOD(ROW(),2)=1), but apply it to the whole range (A1:Z1000).
Then do a second rule, using this formula instead:
=ROW()=1
Choose the different colour you want - this will only apply to the first row. Now it doesn't matter if the inserted row takes the format of the header, because only the first row will be coloured.


I should point out that this would all be a lot easier with a table, as pointed out by others. You can choose from a lot of different styles or even create a new custom style if you want.

Attached is a file showing the CF-solution on Sheet1, a standard table style on Sheet2 and a custom table style on Sheet3. I've only formatted a small range just to show how it works.

[vlady]

how about this one
select all

forum.gif

then CF
=AND(ROW()<>1, ODD(ROW())=ROW()) -> this will leave row 1 untouched but the other odd rows will have CF

then

=EVEN(ROW())=ROW() -> CF all even rows

[Sossity]

Attached is a file showing the CF-solution on Sheet1, a standard table style on Sheet2 and a custom table style on Sheet3. I've only formatted a small range just to show how it works.

Thanks this finally got my sheets how I wanted them for now.

what conditional formatting formula would I use if I wanted to make the last row of the sheet a different color, like a footer row like I did the header row?

I tried doing the tables in excel, to a sheet with data already in it, and when I hit apply, it did not seem to do anything, all it did seem to do was turn my header text white so it was unreadable on the all white sheet. Do I have to set up a blank sheet first with the table effects, and then input my data?

[Aardigspook]

what conditional formatting formula would I use if I wanted to make the last row of the sheet a different color, like a footer row like I did the header row?

You could use this one:

Please Login or Register  to view this content.

The column reference needs to be a column with something in the last row - there doesn't need to be something in every row in the column, as long as there's always going to be something in the last row. If that column isn't A, just replace both $A:$A ranges in the formula with $B:$B or whatever.

I tried doing the tables in excel, to a sheet with data already in it, and when I hit apply, it did not seem to do anything, all it did seem to do was turn my header text white so it was unreadable on the all white sheet. Do I have to set up a blank sheet first with the table effects, and then input my data?

No, you don't need to set up the table first.

Creating a Table

1. Click anywhere in the data you have.
2. On the Home tab, click 'Format as Table' which will bring up a whole range of possible styles for your table. Pick whichever one looks closest to what you want.
3. A window will pop-up to confirm the range for the table data. Since your data has a header row, make sure you tick the box labelled 'My table has headers', then click OK.
4. Your data will now be formatted as an Excel table. The table will look like whatever style you chose.

The header row will now have drop-down arrows in each cell, which you can use to filter and/or sort.

There will be a little blue triangle in the bottom-right corner of the bottom-right cell which you can use to expand/contract where the table data is. But you don't need to do this manually - by default tables will expand to include new rows/columns (you can turn this off in Excel Options, but I suggest you don't).

TableTools Design ribbon tab

When you click anywhere in the Table you'll see a 'Table Tools Design' tab appear on the ribbon.
Here's where you can:

• Rename the Table;
• Convert the data back to a non-Table range if you want;
• Turn on/off the Header Row, the Total Row, Banded Rows, etc;
• Change the Table style.

The 'Total Row' option will insert a special row at the bottom of your table. Here you can apply various calculations very simply to each column - Sum, Average, Max, etc - using the drop-down button which will appear if you click into one of the Total Row cells.

If none of the Table styles do exactly what you want, you can create your own style. Beside the inbuilt styles, click the bottom button (looks like a CD/DVD 'eject' button upside-down) and select 'New Table Style'. In the window which pops up, give your new style a name (default will be 'Table Style 1' which isn't very useful!). You can individually format lots of elements of the Table, including Header Row, row/column striping (banding), etc.

Table Refs

References to Tables / Table Rows/Columns/Cells look a bit different from normal references, but can make things a whole lot easier. Let's say you have a list of prices in column B, from row 2 to row 99, on another sheet. Normally, to add these up, you'd use =SUM('Other sheet'!C2:C99). If instead the data were in a Table called 'Sales', the formula would be: =SUM(Sales[Prices]) and would automatically update when new rows were added, which is more complicated to do using normal formulae.



I suspect that if you have a look at what you can do with a Table, you'll find that it might be what you want/need for your spreadsheet. This MS support page gives a good introductory overview: https://support.office.com/en-gb/art...9-6C94334E492C


Hope that all helps.

[Sossity]

Thanks for the lengthy explanation, still had a little trouble, I found I have to remove any previous formatting I have, and then the table formatting takes place. Also, add rows and add columns options are greyed out; I tried right clicking as per Ms support page, no options, they are greyed out.

[AliGW]

@ Sossity

Please don't quote whole posts - it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding

For normal conversational replies, try using the QUICK REPLY box below.