Number Formatting And Conditional Formatting in MS Excel

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

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.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube