 | |
|
Worksheet - Excel
spreadsheets are called worksheets
|
|
Workbook - an Excel workbook contains one or more
worksheets
|
|
Formatting toolbar - appears under the Standard
toolbar, and contains the most used formatting commands
|
|
Standard toolbar - contains buttons corresponding to
the most basic Excel commands
|
|
Toolbars – several toolbars are available and they
provide convenient ways to execute commands
|
|
ScreenTips – appear when the mouse points to a button
|
|
Status bar – shows the current activity state of
Excel
|
|
A shortcut menu – requires a right hand mouse click
and a context sensitive menu will appear
|
|
Formula bar - lets the user know both where the active
cell is and what has been entered in the cell
|
|
File menu - like other Office applications contains the
Open Save and Print command
|
|
Page setup - command allows you to set options for your
printed worksheet
|
|
« Portrait
Orientation - printing vertically
down the page.
|
|
« Landscape
Orientation - prints on the page
horizontally across the page
|
|
« Scaling –
adjusts the worksheet to the required percentage of the original size, or
can also resize the worksheet so that it will fit to the required number
of pages, usually 1 page.
|
|
« You can arrange
the top, bottom, left and right margins in the Margins tab. You can
also position the header and footer here. You can preview the result in
the middle preview area.
|
|
« You can also
center the worksheet across the page horizontally and/or vertically.
|
|
« In the
Header/Footer Margin, you can type the header and/or footer. You can
either select a pre-typed text such as the name of the workbook from the
header list/ footer list or type a text of your choice by clicking on the Custom
Header / Custom Footer buttons. You can use the button marked
with
A to change the font characteristics of the text you want to insert.
Note that, what you type will be left-aligned in the Left section,
centered in the Center section, and right-aligned in the Right section.
|
|
« In the Sheet tab, you can
have the gridlines of the worksheet to be printed by selecting the
corresponding check box, Print Gridlines.
|
|
« You can also have the Row
headings, 1,2,3 and Column headings, A,B,C.. to be printed by selecting
the corresponding check box, Print Row and Column headings.
|
|
Cell - the intersection of a row and column form a
cell in a worksheet
|
|
Active cell – is the cell shown in black borders
and shown in the formula bar
|
|
Cell contents - actual entry of a cell containing
formulas (opposite of displayed value)
|
|
Cell reference - each cell has a unique address or
reference such as A9 (cell located at column A and row 9). Rows are
identified by numbers: 1,2,3… whereas columns are identified by letters:
A,B,C…
|
|
To refer to |
Use |
|
The cell in column A and row 10 |
A10 |
|
The range of cells in column A and rows 10 through 20 |
A10:A20 |
|
The range of cells in row 15 and columns B through E |
B15:E15 |
|
All cells in row 5 |
5:5 |
|
All cells in rows 5 through 10 |
5:10 |
|
All cells in column H |
H:H |
|
All cells in columns H through J |
H:J |
|
¨ Undo mistakes
|
|
ü On the Standard
toolbar, click the arrow next to Undo.
|
|
ü Excel displays a list
of the most recent actions you can undo.
|
|
ü Click the action you
want to undo. If you don't see the action, scroll through the list.
|
|
ü When you undo an
action, you also undo all actions above it in the list.
|
|
Tip If you later decide you didn't want
to undo an action, click Redo on the Standard toolbar.
|
|
|
|
¨ Repeat your last
action
|
|
ü On the Edit
menu, click Repeat. ( ctrl + Y )
|
|
ü If you can't repeat
the last action, the Repeat command changes to Can't Repeat.
|
|
|
|
¨ EDIT CLEAR versus
EDIT DELETE:
|
|
The Edit Delete command deletes the selected cell,
row, or column from the worksheet, and thus its execution will adjust cell
references throughout the worksheet. On the other hand, the Edit Clear
(or pressing the Delete key on the keyboard) erases the contents of a
cell and has no effect on the cell references in other cells.
|
|
|
|
Constant - an entry that does not change and may be
numeric or descriptive text
|
|
Formula - a combination of numeric constants, cells
references, arithmetic operators, and/or functions that produce a new value
from existing values. Every formula should start with an equal sign
=.
|
|
Order of computation in formulas:
|
|
1. Any functions are evaluated first
|
|
2. Parentheses are evaluated second
|
|
3. Exponentiation ^ is evaluated next
|
|
4. Multiplication * or Division / is evaluated next
|
|
5. Addition + or Subtraction – is evaluated finally
|
|
Order of evaluation is from left to right among equal
precedence operations
|
|
Absolute reference - a reference that does not change
when copied. It is specified with a dollar sign in front of both the row and
column. ($G$3)
|
|
Relative reference - a reference that adjusts during a
copy operation and is specified without dollar signs. (G3)
|
|
Mixed reference - a reference that adjusts either the row
or column reference but not both. It is specified with a single dollar sign. ($G3 or
G$3)
|
|
|
|
Sample Cell Reference in a Formula |
Meaning |
|
A1 |
Relative address of the cell in row A and column 1;
both the row header and column header may change when the formula is
copied. |
|
$A$1 |
Absolute address of the cell in row A and column 1;
both the row header and column header remain constant when the formula is
copied. |
|
A$1 |
Absolute row, relative column addressing; only the
column header may change if copied to horizontally. |
|
$A1 |
Relative row, absolute column addressing; only the row
header may change if copied vertically. |
|
R1C1 |
Relative addressing; refers to 1 column down and one
column right when the formula is copied. |
|
Range - rectangular group of cells, which are
specified by indicating the diagonally opposite corners
|
|
Copy - this command duplicates the contents of a
cell (range)
|
|
Move
– transfer the contents of a cell
|
|
Cell referencing is important during a move or
copy operation.
|
|
Samples on move operation and referencing
|
|
|
A |
B |
C |
|
|
A |
B |
C |
|
1 |
5 |
|
|
|
1 |
5 |
|
|
|
2 |
2 |
|
|
|
2 |
2 |
|
|
|
3 |
=A1+A2 |
|
|
|
3 |
|
|
=A1+A2 |
Cell A3 is moved to cell C3
|
|
A |
B |
C |
|
|
A |
B |
C |
|
1 |
5 |
|
|
|
1 |
|
|
5 |
|
2 |
2 |
|
|
|
2 |
2 |
|
|
|
3 |
=A1+A2 |
|
|
|
3 |
=C1+A2 |
|
|
Cell A1 is moved to C1
|
|
A |
B |
C |
|
|
A |
B |
C |
|
1 |
5 |
|
|
|
1 |
|
|
5 |
|
2 |
2 |
|
|
|
2 |
|
|
2 |
|
3 |
=A1+A2 |
|
|
|
3 |
|
|
=C1+C2 |
Cells A1, A2 and A3 are moved to C1, C2, C3 respectively
|
|
A |
B |
C |
|
|
A |
B |
C |
|
1 |
5 |
=A3*4 |
|
|
1 |
5 |
=C3*4 |
|
|
2 |
2 |
|
|
|
2 |
2 |
|
|
|
3 |
=A1+A2 |
|
|
|
3 |
|
|
=A1+A2 |
Dependent Cells: Cell B1 depends on A3 and cell A3 is moved
to C3
|
|
A |
B |
C |
|
|
A |
B |
C |
|
1 |
5 |
=$A$3*4 |
|
|
1 |
5 |
=$C$3*4 |
|
|
2 |
2 |
|
|
|
2 |
2 |
|
|
|
3 |
=$A$1+$A$2 |
|
|
|
3 |
|
|
=$A$1+$A$2 |
|
Absolute cell addresses
|
|
¨ Function
- a predefined computational task or calculation, for example
AVERAGE ()
and SUM ().
|
|
Compare AVERAGE(B2,B3,B4,B5) and AVERAGE(B2:B5)
and (B2+B3+B4+B5)/4 when you insert/delete a row between the 2nd
and 5th rows.
|
|
Insert - command adds rows or columns to an existing
worksheet
|
|
Delete - command removes existing rows or columns from
a worksheet
|
|
Edit Delete will delete all contents including
formatting (even the cell itself) while Edit Clear only clears the
contents
|
|
To display the formulae instead of the results:
|
|
Tools Menu ® Options ®
View Tab ® Check the Formulas box. Alternatively,
use the Ctrl and` key combination to toggle between the two view types.
|
|
To separate the Standard and Formatting toolbars:
|
|
Tools Menu ® Customize ®
Options Tab ® Clear the check box that has the
toolbars share one row.
|
|
File Menu, Save as command
|
|
Column width - the width of a worksheet column,
which can be changed with the Format Þ Column
command
|
|
Row height - the height of a row in a worksheet. The
row height changes automatically as the font size changes. Format Þ
Row command may be used to adjust the row height.
|
|
Alternatively you can change the column width or the row
height by dragging from its border. If you double click the border, it
will adjust the measure to fit.
|
|
AutoComplete
|
|
As soon as you begin typing a label into a cell, Excel
searches for and automatically displays any other label in that column that
matches the letters you have typed. It is handy if you want to repeat a label.
But it can be distracting if you want to enter a different label that just
begins with the same letter. To turn the feature on (off), pull down the Tools
Menu, click Options, then click the Edit tab. Check (clear) the box to enable
(disable) the AutoComplete feature.
|
|
|
|
Working with worksheets
|
|
Inserting a worksheet:
Use Insert Worksheet command to insert a worksheet before the active worksheet.
Alternatively, right-click the tab name (worksheet name) and select the insert
command.
|
|
Deleting a worksheet: Right click the worksheet name to
be deleted and select the delete command.
|
|
Renaming a worksheet: Right click the tab name and
select the rename command.
|
|
Moving or copying a worksheet: Right click the tab name
and select the move or copy command. Alternatively, use the drag-and-drop method
to move a worksheet and Ctrl and drag-and-drop method to copy a worksheet.
|
|
|
|
Note that every worksheet may have a different page set-up.
Normally each worksheet is printed separately. To print all the worksheets in a
workbook you should select Entire workbook option in the File Print
command. To print some of the worksheets but not all, select each worksheet and
hold the Ctrl key as you make the selections.
|
|
|
|
To select non-continuous cells (cell ranges):
|
|
Select the first cell (cell range)
|
|
Press and hold the Ctrl key as you select the next
cell (cell range)
|
|
Release the Ctrl key
|
|
