Search Contextures Sites

	Excel Troubleshooting - Format Cells Clear All Formatting Colour the Formula Cells Colour the Constant Cells Colour the Data Validation Cells Colour the Constant Cells With Numbers Finish the Worksheet Formatting Watch the Formatting Video Sometimes you get workbooks to troubleshoot, and the cells are filled with colours, that are only there to brighten up the worksheet. To make the troubleshooting easier, you can clear out the existing colours, and use your own set of colours to highlight cells with formulas, or other cell contents. These instructions show you how to remove the old colours, and format the cells that contain: formulas text numbers data validatiaon Clear All Formatting In the screen shot below, you can see a typical worksheet that I get for troubleshooting. Nobody remembers who built the spreadsheet, but it's not working correctly, and needs to be fixed. The sections are coloured randomly, because someone liked the colours, but they don't help with understanding how to use the worksheet. The first step is to make a copy of the original file, and store it in a safe place. Then, follow these steps to remove all the colour fill from the worksheet, so you'll be able to add your own colour scheme. To select all the cells on the worksheet, click the Select All button at the top left of the worksheet On the Ribbon's Home tab, click the Fill Color arrow, and click on No Fill Colour the Formula Cells The next step is to find and format the cells that contain formulas. On the Ribbon's Home tab, click the Find & Select command Click on Formulas, to select all the formula cells Then, use the Fill command on the Ribbon to format the Formula cells. I usually use grey for the formula cells, which indicates that they shouldn't be changed. Colour the Constant Cells The next step is to find and format the cells that contain constants. These are values -- text or numbers -- that have been typed into the cells. On the Ribbon's Home tab, click the Find & Select command Click on Constants, to select all the cells that contain constants Then, use the Fill command on the Ribbon to format the Constant cells. Colour the Data Validation Cells The next step is to find and format the cells that contain data validation rules. These might be data validation drop down lists, or other types of rules. On the Ribbon's Home tab, click the Find & Select command Click on Constants, to select all the cells that contain data validation Then, use the Fill command on the Ribbon to format the data validation cells. Colour the Constant Cells With Numbers The next step is to find and format the cells that contain constants that are numbers. These might be data entry cells, that we can use in our testing.. On the Ribbon's Home tab, click the Find & Select command There is no command for Constants - Numbers, so click the Go To Special command. Select Constants, and check the box for Numbers. Remove the check mark in the Text, Logicals and Errors boxes, then click OK. Then, use the Fill command on the Ribbon to format the Constants - Numbers cells Finish the Worksheet Formatting Now that all the key cells are colour coded, you can add a legend on the worksheet, to indicate what the colours mean. Then, get started with the troubleshooting. For example, in the screen shot below, the circled cells are constants, but they should contain formulas. When you're finished troubleshooting, and the worksheet is functioning correctly, you can clear the troubleshooting formats. Then, add colour to the data entry cells, or other key cells, to help people understand how the workbook should be used. Watch the Formatting Video

Home     Excel Tips     Excel Files     The Excel Store     Blog     Contact
Privacy Policy		Contextures Inc., Copyright ©2013 All rights reserved.	Debra Dalgleish

Last updated: September 2, 2012