Spreadsheets with
MS Excel 2003
Ravi Soni
98290 83630
Spreadsheet
Spreadsheet is a structure that accepts data
values in tabular form and allows users to
manipulate, calculate and analyze the data in
desired manner.
Spreadsheets are also capable of generating
graphs and charts to show the relationships
among data more interactively.
Spreadsheet - Cells
Technically, it is a matrix of rows and
columns.
The intersection areas of rows and columns
are called “cells”.
These cells are used for entering numbers,
text, formulae and functions.
Generally, spreadsheet programs offer over 2
million cells to work with and each cell can
accommodate upto 240 characters.
Spreadsheet Packages
There are many spreadsheet packages
available in the market.
Most popular are Lotus 1-2-3 and Microsoft
Excel.
Lotus 1-2-3 is written in C language and was
developed by Lotus Development Corp.,
USA.
This package provides the facilities of
multipage spreadsheets.
MS Excel 2003 - Window
Menu Options
•File - Create, open, save, print, and close workbooks.
•Edit - Perform editing functions on your workbook, including copying, cutting,
and pasting data and objects, moving to various locations in a workbook, and
undoing and redoing actions.
•View - Change the workbook's display size and style.
•Insert - Insert new worksheets, or additional space, such as new cells,
columns, or rows; also insert charts, comments, functions, hyperlinks and
other objects into your workbooks.
Menu Options
• Format - Apply formatting to worksheets and their contents.
• Tools - Use Excel's tools, such as the spell and grammar checker, macro
recorder, and protection and collaboration features, and set your Microsoft
Excel preferences.
• Data - Sort, filter, and otherwise arrange the data on your worksheets.
• Window - Switch between open workbook windows, or split the window of
the current workbook.
• Help - Access Microsoft Excel's online help.
Additional menus may appear when you install a program that
includes a Microsoft Excel plug-in.
Toolbars
The Microsoft Excel toolbars group together shortcuts to
common commands; these shortcuts take the form of
buttons. You can click a toolbar button to quickly apply
formatting, save or print a workbook, copy or paste data,
or accomplish another of a variety of tasks.
To show or hide a toolbar, open the View menu, select
Toolbars, and then select the toolbar you want to show
or hide. You can also access the toolbar menu by right-
clicking anywhere on a visible toolbar.
Formula Bar
The formula bar displays the contents of the active, or selected, cell. If
the cell contains a formula, the formula will appear in the formula bar,
while the value resulting from the formula is displayed in the cell itself.
You also use the formula bar to enter formulas. Clicking the Edit
Formula button next to the formula bar opens the Formula Palette,
which helps you select and construct functions. Functions are formulas
that are built into Microsoft Excel, and allow you to perform a variety of
calculations.
The name box to the left of the formula bar displays the current location
of your cursor. If a cell is selected, the cell is displayed here. If you're in
the process of selecting a range of cells, the name box displays the
range (for example, A1 X C9), and changes as the selection changes.
Status Bar
The status bar at the bottom of the Microsoft Excel window
displays the current status of operations being performed on the
open workbook, such as the progress as the workbook is saved.
It also includes the AutoCalculate feature, which displays a
selected calculation for any range (or set) of cells you select,
automatically. By default, it displays the sum of the values found
in the cells.
You can select a different type of calculation by right-clicking the
AutoCalculate feature in the status bar and selecting a different
type of calculation from the menu.
Workbooks and Worksheets
A workbook is the Microsoft Excel file that stores your data. A
workbook can contain many worksheets, which you use to enter
and work with your data. You can enter and display data in
multiple worksheets in a workbook, and perform calculations
using data found across all the different worksheets.
Worksheets are composed of cells, active areas in which you
input data or formulas for calculation. In MS Excel 2003, Cells
are aligned along columns, labeled with the letters A through IV,
and rows, labeled with the numbers 1 through 65536.
When a cell is selected, its location appears in the name box,
located to the left of the formula bar.
Creating a New Workbook
From the File menu, select New
To quickly create a new workbook from the default template,
click the New button on the Standard toolbar.
Saving a Workbook
To save your current (open) workbook, select Save from the File menu,
or click the Save button on the Standard toolbar.
If this is the first time you've save the workbook, the Save As dialog
opens, prompting you to enter a file name and select a location for the
file.
Selecting Worksheets
Use the sheet tabs at the bottom of the Excel
window to switch between sheets in a
workbook. Simply click on the tab for the
sheet you want to activate.
The active worksheet is the worksheet that's
currently selected; its contents are visible in
the Microsoft Excel window. You activate a
worksheet in order to work in it.
Inserting/Creating a Worksheet
To insert a new worksheet into the open workbook, select
Worksheet from the Insert menu, or right-click a sheet tab and
select Insert from the shortcut menu.
When the new sheet is inserted, you can name it and, if
necessary, move it to the desired location in the workbook.
Naming Worksheets
It's helpful to name the individual worksheets in a workbook,
especially when the workbook contains a number of sheets. The
easiest way to do this is to double-click the sheet tab for the
worksheet you want to name, and then type the new name and
press Enter
You can also right-click the sheet tab and select Rename from
the shortcut menu. This highlights the current name, allowing you
to type over it with a new name. Press Enter when you've
finished.
Deleting Worksheets
To delete sheets from a workbook, select the sheets
and then select Delete Sheet from the Edit menu.
You can also right-click the sheet tab (or group of
tabs) and select Delete from the shortcut menu
Keep in mind that when you delete a worksheet, you
delete all the data and functions that the sheet
contains.
Entering Text and Numbers
To enter data into a cell, you must first select, or activate, the
cell. To do this, you click the cell with your mouse, or move the
cursor into the cell using the arrow keys on your keyboard. Once
a cell is selected, type your data and press Enter (or Tab, if you
want to move to the adjacent cell).
Among Excel's options is the ability to turn on and off editing in
cells. When this option is turned on, you can double-click a cell
and type directly into it.
When editing in cells is turned off, you must use the formula bar
to enter and edit data: Select the cell, type the data in the
formula bar (or highlight the data in the formula bar, to edit it),
and then press Enter.
Working with Ranges
In Excel, you can work with multiple cells at 1. Select the range.
once (called a range). To select a range, click
the first cell and, holding down the mouse
button, drag the mouse to the last cell. All the
cells in the range you defined are selected.
2. Type the data.
Ranges in Excel are used in a variety of ways.
You can select a range to apply the same
formatting to all the cells (such as font, size,
color, borders, and shading), or you can enter
the same data into each of the cells in the range 3. Press Ctrl + Enter.
using the Ctrl + Enter shortcut:
Inserting Cells
Use the Insert command to insert a row, a
column, or a blank cell into a worksheet.
Deleting Cells
Select the cell, the range, or the row or column you want to
delete.
From the Edit menu, select Delete. Or, right-click the selection
and select Delete from the shortcut menu.
Moving and Copying Data
To move data from one cell to another:
Select the cell whose contents you want to move.
Point to the edge of the cell.
When the mouse pointer becomes an arrow, click and drag the
cell to the new location.
You can drag and drop entire ranges of cells
Basic Formulas
A formula is an equation that calculates a value for a cell or a
range of cells. You enter a formula into the formula bar preceded
by an equal (=) sign. This tells Excel to calculate a value for what
you've entered, rather than treating your entry as plain text.
Formulas remain behind the scenes; that is, cells display the
resulting values of the formulas you've entered, rather than the
formulas themselves
Basic Formulas
Excel calculates formulas using standard mathematical rules;
specifically, values are calculated from left to right, with any operations
contained in parenthesis performed first.
For example, if you want to calculate the average sales among all three
widgets for the month of January, you might enter the following formula
into a new cell below the monthly total:
=(B2+B3+B4)/3
In this formula, "B2" refers to the value contained in cell B2; "B3" refers
to the value contained in cell B3; and "B4" refers to the value contained
in cell B4. If you later change the value of one of those cells-for
example, you find 46 units of Widget 1 were sold, and not 45 - Excel will
update the total.
The number "3" in the formula is a constant-a value that doesn't
change, unlike the cell references, whose values can change if you edit
the data in the cells.
Excel Functions
Excel provides many built-in formulas, called functions, which make it
much simpler to perform calculations. In the last section, the following
formula was used:=(B2+B3+B4)/3
To calculate the total number of sales for the month of January, you
could enter the formula =B2+B3+B4 into cell B6 (next to Total).
However, it's easier to use the SUM function, particularly when you're
adding together a large number of cells in a range:
=SUM(B2:B4)
Excel Functions
Functions begin with the name of the function (for example,
"SUM") followed by parentheses enclosing the specific values
being calculated (called arguments). These values can be
constants, cells references, or even text you want to combine.
To refer to a range of cells, type the first cell in the range, then a
colon (:), and then the last cell in the range (for example,
"B2:B4").
Formula Palette
Excel's Formula Palette can help you construct
formulas that contain functions by displaying a list
of the built-in functions and the arguments used for
each. To access the Formula Palette:
Select the cell in which you want to enter the formula.
Click the Edit Formula button in the formula bar.
The Formula Palette opens.
Formula Palette
When you select a function, the Formula Palette displays fields in
which you can enter the arguments (the values to be calculated),
as well as a description of the function and the current result of
the calculation.
In the example above, the AVERAGE function has been
selected. The AVERAGE function adds together the values
(called arguments) you supply and then divides this total by the
number of values that were added, which Excel calculates for
you. In this function, you only need supply the values whose
average you want to calculate.
Absolute and Relative Cell
References
By default, Excel uses relative cell references.
Relative cell references are those whose
locations Excel calculates based on the
location of the formula. This means that when
you insert a new row or column into a range
included in a formula, Excel automatically
includes the new data in its calculations, and
edits the formula accordingly. Similarly, when
you copy a formula from one cell to another,
Excel adjusts the cell references.
For example, you can copy the formula in cell
B6 below (which adds together the values in
the range B2:B4) to cell C6, and Excel will
adjust the formula to add together the values
in the range C2:C4.
Absolute and Relative Cell
References
In contrast, absolute cell references are references that refer to a
specific location. These are indicated by a dollar sign ($)
preceding the part of the reference that should be absolute-that
is, before the row reference, before the column reference, or
before each, to make the entire cell reference absolute:
$B4 indicates that column B is absolute, but row 4 is relative.
B$4 indicates that column B is relative, but row 4 is absolute.
$B$4 indicates that the entire reference-to cell B4-is absolute.
When you use an absolute reference, Excel will not adjust
formulas when they're copied to new locations.
Thanks !
