Format Cells in Excel

Excel Format Cells

Formatting cells is one of the key techniques to master because most of the time we need to worry about the formatting cells in excel. The formatting of the cell depends upon the nature of the data we have. In this article, we will show you how to format cells with different scenarios.

How to Format Cells in Excel?

Example #1 – Format Date Cell

If you are not aware excel stores date and time as serial numbers. We need to apply an appropriate format to the cell to see the date and time correctly.

For example look at the below data.

This looks like a serial number for us but when we apply date format in excel in these serial numbers, we get date values.

The date has a wide variety of formats and below is a list of formats we can apply to dates.

You can apply any of the formatting codes to see the date as shown above in the respective format.

• To apply date format select the range of cells and press Ctrl + 1 to open format window. Under “Custom” apply the code as you want to see the date.

Format Cells Shortcut Key

We get the following result.

Example #2 – Format Time Cell

As I told Date and Time are stored as serial numbers in excel. Now it’s time to see the TIME formatting in excel. For an example look at the below numbers.

TIME values are varied from 0 to less 0.99999, so let us apply the time format to see the time. Below is the time format codes we can apply generally.

“h:mm:ss”

To apply the time format follow the same steps.

So we get the following result.

So the number of 0.70192 is equal to the time of 16:50:46.

If you don’t want to see the time in the 24-hour format then we need to apply the time formatting code like the below one.

We get the following result.

Now our time is showing as 04:50:46 PM instead of 16:50:46.

Example #3 – Format Date and Time Together

Date and Time are together in excel. We can format both Date and time together in excel. For example look at the below data.

Let’s apply the date and time format to these cells to see the results. Formatting code for this is “dd-mmm-yyyy hh:mm:ss AM/PM”

We get the following result.

Let’s analyze this briefly now.

The first value we had was “43689.6675” for this we have applied the date and time format as “dd-mm-yyyy hh:mm:ss AM/PM” so the result is “12-Aug-2019 04:01:12 PM”

In this number “43689” represents data and decimal value “0.6675021991” represents time.

Example #4 – Positive and Negative Values

When dealing with numbers positive and negative values are part of it. To differentiate between these two values showing them with different color is the general rule everybody follows. For example, look at the below data.

To apply a number format to these numbers and to show negative values in red below is the code.

“#,###;[Red]-#,###”

If you apply this formatting code you will able to see the above numbers like this.

Similarly showing negative numbers in brackets is also in practice. To show the negative numbers in the bracket and also with red color below is the code.

“#,###;[Red](-#,###)”

We get the following result.

Example #5 – Add Suffix Words to Numbers

For example, if you want to add suffix words along with numbers and still be able to do calculations is great, isn’t it?

If you are showing a person’s weight then adding the suffix word “KG” will add more value to your numbers. Below is the person’s weight in KG’s.

To show this data with the suffix word “KG” apply the below formatting code.

### ” KG”

After applying the code, the weight column looks like as shown below.

Example #6 – Using Format Painter

Format Painter Excel, you can actually apply one cell format to another. For an example look at the below image.

For the first cell we have date format as “DD-MM-YYYY” and remaining cells are not formatted. But we can apply the format of the first cell to the remaining cells by using a format painter.

Select the first cell, then go to the home tab and click on “Format Painter”.

Now click on the next cell to apply the formatting.

Now again select the cell and apply the formatting but this is not the smart way of using format painter. By selecting the cell double click on “Format Painter”. Once you double click we can apply the format to any number of cells, so apply to all the at once.

Things to Remember

• Formatting is a wide variety of subjects and has thousands of formatting styles.
• Number formatting is the ultimate thing you need to learn.
• We can format Date and Time together.
• Format Painter is the tool to use the one cell format to another.

Recommended Articles

This has been a guide to Format Cell in Excel. Here we discuss the top 6 tips to format cells including Date, Time, Date and Time Together, format painter, etc along with examples and downloadable excel template. You may also look at these useful functions in excel –

• Shortcut Key to Start New Line in Excel Cell
• Export Excel into PDF File
• How to Format Phone Numbers in Excel?
• Accounting Number Excel Format