Worksheet Formatting (Ultimate Guide)

Get FREE Advanced Excel Exercises with Solutions!

Worksheet formatting in Excel involves changing the font style, fill color, border style, adjusting row height, column width, number formats, etc. of a sheet. Worksheet formatting is often required to increase the readability of data.

In this Excel tutorial, you will learn about the necessary features to format a worksheet. We will start our process with an unformatted worksheet and use various Excel features to format the worksheet.

The following image shows an overview of formatting a worksheet to increase readability and effectively visualize the data.

Overview of Worksheet Formatting in Excel

What Is the Importance of Worksheet Formatting in Excel?

Formatting a worksheet isn’t about the aesthetic only. It enhances readability, helps in data interpretation, and showcases professionalism. Finding required data from a properly formatted worksheet is easier compared to a messy worksheet. Formatting the worksheet also facilitates effective data analysis from the worksheet.

11 Examples of Formatting Worksheet in Excel

To format a worksheet, you need to change cell shading, borders, fonts, data alignment, and various other elements of a worksheet. Here are 11 examples of formatting a worksheet in Excel:

1. Turning Off Gridline in an Excel Worksheet

Consider the following dataset in an Excel worksheet. There is a data table in range B4:G15 and a title in Cell B2. The data table contains the Brand, Device, Model, Origin  Country, Release Date, and Price of various technological products.

Excel Dataset with no worksheet formatting

The worksheet is not formatted yet, so the dataset is poorly visualized and difficult to understand. To fix this problem, we will format the worksheet using various Excel features.

First, we will turn off the gridlines. Apply the following steps for that:

  1. Go to the View tab.
  2. Uncheck the Gridlines option to disable it.Turning Off Gridlines

As a result, the gridlines will turn off from the worksheet.

Gridlines Turned Off

2. Formatting Title in Excel Worksheet

In this section, we will format the title. Instead of displaying the title in one corner of the row, we want the title in the center of the row.

Here are two ways to do this:

2.1 Using Merge & Center Command

You can use the Merge & Center command to merge the cells of the above data table in the row of the title. This command will align the data in the center of the merged cells.

To apply the Merge & Center feature to format the title, apply the following steps:

  1. Select the row of the title.
    Here, I have selected the range B2:G2.
  2. Go to Home tab > Alignment group > Merge & Center command.Applying Merge & Center Command to Format Title

This will merge all the selected cells and bring the title to the center of the merged cell.

Worksheet Title in Merged Cells

Note: Merged cells function as a single cell. To reference the merged cell, you have to use the cell address of the top-left cornered cell of the range. For example, if you merge cells of range B2:G3, then the reference of this merged cell is B2.

2.2 Using Center Across Selection Option

Merging cells often creates problems in formula-based operations in Excel. To avoid such issues, you can use the Center Across Selection option as an alternative. This option only displays the value as center-aligned. But the value is stored in a single cell only.

To format the title with the Center Across Selection option, follow the steps below:

  1. Select the row of the title.
    Here, I have selected the range B2:G2.
  2. Click the Format Cells dialog box launcher from the Alignment group.
    Clicking Format Cells Dialog Box Launcher from Alignment Group
    As a result, the Format Cells dialog box will appear.
  3. In the Format Cells dialog box:
    • Go to the Alignment tab.
    • Set the Horizontal Text Alignment to the Center Across Selection option.
    • Click OK.

    Setting Horizontal Text Alignment to Center Across Selection option

This will display the title center-aligned to the row. However, you can notice that the title value is in a single cell (cell B2 in this case).

Worksheet Title displayed in center of the selected cells

3. Applying Cell Borders in an Excel Worksheet

Cell borders in Excel help to identify different sections of a worksheet. This is especially important for a worksheet with dense information. In such cases, adding cell borders increases the readability of data.

In this section, we will apply cell borders and change the border color in our worksheet. For the title, we will apply the Thick Bottom Border option and for the data table, we will apply the All Borders option.

Here are 3 examples of adding cell borders to format a worksheet:

3.1 Apply Thick Bottom Border to Title

The Thick Bottom Border option is useful to differentiate between sections. Thus, applying this border to the title will visually separate the title from the data table.

To apply the Thick Bottom Border option in the title, follow the steps below:

  1. Select the row where the title is displayed.
  2. Click the Borders dropdown from the Font group.
  3. Select the Thick Bottom Border option.Applying Thick Bottom Border to Worksheet Title

Now see, there is a thick bottom border in the title row.

Thick Bottom Border in Title

3.2 Apply All Borders Command

Here are the steps to apply the All Border command for the cells of the data table:

  1. Select the data table.
    Here, I have selected the range B4:G15.
  2. Click the Borders dropdown from the Font group.
  3. Select the All Borders option.Applying All Border Command in Data Range

As you can see now, borders have appeared around each cell of the data table.

Data Range with All Borders

3.3 Change Border Color

The border color in Excel is set to black (automatic) by default. However, you may need a different border color based on the background color of the worksheet.

Here are the steps to change the border colors in an Excel worksheet:

  1. Select the row with the title where the thick bottom border was applied.
  2. Click Borders dropdown > Line Color > your preferred color.Selecting Border Color for TitleAs a result, dot symbols will appear at each corner of cells throughout the worksheet.
  3. Click the Borders dropdown again and select the Thick Bottom Border option.
  4. Setting Borders with ColorThis will change the border color of the row with the title.Title Border with Changed Border Color
  5. Similarly, change the border color of the data table as well.Data Range with Changed Border Color

4. Applying Cell Shading in an Excel Worksheet

You can apply fill color or cell shading to highlight important parts of the worksheet. In this section, we will apply cell shading to the title and column header rows to focus attention on these rows.

Here are the steps to apply cell shading in Excel:

  1. Select the row with the title.
  2. Click the Fill Color dropdown from the Font group.
  3. Select a suitable color.Cell Shading in Worksheet TitleAs a result, the selected cell shading will appear in the title row.
  4. Now, select the range with column headers.
    Here, I have selected the range B4:G4.
  5. Click the Fill Color dropdown again and select your preferred color.Fill Color in Column Headers

As a result, the title and column header rows will have a fill color like the following image. Using similar steps, you can apply cell shading to any range in a worksheet.

Formatting Worksheet with Fill Color

5. Changing Font Color in Excel

To properly format a worksheet, we should maintain color contrast between background color and font color. This helps to enhance the readability of data. In the previous section, we have picked a darker color in the column headers. Therefore, we should change the default back font color to a lighter color shade.

You can apply the steps below to change the font color of the column headers row:

  1. Select the row with column headers.
  2. Click the Font Color dropdown from the Font group.
  3. Select your preferred color.
    Here, I have selected the White color.
    Changing Font Color to Format Worksheet in Excel

Now see, the font color of the column headers row has changed to White.

Worksheet with changed font color

6. Changing Font in Excel

Proper use of font styles and font size helps to make any section of a worksheet stand out. By default, you will get Calibri font with 11 font size in Excel. However, this can be easily changed.

The following 2 examples will demonstrate how to change font size and font styles in Excel:

6.1 Change Font Size

In this example, we will change the font size of the title and column header rows to 14 and 12 respectively. Here are the steps to accomplish this:

  1. Select the range with the title.
  2. Click the Font Size dropdown and select 14.
    Changing Font Size in Worksheet Title
  3. Similarly, select the column headers range and set the font size to 12.Changing Font Size in Column Headers
Note: Instead of clicking the dropdown and selecting a default font size, you can type the target font size in the font size box. This is helpful if the desired font size option is not available in the dropdown.

6.2 Change Font Style

We will set the font style to bold for title and column header rows in this example. We will also change the font of the entire worksheet. Here are the steps to accomplish these:

  1. Press and hold the Ctrl key while selecting the title range and column headers range (i.e. range B2:G2 and B4:G4).
  2. Click the Bold command button.Setting Font Style to BoldThis will change the font style of the selected ranges to bold.
  3. Click the Select All button from the top-left corner of the worksheet.
  4. Click the Font dropdown and select your preferred font.
    Here, I have selected the Aptos Narrow font.Changing Font for entire worksheet in Excel

As you can see, the font in the worksheet has changed now.

Worksheet with new font style

7. Changing Data Alignment in Excel

In Excel, there are two types of data alignment: horizontal and vertical. The horizontal alignment depends on the type of data. For example, texts are right-aligned whereas numbers and dates are left-aligned by default. The vertical alignment is set as Bottom Align usually.

However, you can easily change horizontal and vertical alignment options according to your worksheet formatting requirements. In this section, we will change the vertical alignment of the data table and the horizontal alignment of the column headers.

Here are the steps to change data alignment in a worksheet:

  1. Select the data table (i.e. range B4:G15).
  2. Click the Middle Align command from the vertical alignment options.Changing Vertical Alignment for Worksheet Formatting
  3. Afterward, select the column headers (i.e. range B4:G4).
  4. Click the Center command from the horizontal alignment options.Changing Horizontal Alignment for Worksheet Formatting

The data alignment in your worksheet has changed now.

Dataset with modified text alignment

8. Changing Number Format in Excel

Excel deals with different data types such as numbers, dates, times, percentages, currency, etc., and has multiple built-in number formats for these data types. You can also create a custom format to display the data in your preferred format.

Our sample worksheet contains dates formatted as dd-mm-yyyy in the Release Date column and numbers in the Price column. In the following two examples, we will change the number format of these two columns.

8.1 Modify Date Format

Here are the steps to change the date format in Excel:

  1. Select the column with dates.
    Here, I have selected the range F5:F15.
  2. Click the Format Cells dialog box launcher.
    Clicking Format Cells Dialog Box Launcher from Number GroupAs a result, the Format Cells dialog box will appear.
  3. In the Format Cells dialog box:
    • Go to the Number tab.
    • Select Date from Category.
    • Choose your desired data format.
      Here, I have selected the dd mmmm yyyy format.
    • Click OK.

    Choosing Target Date Format

Now look, the selected date format has appeared in the target column. Although, you may notice ### symbols in some cells due to inadequate column width. We will discuss the process to fix this issue in the upcoming sections.

Dataset with Modified Date Format

8.2 Apply Accounting Format

The price values can be displayed in Accounting or Currency format. In this example, we will apply the Accounting format. Here are the steps to accomplish this:

  1. Select the column with price values.
    Here, I have selected the range G5:G15.
  2. Click the Accounting format button.Applying Accounting Number FormatThis will display the data in Accounting format.
  3. If you want to change the number of decimal places, you click the Increase Decimal or Decrease Decimal buttons.Increase or Decrease Decimals

9. Changing Column Width in Excel

Sometimes you may ### symbols instead of actual values in some cells. It usually happens due to insufficient column widths. To fix this problem, you have to adjust the column width in Excel.

Inadequate Column Width in Dataset

You can use the following 3 methods to adjust column width in Excel:

9.1 Use Mouse to Change Column Width

You can drag the column border with the mouse to adjust the column width. Here are the steps for that:

  1. Hover the mouse pointer at the column bar’s right side in the desired column.
    As a result, the drag icon will appear.
  2. Drag the column bar to the right until you get the desired width.Increasing Column Width with Mouse

Now look, the column width is adjusted and the dates are appearing properly.

Dataset with Adequate Column Width

9.2 AutoFit Column

It is also possible to automatically adjust the column width. Here are the steps for that:

  1. Hover the mouse pointer at the column bar’s right side in the desired column.
  2. Double-click on the column bar’s right side.Autofit column width

As a result, the column width will adjust automatically to fit the data in the column.

Automatically adjusted column width

9.3 Set Column Width to a Specific Number

You can also set the column width to a specific number. Here’s how:

  1. Select the desired column by clicking on the column bar.
  2. Right-click on your mouse to open the context menu.
  3. Select the Column Width option.Column Width Option from Right-Click Context Menu
  4. Enter the desired column width value in the Column Width dialog box and click OK.Inserting Specific Column Width

This will adjust the column width in your selected column.

Dataset with Adequate Column Width

10. Changing Row Height in Excel

When you add multi-line texts with the Wrap Text feature, the entire data may not be displayed due to insufficient row height. To fix that you can adjust row heights in your worksheet. You may adjust the row height to simply adjust the vertical spacing in a row as well.

The process of changing row height is similar to the changing column width process shown in the previous section. You can view the process in the following GIF:

Adjusting Row Height

11. Applying Conditional Formatting in Excel

Conditional Formatting in Exel is used for formatting cells that meet a specific criterion. With this tool, various formatting can be added to a worksheet. In this example, we will apply alternative row colors in our worksheet using this tool.

Here are the steps to set alternative row colors by applying Conditional Formatting in Excel:

  1. Select the rows for which you want to set alternative row colors.
    Here, I have selected the range B5:G15.
  2. Click the Conditional Formatting dropdown and select the New Rule option.Applying Conditional FormattingAs a result, the New Formatting Rule dialog box will appear.
  3. In the New Formatting Rule dialog box:
    • Select the rule type Use a formula to determine which cells to format.
    • Set the formatting rule to: =MOD(ROW(B5),2)=0
    • Click the Format button.

    Setting Conditional Formatting RuleThis will open the Format Cells dialog box.

  4. In the Format Cells dialog box:
    • Go to the Fill tab.
    • Select your preferred background color.
    • Click OK.

    Setting Format of the Cells Where Condition Applies

  5. Now, you can preview the selected format in the Edit Formatting Rule dialog box. Finally, click OK in this dialog box as well.Conditional Formatting

Now look, the alternative row colors are applied to the selected range in the worksheet.

Dataset with alternating row fill colors

Download Practice Workbook

Conclusion

To conclude our tutorial, we have covered 11 examples of formatting a worksheet. We used various Excel features to change gridlines, borders, fill color, font color, font alignment, number format, row height, and column width of our sample worksheet. You can use these examples to format your worksheet.
Thanks for reading this article. If you have any feedback or queries, let us know in the comment section.


Frequently Asked Questions

How do I quickly change the format of a worksheet?

Here are the steps to change the format of a worksheet quickly:

  1. Go to the Page Layout tab.
  2. Click the Themes dropdown.
  3. Hover the mouse pointer over various themes to see the change in the worksheet format.
  4. Select your preferred theme.

Can I copy formatting from one worksheet to another?

Yes, you can copy any worksheet format to other worksheets. Here are the steps to copy worksheet formatting to other sheets:

  1. Select the cell or range whose formatting you want to copy.
  2. Press the Ctrl+C keys to copy the cell.
  3. Go to the target worksheet.
  4. Select the cell or range where you want to paste the formatting.
  5. Right-click on the mouse to open the context menu.
  6. Select Formatting from the Paste Options.

How to freeze a row in a worksheet?

While working with worksheets containing a large dataset, we often need to freeze rows to keep specific rows visible when we scroll down. Consider a situation where you need to freeze the 5th row in a worksheet. Here are the steps to freeze that row:

  1. Click the row bar of the 6th row.
  2. Go to the View tab.
  3. From the Freeze Panes dropdown, click the Freeze Panes option.

Worksheet Formatting: Knowledge Hub


<< Go Back to Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Seemanto Saha
Seemanto Saha

Seemanto Saha graduated in Industrial and Production Engineering from Bangladesh University of Engineering and Technology. He has been with ExcelDemy for a year, where he wrote 40+ articles and reviewed 50+ articles. He has also worked on the ExcelDemy Forum and solved 50+ user problems. Currently, he is working as a team leader for ExcelDemy. His role is to guide his team to write reader-friendly content. His interests are Advanced Excel, Data Analysis, Charts & Dashboards, Power Query,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo