There will be times when you would want to format cell or column based on another column's value. Maybe this is that time for you. That's why you are here. Anyway, this can be done easily in excel. Let's learn how to format cells based on another column's value with the help of an example.
Example: Highlight months that have sold more than average sales
So, here I have data on sales done by different dealerships in months of different years. I want to highlight sales in 2019 that are greater than sales in 2018.
To do so, follow these steps.
| =$D2>$C2 |
Hit the OK button.And it's done. All the values in sales 2019 that are greater than the sales in 2018 are highlighted with green fill.
How does it work?
It is easy. First, we select the range on which we want the formula to apply. Next, we use a formula to determine which cell to format in the selected range. The formula is $D2>$C2. Here we have locked columns and allowed rows to change. This is called half absolute referencing. Now, D2 is compared with C2, since D2 is greater than C2, D2 is filled with green colour. Same happens with each cell.
If you wanted to highlight months on instead of sales in 2019, you can directly change "formula applies to" to the range A2:A12.
You can see that formatting is applied to the mentioned reference. Similarly, you can format any range based on any column in excel. The column can be on the different sheet too. You just need to mention the range. You can also mention the non-connected ranges. Just use a comma between ranges in "applied to" section.
As you can see the results in the snapshot above.
Whenever a number is entered in the Excel sheet’s cell, it is in a general formatting. By default, the excel cells do not assign any significance to the numbers that are being entered. But, for better presentation of data, the numbers need some value. Hence, they can be formatted as date or currency or accounting, percentage, fraction, etc. This function does not associate with the number, but just to the cell. Hence, if you enter a number 10/10/1986, the cell will format it as a date. But in the fx tab on the top of the worksheet will still show only 10/10/1986.
Formatting is only for the viewers perception of the data. A mere number such ‘24’ can denote just about anything. But when you put a $ sign before it or a % sign after it, the entire value changes and so does the perception. These formatted numbers and signs do not interfere in any way with the calculations. That is why, in the fx bar, you will still see only the number.
Custom number formatting
Most of the time, Excel will suggest the formatting while the data is being typed in the cells. If it does not, then you can look for more options in the number format menu available in the home tab. If you do not find the required formatting in that list, you can create your own Excel custom format.
Home? Number ? More number formats ? Number tab ? Custom. Here you will find existing number formats. This can be changed to create your own. The original from which the custom number format in Excel was made will still remain in the list, unchanged. If you no longer wish to use that custom format that you created, select it and click delete.
Conditional formatting
Conditional formatting is a whole different story. This is about formatting the individual cells pertaining to its colour, font, borders, etc.
Where does the ‘condition’ in this come? For example, you can set different conditions for the cell to change colour with. You can keep more than one condition for the same cell. For example, cell A1 containing a number lesser than 50 will be white; A2, 50 to 75 might be yellow and A3, 76 to 100 might be red. If you change the calculations or enter new data and the value of A3 drops down to 74, then it will automatically change colour from red to yellow.
Select a cell. Click on Home? Styles ? conditional formatting. From the options you can create the conditions and choose different ways to exhibit it. You can highlight certain numbers that are being repetitive or are lesser than/greater than certain number, etc. Another option is give a condition for highlighting % of a condition.
Excel formatting is a helpful tool to display data in a easy to read manner. It also helps in analysis of the data with its in-built conditional formatting tools.
Hope this article about How to use Number Formatting And Conditional Formatting in MS Excel is explanatory. Find more articles on formatting values and related Excel formulas here. If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook. We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write to us at info@exceltip.com.
Popular Articles :
How to use the IF Function in Excel : The IF statement in Excel checks the condition and returns a specific value if the condition is TRUE or returns another specific value if FALSE.
How to use the VLOOKUP Function in Excel : This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.
How to Use SUMIF Function in Excel : This is another dashboard essential function. This helps you sum up values on specific conditions.
How to use the COUNTIF Function in Excel : Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
