If you’re wondering how to format cells in Excel to make your worksheets look good, you’ve come to the right place!

When you type numerical values or text strings in Excel, it uses default formatting to visualize your worksheet contents. For example, the font type it uses is Calibri in 11 points. The tool doesn’t use any cell colors, font colors, borders, drop-down menus, table styles, and so on.

Such a spreadsheet looks overly boring. Your audience might barely find it attractive. Not to mention, whoever uses the worksheet shall find it harder to read the contents.

However, if you know various methods to format the contents and elements of your Excel worksheet cells, you can enhance readability and look.

Read the article until the end to learn all the popular cell formatting options in Excel. Also, don’t forget to try out the methods to practice these with your own datasets.

What Is Formatting a Cell in Excel?

Formatting a cell in Excel means adjusting its appearance to make data more readable or visually appealing. This includes changing fonts, colors, borders, alignments, texts, numbers, and more.

You can apply number formats to display values in different ways, such as currency or percentages. On the other hand, conditional formatting allows you to highlight cells based on specific criteria.

Cell styles provide pre-defined combinations of formatting options for quick application. You can merge cells to combine their contents into one or wrap text to display long content within a cell.

Additionally, formatting options like cell shading, text color, and font size help organize and emphasize data effectively. Overall, formatting enhances the presentation and clarity of information in Excel spreadsheets.

What Is the Format Cells Dialog in Excel?

Format Cells dialog is the primary cell formatting tool in Excel.

It contains the following tabs:

1. Number
   • This tab allows you to format the numerical content of cells.
   • Options include number type (General, Number, Currency, Accounting, Date, Time, Percentage, Fraction, etc.).
   • You can specify decimal places, use a thousand separator, and control negative number formatting.

2. Alignment
   • Here, you can adjust the horizontal and vertical alignment of cell content.
   • You can also set text orientation, wrap text within cells, and control indentation.

3. Font
   • This tab lets you modify the appearance of the font within the selected cells.
   • Options include font style, size, color, underline, effects (strikethrough, superscript, subscript), and character spacing.

4. Border
   • You can add or remove borders around cells or cell ranges using this tab.
   • Options include line style, color, and border placement (top, bottom, left, right).

5. Fill
   • This tab allows you to fill cells with a solid color, gradient, or pattern.
   • You can choose from a variety of colors and patterns to customize the fill of selected cells.

6. Protection
   • Here, you can control the protection settings for cells.
   • It doesn’t let you change any cell formatting though.

The Format Cells tool might not show all the tabs in specific scenarios. For example, you can call the Format Cells dialog when using the Conditional Formatting tool. In this case, you can only access the Number, Font, Border, and Fill tabs of the Format Cells dialog.

Different Ways to Access Format Cells

The best way to access the Format Cells dialog for a selected cell range is by pressing the Ctrl + 1 keys when working on the Excel app. On a Mac, you must press Command + 1 to access the tool.

You can also go to the Font, Alignment, and Number commands block in the Home tab and click on the tiny arrow at the bottom right corner of the section to bring up the Format Cells dialog.

Alternatively, you can also press Alt + H + FM to call the Format Cells dialog box.

Suppose, you’re creating a conditional formatting rule to programmatically format cells and their data in Excel. Here, you can access Format Cells by clicking the Format button in the New Formatting Rule dialog box.

Format Cell Styles in Excel

If you want to stylize the cells your way, you can change the background color and border.

Highlight the target cell or cell ranges and click on the Fill Color drop-down arrow.

In the context menu that opens, you can choose your favorite color from Theme Colors, Standard Colors, and More Colors options.

Suppose, you want to introduce multiple color coding in the spreadsheet. The best way is to use RGB and HEX color codes for different content.

For example, HEX code EE4B2B or RGB code (238, 75, 43) for bright red color as column headers, HEX code 0096FF or RGB code (0, 150, 255) for bright blue as row colors, etc. If you create a cell background shading guideline like this, you can ensure consistent cell formatting in all worksheets.

To input such codes, go to the Custom tab of the Colors dialog in the More Colors menu of the Fill Color drop-down arrow.

To introduce borders to differentiate direct types of data in the worksheet, highlight the target cells and click on the More Borders drop-down arrow in the Font commands block.

In the context menu that opens, you shall find various border styles, like the ones outlined below:

• Bottom Border
• Right Border
• Left Border
• Top Border
• All Borders
• Outside Borders

If you wish to learn Excel cell borders in detail, check out the following tutorial now:

📒 Read More: 10 Ways to Add Borders in Microsoft Excel

The Cell Styles drop-down in the Styles block of the Home tab lets you choose pre-configured styles for cells.

Suppose, you’d like to customize the cell style of the above dataset to make it attractive and readable.

Highlight the column header row and click on the Cell Styles drop-down menu.

Now, choose any format from the Titles and Headings section in the context menu.

Then, highlight the rest of the cell range in the dataset and pick any cell style from the Data and Model or Themed Cell Styles sections.

To format the number columns in the dataset, select the cells of the Price column and choose Currency for number values with two decimal points or Currency [0] to format numbers to no decimal points.

Similarly, choose Comma style for the thousand separators with decimal points and Comma [0] for those without decimal points.

Using Office Themes

Another cool way to quickly personalize cell formatting is by using a theme from the Themes drop-down in Excel’s Page Layout tab.

Themes contain three elements of cell formatting. These are Colors, Fonts, and Effects.

If your dataset has any or all of the above formatting elements, the selected Office Theme shall modify the elements accordingly.

To select a theme, open the source worksheet and go to the Page Layout tab. There, click on the Themes drop-down and choose one from the context menu.

The chosen theme automatically modifies the whole workbook.

Format Numbers in Excel

Excel comes with 11 preconfigured number formatting styles and 1 custom formatting option.

Suppose, you’ve entered a fractional number in Excel, it’s highly likely that the cell will automatically convert it to a date value.

To reformat the cell to show fractional values, you must choose the specific number formatting.

In the above dataset, I’m trying to input the engine capacity allowed for a few locomotives. When I enter fractional capacities like 1/4, 1/3, etc., Excel converts those into short dates.

To fix this issue, I’ll choose the cell range where I’m going to enter fractional values.

Then, I’ll press Ctrl + 1 to bring up the Format Cells dialog. It shall open with the Number tab selected by default.

In the Number tab, I’ll choose the Fraction category from the left and a Type from the right, like Up to one digit (1/4).

Now, I can type the fractional values without any issues.

Custom number formatting accepts certain code that displays numbers or texts in unique ways.

Suppose, you’d like to get the run time in days in the above dataset.

To achieve this, select the cells below the Run Time column and bring up the Format Cells dialog.

Now, go to the Custom category in the Number tab and enter #,##0 in the Type field.

Click OK to apply the number formatting instructions.

You shall now see an approximate conversion of hours and minutes to days in Excel.

Number Formatting shortcuts

Find below some handy shortcuts to quickly change number formatting in Excel:

• Ctrl + Shift + @: Applies the hh:mm AM/PM time format in a cell
• Ctrl + Shift + ^: Inserts a scientific notation format with decimal places up to two digits
• Ctrl + Shift + #: Introduce the dd-mmm-yy date format
• Ctrl + Shift + $: It converts values to currencies with up to two decimal places
• Ctrl + Shift + %: Applies the percentage formatting to cell values
• Ctrl + Shift + ~: Converts all cell entries to the General number formatting
• Ctrl + Shift + !: Applies a thousand separator with two decimal places number formatting

Format Cell Data as a Table in Excel

The fastest way to apply the table formatting style to a group of cells in Excel is the Ctrl + T shortcut.

Select the source cell range in a worksheet and press the above hotkey to get the Create Table dialog.

Click OK. Excel shall convert the selected data into a table.

If you don’t like the cell color and border styles in the default table, you can use the Format as Table dialog to choose a different style from 60 different preconfigured table styles.

To apply any of the above table styles, simply select your dataset and go to the Format as Table menu.

Click on the style you like. Select OK on the Create Table dialog.

Excel shall apply the table formatting style that you’ve chosen earlier.

Formatting Fonts in Excel

The Font formatting feature of Excel lets’ you customize the following elements of the worksheet:

• Font: It lets you choose the typeface, like Calibri, Arial, etc.
• Font style: You can choose from options, like Regular, Italic, Bold, etc.
• Size: You can enter the font size of your choice to increase or decrease a character’s dimension.
• Underline: It underlines the text or number in a cell.
• Color: It defines the shade of the texts or numericals of the cell.
• Effects: Strikethrough, Superscript, and Subscript are the effects you can choose.

Suppose, you’d like to format the look of the font in the above dataset.

Firstly, select the column headers and press Ctrl + 1 to bring up Format Cells.

There, go to the Font tab and choose Bold (shortcut Ctrl + B) in the Font style menu. Click OK to apply.

Now, select the dataset in the rows below the column header.

Again, access Format Cells and navigate to Font.

Now, choose a text color.

The dataset now looks better than the default view.

Formatting Data Alignment in Excel

As you’ve already noticed, cell content shows in the standard horizontal orientation.

If you wish to place the data at an angle, you can use Alignment formatting for cells in Excel.

Select the target cell range and call the Format Cells dialog by pressing Ctrl + 1.

Go to the Alignment tab.

Click the Text pin inside the Orientation box and drag it up or down along the curved pathway.

The final position of the word Text shows how your cell content shall align.

Click OK to apply the changes you’ve made.

Find above the output of this cell formatting customization.

Format Cells With Conditional Formatting

A programmatic approach to format cells in Excel is by using the Conditional Formatting tool.

Suppose, you’d like to format all the rows in the above dataset that have 40 or more items.

Highlight the cell ranges that contain data.

Click on the Conditional Formatting drop-down and choose New Rule from the context menu.

The New Formatting Rule dialog shall show up.

There, choose the Use a formula… option and enter the following formula inside the Rule Description field:

=$B1>40

Now, click the Format button and choose the custom cell formatting you want if the formula is valid in a specific cell.

Click OK to apply the rule you’ve created.

Excel shall format the selected rows provided that the referred cells in column B match the threshold value, which is more than 40.

Don’t forget to customize the formula when using it on your own worksheet.

How to Format Cells Using Excel VBA

You can customize various elements of a cell using Excel VBA as well. It lets you automatically change many parameters with visual guidance. Use the following script if you want to practice the VBA macro-based method:

Sub FormatCells()
    Dim rng As Range
    Dim fontSize As Integer
    Dim fontStyle As String
    Dim fontColor As String
    Dim cellColor As String

    ' Get the header row
    On Error Resume Next
    Set rng = Application.InputBox("Select the header row by using your mouse", Type:=8)
    On Error GoTo 0

    ' Get the font size
    fontSize = Application.InputBox("Enter the font size", Type:=1)

    ' Get the font style
    fontStyle = Application.InputBox("Enter the font style (Italic or Bold)")

    ' Get the font color
    fontColor = Application.InputBox("Enter the font color (White, Red, Blue, Green, Black, Orange)")

    ' Get the cell color
    cellColor = Application.InputBox("Enter the cell fill color (White, Red, Blue, Green, Grey, Orange)")

    ' Apply the formatting
    With rng.Font
        .Size = fontSize
        If fontStyle = "Italic" Then .Italic = True
        If fontStyle = "Bold" Then .Bold = True
        Select Case fontColor
            Case "White": .Color = RGB(255, 255, 255)
            Case "Red": .Color = RGB(255, 0, 0)
            Case "Blue": .Color = RGB(100, 149, 237)
            Case "Green": .Color = RGB(0, 255, 0)
            Case "Black": .Color = RGB(0, 0, 0)
            Case "Orange": .Color = RGB(255, 165, 0)
        End Select
    End With

    Select Case cellColor
        Case "White": rng.Interior.Color = RGB(255, 255, 255)
        Case "Red": rng.Interior.Color = RGB(255, 0, 0)
        Case "Blue": rng.Interior.Color = RGB(100, 149, 237)
        Case "Green": rng.Interior.Color = RGB(0, 255, 0)
        Case "Grey": rng.Interior.Color = RGB(128, 128, 128)
        Case "Orange": rng.Interior.Color = RGB(255, 165, 0)
    End Select

    ' Repeat the same process for the data rows
    ' Get the data rows
    On Error Resume Next
    Set rng = Application.InputBox("Select the data rows by using your mouse", Type:=8)
    On Error GoTo 0

    ' Get the font size
    fontSize = Application.InputBox("Enter the font size", Type:=1)

    ' Get the font style
    fontStyle = Application.InputBox("Enter the font style (Italic or Bold)")

    ' Get the font color
    fontColor = Application.InputBox("Enter the font color (White, Red, Blue, Green, Black, Orange)")

    ' Get the cell color
    cellColor = Application.InputBox("Enter the cell fill color (White, Red, Blue, Green, Grey, Orange)")

    ' Apply the formatting
    With rng.Font
        .Size = fontSize
        If fontStyle = "Italic" Then .Italic = True
        If fontStyle = "Bold" Then .Bold = True
        Select Case fontColor
            Case "White": .Color = RGB(255, 255, 255)
            Case "Red": .Color = RGB(255, 0, 0)
            Case "Blue": .Color = RGB(100, 149, 237)
            Case "Green": .Color = RGB(0, 255, 0)
            Case "Black": .Color = RGB(0, 0, 0)
            Case "Orange": .Color = RGB(255, 165, 0)
        End Select
    End With

    Select Case cellColor
        Case "White": rng.Interior.Color = RGB(255, 255, 255)
        Case "Red": rng.Interior.Color = RGB(255, 0, 0)
        Case "Blue": rng.Interior.Color = RGB(100, 149, 237)
        Case "Green": rng.Interior.Color = RGB(0, 255, 0)
        Case "Grey": rng.Interior.Color = RGB(128, 128, 128)
        Case "Orange": rng.Interior.Color = RGB(255, 165, 0)
    End Select
End Sub

To learn how to create a macro using the above script, go through this quick Excel VBA primer tutorial:

📒 Read More: How To Use The VBA Code You Find Online

When you execute the macro created from this VBA script, you’ll get the following input boxes:

• Prompt to choose the first set of cell ranges
• Input box for font size
• Prompt to choose the font style, like italic or bold
• Input box to choose the font color from white, red, blue, green, black, or orange
• A prompt to choose the cell fill color from white, red, blue, green, grey, or orange
• The above prompts repeat for the data rows

I tested the script and applied the above cell formatting.

⚠️ Warning: Create a backup copy of your workbook before running any VBA macro on it. You won’t be able to use the Excel undo feature in a worksheet or workbook once you’ve run a VBA script.

Conclusions

There you go! These are some of the best techniques to format cells in Excel. If you practice these methods a few times, you’ll never find presenting Excel reports to layman or expert audiences with great readability and attention.

The Excel tutorial has shown you various manual, semi-automatic, and automatic methods to format cells. My personal choice is Excel VBA since it can guide you through the cell formatting process. I don’t need to remember the steps.

Did the article help you in learning the formatting skills of Excel? Commenet below! If you want more VBA scripts for custom cell formatting, mention the requirements in your comment so I can get you those scripts.