Cells Formatting

Education is not limited to just classrooms. It can be gained anytime, anywhere... - Ravi Ranjan (M.Tech-NIT)

Introduction to Cells Formatting


From the previous lessons, you are already familiar with the way Microsoft Excel displays text (left aligned) and numbers (right aligned). In some situations, you will want to treat numbers as text.

Although Microsoft Excel displays all numbers right aligned, as a smart financial and business application, it can distinguish between different types of numbers. It can recognize a date, a currency, or a percentage values, but the computer wants you to specify the way numbers should be displayed, giving you the ability to decide what a particular number represents, not because the software cannot recognize a number, but because a value can represent different things to different people in different scenarios. For example 1.5 might represent a half teaspoon in one spreadsheet while the same 1.5 would represent somebody's age, another spreadsheet's percentage, or etc.

Microsoft Excel can recognize some numbers by default and would not need much configuration, if any. For example, if you type 12/05/1998 in a cell, Microsoft Excel will recognize it as a date and act accordingly.

Introduction to Numbers Formatting

When it comes to displaying items, Microsoft Excel uses various default configurations, ranging from the font used by your application to the actual display of numbers in cells. The computer's Regional Options or Regional Settings govern how dates, numbers, and time, etc get displayed on your computer.

Microsoft Excel recognizes numbers in various formats: accounting, scientific, fractions, and currency. As the software product can recognize a number, you still have the ability to display the number with a format that suits a particular scenario.

Before finding out how to display numbers in different situations, you should be familiar with the way the application treats your numbers. That's why once again we will review some of the things we have already learned.

 Practical Learning: Exploring Cells Number Formats

  1. Start Microsoft Excel and open the Allentown Car Sales1.xlsx workbook
  2. In Sheet1, select Cells F3:F10
  3. On the Ribbon, click Home. In the Number section, click the Comma Style button Comma Style.
    Now these thousand numbers display with a comma sign which makes it easier to read. Microsoft Excel also allows you to be more precise with these numbers, that's why it added two decimal values
  4. Press Ctrl + Home to see the result
  5. The values in the F column represent car mileage values; we don't need to display these numbers with decimal places
    Select cells F3:F10 again
  6. To remove the decimal values, in the Number section of the Ribbon, click the Decrease Decimal button . That removes one decimal value. Click the Decrease Decimal button again 
  7. Click cell F1 to see the result
  8. Select cells G3:G10
  9. In the Number section of the Ribbon, click the Currency Style button Currency. The numbers in column G are now treated as money values and a $ sign has been appended to them
  10. Select cells H3:H10
  11. In the Number section of the Ribbon, click the Percent Style button . Now the numbers in column H are treated as percentage values
  12. Our percentage numbers need a little more precision; therefore, we will display them with at least two decimal places. While cells H3:H10 are still selected, in the Number section of the Ribbon, click the Increase Decimal button Increase Decimal twice. Notice that the numbers have changed and are now more precise:
  13. Save the workbook