WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Format Cells in Excel

Format Cells in Excel

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

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?

You can download this Format Cell Excel Template here – Format Cell Excel Template

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.

Format Cell in Excel Example 0

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.

Example 0.1

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 the format window. Under “Custom,” apply the code as you want to see the date.

Format Cells Shortcut Key

Format Cell in Excel Shortcut

Format Cell in Excel Example 1

We get the following result.

Example 1.1

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 example, look at the below numbers.

Example 2

TIME values are varied from 0 to less than 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.

Format Cell in Excel Example 2.1

So we get the following result.

 Example 2.2

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.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Format Cell in Excel Example 2.3.0

We get the following result.

 Example 2.4.0

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

Example #3 – Format Date and Time Together

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

Format Cell in Excel Example 3

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

Example 3.1

We get the following result.

Format Cell in Excel Example 3.2

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 the 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 colors is the general rule everybody follows. For example, look at the below data.

Example 4

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

Format Cell in Excel Example 4.0

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

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

Example 4.0.0

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](-#,###)”

Format Cell in Excel Example 4.1

We get the following result.

Example 4.2

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.

Format Cell in Excel Example 5

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

### ” KG”

Example 5.1

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

Format Cell in Excel Example 5.2

Example #6 – Using Format Painter

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

 Example 6

For the first cell, we have the date format as “DD-MM-YYYY,” and the 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.”

Format Cell in Excel Example 6.1.0

Now click on the next cell to apply the formatting.

Example 6.2

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.

Format Cell in Excel Example 6.3

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 cells 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 a 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
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Format Cell Excel Template

Special Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More