Lines from Column A to F are supposed to appear, but as the image above the result has not come out as I expected. In this example, click ‘Fill’ and choose any color you like, then ‘OK’ and ‘OK’. In addition to the previous ‘pink’ fill, you can change the ‘Number’ format, the style and color of the ‘Font’ and ‘Border’. Then click ‘Format’ to set what formatting you want to set when your condition is met.
Since F3 is the top row in the selected range, enter ‘=F3<0’. My condition of ‘Gross Profit Increase’ in Column F is below 0. Put a formula in the text box above for ‘What condition’ and set ‘What formatting’ you want in the below section separately.Īt first, always put an ‘=‘ in the conditional formula. There are many types of rules, but I always use this one: ‘Use a formula to determine which cells to format’. Select all the columns in the table and choose ‘New Rule’. Select it and click ‘Delete Rule’, then click ‘OK’. Select the cell you’ve set it in and from ‘Conditional Formatting’, choose ‘Manage Rules’. First let’s delete the conditional formatting we made. Suppose you want to highlight entire rows up to the customer names with the same condition as in the previous example. Now, if you want to change the formatting based on other-cell values, how could you do it? In practice, you’ll have this case more often. Until now, the cells that the conditions refer to and the cells that you put formatting in were the same. If a pink color shows and you select it, you can easily see which values are duplicated. If there is no pink color there, there are no duplicate values. Just select a range and choose ‘Duplicate Values’ and click ‘OK’.Ĭlick that column’s Auto Filter and see ‘Filter by Color’. In practice I often use it when there are many rows of data and I want to make sure that there are no duplicate values. This will highlight all duplicated-value cells within the selected range. The ‘Duplicate Values’ function to make sure that there are no duplicate values.Īs you can see, there are many kinds of ‘Conditioning’ and various ‘Formatting’. For example, let’s select the This Month’s Sales column in Column D and choose any favorite color of ‘Data Bars’, then you can visualize the differences in the sales among the others very easily. This Excel version is Excel 2016, but if you use 2007 or newer, this process is almost the same. Conditional Formatting can be used so easily. On the next screen, enter ‘0’ and click ‘OK’, then it’s done. Let’s walk through this and set the Conditional Formatting to highlight the cells whose increases are less than 0.įirst select the range of cells where you want the Conditional Formatting, then select ‘Home’, ‘Conditional Formatting’ and ‘Highlight Cells Rules’ and ‘Less Than’. We would like to find customers with a declining Gross Profit and take countermeasures quickly. This example shows customers’ Sales, Gross Profit, and Gross Profit Increase for the last two months. If you haven’t used it until now, you are missing some low-hanging fruit.
You can make your data analysis actionable very easily with this Conditional Formatting.