16th March 2010

Main Menu

Home Page

Tips & Tricks Section

PC Game Cheats

PC Upgrading Guide

PC Maintenance

Buying Guides

Cheap Laptops & Notebooks

Desktop Computers

External Hard Drives

Inkjet and Laser Printers

Tech Support

Resources

Sitemap

Search this Site

Computer Links

Excel Formats - Including Number, Alignment, Font, Border, Patterns and Protections

Welcome to part two of our Tutorial: Microsoft Excel ® Formats.

In this tutorial we will cover the various Excel cell formats, including how to change the formats of cells, rows and columns. Understanding the different cell formats is important if you plan to create spreadsheets in Microsoft Excel.

Continued from Using Excel - (worksheet overview)

Before you follow this tutorial you must read the terms of our disclaimer.

The format of a cell controls how the cell looks, and also defines how the information (data) stored inside it will be treated and displayed.

There are numerous format options for changing the look of a cell, including borders and background colours (colors). The appearance of the contents of a cell can also be altered, including different date formats, number formats, fonts, alignment and more.

There are several ways to view/change the Format of a cell in Microsoft Excel ®:

Select the cell/s to view/change, click Format on the menu bar and then click cells (fig 1.1)
Right click on the cell/s and select Format from the menu that appears (fig 1.2)
Select the cell/s and then Hold down the CTRL key and press 1

The Format Cells Dialogue Box In Microsoft Excel ®
In fig 1.3 on the right hand side, you can see a typical Format Cells dialogue box. It is with this dialogue box that you can view and change the format of cells in your Microsoft Excel worksheets. There are 6 tabs: Number Alignment Font Border Patterns Protection It is likely that you will use all of these tabs while you are creating spreadsheets, some maybe more than others.
Each tab has various cell formatting options, we will cover each tab briefly below.

Number Format Tab The Number format options change the way the contents of a cell are treated and displayed. In fig 2.1 you can see examples of the different fomats when applied to the value 23.45
General: Use this format for cells with general numbers, Excel will make no changes to the format of the number. Number: The Number category gives you many options for the way numbers are displayed. You can choose the amount of decimal places and also whether to use a separator for values over 999 (for example 1,188). The way negative numbers are displayed can also be changed, including showing negative numbers in red. Currency: The Currency category allows you to specify a currency symbol which will be displayed next to the value (see fig 2.1). The Excel Currency category has the same decimal point and negative number options as the Number category, but you do not have the separator option.
Accounting: The Accounting category is the same as the Currency category, except it will align the currency symbols and decimal points. You do not have any options to control how negative numbers are displayed. Date: The Date category will treat and display the contents of the cell as a date. The location option dictates the available date formats, ensure the location is correct, then you can choose the display format of the date. Time: The Time category, unsuprisingly, formats the contents of the cell as time. The format display options you have available are dependant on the specified location. Select your location (unless selected) and you can then choose from relevant time formats.

Percentage: The Percentage category will multiply the cell contents by 100, then display the result with a percentage symbol at the end.

Fraction: This category will display the contents of a cell as a fraction. You have many options to dictate the format of the fraction, your choice will depend on how you need to represent the contents of your cell.

Scientific: The Scientific category will display the contents of the cell in Scientific notation (see fig 2.1).

Text: The text format leaves the contents of the cell exactly as they are, this is useful when entering text descriptions which may contain numbers or special characters.

Special: The Special category has options only for certain locations. For example, selecting the United States location will allow you to enter ZIP codes and phone numbers whilst preserving the leading zero.

Custom: We will be covering the Custom category in an upcoming tutorial.

Alignment Format Tab

This Alignment tab of the Format Cells dialogue box allows you to control the alignment and orientation of the contents of a cell. There are also options for Wrap text, Shrink to fit, Merge cells (covered in an upcoming tutorial), and Text direction.

Font Format Tab

As you would expect, the Font tab gives you control over the typeface, style, size, and other font related attributes.

Border Format Tab

The Border tab allows you to change the width, style and colour (color) of the border applied to the cell/s. You can choose to have borders around the entire cell, on certain edges, and even diagonally through the cell/s.

Patterns Format Tab

The patterns tab allows you to control the appearance of the background of the cell, you can choose from a solid colour (color) or a preset pattern.

Protection Format Tab

This tab allows you to specify which (if any) cells you want to protect from changes. Microsoft Excel allows you to set up a password to protect the cells, we will be covering password protecting a workbook and worksheet in a future tutorial.