Excel Format Cells

Formatting cells in Excel is one of the key techniques to master. We need to worry about the formatting of cells in Excel most of the time. However, the cell formatting depends upon the nature of our data. This article will show you how to format cells with different scenarios.

How to Format Cells in Excel?

Example #1 – Format Date Cell

Excel stores date and time as serial numbers if we are not aware. We need to apply an appropriate format to the cell to see the date and time correctly.

For example, look at the below data.

It looks like a serial number for us, but we get date values when applying date format in excelDate Format In ExcelThe date format in Excel can be changed either from the “number format” of the Home tab or the “format cells” option of the context menu.read more to these serial numbers.

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

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

  • To apply the date format, we must first select the range of cells and press “Ctrl + 1” to open the format window. Then, under “Custom,” we must apply the code as we want to see the date.

Format Cells Shortcut Key

We get the following result.

Example #2 – Format Time Cell

As we said, the date and time are stored as serial numbers in Excel. Now, it is time to see the TIME formatting in excelTIME Formatting In ExcelTo format time for any decimal value, use the following steps: Right-click on the cell>choose format cell> a window will open, go to time category> you will find all the time format types based on the decimal value> you can see a preview of all cell time formats, select one> save, and you’re done.read more. For example, look at the numbers below.

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

“h:mm:ss”

To apply the time format, we must follow the same steps:

So, we get the following result.

So, the number “0.70192” equals the time of 16:50:46.

If we do not want to see the time in the 24-hour format, we need to apply the time formatting code like the one below.

Now, our time is shown 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 the date and time together in Excel. For example, look at the below data.

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

Let us 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.”

“43689” represents data in this number, 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 by showing them with different colors is the general rule everybody follows. For example, look at the below data.

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

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

If we apply this formatting code, we can see the above numbers.

Similarly, showing negative numbers in brackets is also in practice. For example, to show the negative numbers in the bracket and red color, below is the code.

“#,###;Red”

Example #5 – Add Suffix Words to Numbers

If we want to add suffix words along with numbers and still be able to do calculations, it is great.

If we show a person’s weight, adding the suffix word “KG” will add more value to the numbers. Below is the person’s weight in “KG.”

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 ExcelFormat Painter ExcelFormat painter in Excel is a tool used to copy the same format of a single cell or a group of cells to the other cells. You will find it on the home tab in the clipboard section.read more, we can apply one cell format to another. For example, look at the below image.

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

We must select the first cell, then go to the “Home” tab and click on “Format Painter.”

Now, we must 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. Instead, double-click on “Format Painter” by selecting the cell. Once we double-click, we can apply the format to any number of cells, applying all 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 the date and time together.The Excel “Format Painter” is the tool to use one cell format for another.

This article is 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., examples, and a downloadable Excel template. You may also look at these useful functions in Excel: –

  • Shortcut Key to Start New Line in Excel CellExport Excel into PDF FileHow to Format Phone Numbers in Excel?Accounting Number Excel Format