Excel Color All Sales Green for a Day if Total Sales Exceed $1,000
This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Color All Sales Green for a Day if Total Sales Exceed $1,000
Problem: My company offers a bonus pool on any day when the total sales exceed $1,000. I have invoice data by date. I would like to highlight all records for the days that exceed $1,000 in sales.
Figure 1122 Highlight if total sales for day is more than $1,000.
Strategy: You can use a formula version of conditional formatting to perform a complex task such as this. But first, before getting into conditional formatting, you should develop the formula you need:
1. The first task is to add a column that will total all sales for this day. The SUMIF function can do this. There are three arguments in the SUMIF function: =SUMIF($A$2:$A$30,$A2,$C$2:$C$30). This function tells Excel to examine each cell in A2:A30. If the cell value is equal to cell A2, then it adds up the corresponding cell from C2:C30.
Enter the formula in D2. Double-click the fill handle to copy the formula down. Every row contains the total sales for that day. There are a lot of dollar signs in the formula. As you copy the formula down in your temporary column D, you want the ranges in the first and third parameters to be frozen. In the temporary formula in column D, there is no reason to freeze the A2 in the second parameter. However, in the New Formatting Rule dialog, this formula will be applied to cells in A, B, and C, so it is important to freeze the second parameter to column A.
2. The formula for conditional formatting requires a formula that evaluates to either TRUE or FALSE. To convert the current formula, edit the formula and add >1000 to the end. The temporary formula will now show either TRUE or FALSE. The formula in D correctly identifies all of the individual records where total sales for the day exceed $1000.
To set up the conditional format, you follow these steps.
1. Copy the formula that is working by going to cell D2 and pressing the F2 key to put the formula in Edit mode. In the formula bar, drag to highlight the entire formula.
2. Press Ctrl+C to copy the formula from the formula bar. Copying from the formula bar allows the text of the formula to stay on the Clipboard after you press the Esc key.
3. Press the Esc key to exit Edit mode.
4. Select cells A2:C30. Select Home, Conditional Formatting, New Rule.
5. In the top of the New Formatting Rule dialog, choose Use a Formula to Determine Which Cells to Format. The bottom half of the dialog will redraw to allow you to enter a formula.
6. Click in the Formula box of the dialog and press Ctrl+V to paste the formula.
7. Click the Format button. The Format dialog will appear. If desired, specify a border, fill, and/or font color. New in Excel 2007, you can specify a number format as well.
8. On the Fill tab, choose a green fill. On the Font tab, change the font color to white. Click OK to close the Format dialog.
9. The New Formatting Rule dialog should appear similar to below. If it does, click OK to apply the rule.
Figure 1123 Fill out the new rule and select a format.
If everything worked okay, all the rows for the December 3 will be highlighted in green.
Figure 1124 Highlight all records for a day when the sales exceed 1,000.
You can now safely delete your temporary formula in column D.