- You want to learn about Excel.
- You want to buy computer parts to build a PC.
- You research the prices at Best Buy and Circuit City.
- You research the prices at more stores.
You have optimized the computer part prices and determined that the lowest total cost is $640.63. Before you pat yourself on the back for your hard work, you must realize that although cost is important, it means little unless you determine where to shop. The following Exhibit cleanly indicates the best price option and its respective store.
One option to create the exhibit is to use a manual “select cell, highlight, change font” (rinse, repeat, recycle many many times). This manual process may be fine for small projects, but the task can quickly become tedious if information changes or expands quickly. Let’s create the exhibit using Excel’s highly powerful and usable Conditional Formatting (Excel 2003: Format > Conditional Formatting).
- If an additional “cheaper” column is available, this task is easy. Choose the appropriate cells and set the “Cell Value Is” to refer to the Cheaper column. (In the example below, column I refers to the before mentioned “cheaper” column.) I have chosen the “equal to” option, but there are many other options that are easy to understand.
- Now, lets make the assumption that you have no interest in creating an additional “cheaper” column. No problem. Instead of the default “Cell Value Is“, change the condition type to “Formula Is“. This allows you to write an Excel formula which must solve to True or False (or equivalent). In the example below, columns C through G refer to prices specified in Best Buy through Walmart.
Conditional Formatting is a very useful tool. When working for a financial firm, I used Conditional Formatting to error check, show real time changes in Equity Prices (Bberg BDP function), and highlight cells for concern. What is really nice about Conditional Formatting is that you can utilize all the popular Excel formulas. In many ways, you can create instant feedback for your spreadsheets because you can set up cells to format automatically based on your input/formulas. Unfortunately, there are restrictions. Excel 2003 only allows 3 conditions, the size of the file will increase, and large spreadsheets can become noticeably slower. Microsoft expanded this tool in Excel 2007 and offers >3 Conditional Formatting formulas and more intuitive default conditions. I have yet to explore all the Excel 2007 additions, but perhaps this could serve as a post in the future!? Thanks to Peter for his original post.
Note* If you need to build a computer, do your own research. These prices most likely do not reflect actual store prices!
Comments are now closed.