Formatting Worksheet in Excel: 11 Examples

1. Turning Off Gridline in an Excel Worksheet

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

The gridlines will turn off from the worksheet.

Method 2 – Formatting Title in Excel Worksheet

2.1 Using Merge & Center Command

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

1. Select the row of the title.
   We selected the range B2:G2.
2. Go to Home tab > Alignment group > Merge & Center command.

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

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.
   We selected the range B2:G2.
2. Click the Format Cells dialog box launcher from the Alignment group.
   
   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.

   

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

Method 3 – Applying Cell Borders in an Excel Worksheet

3.1 Apply Thick Bottom Border to Title

The Thick Bottom Border option is useful for differentiating between sections. Thus, applying this border to the title will visually separate it 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.

There is a thick bottom border in the title row.

3.2 Apply All Borders Command

1. Select the data table.
   We selected the range B4:G15.
2. Click the Borders dropdown from the Font group.
3. Select the All Borders option.

The borders have appeared around each cell of the data table.

3.3 Change Border Color

1. Select the row with the title where the thick bottom border was applied.
2. Click the Borders dropdown > Line Color > your preferred color. 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. This will change the border color of the row with the title.
5. Change the border color of the data table as well.

Method 4 – Applying Cell Shading in an Excel Worksheet

1. Select the row with the title.
2. Click the Fill Color dropdown from the Font group.
3. Select a suitable color.The selected cell shading will appear in the title row.
4. Select the range with column headers.
   We selected the range B4:G4.
5. Click the Fill Color dropdown again and select your preferred color.

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.

Method 5 – Changing Font Color in Excel

1. Select the row with column headers.
2. Click the Font Color dropdown from the Font group.
3. Select your preferred color.
   We selected the White color.
   

The font color of the column headers row has changed to White.

Method 6 – Changing Font in Excel

6.1 Change Font Size

1. Select the range with the title.
2. Click the Font Size dropdown and select 14.
   
3. Select the column headers range and set the font size to 12.

6.2 Change Font Style

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.This 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.
   We selected the Aptos Narrow font.

The font in the worksheet has changed now.

Method 7 – Changing Data Alignment in Excel

1. Select the data table (i.e. range B4:G15).
2. Click the Middle Align command from the vertical alignment options.
3. Select the column headers (i.e. range B4:G4).
4. Click the Center command from the horizontal alignment options.

The data alignment in your worksheet has changed now.

Method 8 – Changing Number Format in Excel

8.1 Modify Date Format

1. Select the column with dates.
   We selected the range F5:F15.
2. Click the Format Cells dialog box launcher.
   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.
     We selected the dd mmmm yyyy format.
   • Click OK.

   

The selected date format has appeared in the target column. 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.

8.2 Apply Accounting Format

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

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

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

9.1 Use Mouse to Change Column Width

1. Hover the mouse pointer at the column bar’s right side in the desired column.
   The drag icon will appear.
2. Drag the column bar to the right until you get the desired width.

The column width is adjusted, and the dates appear.

9.2 AutoFit Column

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.

The column width will adjust automatically to fit the data in the column.

9.3 Set Column Width to a Specific Number

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.
4. Enter the desired column width value in the Column Width dialog box and click OK.

This will adjust the column width in your selected column.

Method 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 adjust the vertical spacing in a row as well simply.

Method 11 – Applying Conditional Formatting in Excel

1. Select the rows for which you want to set alternative row colors.
   We selected the range B5:G15.
2. Click the Conditional Formatting dropdown and select the New Rule option.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.

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

   

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

The alternative row colors are applied to the selected range in the worksheet.

Download Practice Workbook

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.

<< Go Back to Learn Excel