Categories
Microsoft Excel

The Complete Guide to Basic Formatting in Excel

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

There are 6 areas 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!

Excel Courses: We offer Microsoft Excel training delivered online by a live instructor. Participants can connect from anywhere in Canada.

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.

Controls in the Font Group
Controls in the Font Group

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.

Default Font Attributes in the Options Dialog Box
Default Font Attributes in the Options Dialog Box

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 bolditalic, and underline buttons in the Home > Font group to make the desired changes.

Bold, Italic, and Underline Buttons
Bold, Italic, and Underline Buttons

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.

Alignment Controls
Alignment Controls

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.

Indent Controls in the Alignment Group
Indent Controls in the Alignment Group

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.

Merged Cells
Merged 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.

Comma Style in the Number Group
Comma Style in the 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.
Comma Style Used to Format Numbers
Comma Style Used to Format Numbers

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.

Increase and Decrease Decimal Buttons
Increase and Decrease Decimal Buttons

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.

Accounting Number Format
Accounting Number Format

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.

Percent Style
Percent Style

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 is retained until you select a different color or close the program.

Fill Colour Control
Fill Colour Control

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.

Selecting a Border
Selecting a Border

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

Border Above the Total Row
Border Above the Total Row

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.

CountryFormatExample
CanadaYMD20/04/15
United KingdomDMY15/04/20
United StatesMDY04/15/20

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.
Formatting a Date
Formatting a Date

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.
Format Painter
Format Painter

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.
Formatting Applied Using Format Painter
Formatting Applied Using Format Painter

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

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.

B4:E7 Selected
B4:E7 Selected

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.

Conditional Formatting Menu
Conditional Formatting Menu

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.

Conditional Formatting Parameters
Conditional Formatting Parameters

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

Conditional Formatting Results
Conditional Formatting Results

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.

Conditional Formatting with Data Bars
Conditional Formatting with Data Bars

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.

Conditional Formatting with Color Scales
Conditional Formatting with Color Scales

Icon Sets represent your data graphically using different symbols.

Conditional Formatting with Icon Sets
Conditional Formatting with Icon Sets

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

By Michael Belfry

Working as a full-time training consultant, Michael provides Microsoft Office courses to government and private sector clients throughout North America.