Qling | Resources | Excel & Google Sheets - Format Cells

Format Cells in Worksheets

Whenever you write some text or enter some data in a cell, Excel categorizes it into General type data. You can set the data type to be a more specific kind of data, for example, date, currency, accounting, text, and so on.

You also have the flexibility to select how a particular type of data should be represented. For instance, you can select the date to be displayed as 1 January 2021 instead of 1-1-21.

Format Cells in Worksheets

Whenever you write some text or enter some data in a cell, Excel categorizes it into General type data. You can set the data type to be a more specific kind of data, for example, date, currency, accounting, text, and so on.

You also have the flexibility to select how a particular type of data should be represented. For instance, you can select the date to be displayed as 1 January 2021 instead of 1-1-21.

A white table top with a keyboard, pens, and a writing pad

You can even select the number of decimal places that should appear for a particular number and so on. There are too many different cell formats, however, on this page, we will look at some of the most popularly used cell formats. A particular type of cell formatting can be done by clicking on the arrow in the number group on Home Tab as highlighted in the picture below:

Click on the arrow in number group on Home Tab to specify cell formatting

On this page, we will look at the following Cell Formats in detail:

Click on the arrow in number group on Home Tab to specify cell formatting

Number

Once you click on the arrow in the Number Group, you get the Format Cells dialog box.

You can format the cell as per the data that you have in a particular cell.  As illustrated below, since the data in the cell is a number, you have the following options:

  1. Select the number of decimal places to be shown (2, 3, 4...)
  2. Use a comma as a 1000 separator (to show as 1,000)
  3. Select how negative numbers should be represented
Select Number as the Category in Format Cells Dialog box
Select Number as the Category in Format Cells Dialog box

Excel shows you the output of your choices in the Sample box. This is extremely useful as it helps you in getting a quick preview of how your data will look as you select from the formatting options.


Currency

For representing data is Currency, select the category as Currency in the Format Cells Dialog Box. On selecting Currency as the Category, you will be presented with the same options as Number Format, and in addition to the above options, you also have the option to select the currency symbol or abbreviation (USD, INR, GBP, etc.).

This is particularly useful when you are representing data such as revenue, sales, budgets, spends, etc. 

Select Currency as the Category in Format Cells Dialog box
Select Currency as the Category in Format Cells Dialog box

Date

Dates can often confuse especially when you work in collaboration with teams spread across countries. You may not be sure if the dates in Excel are in mm-dd-yyyy format or dd-mm-yyyy format. E.g., 04-03-2020 could be 4 March 2020 or 3 April 2020 on how you read it.

You can format cells containing dates in a way to minimize this ambiguity using the options provided in the 'Date' Category in the Format Cells dialog box.

Excel allows you to select the type of format that you want to use to represent the date. Depending upon your Locale (Location) settings, the date formatting can still be impacted by the date and time settings that you may have selected on your computer.

However, Excel provides a solution to display the dates in a format of your choice irrespective of the date and time settings on your computer.

In the image below, other than the first two date types which begin with an asterisk (*), all other formats do not get impacted by your computer setting. This is indicated by the highlighted text in the image below:

Select Date as the Category in Format Cells Dialog box
Select Date as the Category in Format Cells Dialog box

Fractions

While writing fractions (1/2, 1/3, 1/4…) in Excel, the most common challenge that you face is that Excel automatically assumes and changes the fraction to a date format. E.g., 2/3 automatically becomes 02-March.

If you want to type a date using this style, then this is a great feature. However, if you want to indeed write a Fraction then you could format the cell as a Fraction. Therefore, in the above example if you select the cell formatting to be Fraction, then it will allow you to write 2/3 and not convert it into 02-Mar by default.

To set the format type as Fraction, click on the arrow in the Number group and select the Category as Fraction as highlighted below. If the fraction had been converted to date, then you may have to type in the fraction once again after changing the format to Fraction.

In the example below, the cell V2 has been formatted as a Fraction and Excel allows you to type 2/3 and does not convert it into the data 02-Mar. Also notice that the formula bar shows the value of the fraction 2/3 as 0.66… 

Select Fraction as the Category in Format Cells Dialog box
Select Fraction as the Category in Format Cells Dialog box


LinkedIn Logo
Facebook Logo
Instagram Logo
Pinterest Logo