This blog was authored by Y. Karp. Visit www.ykarp.com for some light-hearted blogs from the same author.
Let’s say that you have a very complicated spreadsheet, or that you rely on your spreadsheet to give you information that will determine what steps you take next. Now let’s say that the rows and rows of numbers don’t actually make it easy for you to see the information that you really need to see. Why not use Conditional Formatting to highlight problem areas or important data?
Conditional Formatting. It is just that. Tell Excel to format cells in your spreadsheet according to the conditions you set forth.
If your spreadsheet is dynamic, that is, the data is constantly changing or being updated, you can set it to highlight the interesting bits with colors or other types of text formatting (bold, underline, font size etc). The cell range covered by the conditions you set will update automatically as your data changes and meets the conditional criteria. For example, if my spreadsheet tracks inventory and I want to know when the number of items left in a particular line of stock reaches, say, 100 units, I can set Conditional Formatting to color that cell in red. This is a particular handy trick if you need to find many types of the same information in a large spreadsheet (e.g.: highlight all cells where the value is zero; where the salesman is Joe; or where the customer is more than 90 days past due on his payments). The function is flexible in that it not only allows for conditional formatting based on the actual cell value, but also based on the formula contained within the cell. Another ‘plus’ is that the interface for this function is user friendly and easy even for beginners to understand.
Conditional Formatting is a very useful feature but is also limited to the choices provided by Excel. For instance, in most of the earlier versions of Excel, the number of Conditional Formatting arguments you can create is restricted. Further, Conditional Formatting is not effective when two arguments overlap. For example, if you ask Excel to color all cells with the value 1 in blue and then create another Conditional Formatting argument to color all cells with a value less than 3 in green, Excel will apply the first argument to the cells containing a number 1 and it will apply the second argument to cells containing the number 2. The first argument will always prevail. Another limitation lies in the fact that Excel has a list of conditions you can set and, although they meet most requirements most of the time, there may be occasions when it just doesn’t do what you would want it to do.
Despite all this, Conditional Formatting is a great way to easily highlight important information in your spreadsheet and can actually be a very useful tool.
If you would like to make the most of your business information and understand what your data is telling you, contact Easy Data Solutions for customized Excel spreadsheets and business information models. Or, if you would like to learn more about using Excel and its powerful features, sign up for a tailor-made coaching course for value added learning.
Website: www.easydatasolutions.co.il
Email: in**@******************co.il