Excel > Back to Basic

Cell formatting in Excel

How to format cell in MS Excel?

In the last post, we talked about Excel Worksheet and Workbook. In this post, we shall learn about formatting and doing other formatting activities related with cells of Excel worksheet.

The default data type of any cell in excel is 'General', to prove this write any numeric data in a cell like this.

Excel cell

Now right click that cell and select 'Format Cells...' or simply press CTRL+1 (1 should not be pressed from numberic key pad).

Format cells

and this shows a Format Cells dialog box. Notice below dialog box picture and see that Category list has 'General' selected by default.
Format cell dialog box in excel

This means that any cell of the Excel worksheet can accept any type of data and all are treated as string (however mathematical operations may parse the string to integer automatically).

How to format data for decimal characters, currencies values, percentage, and comma separation?

To format data into Number Formats, we can use either 'Number' group from the Ribbon as displayed in below picture or Number, Currency and Accounting category from the Number tab of Format Cells dialog box (as displayed above).

format numeric data

For example to keep decimal characters after a number, we can use 'Increase Decimal' icon from the Ribbon group.

increase decimal icon in excel

Same as above we can also use 'Decrease Decimal' to decrease the decimal characters.

We can also use other icons from the group for Percent style, Comma Style, Accounting Number Format.

comma separator in number style

Apart from Toolbar Icons, we can also use Format Cells dialog box to achieve same thing.

Currency symbol prefix in excel

How to change the color of the cells in Excel?

To change the color of the cells, first select the cell and then use Font group icons as displayed below. To change the background color, use Fill color icon and to change the font color use Font Color toolbar icon.

Change the cell color in excel

Apart from using Toolbar icons, we can also use Format Cells dialog box and select Font tab. Look at the picture below.

format cells font formatting in excel

We can also achieve following formatting using this

  • Bold
  • Italic
  • Underline
  • Font size
  • Font style
  • Strike through
  • Superscript
  • Subscript

How to change the border style of the Excel cells?

To change the border style of the cell, select the cells we want to format and then click on the Border icon dropdown and then select the border style we want to apply.

Cells Border style in excel

In this case, the applied border looks like this.

all side border in excel cell

Instead of following above steps, we can do border formatting using Format Cells dialog box and select Border tab and select desired border.

change cell border dialog box in excel

How to copy the format of a cell to other cells in MS Excel?

To copy the format of a cell to other cells, we can use 'Format Painter' icon from the Ribbon.

First select the cell you want to copy the format from and then click on 'Format Painter' icon as displayed above.

copy cell formatting in excel

The mouse icon changes like below. Now select the target cells you want to paste the format into.

format change icon in excel

and the result looks like below.

all side border in excel cell

How to clear the formatting of cells in MS Excel?

To clear the formatting of the cells, first select the cells you want to clear format from. Now click on the Clear icon from Editing group on the Ribbon as displayed in the picture below and click on 'Clear Formats'. This clears the format of the cell.

Clear format in ms excel

To clear the cell formats including the cell data, click on 'Clear All'.

This also helps in following

  • Clear Contents - To only clear the content, not the formatting
  • Clear Comments - To only clear the comment of that cell (we will learn how to add comment for the cell later on)
  • Clear Hyperlinks - In case a string has become hyperlink and we want to remove the hyperlink, we can use this option.
 Views: 322 | Post Order: 7

Write for us