Conditional Formatting to Spotlight Important Values in Spreadsheets
The size of an Excel spreadsheet can range from a few rows and columns, a single printed page or an enormous number of printed pages. Whatever the size, the importance of a spreadsheet are the results derived or the trends produced from the analysis. So if the results or trends are the ultimate goal, why are these values buried in some spreadsheets?
Want more articles like this?
Create an account today to get BiggerPocket's best blog articles delivered to your inboxSign up for free
Have you ever seen a detailed and elaborate spreadsheet that has all the correct calculations and analyses of the desired numbers, but is almost impossible to find the analyzed results or the prevailing trends spotlighted? Scrolling through screen after screen hunting for the results, you decide to give up.
Have you ever looked at a rent roll spreadsheet and wished that the delinquent rent payments would have a ‘red flag’ that would immediately catch your eye. How about looking at an Amortization Schedule with a 30 year term of payments and wished that each year would be clearly denoted? Or wished that alternating rows of the schedule were a different color in order to make it easier on your eyes?
Spreadsheet data should be correctly entered and use the correct formulas to produce accurate results. But just stopping there, is not enough. If your spreadsheet will be viewed by others, make it clear where the results are and show the important trends of your analyses.
If you will be the only one viewing the spreadsheet, does it really matter how it looks? Ever create a spreadsheet six months ago, then return to it and end up wasting time trying to figure out what and where are the trends and important results are buried?
One way to overcome these issues of spotlighting values and trends would be to search through the spreadsheet and apply cell formatting to the important values. If you have a rather large spreadsheet this could be very time consuming. Plus, you might miss some values or important trends during your search.
Another option would be to use some formulas that would spotlight the important values. This would require you to create the necessary formulas and hope that as new data is entered, the important values will stay the same. Again with manually entering the formulas the spotlighted value may have changed and become invalid. The above options do not seem to be very efficient or practical. There must be a better way. Excel has a better way, it has a built in feature that will spotlight the important values and trends automatically.
What is Conditional Formatting?00
Conditional formatting can address those previously mentioned issues. With this feature, Excel will automatically apply cell formatting based on the contents of cells. In other words, certain conditions must be met before formatting is applied. Conditioning Formatting is located on the Home tab, under the Styles category.
Conditional Formatting Examples
Here are some examples of what conditional formatting can do. First we have a spreadsheet with some hypothetical multifamily data. At first glance we see just a bunch of values, nothing really stands out.
Now let us apply some conditional formatting features to spotlight some of the important values and make the spreadsheet easier to read.
In the first analysis, we have a portion of a Rent Roll. The values to be spotlighted are rents that were not fully paid, the Delinquent column. Notice the three colored flags, red for severe delinquencies, yellow for moderate delinquencies and green for slight delinquencies. Conditional Formatting spotlighted and categorized the delinquencies.
The next analysis is Operating Expenses, again just hypothetical values. The Amounts column has a built in columnar graph with red bars representing the relationship of each expense. Here we can see how each operating expense compares to the other expenses. Real Estate Taxes and Property Management expenses can easily be seen are the larger expenses.
The last analysis is a portion of an Amortization Schedule, showing the first two years of payments. Without Conditional Formatting imagine if it were the full 30 year schedule of payments. You would be presented with 360 rows of monthly payments. Instead, by applying Conditional Formatting the 360 rows have a blue alternating pattern. With more formatting you could even have Excel draw a red line after every twelfth month interval denoting a year.
In Excel 2010, there are over 50 conditional formatting options and rules that you can apply to the cells in your spreadsheets. The format rules and graphic representations include;
- Highlight Cells Rules
- Top/Bottom Rules
- Data Bars
- Color Scales
- Icon Sets
At the bottom of the Conditional Formatting feature, you can create new rules, clear rules and manage rules.
How to Use the Conditional Formatting Rules Manager
The Conditional Formatting Rules Manager is the central location for your formatting options. The Manager allows you to create new rules, edit existing rules and delete rules.
The Rules Manager gives you an overview of all your formatting rules for the entire worksheet, a particular sheet or the current selection. To make your choice, use the “Show formatting rules for:” at the top of the Rule Manager.
On the right-hand side of the Rule Manager are checkboxes for the “Stop If True” feature. You can select the ‘Stop If True’ checkbox to see how conditional formatting might appear in earlier versions of Excel. Earlier versions of Excel do not support more than three conditional formatting rules, or multiple rules applied to the same range.
In earlier versions than Excel 2007:
• The first three rules would only apply.
• The first rule in precedence that is true would apply.
• Ignores rules lower in precedence if they are true.
The order in which the rule is listed in the Rules Manager:
• Apply only the first rule, select checkbox for the first rule.
• Apply only the first and second rules, select the checkbox for the second rule.
• Apply only the first, second, and third rules, select the checkbox for the third rule.
The data bar, color scale and icon set options do not utilize the ‘Stop If True’ feature.
Amortization Schedule, as an Example
In the earlier example, you saw a portion of a 30 year Amortization Schedule with alternating blue rows. I will show you how that was achieved using conditional formatting.
Notice in the above example that the even number rows are blue. Rows that are even numbered are the key to applying conditional formatting for alternate colored rows. Is there a built in Excel function that we can use to accomplish this? Yes, we can use the MOD(). MOD() returns the remainder of a division operation.
=MOD(number, divisor) returns 0 for no remainder or 1 if there is a remainder.
Here are two examples, divide 8 by 4 and divide 5 by 2:
8 / 4 = 2 or =MOD(8,4) returns a 0
5 / 2 = 2.5 or =MOD(5,2) returns a 1
For the Amortization Schedule the Conditional Formatting will be applied to the even number rows. The function to be used is:
Using the above function, Excel will divide each row by 2 and if the remainder is zero, the blue fill will be applied. Here are the step-by-step instructions for this example of conditional formatting:
1. Select rows A12:E35; the body of the schedule.
2. From the ‘Home’ tab, click the ‘Conditional Formatting’ option.
3. Click the ‘New Rule…’ option.
4. In the New Formatting Rule window, click the ‘Use a formula to determine which cells to format’.
5. At the ‘Format values where this formula is true:’, enter
6. Click the ‘Format…’ button.
7. Select the ‘Fill’ tab and pick a blue color.
8. Click ‘OK’ twice.
9. Click outside the selected schedule to see the results of the applied conditional formatting.
That is it, alternating blue rows. Here is an added bonus. If you delete or insert rows inside of the body of the schedule, the alternating blue rows will automatically be updated. This is possible because we use the ROW() as the number inside of the MOD(). However, if we had used ‘$A12’ as the number in MOD() and then deleted or inserted rows, the alternating blue rows would not have updated. You would see two blue rows or two white rows in sequence, the alternation would be destroyed.
Conditional Formatting is a power visual tool in Excel. Spotlighting cells or range of cells based upon specific rules and the contents of cells. Taking the time to learn this feature will make your spreadsheets easier to read, look more professional and help you analyze values when you are number crunching those real estate deals.