A set of formatting attributes that you can apply to enhance the worksheet

Most people use Microsoft Excel to track data and do calculations. The basic formatting techniques covered in our Microsoft Excel courses will help improve the appearance of your spreadsheets and make your data easier to understand.

This article covers the 6 types of formatting you should focus on to give your Excel workbooks a more polished, professional look:

• Formatting Text
• Formatting Numbers
• Formatting Cells
• Formatting Dates and Times
• Using Format Painter
• Conditional Formatting

Let’s get started!

Formatting Text

Even though most spreadsheets contain numbers, the headings and titles in your worksheet give those numbers context. The first stop we’ll make on our quest to master basic formatting in Excel involves formatting text.

Font and Font Size

All of the most recent versions of Excel use Calibri 11pt as the default font. Calibri is an easy to read sans-serif font that became the default back when Excel 2007 was released.

Calibri is the name of the font, 11pt is the font size.

This might be more information than anyone needs but font size is measured in points. 1pt = 1/72 of an inch. If you’re using a 36pt font, the text will be 1/2 inch tall, if you print the worksheet.

If you’d like to use a different font, simply select the cell(s) you’d like to format, click the down-arrow beside the Font control in the Home > Font group, and select a different font.

Similarly, to make the font bigger or smaller, you’d click the down-arrow beside the Font Size control in the Home > Font group and select a different font size. You can also use the Increase Font Size or Decrease Font Size buttons in the font group.

How Do I Change the Default Font in Excel?

To change the default font in Excel, click the File tab and select Options. On the General tab, you can modify the default font and font size in the When creating new workbooks section.

Bold, Italic, Underline

You can change the appearance of your headings, titles, and other text by applying attributes like bold, italic, and underline. Use the bold, italic, and underline buttons in the Home > Font group to make the desired changes.

Alignment

You can change the alignment of headings in your worksheet. There are 6 buttons in the Home > Alignment group that let you set the vertical and horizontal alignment of text in your cells. The screenshot shows that the headings in B2:F2 are centered horizontally.

Indent

When you indent text, you leave space between the left margin of a cell and its contents. The Increase Indent and Decrease Indent buttons are in the Home > Alignment group. This screenshot shows that the text in A13:A15 has been indented below Beverages and Hot Drinks.

Wrap Text

If you need to display a lot of information in a cell, you can enable text wrapping. Click the Wrap Text button in the Home > Alignment group once to turn text wrapping on, click the button again to turn it off.

Merge & Center

If you’d like to center a title above your worksheet’s data, drag to select a range of cells then click the Merge & Center button in the Home > Alignment group.

This example shows the Income title centered over the rest of the worksheet’s data. A1:F1 was comprised of 6 individual cells. After clicking Merge & Center, the cells were merged and the title was centered within the newly merged cell.

Clicking Merge & Center again will unmerge the cells.

Formatting Numbers

Whether cells contain values or calculations, it is easier to interpret figures in a spreadsheet if your numbers are formatted consistently. The second topic we’ll look at involves formatting numbers.

Comma Style

To format cells using comma style, select the cell(s) you want to format then click the Comma Style button in the Home > Number group.

When you apply the comma style format to cells containing numbers, two things happen:

1. A “thousands separator” is added to the number.
2. The number displays two decimal places.

The thousands separator is a comma that is displayed making it easier to interpret values at a glance. Two decimals are displayed by default.

If you’d like to increase or reduce the number of decimal places, click the Increase Decimal or Decrease Decimal buttons in the Home > Number group.

Accounting Number Format

To indicate that numbers represent dollars, euros, or other currencies, select the cell(s) to be formatted and click the Accounting Number Format button in the Home > Number group.

Percent Style

To display numbers representing percentages, select the cell(s) to be formatted and click the Percent Style button in the Home > Number group.

Formatting Cells

Formatting cells is the third area where basic formatting can enhance the presentation of your worksheet data. Once again, consistent formatting for spreadsheet elements, like titles, totals, etc., will help people understand what the data represents.

Fill Color

Adding color to the background of your headings will visually set them apart from the detailed revenue figures in your worksheet. In the screenshot, I have added a light green fill color to cells A3:G3.

To access all of the colors, as shown in my example, you need to click the down-arrow next to the Fill Color button in the Home > Font group.

To apply the same fill color to subsequent cells, you can just click the Fill Color button (without clicking the down-arrow). Excel remembers the color you used until you choose a different color or close the program.

Changing Excel’s background color can be a great way to add emphasis to key areas of your worksheet.

Borders

Like fill color, borders add visual cues to the purpose of your data. I typically use borders to separate totals in the bottom row of my worksheet from the detailed revenue figures above – but you can use borders to emphasize or separate any type of data.

To apply a border, select a range of cells, click the down-arrow next to the Borders button in the Home > Font group, and select the border style you want.

In this example, I have added a Bottom Border to A7:G7.

Formatting Dates and Times

Dates and times are tricky in Microsoft Excel. Mainly because there are so many ways to enter dates and times.

Let’s tackle dates first.

This table displays dates, as they would appear in different parts of the world. April 15, 2020 was used – with the “short date” format applied.

Variation aside, the most important thing to remember is make sure any cells containing dates are formatted as dates. That way you can do calculations or sort and filter spreadsheets based on dates.

To format cells containing dates:

1. Select the cell(s) to be formatted.
2. Right-click in the selected range and select Format Cells… from the shortcut menu.
   The Format Cells dialog box appears.
3. Verify that the Number tab is select in the Format Cells dialog box.
4. Select Date from the Category: box.
5. Select an appropriate format from the Type: box.
6. Click OK.

The process for formatting time is virtually identical to the steps outlined above only you select Time from the category: box in step 4.

Using Format Painter

The next topic, Format Painter, has saved me so much time over the years!

It’s a shortcut that a lot of people seem know about… but, for some reason, they just don’t use it as much as they could.

Essentially, Format Painter is a shortcut that lets you copy formatting from one cell and easily apply it to another cell (or cells).

In the spreadsheet below, the word “Dog” in cell B12 has a black background with bold, white text.

Here is the process I would use to copy the formatting attributes in cell B12 to the cells containing “cat” and “hamster”.

1. Select cell B12.
2. Click the Format Painter button in the Home > Clipboard group.
   The pointer changes to a plus sign with a paint brush.
3. Drag to select C12:D12.
   The formatting is copied to the selected cells.

Once you copy formatting from one cell to another, Format Painter is turned off and you’ll have to initiate the process again to copy the formatting to a second group of cells… unless you double-click Format Painter.

To copy formatting attributes to multiple ranges:

1. Select cell B12.
2. Double-click the Format Painter button in the Home > Clipboard group.
   The pointer changes to a plus sign with a paint brush.
3. Drag to select B14:D14.
4. Drag to select B16:D16.
   The formatting is copied to both destination ranges.
5. Press Esc or click the Format Painter button again to turn Format Painter off.

The formatting from cell B12 was copied to each cell containing animal names.

If you want to become even more proficient with this feature, you should learn the shortcut key for Format Painter!

Conditional Formatting

The last step in our journey to master basic formatting techniques in Microsoft Excel is conditional formatting. Conditional formatting lets you change a cell’s appearance, based on its contents. This feature has been an Excel staple for as long as I can remember – and Microsoft keeps adding new capabilities.

Think about the sample spreadsheet we’ve been referring to throughout this article. The detailed revenue numbers in B4:E7 range from 350 on the low end to 3,200 on the high end.

What if we wanted to draw attention to numbers higher than 2,000? We could use conditional formatting to change the fill color of those cells to bright green!

Here is the process.

Step #1 – Drag to select B4:E7.

Step #2 – Click the Conditional Formatting button in the Home > Styles group, point to Highlight Cells Rules, then select Greater Than.
The Greater Than dialog box appears.

Step #3 – In the Greater Than box, type 2000 in the Format cells that are GREATER THAN: box and select Green Fill with Dark Green Text from the drop-down menu and click OK.

Cells that are greater than 2,000 are now formatted in green.

If you find conditional formatting useful in analyzing data, you could create additional rules. For example, you could specify that all values between 1,000 – 2,000 should be formatted with a yellow background, and values below 1,000 should appear in red.

Additional Options for Conditional Formatting

Data Bars provide an easy way to compare values in a range without specifying rules. With data bars, Excel uses a graphic behind each number to represent its relative size.

Color Scales let you analyze data with color. In this example, the highest numbers are green, mid-range values are orange, and the lowest values are red.

Icon Sets represent your data graphically using different symbols. For more information, check out this article on how to use icon sets.

Each of the additional options presented are available when you click the Conditional Formatting button in the Home > Styles group.

Want to Know More?

If you want to know more about formatting, we offer 3 levels of Microsoft Excel training. We provide onsite instruction in Toronto and Ottawa – but we also provide online training, for organizations located anywhere in Canada or the US.

What is a set of formatting attributes that you can apply to enhance the worksheet?

What are formatting attributes in Excel?

What enhances the readability of a worksheet?

What are the types of formatting in Excel?