UNIT – IV

Microsoft Excel: Introduction – Creating a Worksheet – Formatting and Printing a

Worksheet – Creating Charts

4.1 Introduction

A spread sheet is essentially a matrix of rows and columns. Consider a sheet of paper on
which horizontal and vertical lines are drawn to yield a rectangular grid. The grid namely a
cell, is the result of the intersection of a row with a column. Such a structure is called a
Spread sheet.

A spread sheet package contains electronic equivalent of a pen, an eraser and large sheet of
paper with vertical and horizontal lines to give rows and columns. The cursor position
uniquely shown in dark mode indicates where the pen is currently pointing. We can enter text
or numbers at any position on the worksheet. We can enter a formula in a cell where we want
to perform a calculation and results are to be displayed. A powerful recalculation facility
jumps into action each time we update the cell contents with new data.

MS-Excel is the most powerful spread sheet package brought by Microsoft.

The three main components of this package are

 Electronic spread sheet
 Database management
 Generation of Charts.

Each workbook provides 3 worksheets with facility to increase the number of sheets. Each
sheet provides 256 columns and 65536 rows to work with. Though the spreadsheet packages
were originally designed for accountants, they have become popular with almost everyone
working with figures. Sales executives, book-keepers, officers, students, research scholars,
investors bankers etc, almost any one find some form of application for it.

A spread sheet looks a lot like a table you might see in any word processing package, but it
has some very important features that most tables do not. The first is that it is designed to
make repetitive and/or complicated calculations very easy to carry out. Secondly, most spread
sheet programs have advanced graphing capabilities that make producing graphs from the
data on the spread sheet relatively simple.

Starting Excel
 Switch on your computer and click on the Start button at the bottom left of the
screen.
 Move the mouse pointer to Programs, then across to Microsoft Excel, then click on
Excel as shown in this screen.

1 | Department of Information Technology, Govt Arts College, Coimbatore

  When you open Excel a screen similar to this will appear

Entering formulas
There are two ways to enter formulas in Excel, either use one of the functions already
programmed in Excel, or enter your own from scratch.

Entering your own formula

To enter your own formula start by typing an equal sign (this tells Excel you are entering a
formula) and then entering the formula using operands and operators. Operands can either
be numbers you enter, or can be cell references. To enter a cell reference into a formula either
type it, or click the cell.

2 | Department of Information Technology, Govt Arts College, Coimbatore

Toolbar Name Usage
Title Bar Displays the title of the workbook you are
currently in.
Menu Bar Menus, left click menu to see choices.
Formatting Toolbar. Various formatting shortcuts
Standard Toolbar Standard Tools, similar to other Microsoft
products, and some special tools for Excel.

Formula Bar Two important fields, the left field shows the
cell address of the cell your cursor is currently
located in. The right field displays the 'actual'
contents of the cell, this field is especially
important when you are entering formulas
Tab Bar Allows you to move through sheets. Note the
active sheet is always highlighted.
Status Bar Displays a description of what Excel is doing.

4.1 CREATING A WORKSHEET

1. Entering Labels and Values

Move to desired cell with the mouse or arrow key. Type letters or numbers into cell. Press
Enter or an arrow key to insert your entry into the current cell.
2. Entering Formulas and Functions
=b2-b3 is a basic formula that will subtract the number that is in b3 from the number in b2.
All formulas must begin with an equal sign. The operators used for other calculations are:
a. + (plus sign) Addition =b12+b13
b. * (asterisk) Multiplication =b12*b13
c. / (forward slash) Division =b12/b13
A function is a built-in formula with a name that Excel recognizes. Common functions are
SUM, AVERAGE, MAX, MIN, and COUNT. Functions consist of the function name and the
cells to be summed. In these examples b2 is the first number to be summed or averaged and
d2 is the last. Every cell between these two is included in the function.
=SUM(b2:d2) =MIN(b2:d2)
=AVERAGE(b2:d2) =COUNT(b2:d2)
=MAX(b2:d2)
3. Editing Cells
a. Select the cell(s) to edit and press delete to remove contents.

3 | Department of Information Technology, Govt Arts College, Coimbatore

b. Select the cell(s) to edit and type over to change the contents.
c. Select the cell(s) to edit and make changes by clicking in the edit area, then use the delete
and backspace keys or insert text.

4. Copying Items
Select the cells containing the item(s) you want to copy. To fill in adjacent cells, drag the fill
handle of the selected cell(s) (be sure the mouse pointer is a + ) to the copy destination.
5. Deleting Columns Or Rows
Place the cursor in the column or row you want to delete. Choose Edit, Delete from the
menu. Select either entire row or entire column and choose OK.
6. Inserting Columns or Rows
Place the cursor where you want to insert the row or column. Choose Insert, Column or
Row.
Click and drag the column boundary on the right side of the column heading until the column
is the width you want.

Creating a new workbook

 Click on File menu and then click on New

4 | Department of Information Technology, Govt Arts College, Coimbatore

  Click Workbook and then click OK button. You will get the screen as shown below.

 Enter data as shown in the figure below :

5 | Department of Information Technology, Govt Arts College, Coimbatore

Saving Workbook
 Click on File menu and then click save. You will get the below Screen

 In the File name text box, type sample and then click Save button

Opening an existing workbook

 Click on the File menu and click on Open. The open dialog box will appear
 Click on some file (Example: sample.xls), then click on Open.

Closing your workbook

 Click on File menu, then click Close to close your workbook Cursor Management

Moving around the worksheet

 Open sample.xls workbook.
 Move the cursor in your worksheet by using the arrow keys on the right-hand side of
the keyboard.
 When you have got lots of rows of data you can move the cursor more quickly by
using the PgUp and PgDn keys to move up and down a screen at a time.
 To move one screen to the right, press the Alt key and PgDn keys together.
 To move one screen to the left, press the Alt and PgUp keys together.
 To move further to the right, just keep pressing the right arrow key
 To move back to cell A1, press the Ctrl and Home keys together.
 Pressing the Home key on its own takes you back to column A

6 | Department of Information Technology, Govt Arts College, Coimbatore

  To move to the last column press the Ctrl and right arrow keys together.
 To move to last cell containing data, press Ctrl and End keys together.
 To move to the last row (65,536), press Ctrl and the down arrow keys together.
 You can also move the cursor with the mouse. Move the mouse pointer to the location
you want. Press and release the left mouse button once when the cursor is where you
want it.

Moving to a Specified cell
 Click on the Edit menu, choose Go To. You will get the below Screen


 Enter the destination cell reference in the Reference text box.

 Click OK to move directly to the specified cell.

Create a Complex Series

Using AutoFill
 Select the first cell in the range you want to fill.
 Enter the starting value for the series, and then click the Enter button on the formula
bar.
 Position the mouse pointer on the lower-right corner of the selected cell, and then
hold down Ctrl.The pointer changes to the fill handle pointer (a black plus sign with a
smaller plus sign).
 Drag the fill handle pointer over the range where you want the value extended. The
destination value appears in a small box.
 Click the AutoFill Options button, and then click an option that specifies how you
want to fill in the data.

7 | Department of Information Technology, Govt Arts College, Coimbatore

  If necessary, click a sheet tab scroll button to display other tabs.
 Click a sheet tab to make it the active worksheet.
 To select multiple worksheets, press and hold Ctrl as you click other sheet tabs.

Name a Worksheet
 Double-click the sheet tab you want to name.
 Type a new name.The current name, which is selected, is replaced when you begin
typing.
 3 Press Enter.

8 | Department of Information Technology, Govt Arts College, Coimbatore

 4.3 FORMATTING AND PRINTING A WORKSHEET

Formatting a worksheet
Formatting a spread sheet means changing the way it looks to make it neater and more
attractive. Formatting changes can include modifying number styles, text size and colours.
Many people format their spread sheet as they go but a lot of time and effort can be saved by
waiting until the sheet it relatively complete before worrying about the formatting. This is
because it is a lot easier to format a sheet once it already has a good amount of content.

Using AutoFormat
Excel’s AutoFormat feature allows you to choose from a number of different table formats
and then apply the formatting settings to a range of cells you specify. When you apply an
AutoFormat, it will replace any existing formatting on the selected cells. You can add your
own formatting afterwards though. Using AutoFormat on a Table
1) Open your Grades workbook if it is not still open.
2) Select the cell range A5:H18.
3) From the menu select Format, AutoFormat. A dialog box like the one below will
appear.

A list of previews for each format appears.

9 | Department of Information Technology, Govt Arts College, Coimbatore

4) Click the Options button.

You can now choose what parts of the formatting you want to use. For example, if you didn’t
want the column widths to be changed, you could turn off the Width/Height option.

5) Scroll through the list to see the available formats. Click on one you like and then click
OK.

6) If you like, you can repeat the process with the cells still selected to try other
AutoFormats.

Formatting Using Shortcuts

Many of the most commonly used formatting options can be selected from the formatting
toolbar or by using keyboard shortcuts.

Change the font style for the selected cells

Turns Bold formatting on and off in the selected cells [Ctrl] [B]
Formats the selected cells with the percent style (##%)
Change the background colour of the selected cells

Basic Number Formatting

It is often important to change the way numbers are presented in a spreadsheet. For example,
you might want a number that looks like.
3845
To appear as
$3,845.00

In Excel it is not necessary to type things such as dollar signs yourself since the formatting
options can place them in for you. Formatting options can easily be changes later as well.

1) Select the cells that have the grades for the students. These should be cells B6:F18.
Some of the AutoFormats chosen before may have formatted these numbers as dollar
amounts which is not appropriate in this instance.

10 | Department of Information Technology, Govt Arts College, Coimbatore

 2) Click the Comma formatting icon.
3) Click the Decrease Decimals icon twice.

The numbers should now be back to normal. Now we will format the main heading so that it
becomes centred across several cells.
4) Select cells B1:H1.
5) Click the Merge and Centre icon.

The selected cells will now be merged in to one with the contents centre aligned.

Column Widths and Row Heights

Column widths and Row heights can be adjusted by going to the menu and selecting Format,
Column, Width or Format, Row Height. It is usually easier, however, to do it using the mouse
as demonstrated in the following exercise. Adjusting Height and Width

1) Move your mouse pointer so that it is between the column A and B headings. You
mouse pointer will change to a double arrow shape.
2) Click your mouse button and drag to the right. A caption will appear telling you how
wide the column will be.
3) Keep on dragging until the column width reaches 15 (110 pixels). You can also resize
a column so that it will automatically fit the contents of the column.
4) Move your mouse pointer so that it is between the column A and B headings again.
5) Double-click to automatically fit the column’s contents.
6) Select column B through to column F by dragging across the column headings.

7) Follow the first three steps in this exercise to change the width of any of the selected
columns to 9.29 (70 pixels). All of the selected columns will become the same width.

If you use the AutoFit method, then each column will resize to fit its contents so each column
may end up a different size.

8) Use any of the above methods to select suitable widths for columns G and H.
These same methods can also be used for adjusting the height of rows. Row height will often
change automatically when you change the size of text in the row.

Advanced Formatting Options

Many additional formatting options are not available on the Formatting Toolbar. These
additional options can be found in the Format Cells dialog as shown below

11 | Department of Information Technology, Govt Arts College, Coimbatore

There are six tabs along the top of the dialog box which allow you to go to different
categories for formatting. The first five tables will be demonstrated below. Sheet protection
will be explained in a later exercise.

Number Formats
Numbers, dollar amounts, percentages, dates and times are all treated by Excel as numerical
values. As far as Excel is concerned, they are all numbers. The only difference is the way
they’re formatted. In an earlier exercise, we used icons on the toolbar so change the number
format of the main cells in the table. The toolbar icons only give a few format choices. The
Formatting options however, give numerous number formatting options and even allow you
to create your own custom number formats.

12 | Department of Information Technology, Govt Arts College, Coimbatore

Changing Number Formats

1) Select the cells with the table averages (B15:F15).

2) Access the Format Cells dialog box by using one of the following methods.
• From the menu, select Format, Cells.
• Right-click the selected cells and choose Format Cells.
• Press [Ctrl] [1].
3) Make sure the Number tab is selected.
4) From the list of Categories on the left choose Number.
5) Set the number of Decimal Places to 2. Note that a sample of the selected number
format appears at the top.
6) Click OK to confirm the change.

Changing Date Formats

1) Select cell A3.
2) Access the Format Cells options as shown above.
3) Choose Date for the number category.
4) Select a date format which includes the name of the month rather than the number of
the month.
5) Click Ok when done.

13 | Department of Information Technology, Govt Arts College, Coimbatore

Font Options
1) Select cell B1.
2) Access the Format Cells options.
3) Select the Font tab.
4) Select the following options as shown to the right:
• Font Style Bold
• Size 14
• Underline Double
• Colour Dark Blue
5) Click OK to confirm the changes.

Borders and Shading

In addition to using the borders icon, there are two other ways to format the borders of
selected cells.

Format Borders Using the Cell Format Options

1) Select cells A1:A3 (the ones with your name, age and date of birth).
2) Access the Format Cells options and click the Border tab.

14 | Department of Information Technology, Govt Arts College, Coimbatore

 3) Select a line style from the options to the right.
4) Choose a line colour from below the line styles.
5) The rest of the options allow you to specify which cell borders will have lines. Click
the outline button to place borders around the outer edge of the selected cells.
6) Click Ok to confirm the options.

Editing Border Patterns

1) Select the cells that you have been drawing borders around (K6:L10).
2) Access the Format Cells options and click the Patterns tab.

15 | Department of Information Technology, Govt Arts College, Coimbatore

 3) Click the patterns drop-down list .
4) When the list of pattern appears, click the diagonal stripe pattern.
5) Click the Patterns list again and choose Tan from the list of colours below the patterns.
6) From the list of colours under cell shading click yellow.
7) Click OK to confirm the choices.

Removing Formatting
1) Click on a blank cell in your spread sheet.
2) Click the Fill Colour icon to change the background colour of that cell.
3) Make sure that cell is still selected and press [Del].

Pressing delete will remove the contents of a cell but won’t affect the formatting in that cell.
If you want to remove formatting, such as the background colour in this cell, you need to use
a different method.
4) From the menu choose Edit, Clear, Formats.

All formatting will now be removed from that cell.

5) Save the changes to your Grades worksheet.

Printing a Worksheet
Previewing a printout
 Open cash.xls spreadsheet.
 Click on the File menu and click on Print Preview. A screen similar to this should
appear.

16 | Department of Information Technology, Govt Arts College, Coimbatore

  Since the size of the text is very small, you can click on Zoom button, it magnifies the
worksheet. Clicking on Zoom second time returns you to the original preview format.
 Press PgDn to move through your worksheet if it is more than one page long.
 Before printing make sure that your printer is switched on, is loaded with the
appropriate paper, and is on-line.
 If you are happy with the layout of your document, click on the Print button to obtain
a printout. You should see a message on screen telling you that your file is being
printer, and on which paper.

Printing landscape
 To select landscape mode, click on the File menu, Page Setup this screen will appear.
 Click on the Landscape button.

Fitting your worksheet to one page

 In the above screen click on the Fit To: box and type: 1 page wide by 1 page tall.
 If you need to make changes to your worksheet before printing, click on the Close
button to return to your workbook.

Adjusting margins
 In the Page Setup dialog box, click the Margins tab and enter the appropriate sizes(in
inches or centimeters)

Setting Header/Footer to your worksheet
 From the Page Setup dialog box, click on the Header/Footer tab to display the below
screen.

17 | Department of Information Technology, Govt Arts College, Coimbatore

  In the Header box either you select a title from the drop down menu or enter your
own title. Similarly for Footer box also you can set your own title.
 Click on OK.

Printing selected cells

 Open cash.xls spreadsheet.
 Click on the row 2 button (or any other row containing data) to highlight the entire
row.
 Click on File, Print Area, Set Print Area. The preview screen should only display the
selected cells. (Row 2).
 If the preview is satisfactory, click the Print button to print out only row 2.
 Click on File, Print Area, Clear Print Area to reset the Print Area.

4.4 CREATING CHARTS

Creating charts is a powerful feature in Excel. A chart uses values in a worksheet to create a
graphical representation of their relationship. With Excel charts, you can summarize,
highlight, or reveal trends in the data that might not be obvious when simply looking at the
numbers. When creating a chart, each column of data on the worksheet is part of the data
series. Each individual value within the row or column is called a data point.

18 | Department of Information Technology, Govt Arts College, Coimbatore

Creating a Pie Chart
 Open cash.xls spreadsheet.
 Select the cells A1 to G5 as shown below

 Click on Insert menu and click Chart option. This will start the Office Assistant, to
guide you through creating chart.
 Follow the instructions in each step of the Wizard. The Assistant explains each step.
 At step 3, you can specify the Chart title, X-axis title and Yaxis title separately.
 At step 4, click As object in sheet 1, then click Finish.
 Your chart is now finished. Save as cash4. Your chart is saved with the spreadsheet.
This type of chart is known as an embedded chart and is saved with its worksheet.

Creating charts when the data range is not continuous
 Open cash4.xls
 If your requirement is create a chart to show expenditure for February, then first select
cells A2 to A5.
 Hold down the Ctrl key and, while holding it down, select cells C2 to C5. Your screen
should be similar to this one.
 Click on the Chart Wizard and create a column chart. Your screen should look similar
to this.
 If your chart doesn’t appear to show any data, you probably included some other
cells, probably A1 and/or C1. If so, delete your chart and re-select the correct range.

Sizing a chart
¨ Open the cash3.xls created earlier. A screen similar to this one should appear.

19 | Department of Information Technology, Govt Arts College, Coimbatore

  The small black markers at each corner and mid-way along each side of the chart.
These indicate that the chart is selected, and are called its selection squares.
 Click on the mid-point marker on the right-hand side, hold down the left mouse
button and drag the mouse to the right about one inch(3cm), then release the mouse.
The width of the chart will have increased.
 Now practice the same operation on the mid-point marker of each of the other sides of
the chart.
 Now try the above, but this time on one of the four corner markers. Note that when
you use these techniques, the whole chart changes in size, but it retains its original
proportions.
 Now use the same technique to reduce the size of the chart.

Deleting Charts
 Make sure the chart is selected(the small black markers are visible). If not, move the
mouse pointer into the chart area and click and release the left mouse button once.
 Press Delete to delete the chart.

Moving charts and graphs

 Make the chart active.
 Move the mouse pointer into the chart area.
 Hold down the left mouse button and drag the chart to the desired position.

Chart headings and labels

 While creating charts the step3 asks for Chart heading, labels for X-axis and Y-axis.
You can define your own labels or click Next button so that the default values can be
accepted.

20 | Department of Information Technology, Govt Arts College, Coimbatore

  For example Chart title is Expenditure, X-axis label is months and Y-axis label is
Sales

Editing chart items

 Create the chart as shown below and save it as cash4.xls.
 Click the chart title(Expenditure). Selection markers(small black squares) will appear
around the selected item.
 You can move or size the title in the same way that you can move or size a chart.
Click the title box and drag it up by about one inch (3 cm), then release the mouse.
 You can format the title by selecting it, then right clicking and then selecting “Format
Chart Title” from the drop down menu. You will get the below screen.

21 | Department of Information Technology, Govt Arts College, Coimbatore

  You can select font type, font style and font size as shown above
 Click OK.

Adding text to a chart

 Open cash3.xls worksheet.
 Click View menu, click Toolbars, Drawing.
 Click the Text box icon on the Drawing toolbar.

Draw a text box inside the chart area as shown below

 Click inside the text box. A flashing text cursor will appear. Now type Household
Expenditure
 You can use the same procedure for any other text that you want to appear in charts.

Adding a legend to a chart

 Create a pie chart as shown below

22 | Department of Information Technology, Govt Arts College, Coimbatore

  Display the Chart toolbar, by dropping down the view menu and clicking Toolbars,
Chart. In the above figure the legend is already added.
 Click inside the pie chart, then click once on the add or delete legend button on the
Chart toolbar. The legend will be added if not already present and removed if it is
currently present.

 You can also add or delete a legend from the Chart, Chart options menu

Adding gridlines to a chart

 Open cash3.xls worksheet and change chart type to Column chart.

 Click Chart, Chart options to display this box.
 Click the Gridlines tab and tick the gridlines boxes required.

Adding data labels to a chart

 Open cash3 worksheet and change chart type to pie chart.
 Drop-down the chart menu and click Chart options.
 Click on the Data Labels tab.
 Click on Show label and percent. Your screen should look similar to this.

23 | Department of Information Technology, Govt Arts College, Coimbatore

24 | Department of Information Technology, Govt Arts College, Coimbatore