Sign Up For Our Newsletter


Conditional Formatting in Excel 2007


Use Excel '07 to Make an Interactive Practice Sheet using Conditional Formatting

The picture above shows what an Excel worksheet would look like for the activity described below.

Open an Excel Workbook. Click in Row heading 1 and drag down to row heading 12. Those rows should all be highlighted. Place your cursor between any two row header numbers and you will see it change into a heavy line with arrows pointing up and down. Click and drag the cursor down until you see Height: 45.00 (60 pixels) . The grid is now basically square.

Step One - Click in cell B2 and drag right one cell and then down one cell. Four cells are now selected (yes, even cell B2, although it is still white).

Step Two - Draw a border around the cells with the Border button which is found in the Font area on the Home tab.

Click on the down arrow to the right of the Borders button and choose the one that looks like four boxes. This button is named All Borders .

Step Three - Repeat the above process starting in cell E2, and then once again starting in cell H2. When you are finished you should have three sets of four boxes across rows 2 and 3.

Step Four - The next thing to do is to color the boxes of four with two colors. When you fill them with color make one block ¼ of the selected color, one block ½ of the selected color, and make the remaining block ¾ of the selected color. Click in one cell to start the process. The coloring tool is also found in the Font area on the Home tab. This tool looks like a bucket spilling paint. Click on the down arrow to to the right of the paint bucket to select a color. Continue coloring until the three boxes are colored ¼, ½, and ¾ of the color you are using. Next, select a second color and fill the remaining fractions of the three blocks with the second color. If you don't like the Theme Colors , click on the More Colors area at the bottom of the color palette.

Step Five - Click in the left cell immediately below the first box and type the letter A. At the cell below left corner of block 2 type the letter B, and then type the letter C below block three.

Step Six - Click on the letter A, then hold down the Ctrl key as you click on each of the other letters. Change the font size to 36. This can be done with the pull down arrow on the font size block.

Step Seven - Click in cell B5 and type the words, Which is ½ blue? (or your chosen color). In cell B6 type Which is ¼ blue? (or your chosen color), and in cell B7 type Which is ¼ yellow? (or your second chosen color).

Step Eight - Click in cell B5 and drag down to cell B7 to select all three cells. Use the font size block in the Font area on the Home tab to change them to size 20.

Step Nine - Click in cell G5 and drag to cell G7. Use the All Borders button to draw boxes around all three cells. Then, use the Fill Color bucket to fill all three cells with light yellow.

Step Ten - Click into cell C8 and type, “Type answers in the blocks, correct answers will turn green.”

Step Eleven - Click and drag to highlight all three of the light yellow boxes. Use the Font Size box to change font size to 20.

Step Twelve - Save your work before going further.

Setting conditional formatting in the Answer boxes

Step One - Click into cell G5 (the first light yellow block). Decide if the answer is A, B, or C before going further.

Step Two - In the Styles area on the Home tab, click on the conditional formatting button. Slide down to Highlight Cells Rules and then slide over and down to Equal To and click one time.


Step Three - In the Equal To window type the letter B (if that is the correct answer on your worksheet) in the Format cells that are EQUAL TO block. On the right side click on the down arrow to select the formatting you wish to use.

If you don't like one of the built in formats, select Custom Format and make the changes you want.

Come back to the Styles area on the Home tab, click on the conditional formatting button. This time, slide down to More Rules and click one time. Use the pull down menu to change greater than to not equal to and enter the same letter you entered in the process above.

After changing the cell value, click on the format button to open the same window shown two images above. Make the formatting color different from the correct answer formatting.

Step Four - Move to cell G6 and continue the same process until conditional formatting has been set for cells G6 and G7.

Step Five - As a last step, let’s remove the grid lines from this worksheet. To remove the grid lines, go to the View tab and remove the checkmark beside the word Gridlines in the Show/Hide area.

Step Six - Save your work.

Sample worksheet on this topic

Internet4classrooms is a collaborative effort by Susan Brooks and Bill Byles.




Use of this Web site constitutes acceptance of our Terms of Service and Privacy Policy

1694708720544283 US 1 desktop not tablet not iPad device-width