Tricks for MS Excel: Add a frozen header row and set alternate background colors for rows

Here are couple of tricks for MS Excel users.

First we take a look at how to add a “Frozen” header row. Frozen header row is the one you could have for a very long worksheet. This row will be frozen (to act as the column header) and rest of the rows will scroll. Here’s how to do this.

1. Select the row below the one you want to be the header (or be frozen)

2. From Window menu select Freeze Panes (for MS Excel 2007 click on the View tab and inside Window panel click on Freeze Panes and pick the first option which is also Freeze Panes)

The frozen row will have a border at the bottom. Simple but an essential option for MS Excel users.

Second trick is the alternate background color for rows in the MS Excel worksheet.

1. Select the rows

2. From the Format menu go to Conditional Formatting (or in MS Excel 2007 under Home tab click on Conditional Formatting from Styles panel and click on New Rule)

3. When the window appears select Formula Is… (or on MS Excel 2007 select the last option Use a Formula to Determine Which Cells to Format)

4. Add this formula: =mod(row(),2)=1

5. Then click on Format button and go to Fill tab

6. Pick the color that will be used as the alternate color

7. Hit OK and go back to the worksheet

You will see the worksheet has alternate background colors.

Profession: Web Application Developer Employment: Technical Lead, Interactive Division, GANZ Location: Toronto, Canada

You Might Also Like

  • Bonnie Reed

    Very helpful information for the purpose needed. Thank you.

  • J Graff,

    J, you simply click on the button you clicked to FREEZE the row which should have the option UNFREEZE PANES. So for MS Excel 2007+ go to View tab, Click on Freeze Panes option where you’ll find unfreeze option.

  • J Graff

    This is great but how do you unfreeze the top row ????? Please help….

  • Neha

    Hey also let me know how can I get the summary of the table into a new table if I am creating a Leave Record Database into excel sheet?

  • Nakib

    Thank you for the tip

    • Neha

      Thanks for the post, its a great help but can you please help me more?
      Can we freeze 3 to 4 no. of rows and columns at a time in single sheet?
      Do the needful.