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.

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:

• Home ⇒ Format ⇒ Cells ⇒ Format Cells
• Right-Click ⇒ Format Cells command
• Home ⇒ Number group ⇒ click on the dialog box launcher.

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.

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.

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.

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.

You can customize the border,

Steps:

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

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

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

2.2

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

Steps:

• Select the More Borders option.

• 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.

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

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 Home ⇒ Font group of commands ⇒ open the Fill Color menu and choose a 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.

4.2 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.

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.

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.

6. Changing the Alignment of Cell Contents

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

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

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

• Bottom alignment moves the cell contents to the bottom of a cell.

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

• Left alignment moves the cell contents to the left of a cell.

• Right alignment moves the cell contents to the right of a cell.

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.

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.

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

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.

• 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.

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

You can also rotate the text 90 degrees.

• Use the Rotate Text Up.

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.

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

10. Merging Cells

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

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

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

• You may apply a background color if you want in the merged cells.

11. Applying Default Cell Styles or Create New

To insert a cell-style format,

Steps:

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

To create a custom style,

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

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

• 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.

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

• 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.

If you want to remove the style,

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

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.

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.

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.

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.

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.

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.

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.

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.

Here are the formatted negative numbers.

The codes below are also for formatting negative numbers.

Codes:

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

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.

Now, we will show an example of using the Superscript feature. Mark the Representative employees by Representative⁽¹⁾, Representative⁽²⁾, and Representative⁽³⁾.

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.

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

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

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

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.

To format the dates,

Steps:

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

Click on the image to enlarge

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

To format the factional numbers,

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

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

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.

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

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

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.

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

• 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.

Click on the image to enlarge

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

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.

• 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.

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…

• 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.

You will see the content of the cell C6 shaded.

Some Useful Shortcuts to Format Cells in Excel

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.

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

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.

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.

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

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.

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.

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.

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

How to AutoFormat Cells in Excel

Steps:

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

• Select All Commands from the “Choose Commands from” drop-down.
• Select AutoFormat.
• Click the Add ⇒ button.
• Click OK.

Click on the image to enlarge

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

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