Learning Objectives

By the end of this session, students are expected to be able to:

Insert and delete cells in Microsoft Excel

Manage text and cell alignments in Microsoft Excel

Format numbers in Microsoft Excel

Apply font, color and borders to cells in Microsoft Excel

Inserting a Cell

When working in an Excel 2003 worksheet, you may need to insert or delete cells without inserting or deleting entire rows or columns.

To insert cells

Select the location where the new cell(s) should be inserted.

It can be a single cell or a range of cells.

Right-click and choose Insert

The ‘Insert’ dialog box opens.

Select either

Shift cells right to shift cells in the same row to the right.

Shift cells down to shift selected cells and all cells in the column below it downward.

Choose an option

click the OK button

your result displays in the spreadsheet.

Deleting a Cell

Right-click and choose Delete.

The ‘Delete’ dialog box opens.

Select either

Shift cells left to shift cells in the same row to the left.

Choose an option

Click the OK button.

Your result displays in your spreadsheet.

Shift cells up to shift selected cells and all cells in the column above it move upward

Choose an option

Click the OK button.

Merge and Centre

This is performed when you want to select one or more cells and merge them into a larger cell.

The contents will be centered across the new merged cell.

The picture below shows why we might want to merge two cells.

The spreadsheet presents last month and this month sales and expenses for Sally.

Notice that Sally’s name appears above the last month column.

Source

G

oodwill Co

m

munity Foun

dation, 2002

To merge two cells into one

Select the cells that you want to merge.

It can be cells in a column, row or both columns and rows.

Click the Merge and Center button on the standard toolbar.

The two cells are now merged into one.

Using the Standard Toolbar to Align Text and Numbers in Cells

You’ve probably noticed by now that Excel 2003 left-aligns text (labels) and right-aligns numbers (values).

This makes data easier to read.

Figure

Source

5

Left Ali

G

oodwill Co

g

n of Text

m

munity Foun

a

nd Right A

dation, 2002

ligned of

N

umbers in

C

ells

You do not have to leave the defaults.

Text and numbers can be defined as left-aligned, right-aligned or centered in Excel 2003.

The picture below shows the difference between these alignment types when applied to labels.

Text and numbers may be aligned using the left-align, center and right-align buttons of the Formatting.

To Align Text or Numbers in a Cell

Select a cell or range of cells

Click on the Left-Align, Centre or Right-Align buttons in the standard toolbar.

The text or numbers in the cell(s) take on the selected alignment treatment.

Changing Horizontal Cell Alignment

You can also define alignment in the Alignment tab of the ‘Format Cells’ dialog box.

Horizontal section- features a drop-down that contains the same left, centre, and right alignment options in the picture above and several more:

Fill-Fills the cell with the current contents by repeating the contents for the width of the cell.

Justify-If the text is larger than the cell width, ‘Justify’ wraps the text in the cell and adjusts the spacing within each line so that all lines are as wide as the cell.

Centred Across Selection- Contents of the cell furthest to the left are centred across the selection of cells. (Similar to merge and centred, except the cells are not merged).

To change horizontal alignment using the format cells dialog box

Select a cell or range of cells.

Choose Format Cells from the menu bar.

You could also right-click and choose Format Cells from the shortcut menu.

The ‘Format Cells’ dialog box opens.

Click the Alignment tab.

Click the Horizontal drop-down menu

select a horizontal alignment treatment.

Click OK to apply the horizontal alignment to the selected cell(s).

Changing Vertical Cell Alignment

You can also define vertical alignment in a cell, similar to how it is done for horizontal alignment.

In Vertical alignment, information in a cell can be located at the top of the cell, middle of the cell or bottom of the cell; the default is bottom.

To change vertical alignment using the format cells dialog box

Select a cell or range of cells.

Choose Format Cells from the menu bar

You could also right-click and choose Format Cells from the shortcut menu.

The ‘Format Cells’ dialog box opens.

Click the Alignment tab.

Click the Vertical drop-down menu

select a vertical alignment treatment.

Click OK to apply the vertical alignment to the selected cell(s).

Changing Text Control

Text Control: Allows you to control the way Excel 2003 presents information in a cell.

There are three types of text control: Wrapped Text, Shrink-to-Fit and Merge Cells.

Wrapped Text: wraps the contents of a cell across several lines if it’s too large than the column width.

It increases the height of the cell as well.

Shrink-to-Fit: shrinks the text so it fits into the cell; the more text in the cell the smaller it will appear in the cell.

Merge Cells: can also be applied by using the Merge and Center button on the standard toolbar.

To change text control using the format cells dialog box

Select a cell or range of cells.

Choose Format Cells from the menu bar.

The ‘Format Cells’ dialog box opens.

Click the Alignment tab.

Click on either the Wrapped Text, Shrink-to-Fit or Merge Cell check boxes-or any combination of them-as needed.

Click the OK button.

Text Control

Figure

11: Text Control

Source

Goodwill Community Foundation 2002

Changing Text Orientation

Text Orientation, allows text to be oriented 90 degrees in either direction up or down.

To change text orientation using the format cells dialog box

Select a cell or cell range to be subject to text control alignment.

Choose Format Cells from the menu bar.

The ‘Format Cells’ dialog box opens.

Click the Alignment tab.

Increase or decrease the number shown in the ‘Degrees’ field or spin box.

Click Ok button.

Changing Text Orientation

Source

G

oodwill Co

m

munity Foun

dation 2002

o

Formatting Numbers in the Format Cells Dialog Box

Numbers in Excel can assume many different formats

Date, Time, Percentage or Decimals.

To format the appearance of numbers in a cell

Select a cell or range of cells.

Choose Format  Cells from the menu bar.

You could also right-click and choose Format Cells from the shortcut menu.

The ‘Format Cells’ dialog box opens.

Click the Number tab.

Click Number in the Category drop-down list.

Use the Decimal places scroll bar to select the number of decimal places (e.g., 2 would display 13.50, 3 would display 13.500).

Click the Use 1000 Separator box if you want commas (1,000) inserted in the number.

Use the Negative numbers drop-down list to indicate how numbers less than zero are to be displayed.

Click the OK button.

Formatting Date in the Format Cells Dialog Box

The date can be formatted in many different ways in Excel 2003.

Here are a few ways it can appear

October 6, 2003

10-Oct-03

To Format the Appearance of a Date in a Cell

Select a cell or range of cells.

Choose Format  Cells from the menu bar.

The Format Cells dialog box opens.

Click the bullet one to three tab.

Click Date in the Category drop-down list.

Select the desired date format from the Type drop-down list.

Click the OK button.

Formatting Time in the Format Cells Dialog Box

The time can be formatted in many different ways in Excel 2003.

Here are a few ways it can appear

13: 30

1:30 PM

To format the appearance of time in a cell

Select the range of cells you want to format.

Choose Format Cells from the menu bar.

The Format Cells dialog box opens, click the Number tab.

Click Time in the Category drop-down list.

Select the desired time format from the Type drop-down list

Click the OK button.

Formatting Percentage in the Format Cells Dialog Box

There may be times you want to display certain numbers as a percentage.

For example, what percentage of credit cards bills account for your total monthly expenses?

To Express Numbers as a Percentage in a Spreadsheet

Select a cell or range of cells.

Choose Format  Cells from the menu bar.

The Format Cells dialog box opens

Click the Number tab.

Click Percentage in the Category drop-down list.

Define the Decimal Places that will appear to the right of each number.

Click the OK button.

Change Font Type, Size and Colour

In Excel 2003 font consists of three elements

Typeface or the style of the letter

Size of the letter

Colour of the letter

The default font in a spreadsheet is Arial 10 points

The typeface and size can be changed easily.

The amount of typefaces available for use varies depending on the software installed on your Computer.

To apply a typeface to information in a cell

Select a cell or range of cells.

Click on the down arrow to the right of the Font Name list box on the Formatting toolbar.

Click on the Typeface of your choice.

The selection list closes and the new font is applied to the selected cells.

Change Font Type, Size and Colour

To apply a font size to information in a cell

The ‘Font Size’ list varies from typeface to typeface.

The Arial font sizes, for example, are 8, 9, 10, 11, 12, 14, 16, 18, 20, 22, 24, 26, 28, 36, 48 and 72.

Select a cell or range of cells.

Click on the down arrow to the right of the font Colour list box.

Click on the Colour of your choice.

The selection list closes and the new font Colour is applied to the selected cells.

Underline, Italics and Bold

you can also apply Bold, italics, and/or underline font style attributes to any text or numbers in cells.

To select a font style

Select a cell or range of cells.

Click on any of the following options on the Formatting toolbar.

Bold button (Ctrl + B).

Italics button (Ctrl + I).

Underline button (Ctrl + U).

The attribute(s) selected (bold, italics, or underline) are applied to the font.

The Bold, Italics, and Underline buttons on the Formatting toolbar are like toggle switches.

Click once to turn it on

Click again to turn it off.

Design and Apply Styles

Styles can save a lot of time when formatting a spreadsheet.

Style: A unique collection of font attributes (Number, Alignment, Font, Border, Patterns and Protection).

Many different styles can be created in a spreadsheet, each with different attributes and names.

When applied to a cell, information in it resembles the attributes defined for that style.

Applying a Style

Select the cell or range of cells.

Choose Format  Style from the menu bar

Changing Style Attributes

You can change the style attributes (Number, Alignment, Font, Border, Patterns and Protection) for any Style Name.

You can create new styles by clicking on the Add button in the Style dialog box.

Adding a Border to Cells

Borders can be applied to cells in your worksheet in order to emphasize important data or assign names to columns or rows.

To add a border to a cell or cell range

Select a cell or range of cells.

Click on the down arrow next to the Borders button.

The Border drop-down appears.

Choose a borderline style from the Border drop-down menu.

The selected cells display the chosen border.

Border Drop-Down Menu

Adding Colour to Cells

Colour can be applied to cells in your worksheet in order to emphasize important data or assign names to columns or rows.

To add Colour to a cell

Select a cell or range of cells.

Click the down arrow next to the Fill Colour button.

A ‘Fill Colour’ drop-down menu displays.