Conditional Formatting is an excellent tool in Microsoft Excel that allows you to format cells based on their values. The default formatting options are useful, but they only apply to the value of the selected cell. In some cases, you may want to format a cell based on another cell’s value. For example, you may want to highlight the name of a store that has an average score below 3. In this tutorial, we’ll show you how to create a new Conditional Formatting rule in Excel based on another cell’s value.
To get started, open the Excel file and select the cell you want to format. In this example, we have monthly Store Satisfaction Scores for our stores, and we want to format the name of stores with average scores below 3. Click on the “Conditional Formatting” button in the “Home” tab, and then click on “New Rule.”
There are several rule types to choose from, but we’re going to select “Use a Formula to Determine Which Cells to Format.” In the “Format values where this formula is true” box, type the formula that will determine the value to trigger your formatting. In this case, we’ll use the AVERAGE formula. Type “=AVERAGE,” select the range of cells you want to average, and then type “<3." Click "OK" to continue.
Next, select the specific formatting that you’d like to see. For this example, we want to make the font color red and bold. Select the “Font” tab and choose “red” and “bold.” You also have options to change the number formatting, border type, or fill based on the value of your formula result, but we only want the font formatting. Click “OK” if you’re happy with what you see in the Preview Window.
To copy the formatting to other cells, select the cell you just formatted and use the Format Painter in the “Home” tab to copy the format to any other cells you desire.
By following these steps, you can create your own Conditional Formatting rule in Microsoft Excel. You can format cells based on another cell’s value, making it easier to see which cells meet specific criteria. This feature can save you a lot of time and effort in formatting your spreadsheets.
In conclusion, creating a new Conditional Formatting rule in Excel is a straightforward process. By using this feature, you can format cells based on another cell’s value, making it easier to analyze and understand your data. Conditional Formatting is a powerful tool that every Excel user should know how to use.