Block V

UNIT 2-FORMATTING AND EDITING

Structure

2.0 Introduction
2.1 Objectives
2.2 Formatting Toolbars
2.3 Fonts
2.4 Background Colors
2.5 Merge Cells
2.6 Number Formats
2.7 Cell references(Column and row)
2.8 Summary
2.9 Answers/Solutions
2.10 Further Reading

2.0 Introduction
A Toolbar with buttons performs some of the most common tasks in Excel. Toolbars contain buttons that
give you quick mouse access to many commands and features in Excel. For example, you can open a file
by clicking the new button on the Standard toolbar. Excel has toolbars for different purposes. When you
first start Excel, the Standard toolbar and Formatting toolbar appear. You can display or hide many of the
built in toolbars by pointing to any toolbar and clicking the right mouse button. When you are working in
an Excel worksheet, you can also display or hide most toolbars by choosing toolbars from the View
menu. You can see the name of each toolbar button in a ToolTip. When you point to a button with the
mouse the button name will appear in a box. You can turn ToolTips on and off in the View Toolbars
dialog box. Formatting toolbar contains buttons what help you format objects, cell contents and cells. You
can also format chart objects, such as text and gridlines. This toolbar contains the tools or icons which can
be used to apply any other effects, such as font name, size of the character, Bold, Italic, Merge Cell etc.,
on the created list.

2.1 Objectives

After going through this unit, you should be able to:

• define a Toolbar;
• define formatting and editing toolbar;
• different styles of Fonts;
• setting Background colors;
• concept to Merge cells;
• various type of Number formats;
• methodology of cell references(column and row)

1
2.2 Formatting toolbars

The quickest way to format one or more objects on the report is to select the object or objects
you want to format and then choose options from the Formatting toolbar. To select a single
object, just click it with the mouse. You may select multiple objects to format at once using
several methods. By CTRL clicking or SHIFT clicking on more than one object, you may select
a specific set of objects (you’ll notice that all objects you’ve selected will have a shaded outline
around them). You may also select multiple objects with an “elastic box” by first deselecting any
already selected objects (click on a blank area of the report canvas) and then holding down the
life mouse button while dragging the box around desired canvas (for example the details shaded
area in the design tab or the D shaded area in the preview tab) and choose Select All Section
Objects from the pop-up menu.

This toolbar contains buttons and drop-down boxes to allow you to format the contents of the
cells. In order to display the Formatting toolbar, right-click any toolbar or menu bar and then
select Formatting option. Once you have selected objects that you wish to format, click buttons
in the Formatting toolbar to format the selected objects.

Font - Provides a list of all the available fonts (based on your current printer
selection).It changes the font of the selected text

Font Size - Lets you adjust the character size (based on your current font selection).
It changes the size of selected text and numbers.

Bold - Toggles bold on the current selection. It makes selected text and numbers bold.

Italic - Toggles italics on the current selection. It makes selected text and numbers italic

Underline - Toggles underline on the current selection.Underlines selected text and

numbers.

Align Left - Aligns data to the left edge of the cell.Aligns to the left with a ragged right
margin.

Centre - Aligns data in the middle of the cell.Centers the selected text

2
 Align Right - Aligns data to the right edge of the cell.Aligns to the right with a ragged left
margin

Merge and Centre - Combines 2 or more adjacent cells to create a single cell.
Merge and Center - Merges two or more selected cells and centers the entry

Currency Style - Applies the currency format "£#,##0.00".Currency Style - Formats

selected text to display currency style

Percent Style - Displays the number as a percentage by applying the format "0%".
Percent Style - Formats selected cells to display percent

Comma Style - Adds a comma and two decimal places to the number by applying the
format "#,##0.00".

Comma Style - Formats selected cells to display commas in large numbers

Increase Decimal - Adds one decimal place from the number.Increase Decimal - Increases
the number of decimals displayed after the decimal point

Decrease Decimal - Removes one decimal place from the number.Decrease Decimal -
Decreases the number of decimals displayed after the decimal point

Decrease Indent - Decreases the indent by 1 or removes the indent completely.

Decreases the indent to the previous tab stop

Increase Indent - Increases the indent by 1.Indents the selected paragraph to the next tab
stop

Borders - Applies a border to the current selection.

Marks text so that it is highlighted and stands out

Fill Colour - Applies a colour to the background of the current selection. This is actually a
tear off toolbar.

3
 Font Colour - Changes the colour of the font of the current selection. This is actually a tear
off toolbar.

To find the hidden buttons in the toolbar, click the small arrow at the right end of the toolbar and
click the Add or Remove Buttons. The Add and Remove Button displays the Fomatting and
Customize options. The Formatting options are shown as below:

Using the formatting toolbar:

Use the Formatting toolbar to change fonts and effects as follows:

1. Select cells A13 to D13.
2. Click on the Bold button B.
3. Click on cell A5. Click on the Bold button B and the Italic button I
4. Select cells A10 and A11. Click on the Italic button I.
5. Click on cell A26 then click on B.
Features can be turned on or off by clicking on the appropriate button on the
Formatting toolbar.
6. Save the workbook.

4
Formatting Shortcut Keys:

The following shortcut keys can be used for formatting.

Ctrl B or Ctrl 2 Bold

Ctrl I or Ctrl 3 Italics
Ctrl U or Ctrl 4 Underline
Ctrl 5 Strikethrough
Ctrl Shift & Border around cell(s)
Ctrl Shift_ Removes cell border
Ctrl Shift ‘ General number format
Ctrl Shift ! Two decimal places, commas
Ctrl Shift $ Currency format, two decimal places
Ctrl Shift % Percentage format, no decimals.
Ctrl Shift F Moves to the Font box- type in the font
required or press or to select, then press
Enter.
Ctrl Shift P Moves to the Font Size box- type in the size
required or press or to select, then press
Enter.

2.3 FONTS

A font is a style of type. In addition to the Formatting toolbar, options can be changed in the
[Format] cells, Font dialog box where you can preview fonts and formatting changes before
applying to cells. Remember to use fonts that fit the purpose and style of the worksheet.

Exercise:

1. Open the workbook.

2. Click on cell A1.
3. Chose [format] cells and click on the font tab.
4. Click on the font: and select impact.
5. Click on Bold in the Font style: box.
6. Click on the Size: and select 24.
The preview section at the bottom right of the dialog box displays the chosen font and
format options.

5
 7. Click on the color: and select Dark Blue.
8. Click on OK.
9. Change the font for the subtitle in cell A2.
a. Click on cell A2 and choose [Format] Cells.
b. From the Font tab, ensure Arial is selected from the Font: box.
c. Click on Bold in the Font style: box.
d. From the Size: box select 12.
e. Click on OK.

List Font Names in their Font tells excel to display fonts on menus and in any formatting dialog
box in the typestyle that the font name represents. (See Figure………). This option is on by
default, but you can turn it off to slightly improve Excel’s performance. With this option off, you
must imagine what a font looks like by viewing only its name.

Excel can display font names in the typestyle they represent as shown below:

6
2.4 BACKGROUND COLORS
Generally, cells present a white background for displaying data, but you can apply other colors
or shading to the background. You can even combine these colors with various patterns for a
more attractive effect. In addition, you can change the color of the data contained within your
worksheet’s cells.

1. Select the cells whose background color and/or font color you want to change.
2. To change the color of the text in the selected cells, click the Font color down arrow
on the Formatting toolbar and choose a color from the list (here, White).
3. To change the color of the selected cells’ background, click the Fill color down
arrow and choose a color from the list (here, blue).
4. Excel applies the colors you choose.

7
Be sure a shading or color pattern doesn’t interface with the readability of your data. To improve
readability, you might need to make the text bold or select a text color that goes well with your
cell’s background color. Also, be aware that if you print the worksheet to a non color printer, the
color you select prints gray- and the darker the gray, the less readable the data. Yellows generally
print as a pleasing light gray that doesn’t compete with the data.

8
2.5 MERGE CELLS

Occasionally, you may want to merge two or more cells to create a large cell that spans several
columns or rows. Such a cell is useful for adding a title to your worksheet or inserting row labels
that apply to several rows in a worksheet.

To merge cells, follow these steps:

1. Select the cells want to merge.

2. Open the Format menu and choose Cells. The Format Cells dialog box appears.
3. Click the Alignment tab to bring it to the front, as shown in figure.

Figure: You can merge two or more cells to create a single, larger cell.

You can choose to merge the selected cells.

9
 1. Click Merge Cells.
2. Click OK. Excel merges the cells and displays a single large cell in place of the
smaller cells.
3.
To return the cells to their original (premerged state), repeat the steps, removing the check mark
from the Merge Cells box.

2.6 NUMBER FORMATS

Number Formats are codes that help you to control the appearance of numbers in Excel. You can
view or edit a number format by double clicking on a text element, then clicking on the Number
tab in the dialog. There are a number of built in categories, and you will have to experiment with
different ones to learn all the options you have. Most likely you will find an appropriate number
format within the choices provided, but it is also very easy to add your own. If your desired
number format is similar to one you can find in the dialog, select it, and then click on the Custom
category. The chosen number format will be displayed in an edit box, where you can adjust it as
needed.

Every cell in a blank Excel worksheet starts with a General format, which essentially is no
format at all. Type a text entry, and Excel moves it to the left. Type a number, and Excel moves
it to the right. If you type a date, Excel automatically applies a date format. Type a time, and
excel applies a time format, other than that, Excel does little to the entries you type because it

10
doesn’t know whether a number represents dollars, percentage points, or the number of cattle on
a ranch.

To you and me, however, values do have significance, and we need to see what those values
represent. Fortunately, Excel provides several number formats that clearly show what the values
represent. The following table lists and describes the available number formats.

Excel Numbering Formats

Number Format Description

General Displays numbers just as you type then.

Number Display the whole number plus two decimal places. If you type 457, the
cell displays 457.00.
Currency Displays a dollar sign, followed by the whole number and two decimal
places. If you 457, the cell displays $457.00. Excel can display negative
numbers in red, enclosed in parentheses, or both, or insert a minus sign
before the number.
Accounting Similar to currency, except it provides no special display for negative
values.
Date Provides several options for displaying dates, including MM/DD/YYYY
and DD-MM.
Time Provides several options for displaying the time, including HH:MM:SS
and HH:MM:SS AM/PM.
Percentage Displays the number you type, followed by two decimal places and a
percentage symbol for example, 99.99%.

Number Format Description

Fraction Displays a fraction as a fraction, rather than as a date or text entry.

Scientific Displays a number in scientific notation. That is, Excel displays the base
number, followed by E+#, where E+# indicates the number o fdecimal
places you need to move the point to the right. For instance 42,578 shows
up as 4.E+4.
Text Treats number as text and left-aligns each number in its cell.
Special Handles formatting for special entries, including ZIP codes, Social
Security numbers, phone numbers, and other numerical entries that are not
actually values.
Custom enables you to create your own numeric format.

11
To change the number formatting for a particular cell or group of cells, follow these steps:

1. Click the cell or drag over the cells whose number format you want to change. (To
select multiple cells, see “Selecting a Cell Range,” later in this chapter.)

2. Open the Format menu and choose Cells. The Format Cells dialog box appears.
3. If necessary, click the Number tab to bring it to the front.
4. In the Category list, click the general category that best represents the type of
number format you want. For example, to display values as dollar amount, click
Currency

12
2.7 CELL REFERENCES (COLUMN AND ROW)

Excel work sheet/spread sheet is filled with no.of cells. These cells are used for entering all kinds
of data. In some situations there will be the need of referring a cell for doing some arithmetical
or any operation. To do such operations we must know the cell reference. Generally cells are
referred by using the respective column name and the row name. The columns are named as A,
B, C, D…………..respectively and the rows are named as 1, 2, 3………. The first cell in the
spread sheet referred as A1 and then the next is referred as A2. Similarly the second column cells
are referred as B1, B2, and B3……… respectively.

Think if there are two values in A7 and B7. Then if we want to add the two values in
those cells and to put the result value in D7 cell then we must write “=A7+B7” in the D7 cell.
Then the result will be placed in “D7” cell as shown below.

13
2.8 SUMMARY

This unit covers the detailed descriptions of all the buttons in the toolbar. Here you know how
the existing data is manipulated to you requirement. Going through the fonts you know the types
of fonts and the corresponding structure. In Format cells dialogue box, Font option presents
Font, Font style, Size, Underline, Color, Normal Font, Effects and preview. Background colors
are used to change the color of the existing background color. Merge cells are mainly used to
produce any type of reports. Number formats and their descriptions are used to represent the
structures of the numbers of the selected type. Cell references helpful to know the row number
and column number where you are accessing the data.

14
2.9 CHECK YOUR PROGRESS

1. Describe briefly Adding and Removing buttons form the Menu.

2. Develop excel worksheet using Formatting and Editing Toolbars (Fonts, Background
colors, Merge Cells, Number Formats) in the following format.

15
 3. Create an application using Cell References in the following format

SOLUTIONS

SOLUTION – 1

1. If the button that you want is not seen on the toolbar, choose by pressing the More Buttons
drop-down arrow. Once you have located the button you want, you can click on it to add it to
the toolbar. Similarly you can remove a toolbar button from the toolbar.

16
1. Click on the More Buttons drop-down arrow.
2. Click Add or Remove Buttons button.
3. Uncheck the buttons you want to remove from the toolbar. These will be removed from
the toolbar. Check the buttons you want to add to the toolbar. Such buttons will be added.
4. If an arrow appears at the bottom of the list, click on the arrow. Additional toolbar
buttons will be displayed.
5. Click anywhere on the document to close the menu.

SOLUTION -2

1. Enter the data for the Household Budget in the given format.
2. Highlight row1 and merge the cells.
3. In the merged cells place the heading Household Budget with bold and select the
background color as green.
4. After entering the data, select the number format for currency and change the currency
format to dollar.

17
 SOLUTION-3

1. Enter the data in a given format.

2. Display the sum of the salary by referencing the cell numbers.
3. Highlight the cell D9 and refer the cell number as “ =D5+D6+D7+D8” in the formula
bar.

2.10 FURTHER READING

• Read more: http://peltiertech.com/Excel/NumberFormats.html#ixzz0gFlNDFiR

• Leon Alexis, Leon Mathews, (2001 ), “Introduction to Computers with MS-Office
2000”, Tata McGraw-Hill Publishing Company Limited ,New Delhi

• Kraynak Joe, (2004), “Absolute beginner's guide to Microsoft Office Excel 2003”, QUE
publication
• http://www.ehow.com
• www.excelself.com

18