If you want to build the original calendar from scratch, you can review the earlier article — or you can download the example file.
In that file, you'll find three sheets: This technique relies on the NETWORKDAYS() function, which is available in Excel 2007 and later versions.
We want to mark badges that expire within 60 days but are not yet expired with a yellow background color, and expired badges with a red background color.
In this example, cells with employee ID numbers who have certification dates due to expire within 60 days are formatted in yellow, and ID numbers of employees with an expired certification are formatted in red. The first rule (which, if True, sets cell background color to red) tests a date value in column B against the current date (obtained by using the TODAY function in a formula).
Given the power of using conditional formatting on traditional Excel ranges, many Excel users would like to use this tool on charts and often ask the question, “Can we apply conditional formatting to charts?
=IF(B3=MAX(B: B), B3,0) The formula populates all other cells in Column C with zero.Assign the formula to the first data value in column B, which is B2. This formula tests the cells in column B (cells B2: B15).If the formula for any cell in column B evaluates to True, its corresponding cell in column A (for example, A5 corresponds to B5, A11 corresponds to B11), is then formatted with a red background color.Consider the data presented in Figure 1; sales data is presented for each of thirteen salespersons.You have been charged with the responsibility of creating a chart containing this data and also having the top-performing salesperson’s data as well as the bottom-performing salesperson’s data formatted differently than the rest of the data on the chart.