How to Format Cells in Excel: Everything You Need to Know

 

What Options Are Available to Format Cells in Excel?

1. Ribbon Commands

The Ribbon has various commands for formatting a cell in Excel. You can find options for formatting alignment, font, styles, numbers, etc. Below are some formatting options for the Number group.

ribbon commands to format cells


2. Commands on Format Cells Dialog

There are four ways we can use to open the cell formatting options – the Format Cells dialog box.

Using Keyboard Shortcut:

  • HomeFormatCellsFormat Cells
  • Right-ClickFormat Cells command
  • HomeNumber group ⇒ click on the dialog box launcher.Opening Format Cell Dialog Box from Ribbon (Number Group)

1. Changing Cell Size

Steps:

To change the length of a cell or increase the column width,

  • Drag the column width icon left or right to adjust. Drag the row up or down to increase or decrease its height. You can also double-click to adjust the height or width automatically.

Increasing Column Width and Row Height

All the data can be seen clearly after formatting the cell size.

The options for changing the cell size are also under the Format group in the ribbon. See the following image.

Cell Size Options from Ribbon


2. Applying Cell Borders

2.1

Steps:

To apply borders to the data table,

Steps:

  • Select the data range and select All Borders under the Border drop-down. Follow the image below.

Applying Border to All Cells

The drop-down menu has many border options. If you want to use the border around the data table only, you can use the Outside Border. See the below.

Applying Outside Border

You can customize the border,

Steps:

  • To draw the border manually.
  • Select Draw Border under the Borders drop-down.

Applying Draw Border Feature

  • You will see dots at the edge of each cell. Connect them with your mouse.

Dotted Line Appeared

  • Select the Draw Border option from the Border drop-down. The borders will be created, and the dots will disappear.
  • Press Esc.

Borders to Divide Different Department

Note: There are some other features under the Draw Borders option.

  • Draw Border Grid feature helps you to draw borders without dragging the cursor like the Draw Border
  • Erase Border feature removes borders when necessary.
  • The Line Color and Line Style features help to set up border colors and styles.

2.2

If you want solid lines for outside borders and dotted lines for inside borders,

Steps:

  • Select the More Borders option.

Applying More Borders Feature

  • The Format Cells dialog box will appear.
  • Select the Border tab and choose the Line Styles and corresponding Border sides. You can see a preview of the border style in the Border section of the dialog box. You can choose different colors for borders.

In the image below, the Line Styles for the outside border are marked by red rectangles. For the inside borders, line styles for inside borders are marked dark blue.

Customizing Border from Border Tab

  • Click OK to see the desired border style in the data table.

Dataset with Desired Cell Borders

Note: To remove the border from a cell or range of cells, select them and use the No Border command from the border drop-down. Or you can press Ctrl + Shift + –.


3. Changing Cell Background Color

Steps:

By default, an Excel cell is in No Fill background format. To change the cell color to another color:

  • Select the cell ⇒ go to HomeFont group of commands ⇒ open the Fill Color menu and choose a color.

Applying Cell Background Color


4. Changing Font Type, Font Size, and Font Color

4.1 Changing Font Type

  • Select the cell or the range of cells and choose a font of your preference from the Font drop-down.

We set the font of the column headers to Amasis MT Pro.

Changing Font


4.2 Changing Font Size

Changing Font Size

In the image above, the IDs are sized to 14.

  • Apply any size (including fractional numbers) manually in the Font Size box.

The Font group also has 2 buttons to increase and decrease the font size. Follow the image below to see the procedure.

Using Buttons to Increase or Decrease Font Size

Note: Font size can also be changed using a keyboard shortcut. Just select the cell and press Alt + H + F + G to increase the font size. To decrease the size, press Alt + H + F + K.


4.3 Changing Font Color

Chester Paul, an employee, failed to achieve his work target. You want to mark his name in red font.

  • Select his name and open the drop-down of the Font Color
  • Choose Red, and you will see that the name is red.

Changing Font Color


5. Making Cell Content Bold, Italic, and Underlined

We can apply 3 different styles to the font of a text. These are: Bold, Italic, and Underline. You can find these styles in the Font group. Whenever you need to change a font style, just click the corresponding button.

We applied these styles to the text in cell C6.

Changing Font Style


6. Changing the Alignment of Cell Contents

In the following image, we’ve shown the Center and Middle alignments on the Full Name column.

Center and Middle Alignment

  • Top alignment feature moves the cell contents to the top of a cell.

Top Alignment

  • Middle alignment keeps the cell contents in the middle of a cell.

Middle Alignment

Bottom Alignment

  • Center alignment keeps the cell contents in the center position of a cell.

Center Alignment

Left Alignment

Right Alignment


7. Increasing or Decreasing Cell Indent

Indent is a formatting feature in Excel that lets the users move any data within a cell by changing the indent. In the following image, we have shown how to indent data into a cell.

Increasing and Decreasing Indent


8. Changing Text Orientation Inside Cells

To apply text orientation on a set of cells,

Steps:

  • Select the cells and choose any of the orientation options of your preference under the Orientation drop-down.

The following image shows that the columns are wide due to the headers, while the data under the header are narrower.

Data Table with Wide Columns

We applied Angle Counterclockwise orientation from the Alignment ribbon section to the range B5:E5. See the image below.

Applying Angle Counterclockwise Rotation

To use custom orientation,

  • Select the B5:E5 range first.
  • Select the Format Cell Alignment option under the Orientation drop-down, or press Ctrl + 1 to open the Format Cells dialog box.

Opening Format Cell Dialog Box for Orientation

  • Set an angle for the Orientations under the Alignment
  • Set up the Horizontal and Vertical Text Alignments according to your preference.
  • Select a Text control. We chose Wrap text.
  • Click OK.

Custom Orientation

You can see the custom-oriented texts in the following image.

Custom Oriented Texts

You can also rotate the text 90 degrees.

  • Use the Rotate Text Up.

Applying Rotate Text Up Orientation to Text


9. Wrapping Text in a Cell

The employees’ names are not fully visible here. We can use the Wrap Text command to fix the problem without increasing the column width.

Names Not Fully Visible

  • Select the cell range (B5:B12).
  • Select the Wrap Text command and autofit the column width if needed.

Use of Wrap Text Command


10. Merging Cells

The title is at the top left corner of the table.

Title in a Single Cell

To center the title at the top of the data table,

  • Use the Merge & Center command from the Alignment group.

Merging Cells Using Command

  • You may apply a background color if you want in the merged cells.
Note: The Merge & Replace command stores the data of the first cell in the merged cells. Say, you have two different data in cells A1 and B1. If you merge up to cell C1, only the data of A1 will be merged. The data of B1 will be lost. So you should not keep any data in the adjacent cell.

11. Applying Default Cell Styles or Create New

To insert a cell-style format,

Steps:

  • Select the cell or range of cells,
  • Go to HomeCell Styles drop-down and choose the style you want.

Applying Cell Style to a Row

To create a custom style,

  • Select New Cell Style from the Cell Styles drop-down.

Initiating New Cell Styles

  • The Style dialog box will appear.
  • Provide a Style name and click the Format… button.
  • Check or uncheck the Style includes parameters.

Formatting New Cell Styles

  • The Format Cells dialog box will appear.
  • Go to the tabs and choose your formatting. We will format the Font and Fill

The following picture shows the Font, Font Color, Font Style, and Size of the font that I chose.

Setting up Font for Cell Styles

We applied a Fill background color to the column headings. We also select a Pattern Color and Style to make the header look unique.

Applying Fill Color to Cell Styles

  • Click OK. The name of the Custom Style appears under the Cell Styles drop-down.
  • Select it for the headers, and you can see the style applied.

Custom Cell Style on Selected Row

If you want to remove the style,

  • Select the styled cells and choose Normal under the Cell Styles drop-down.

Removing Cell Styles

If you want to edit the Custom style,

  • Open the styles from the Styles drop-down,
  • Right-click on the Custom Style, and select Modify from the Context Menu.

Modifying Custom Style


12. Use of Thousand Separators in Numbers

Here, we have used the Thousand Separators for large and whole numbers. You can find the Thousands Separator button in the Number group.

Use of Thousand Separator


13. Increasing or Decreasing Decimal Places

You can change the decimal places by selecting the buttons to increase or decrease decimal places in the Number group. See the image below.

Increase or Decrease Decimal Places

Note: There are also some other buttons to insert the thousand separator, percentage, and accounting format.

14. Changing Number Formats

Excel can recognize the following data types when you write them in a proper format: number, text, date, time, and percentage. However, we can format them according to our preferences. The upcoming sections will show a detailed description


What Are the Available Number Formats in Excel?

The following image shows the number of formats available in Excel.

Number Format Drop-down

As you can see, there are 12 default formats available in Excel. Additionally, you can modify the custom format based on your data. When we do cell formatting, most of the time it refers to formatting the numbers.

Go through the table below to get a basic idea of them.

Format Description
General When you type a number into Excel, it uses the default number format. Numbers typed with the General format are often displayed exactly as you input them. If the cell is too small to display the complete number, the General format rounds the numbers with decimals. For large numbers (12 or more digits), the General number format additionally employs scientific (exponential) notation.
Number Used to display numbers in general with decimals. You can choose the number of decimal places to display, whether to use a thousand separator, and how to display negative integers.
Currency Used for showing values with the default currency symbol. You can specify the number of decimal places that you want to use, whether you want to use a thousand separator, and how you want to display negative numbers.
Accounting We also use it for monetary values, however, it aligns currency symbols and decimal points in a column.
Date Date and time serial numbers are displayed as date values according to the locale (place) that you pick. Date formats that begin with an asterisk (*) respond to changes in the Control Panel’s regional date and time settings. Control Panel adjustments do not affect formats denoted by an asterisk.
Time This format’s functionality is similar to that of the Date format. It just displays date and time serial numbers as time values.
Percentage The cell value is multiplied by 100, and the result is displayed with a percent (%) sign. You can define the number of decimal places to be used.
Fraction Displays a number as a fraction based on the fraction type you provide.
Scientific Displays a number in exponential notation, replacing part of it with E+n, where E (Exponent) multiplies the preceding number by 10 to the nth power. A 2-decimal Scientific format, for example, displays 276384772367 as 2.76E+10, which is 2.76 times 10 to the 10th power. You can define the number of decimal places to be used.
Text Treats cell content as text and displays it precisely as you input it, even when you type numbers.
Special Can be used to represent a number as a postal code (ZIP Code), phone number, or Social Security number.
Custom You can change a copy of an existing number format code. This format is used to add a custom number format to the list of number format codes. Depending on the language version of Excel installed on your computer, you can add between 200 and 250 special number formats.

When you insert a number in a cell, Excel displays it in its default form. This number is treated as General by Excel.

The following image shows different number formats like Number, Accounting, Currency, Percentage, Fraction, and Text for the numbers in column B. Also, you will see more number formats, such as Short Date, Long Date, Time, and Scientific.

Displaying Different Format of General Formatted Numbers

You can apply these formats from the Number drop-down. To apply these formats, you just have to select the range of cells containing numbers and select a suitable format from the drop-down.

These are the default number formats employed by Excel.


Number Formatting Not Working in Excel?

Sometimes, you may see a series of hash symbols (#) while entering a number in a cell. This may happen because of improper formatting of the cell.

For instance, if your cell is formatted to date but you enter a negative number, you will face this problem.

Number Formatting Issue and Solution

You may also notice that there is another issue with the number in cell J6. This number is formatted to date but exceeds the serial number a date can store. The highest date in Excel is 12/31/9999, which is recognized as 2958465 by Excel.

You may also see the hash symbols if the numbers don’t fit in a cell completely. In that case, just increase the column width.


How to Use the Format Cells Dialog Window to Format Cells in Excel?

Some formatting options are not available in the ribbon but you can get them in the Format Cells dialog box.

1. Displaying Negative Numbers

Sometimes, users prefer to express negative numbers differently. Sometimes it’s better to show negative numbers differently so the user can understand that there is a decrease in the data, especially in accounting.

Here are some default options to show negative numbers.

  • Here, we have selected some numbers and navigated to the Number option of the Format Cells dialog box.
  • After that, we selected one of the four Negative number formats and clicked OK.

Formatting Negative Numbers

Here are the formatted negative numbers.

Formatted Negative Numbers

The codes below are also for formatting negative numbers.

Codes:

  1. #.00; (#.00)
  2. 00_); (0.00)

Custom Formatting for Negative Numbers

Note: To see how to use the codes in the Format Cells dialog box, follow the process shown in the Customizing Number Format section.


2. Adding Strikethrough/Superscript/Subscript

Strikethrough means a line through the text in a cell. If something is excluded or finished from your dataset but you want to keep the record in the sheet, you can use a Strikethrough for that purpose.

The employee, Ryan Russell, resigned from the company. We are going to apply Strikethrough in the row where his entries are stored.

  • To put the Strikethrough in the row, select the range of cells and press Ctrl + 1 or click the dialog box launcher in the Font group to open the Format Cells dialog box.
  • Check Strikethrough and click OK. Make sure you select the Font tab of the Format Cells window.

Adding Strikethrough

Now, we will show an example of using the Superscript feature. Mark the Representative employees by Representative(1), Representative(2), and Representative(3).

Steps:

  • To use Superscript in a cell, double-click on that cell to go to the edit mode.
  • Click the dialog box launcher of the Font group.

Editing Cell Content

  • The Font tab of the Format Cells window will appear.
  • Check Superscript and click OK.

Adding Superscript

  • Enter the number as we showed earlier. You will see the number as Superscript to the text.

Typing Number to Superscript

  • Press Enter and apply the procedure to the other “Representative.” Alternatively, you can copy the data and replace 1 with 2 and 3.

Superscript Added to the Texts

You can add a Subscript to a text in the same way.


3. Some Built-in Number Formats That Are Not Available in Number Drop-down

The following image shows the date and fraction formatted numbers. We want to add the years to dates and make the fractions more precise as the default format returns approximate values up to 1 digit.

Fractions and Dates Are not Precise

To format the dates,

Steps:

  • Select them and press Ctrl + 1 to open the Format Cells
  • Select DateType ⇒ type of date format containing year (14-Mar-12).
Formatting Date with Year

Click on the image to enlarge

  • Click OK. The years will be added to the dates.

Formatted Date with Years

To format the factional numbers,

  • Select them and open the Format Cells
  • Select FractionUp to two digits (21/25). You may also choose the Up to three digits option for better precision.

Selecting Faction Format

Click OK. The fractions will have a more precise format.

Formatted Fraction Numbers

These are some built-in number formats that are not available in the ribbon list.


4. Customizing Number Formats

We commonly use units such as kilometers, kilograms, degrees Celsius, inches, centimeters, etc., but we cannot normally use those units in numeric data. Here, I’ll show you how to format numeric data to such units using the Custom Format feature.

Here, we have some distances between two places and their temperatures. To convert numbers to km (kilometer) units, use the following code for the data range.

Code:

0.00 “km”

To convert numbers to degree Celsius units, use the following code:

0.00 °C

To format the distances with kilometers units,

Steps:

  • Select the range of numbers.
  • Open the Format Cells dialog box by pressing Ctrl + 1.
  • Select Custom >> Type the code 00 “km” in the Type section.
  • Click OK.

Custom Format to Add Kilometer Units

You can see the numbers converted to the kilometer units next.

Numbers Converted to KM Units

  • Insert the code for degree Celsius units in the Format Cells dialog box.

Formatting Unit to Kilometers and Degree Celsius

Thus you can customize number formats to present numeric data in any form.


How to Protect Cell Format in Excel

By default, all the cells in Excel are locked, although it doesn’t have any effect unless you protect the sheet. If you don’t want to lose the formatting in a sheet, you need to protect the sheet.

Steps:

  • Select Format Protect Sheet.

Opening Protect Sheet Option

  • In the Protect Sheet dialog box, insert a password and check the following options shown in the image below.

Applying Protect Sheet Features

  • Reenter the password in the next pop-up window, and you will see that the formatting features are grayed out. No one can make any changes to the sheet, and if someone does, a warning message will be delivered.
Protected Sheet

Click on the image to enlarge

  • To enable formatting in the sheet, unprotect it with the password.

Unprotecting Sheet


How to Shade Cells in Excel

Shading a cell means inserting a background color to that cell and applying some effects or pattern color. We have already seen how to insert background color in a cell. So let’s learn some new tricks for formatting cells.

Steps:

  • Select the cell you want to shade and open More Colors options from the Fill Color drop-down. Here, I’ll shade the C6 cell.

Opening More Colors Option

  • The Colors dialog box will pop up.
  • You will find some default colors in the Standard. However, I want to make a custom color. So I selected the Custom tab and dragged the marked icons in the image to create a new color.

Creating Shade Color

The color is under the New portion.

 

  • Click OK to continue.
  • Insert some color effects to provide shading in the cell.
  • The background color will be applied to the cell.
  • Keep the cell selected and press Ctrl + 1 to open the Format Cells dialog box.
  • Select Fill >> apply a Pattern Style >> click the Fill Effects…

Applying Pattern Style

  • The Fill Effects dialog box will pop up.
  • Select Two colors >> Color 1 and Color 3 according to your preference.
  • Choose one of the Shading styles. Here, I select the Diagonal up style and then choose the 1st Variant.
  • Click the OK button in the Fill Effects and Format Cells dialog box one after another.

Applying Fill Effects

You will see the content of the cell C6 shaded.

Shaded Cell Text


Some Useful Shortcuts to Format Cells in Excel

Keyboard Shortcuts Output
Ctrl + Shift + ~ Returns General format
Ctrl + Shift + % or 5 Returns Percentage format
Ctrl + Shift + $ or 4 Returns Currency format
Ctrl + Shift + ^ or 6 Returns Scientific format
Ctrl + Shift + ! or 1 Returns Number format
Ctrl + Shift + # Returns Date format
Ctrl + Shift + @ Returns Time format
Ctrl + B Returns or removes Bold format
Ctrl + I Returns or removes Italic format
Ctrl + U Returns or removes Underline format
Ctrl + 5 Returns or removes Strike format

How to Copy Cell Format in Excel

a)

Steps:

  • We want to copy the formatting of cell C6 to C8.
  • Select a cell with data and formatting, and press Ctrl + C to copy it.

Copying Cell Content

  • Right-click on the cell where you want the formatting copied and select Paste Options >> Formatting Icon. See the image below.

Pasting Format

You will see that the formatting is copied only on C8.

This can also be done for multiple cells. Just select a range of cells before pasting the format.

Pasting Format to Multiple Cells

b)

Say you want the E6 cell to have the formatting of cell C6.

Steps:

  • Apply the Format Painter on E6.
  • Select the C6 cell and click the Format Painter button from the Clipboard. You will see a painter icon (marked as 3) beside the cursor.

Initiating Format Painter

  • Click on cell E6. You will see the formatting of the C6 cell painted to cell E6.

Cell Format Copied to Another Cell

Note: You can copy the formatting of a cell to another cell of other workbooks too. Just open the new workbook and apply the same process shown in this section.

How to Clear Cell Format in Excel

  • Select the cell or range of cells and then go to the Editing group >> Clear drop-down >> Clear Formats.

Clearing Cell Format

Here, I cleared the formatting from C6 and E6 cells. This command returns a cell’s default formatting.


How to Perform Multiple Text Formatting in a Single Cell

Let’s say you have a long paragraph inside a single cell like the following image.

Paragraph

Steps:

  • Double-click on the cell or go to the formula bar to enable the text editing mode.
  • Select a text or multiple texts.
  • It will pop up the commands of the Font group automatically. Here, I changed the font color of the selected texts.

Applying Multiple Font Settings

In the following image, I used the keyboard shortcuts Ctrl + I, Ctrl + B, and Ctrl + U to apply Italic, Bold, and Underline commands, respectively.

Multiple Formatting in a Single Cell

Note: You can add line breaks within a cell If you don’t want to use the Wrap Text command. For this purpose, just press Alt + Enter after the text you want the line break.

How to AutoFormat Cells in Excel

Steps:

  • Add this command to the Quick Access Toolbar.
  • Select the Quick Access Toolbar icon ⇒ More Commands…

Opening Quick Access Toolbar More Commands

  • Select All Commands from the “Choose Commands from” drop-down.
  • Select AutoFormat.
  • Click the Add ⇒ button.
  • Click OK.
Adding AutoFormat Command to Quick Access Toolbar

Click on the image to enlarge

Now, the AutoFormat command is added to the Quick Access Toolbar. See the image below.

Applying Format from AutoFormat Command

Here, we selected the data range (B5:G14) and the AutoFormat command. This opens the AutoFormat dialog box, and we see some formats for data tables. We chose a black-themed format here.


Download the Practice Workbook


Excel Cell Format: Knowledge Hub


<< Go Back to Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

Tags:

Meraz Al Nahian
Meraz Al Nahian

Md. Meraz Al Nahian has worked with the ExcelDemy project for over 1.5 years. He wrote 140+ articles for ExcelDemy. He also solved a lot of user problems and worked on dashboards. He is interested in data analysis, advanced Excel, statistics, and dashboards. He also likes to explore various Excel and VBA applications. He completed his graduation in Electrical & Electronic Engineering from Bangladesh University of Engineering & Technology (BUET). He enjoys exploring Excel-related features to gain efficiency... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo