Excel Tip of the Week #136 - Quick formatting for formulas, inputs, and blanks

Hello and welcome back to the Excel Tip of the Week.  This week, we have a Creator post in which I'm going to show you a quick and easy trick for formatting different types of cells differently.

Cell types and the need to be clear

Principle #10 of the 20 Principles for Good Spreadsheet Practice is "Separate and clearly identify inputs, workings and outputs."  This is a key part of making your spreadsheets easy to read and use, and will avoid mistakes where a user doesn't realise that a formula is present in a certain cell; or where they can't find where to change a certain input.

Often, it's disruptive to your work flow to have to constantly switch between making your spreadsheet and tinkering with formatting settings.  Thankfully, there are some handy tools in Excel that will help you quickly format the three main cell types - blanks, constants, and formulas - in different ways.

Go To Special

The options we need are found in the Go To Special menu:

This menu allows you to select all the cells with a given property.  If you use it with a single cell highlighted, the entire spreadsheet will be considered.  If you highlight a range beforehand, only cells in that range will be considered.

This lets you do something like the following:

From here, you can then apply your chosen format to the highlighted cells (formula cells in this example).

You can then repeat the process to highlight the input cells (constants) and if appropriate the background cells (blanks).

It's worth noting that this method isn't perfect.  "Constants" will also highlight any text and/or headings that you may have.  You can work around this with one or both of these methods:
   - Don't highlight the rows/columns with the headings in them
   - Where the first method isn't practical (e.g. if the headings are mid-way through a range), highlight using the normal method and then go back and reformat the headings

You can have a practice at applying these techniques on the example table, which you can download from the bottom of this page.

Previous post - Why merging cells sucks 
TOTW index
Next post - R1C1 reference style

This blog is brought to you by the Excel Community where you can find additional blogs, extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Excel with Business. There is also an online forum where you can ask questions and share ideas with other community members.