How to Format Your Excel Worksheet?

In this lesson, you will learn everything about formatting in Excel.

Two Ways to Format in Excel

Formatting in Excel can be accomplished in two ways:

  • Using Predefined Styles: This approach is ideal for novice users who rely on standard tables and charts.
  • Custom Formatting: If you need full control over your worksheet’s appearance, choose custom formatting. We’ll discuss predefined styles at the end of this lesson.

Method 1: Step-by-Step Formatting

Let’s assume we’ve been tasked with creating a table of data as shown below. We’ve collected the relevant data and inserted them into the table. However, the table has several issues.

formatting excel table

Adjusting Column and Row Widths

First, let’s ensure that all data and category descriptions are fully visible. Currently, data in cells ‘I23’ and ‘I32’ are cut off and replaced by ‘###’.

Similarly, descriptions in cells B10 and B32 are also partially obscured. To expand a column, simply double-click on the dash between columns, or click and drag the left button to adjust the width manually.

excel formatting table

To make columns equally wide, select the columns you want to align and adjust any of them to the desired size.

Excel format size

Inserting/Deleting Rows

Next, we’ll remove the first three rows at the beginning of the table. To do this, select the first three rows, right-click, and choose Delete.

See also  How to Use Excel Fast: A Guide for Beginners and Advanced Users

Excel format rows delete

You can also insert rows or columns using the Insert command. Let’s insert a row above the table.

Excel format row insert

Adding Borders

When we insert a row at the beginning, Excel selects the entire table. Click on the Borders option, then choose All Edges and Thick Border to make the borders bold.

Excel format all borders

You can also select the column headers and apply bold borders. No need to reopen the Borders menu; the last selected options can be applied directly.

Excel format headers borders

If you require dual or diagonal lines within cells, right-click on a selected cell or area, then choose Format Cells.

Excel format right click format cells

In the Format Cells dialog, navigate to the Border tab to explore various border formatting options.

Excel format cells dialog box borders

Merging Cells

We’ll merge cells to improve the appearance.

Excel format diagonal lines

Select the cells to be merged and click Merge and Center.

Excel format merge and center

Excel format right click merge and center

Excel format header merge and center

Excel format dialog box merge and center

Excel format cells merge and center

If you wish to adjust the vertical alignment of merged cells, right-click, choose Format Cells, and set the vertical alignment.

Excel format calls alignment vertical center

Changing Cell Background Color

To change a cell’s background color, select the cell or area, click the Fill Color option, and choose the desired color.

Excel format cell colour

For subsequent cells, click the Fill Color icon, and it will remember your previous choice.

Excel formatting colour remembered

Using Format Painter

To apply the same format to multiple cells or areas, use the Format Painter. First, format one cell (e.g., line 21) as desired.

Excel format painter

Then, click the Format Painter icon and click on the target cell or area to apply the format.

Excel format painter button paintbrush

Click in cell B25 and the entire row of the table takes the appropriate format.

Excel format painter other row

Gridlines and Font Size

To improve the document’s appearance, you can disable gridlines and adjust font size easily.

See also  How to use Goal Seek in Excel

Excel format grindlines ribbon

Excel format font size

To hide or reveal rows and columns, select them accordingly.

Excel format all rows and columns rectangle

Excel format unhide rows

Method 2: Formatting as a Table

For more complex tables, you can format them as tables. Activate a cell within the table, go to Home, select Format as Table, and choose a color scheme. Ensure that the table area is correctly recognized. If needed, you can remove filter symbols from header cells.

Excel format as table

A window will appear, in which we are asked to confirm whether the area of the table was properly recognized.

Excel format as table formatting

The table is formatted according to the selected model. If it does not suit us, that header cells contain symbols of the filter can be disabled by selecting Sort & Filter and then click Filter.

Excel format sort and filter

When you click the right mouse button on the table, we can add a row sum, as is shown below.

Excel format table right click menu table

Sum which will be introduced only for June, copy to the other months.

Excel format table total row sum only for one column

Exercise can be considered completed.

Excel format table row copied

On the Design click on the arrow marked below to view all available styles.

Excel format column chart styles

Choose the style that suits you.

Excel format chart style choose one

Select the title by clicking on it and the cards Format select one of the Word Art styles.

Excel format column chart font style word art styles changed

Keep in mind that impressing recipients with elaborate styles should not come at the cost of a professional approach. Simplicity often speaks louder than complexity in Excel formatting.