Format Cell in Excel – Table of Contents
Excel Format Cell
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. Formatting of the cell depends upon the nature of the data we have. In this article, we will show you how to format cells in excel with different scenarios.
How to Format a Cell in Excel?
Below are some of the examples 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 excel format to the cell to see the date and time correctly.
For an example look at the below data.
This looks like a serial number for us but when we apply date format in this serial numbers, we get date values.
The date has a wide variety of formats and below are a list of formats we can apply to dates.
You can apply any of the formatting code 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.
Excel Format Cells Shortcut Key
We get the following result.
Example #2 – Format Time Cell in Excel
As I told Date and Time are stored as serial numbers in excel. Now it’s time to see the TIME cell format 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 0.70192 is equal to the time of 16:50:46.
4.9 (1,353 ratings)
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 an example look at the below data.
Let’s apply 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 date 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 an 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, 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.
- Date and Time are together in excel formatting.
- 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
- Share an Excel Workbook
- Export Excel into PDF File
- Venn Diagram Excel Examples
- Conditional Formatting in VBA
- How to Format in VBA?
- How to Format Phone Numbers in Excel?
- Accounting Number Excel Format
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
