What Is Excel Format Cells?
Format Cells in Excel is a feature that enables one to modify the appearance of the data in a single cell or cell range without changing the original data value.
Users can use the Format Cells option when they must use the actual value as is in calculations but display it in a format different from the default settings.
For example, the table below contains a date value, a negative number, and a time value.
To display the values in column D according to the corresponding formats specified in column C, we will copy the given numbers in the target cells. Then, use the feature Format Cells in Excel based on value to change the data types to the specified formats.
- Now, select a target cell, and press the excel keyboard shortcut Ctrl + 1 to access the Format Cells window.
- Next, click the Custom category in the Number tab. And then, enter the corresponding data format specified in column C in the field just below the section heading, Type, and
- Click OK.
Repeat the above steps for each target cell one at a time.
Furthermore, the above method is one way to access and use the option Format Cells in Excel based on value. But there are a few more ways to access and apply the feature, which we shall see in the following section.
Table of contents
Key Takeaways
- The Format Cells in Excel featurehelps modify the data format in one or multiple cells without changing the actual values.
- Users can use this feature to display data in the specified format, whether an existing or customized template.
- We can access the Number tab in the Format Cells feature from the Home tab (Number Format → More Number Formats, Format option, or the Number Dialog Box Launcher). Alternatively, we can use the keyboard shortcut Ctrl + 1.
- The Number tab in the Format Cells feature contains twelve format categories, with the General being the default format in which Excel displays the data.
How To Format Cells In Excel?
We can access the Format Cells window using the following methods, namely:
- Access from the Home tab.
- Using the Format Cells in Excel Shortcut.
Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)
If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.
Method #1 – Access from the Home Tab
We can open the Format Cells window from the “Home” tab in three ways, namely,
- Using the More Number Formats Option Under the Number Format Feature.
- Using the Number, Font, Or Alignment Group Dialog Box Launcher.
- Using the Format Option.
#1 – Using the More Number Formats Option Under the Number Format Feature
Select the target cell where we must change the data format → click the “Home” tab → click the Number Format drop-down button → select the More Number Formats option.
#2 – Using the Number, Font, Or Alignment Group Dialog Box Launcher
Select the target cell and click the Number, Font, or Alignment groupDialog Box Launcher. And depending on the group, the corresponding tab in the Format Cells window will open.
#3 – Using the Format Option
Select the target cell, and click the “Home” tab → click the Format drop-down arrow → select the Format Cells option.
Method #2 – Using the Format Cells in Excel Shortcut
We can use two keyboard shortcuts to access the Format Cells window.
- Pressing Ctrl + 1 will straightaway open the Format Cells window.
- Select the target cell or cell range and press the shortcut keys Shift + F10 to access the context menu. And then, we can select Format Cells from the context menu. [Alternatively, right-click the target cell or range to open the context menu.]
The above methods will open the Format Cells window, where we can choose the appropriate format category and the type to apply in the chosen cell, as shown below.
Let us take a basic example.
The table below contains a list of negative decimal values. We will display the negative values as fractions up to two decimal places within brackets and in Red font.
The steps to format cells in excel are as below –
- Select cell B2, enter the formula =A2, and press Enter.
And using the fill handle, update the formula in cells B3:B4. - Select the cell range B2:B4, and click Home → Number Format → More Number Formats to open the Format Cells window.
[ Alternatively, select the cell range B2:B4, and click Home → Number group Dialog Box Launcher.
Or, select the cell range B2:B4, and click Home → Format → Format Cells….
Or, select the cell range B2:B4, and right-click or press the keyboard shortcut Shift + F10 to open the context menu. And choose Format Cells.
Or, select the cell range B2:B4, and press the keyboard shortcut Ctrl + 1.]
The Number tab in the Format Cells window will open. - Choose Custom as the category in the Number tab in the Format Cells window. And enter the specified fraction format in the field below the section heading, Type, as shown below.
Finally, click OK to get the negative values displayed in the required format, as shown below.
Examples
Sometimes, we may need to custom Format Cells in Excel. And the required format type will be unavailable in the applicable category in the Number tab. Thus, we can create the specified custom format template under the Custom category in such scenarios. And then apply the format in the target cells.
Let us consider the following custom Format Cells in Excel examples to use the feature effectively.
Example #1 – Format Date Cell
The table below contains a list of product codes and their order dates. But the order date format is General in Home → Number Format. And as Excel considers date values as serial numbers, we see the dates in column B as serial numbers.
If the requirement is to display the order dates as proper date values in column D according to the format provided in cell C2. Then, the steps are as follows:
- Step 1: Select cell D2, enter the formula =B2, and press Enter.
And using the fill handle, update the formula in cells D3:D7.
- Step 2: With the target cells D2:D7 selected, choose Home → Number Format → Short Date.
So, we obtain the date values in the Date format.
However, the target cells do not show the date values in the date format specified in cell C2.
Thus, we must check the other Date format types from the Format Cells window.
- Step 3:
- With the target cells selected, open the Format Cells window using the keyboard shortcut Ctrl + 1.
- And as the target cells’ format is Date, the Date category shows the default date format type.
- Furthermore, when scrolling down the format types, we will observe the required date format is unavailable.
Thus, we must check the Custom tab for the required date format. It shows a few date format types. But we will be unable to Format Cells in Excel, as the existing ones do not include the format type we want for our data.
- Step 4: Enter the required date format “ddd, mmm d, yyyy” in the field provided under the section heading, Type.
Finally, clicking OK will apply the specified custom date format in the target cells, as shown below.
Thus, if the existing format types fit our requirements, we can use them. Otherwise, we can create a customized date format using the characters “d”, “m”, and “y”in the Custom tab and apply it in the required cells.
Example #2 – Format Time Cell
The following table contains five employees’ swipe in and out time data.
However, the time values in the cell range B2:C6 are serial numbers in decimal format as the data format set in Home → Number Format is General.
The steps to display the time data in the proper time format are:
- Step 1: Select the cell range B2:C6, and right-click to open the context menu. And then, choose Format Cells to open the Format Cells window.
- Step 2: Choose the Category in the Number tab as Time and select the required format type from the available options.
On the other hand, we can also choose the Custom category, and select the appropriate time format type to apply to the chosen cells. We shall select the format “h:mm AM/PM” in this example.
Finally, click OK to display the time values in the target cells in the chosen time format type.
Example #3 – Format Date and Time Together
The table below contains five orders’ numbers and their delivery receive time details.
However, the column B values appear as serial numbers instead of expressions containing date and time values, as the cells’ data format is General in Home → Number Format.
The steps to achieve the desired format showing date and time together for the column B values are as follows:
- Step 1: With the column B data selected, press Ctrl + 1 to access the Format Cells window.
And when we click the Date or Time category, we find format types showing date and time together.
But, if the listed options do not meet the requirement, we can check the Custom tab.
- Step 2: The Custom tab shows two suitable format types. However, if the requirement is to display the column B values in the format “dd-mmm-yyyy hh:mm AM/PM”, unavailable in the Custom category. Then, enter the required format type in the field below the section heading, Type, as shown below.
Finally, click OK to show the column B values in the specified format containing the date and time.
The source data in column B showed the values as decimal numbers.
So, once we change the column B data format using the above steps, Excel converts the whole part of the values into dates in the proper date format. And it shows the fractional part of the values as time values in the specified time format.
Example #4 – Positive and Negative Values
The following table shows a customer list and the balance points in their accounts in column B. And while some numbers in column B are positive, others are negative.
If the requirement is to distinguish between positive and negative values. Then, we can format the negative numbers to display in Red and within brackets.
- Step 1: With the cells B2:B6 selected, open the Format Cells window using the shortcut Ctrl + 1.
On selecting the Number category in the Number tab, we will see a few options to display negative values, which we can apply by choosing the required option and clicking OK.
On the other hand, we can use the Custom category to display the negative values in the required format type.
- Step 2: Click the Custom category and type the format specified in the field just below the section title Type.
The specified number format will show the positive numbers unchanged but with commas inserted according to the number of digits (“#,###”). And the negative values will display in Red, within brackets (“(-#,###)”), with commas inserted according to the number of digits.
And click OK to get the positive and negative numbers displayed distinctively, as shown below.
Example #5 – Add Suffix Words to Number
The table below shows a list of Vineyards and their sizes in acres.
If the requirement is to add the unit acres as a suffix to each value in column B. Then, we can do so from the Custom category in the Number tab in the Format Cells window. The steps are:
- Step 1: With column B cells B2:B6 selected, click the shortcut Ctrl + 1 to open the Format Cells window.
Next, choose the Custom category and update the required format type, “#,###” acres””, in the field below the section heading, Type.
And clicking OK will result in the numbers in column B displayed with the unit acres suffixed and commas inserted based on the number of digits in the values.
Furthermore, this method will allow us to perform calculations involving such numbers with units.
Example #6 – Using Format Painter
The table below shows trial results in column B as part of an experiment.
If the data type in cell B2 is in the custom scientific notation format “0E+00” and the requirement is to apply the same format to the remaining results.
Then, we can use the Format Painter option from the Home tab to apply the format in the remaining target cells. The steps are:
- Step 1: Select cell B2, and click Home → Format Painter.
- Step 2: Using the fill handle in excel, apply the chosen format in cells B3:B6.
And once we drag the fill handle till the last target cell, the Format Painter will apply the chosen custom scientific notation format type to all the remaining target cells.
Important Things To Note
- Though the feature Format Cells in Excel displays the numbers in the chosen format type, their values remain unchanged.
- Check the format categories in the Number tab in the Format Cells window for the appropriate format type. And if you must create a custom format, select the Custom category and add the required custom format template to the existing list of format types.
- If you are unsure about the custom format template, click an existing similar format type in the applicable format category in the Number tab in the Format Cells window. And then, choose the Custom category, where the field below the section heading, Type, will automatically display the chosen format type’s template. Next, modify the format template in the Custom category and apply it to the target cells.
Frequently Asked Questions (FAQs)
We can format cells as currency in Excel using the format types in the Currency or Custom category in the Number tab in the Format Cells window.
For example, the table below shows a list of items and price drops as negative values in column B.
And though the column B header indicates that the column B cells contain currency values, the values do not appear as currencies.
So, here is how to format the column B values as negative currencies, with the Minus sign replaced with brackets and Red font. And the values will display with commas based on the number of digits they contain. The steps are:
• Step 1: With the target cells selected, open the Format Cells window by pressing Ctrl + 1.
And then, we can select the Currency or Custom category to choose the required negative currency value format type.
The Custom category will show the required format template based on the format chosen in the Currency category. However, if we must modify it, we can click the template type from the options listed in the Custom category. On doing so, the option will get displayed in the field right below the section heading, Type, as shown below. And we can change the template according to our requirements.
Finally, clicking OK will show the values as currencies in the specified format.
We can Format Cells in Excel to keep leading zeros by creating a format template, “0000000000”, in the Custom category in the Number tab in the Format Cells window.
For example, the requirement is to display the 10-digit bank account numbers in column A.
However, as the first few digits are zeros followed by the last one or two digits being non-zero, Excel removes the leading zeros, though we enter them when updating the list.
Thus, the steps to avoid Excel from removing the leading zeros are:
• Step 1: Select the cell range A2:A6, and press Ctrl + 1 to open the Format Cells window.
• Step 2: Choose the Custom category, and update the required format template in the field below the section heading, Type, as shown below.
Finally, clicking OK will show the leading zeros in all the bank account numbers.
The template ensures the final value contains 10 digits, with the leading zeros completing the value.
We can format cells for phone numbers in Excel using the Special or Custom category in the Number tab in the Format Cells window.
For example, the table below contains a list of employees and contact numbers. But the contact numbers in column B are not in the proper phone number format.
So, the steps to display the column B values in the proper phone number format are as follows:
• Step 1: With the target cells selected, press Ctrl + 1 to access the Format Cells window.
And then, choose the Number tab → Special category → Phone Number format type.
We can use the format that Excel offers, or the Custom tab that will show the format template of the Phone Number format type chosen in the Special category.
We can use the template as is or modify it according to the requirement in the field below the section heading, Type.
And then click OK to achieve the outcome, as shown below.
In the above example, we chose to apply the phone number format template Excel offered. And thus, the Home → Number Format shows the target cells’ format as Special. On the other hand, if we had modified the template in the Custom category, the Home → Number Format option would show Custom as the format.
Download Template
This article must help understand the Format Cells in Excel, with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
This has been a guide to Format Cells In Excel. Here we learn to format and customize cells using different methods & shortcuts, along with examples & a downloadable excel template. You can learn more from the following articles –
Leave a Reply