Conditional Formatting, Part 4 USING ICON SETS TO CREATE MORE INFORMATIVE SPREADSHEETS By Nate Moore, CPA, MBA, CMPE have discussed many ways to use Excel’s conditional formatting feature in the past three issues of Billing. Now that we are We familiar with conditional formatting, we will add icon sets to make your spreadsheets even more informative. To follow along with these examples, look for the download link at mooresolutionsinc.com/articles.php. Once you have downloaded the spreadsheet, look for the “May Jun 13 Practice” tab. What Are Icon Sets? icon sets differ from the data bars and color scales that we have worked on previously. instead of shading cell backgrounds to indicate comparisons, icon sets are a built-in set of arrows, stoplights, and other symbols that can either be placed next to a value in a cell or used as the only piece of information in a cell. for example, compare figures 1 and 2. both figures report new patient referrals from a group of physicians. in figure 2, you can quickly determine whether referrals are up, flat, or down from each provider thanks to the arrows next to the values in the change column. you can easily add icons to your spreadsheet. to follow along with this example, go to the “may jun 13 Practice” tab, highlight cells d4 through d12, and click “conditional formatting” from the home tab of the ribbon. select “icon sets” and choose the top left option, “3 arrows (colored),” as shown in figure 3. your screen should look similar to figure 4. take a minute to look at the wide variety of predefined icons you can choose from under “conditional formatting” and “icon sets.” there are several different versions of arrows, stoplights, and indicators, including cell phone bars, stars, and flags. Customizing Icon Sets look carefully at figures 2 and 4. the numbers are the same, but the arrows differ in color and direction in the two examples. rather than accept the default categorization of green, yellow, and red arrows that we created in figure 4, i customized the way the arrows work to produce figure 2. to customize icon sets, select cells d4 through d12 again, choose “conditional formatting,” then “manage rules.” your screen should look like figure 5. click “edit rule” to bring up the edit formatting rule window, as shown in figure 6. first notice the drop-down box underneath the heading “format all cells based on their values.” you may remember this screen from prior conditional formatting articles in Billing, since you can change to two-color or three-color scales or data bars from this drop-down menu. underneath the “format style” drop-down box is an “icon style” dropdown box that allows you to choose which set of icons you want to use. the “reverse icon order” button to the right allows you to make high numbers red instead of green, which helps if you are reporting golf scores or cholesterol levels. check the “show icon only” box to hide the value in the cell and only show the icon. the “display each icon according to these rules” section allows you customize when icons change. in figure 6, the green icon is displayed when the cell’s value is greater than or equal to 67 percent, the yellow icon is displayed when the cell’s value is less than 67 percent and greater than or equal to 33 percent, and the red arrow is displayed when the cell’s value is less than 33 percent. click on the drop-down arrow next to “type” to the right of the edit formatting rule window. you will see options based on the number, percent, formula, or percentile in the cell. to match the settings i used, look at figure 7. once you have changed your settings to match figure 7, your screen should look like figure 2. to save space on dashboards or other reports, you can check the “show icon only” box to only show the icon instead of the value. in figure 8, the trend in patient satisfaction is displayed using circles similar to those used in Consumer Reports. you can replicate the report in figure 8 using cells b18 through d27 on the “may jun 13 Practice” tab on the sample spreadsheet. to see my solution, highlight the same cells on the “may jun 13 answers” tab and select “conditional formatting” and “manage rules.” once you can make the circles work, create a rule to make any scores of 4 or 5 green, make 3 yellow, and anything less than 46 hbma billing • may. june.2013 (continued on page 50)
Billing_MJ13
To see the actual publication please follow the link above