Conditional Formatting, Part 3 ADDING COLOR SCALES By Nate Moore, CPA, MBA, CMPE fter reading the articles in the last two issues of Billing, you have learned a lot about how to use conditional formatting to make your spreadsheets A more powerful. In this issue, we’ll add color scales to make conditional formatting even more useful. To follow along with the example, look for the download link with this article at mooresolutionsinc.com/articles.php. Once you have downloaded the spreadsheet, look for the “Mar Apr 13 Practice” tab for this example. Color Scales like the data bars we discussed last time, color scales are a way to analyze a cell’s value in comparison to other cells. look at the reimbursement amounts for “treatment 1” in figure 1. notice how the lowest reimbursement amounts from medicaid and medicare are shown in red, while humana’s reimbursement is shown in orange and aetna’s amount is shown in yellow. as reimbursement increases, cigna is shown in chartreuse (yellow-green) and the highest amounts, bcbs and Wellpoint, are shown in green. figure 1 is a color scale formatted so that the lowest amounts are displayed in red, the middle amounts are shown in yellow, and the highest amounts are green. amounts between the low red cells and the medium yellow amounts are shown in orange, while amounts between the middle yellow cells and high green cells blend yellow and green. once you know what the colors mean, it’s easy to tell at a glance how each payor’s reimbursement relates to the other payors for the treatment. setting up a color scale and customizing the colors is easy. to follow along with this example, go to the “mar apr 13 Practice” tab, highlight cells b4 through b10, and click “conditional formatting” from the home tab of the ribbon. select “color scales” and choose the top left option, “green – Yellow – red color scale” as shown in figure 2. Your screen should look similar to figure 1. before we review the different ways to customize a color scale, look at the other preset color scale options besides the “green – Yellow – red color scale” we chose. You’ll find a variety (12 in excel 2010) of default choices with combinations of red, yellow, blue, white, and green colors. Customizing Color Scales if one of those choices is not exactly what you are looking for or you want more control over the high, medium, and low points of your color scale, select cells b4 through b10 again, choose “conditional formatting” and “manage rules.” Your screen should look like figure 3. click “edit rule” to bring up the “edit formatting rule” window, as shown in figure 4. first, notice the drop-down box underneath the heading “format all cells based on their values.” You can choose from a two-color or a three-color scale. You can also switch to data bars or icon sets from this drop-down menu. We’ll look at icon sets in the next issue of billing. the two-color and three-color scales are similar, so we’ll use the three-color scale in this example. underneath the “format style” drop-down box we just saw are columns that allow you to control the minimum, midpoint (if you have a three-color scale), and maximum values in your color scale. figure 4 shows the minimum set at the lowest value. the “type” drop-down box underneath “minimum” also has options for a number, percent, formula, or percentile. if you wanted everything under $1,000 to be red in our example, you could choose “number” as the type and enter 1,000 as the value. if you are following along, change the bcbs reimbursement for “treatment 1” from $1,356 to $1,000. Your screen should look like figure 5. change the bcbs reimbursement back to $1,356 and the cell will automatically change back to green. there are similar options for the midpoint and maximum settings of a color scale. now, click on the “color” drop-down in the “minimum” column. You will find a wide variety of preset colors based on your spreadsheet theme. if you need a specific color not shown, just click on “more colors” at the bottom of the window. by changing the color setting for the minimum, midpoint, and maximum values, you can create a color scale that matches what you need for your spreadsheet. the “Preview” area at the bottom of the window shows you what your color scale will look like and how colors will blend between the minimum, midpoint, and maximum. excel provides 48 hbma billing • march.aPril.2013 (continued on page 50)
Billing_MA13
To see the actual publication please follow the link above