Discussion
Once
accountants want to sum up accounting data of a firm, they use an arrangement,
which allows them to work on data presented in a format of rows and columns.
Known as a worksheet this arrangement enables them to easily present the
information in summary form and perform simple calculations.
Highly
interactive computer programs are
available that allow you to present and work with DATA in an arrangement of rows and columns. Known as Spreadsheets,
these programs started out as electronic versions of accounting worksheets with
one major purpose - simple row and column arithmetic.
Microsoft Excel
Discussion
Microsoft
Excel is a spreadsheet that belongs
to the category of computer programs described above. MS Excel runs under MS
Windows and shares the common interface of other Windows particularly MS Office
applications
Some
of the important applications which spreadsheets are used for include:
·
Financial Modeling: You can create financial models, which allow you to work out
"what if" situations. In a spreadsheet changing variables within the
model yield differing results. Data can be consolidated automatically to yield
summaries at various levels.
·
Excel provides
many statistical, analytic and scientific functions, which allow you to analyze
numerical data and present findings.
·
Excel provides
powerful and flexible presentation tools including charts and graphs.
·
As a data
management tool Excel provides an interface which allows easy entry, editing,
sorting and filtering of data.
·
Excel can access
data in a wide variety of formats and is an ideal tool to analyze and
manipulate data.
Discussion
You
can start Excel the same way you can start other windows programs from the
Start menu on the windows taskbar.
Procedure
1.
Click the Start button on the Windows taskbar.
2.
Point to Programs.
3.
Select Microsoft Excel
The MS Excel Environment
Discussion
As
is common with other windows applications Excel provides you with and environment or interface to do your work. When Excel starts what you see on the
screen is a rectangular box or window with different parts to it. This is the MS Excel window. It consists of the application window and the workbook
window.
the application window
The application window contains the menu bar, toolbars, status bar, and the workbook window. The bars allow you to navigate
your way within Excel to perform various tasks
the workbook
window
The workbook window
opens within the application window when you start Excel, open an existing
file, or create a new workbook.
The workbook window contains worksheets, tab scrolling buttons and scroll bars,
which allow you to move quickly through a workbook and the Worksheet Area where you enter your data and perform your calculations.
Several windows may be open at the same time.
The
Mouse Pointer usually appearing on
the screen as an arrowhead is the mouse
device used to select commands or
actions to be executed. Within the Worksheet area it changes shape within the
worksheet area appearing as a cross.
Workbooks and Worksheets
Discussion
Workbooks
are files that Excel uses to store data.
Worksheets
which are the constructs within workbooks that Excel provides for you to
present and work on data
Navigating Excel
Discussion
The
bars within the Excel screen/window allow you to access the various tools Excel
provides to execute specific actions. It is essential to understand the use of
these bars, as they will enable you to work smoothly Navigating within Excel
via these bars is a simple task and you can do it easily using the mouse.
title bar
The title bar appears at the top of the application window and contains the name MS Excel and
the name of the workbook file you're working on.
menu bar
Right below
the title bar is the menu bar
showing the command options available
as a choice of items. Each item has varying levels of sub menus within it from
which you can select actions to carry out.
The menu bar provides access to the many features of Excel. Each menu contains commands grouped by function. Some menu
commands are grayed or dimmed, which indicates that the command is not available for the current task.
When you select an item on the menu bar, the corresponding
menu appears from which you can select the desired command.
When a menu command is followed by an ellipsis (...), selecting it opens
a dialog box in which additional information is entered.
In addition to the standard menus on the menu bar, Excel
also contains shortcut menus that may be accessed by clicking
the right mouse button. Shortcut menus contain commonly used commands and are context-sensitive. Therefore, the
options available on the shortcut menu vary, depending on the area of the
window or the object selected.
Procedures
1.
Point to and click an item on the menu bar.
2.
Click on a command to execute it.
toolbars
Next are the Toolbars, which contain shortcuts to the
commands. These are displayed as buttons with icons or pictures of the action
or item of choice. Each button executes a specific menu command.
There
are many toolbars providing a grouping of related commands and word gives you
the option of choosing which you want displayed.
When Excel starts the Standard
and Formatting toolbars appear by
default. The Standard toolbar
contains buttons used for many general Excel functions, whereas the Formatting toolbar contains buttons and
pull-down lists used to enhance the appearance of text and paragraphs.
You can choose to display
one, several, or all the toolbars at any given time, or you can hide all the toolbars. You use the Toolbar submenu on the View menu to display and hide toolbars.
Procedures
1.
Point to and click on the View command on the menu bar
2.
Select and click on the Toolbars command
3.
Select a toolbar to display by checking the box to the left of its name.
Procedures
1.
Point to a blank
part of the Formatting toolbar (anywhere away from a button, for example below
any button) and double click.
2.
Try moving the
floating toolbar across the screen by clicking and dragging the Formatting
toolbar's Title Bar.
3.
Anchor the
Formatting toolbar to the bottom of the screen by dragging the floating toolbar
all the way to the bottom edge of the screen, then release the mouse button.
Procedures
1.
Point to a button on the toolbar and
note the ScreenTips
2.
Click on a button to execute the
associated toolbar command.
status bar
At
the bottom of the Excel window you will find the status bar which gives you
information about your location in the workbook.
scroll bars
There
are two scroll bars - the vertical scroll bar at the right and the horizontal
scroll bar at the bottom of the workbook window. These allow you allow you to
quickly move around in a worksheet window. They allow you to move up and down
and right and left in the worksheet continuously or scroll through the
worksheet.
Exiting Excel
Discussion
When you have finished
using Excel, you should exit the
application properly, since Excel performs necessary housekeeping before it closes.
If the current document has been modified but not saved,
Excel prompts you to save the changes before exiting.
Procedures
1.
Point to and
click on the File menu.
2.
Select and click
on the Exit command.
USING EXCEL
Workbooks
An Excel document is called a workbook. Workbooks are
files that Excel uses to store your data.
Here
are some things to keep in mind:
1.
Think of a
workbook as a binder.
2.
Think of every
worksheet as a page in the binder.
Creating A New Workbook
When
you start Excel, a workbook named Book 1 is automatically displayed. Book 1 is
a new, unsaved workbook that is ready for input.
There are several ways to create a new workbook.
Procedure
1.
Click on the New button on the Standard toolbar
2.
Hold down the
Ctrl key and press N
3. You can
also create a new workbook with the New
command from the File menu.
Procedure
1.
Select the File menu
2.
Select the New command
3.
Select General tab
4.
Select Workbook
5.
Click OK
If
the first thing you do is open an existing workbook, Book 1 is closed
automatically - there's no need to close it on your own.
Worksheets
Discussion
Worksheets
are the areas or space in an Excel file or workbook where you enter your data
and perform your calculations. A workbook or Excel file can contain many
worksheets though the default number is set at three.
cells
The
area or space at which a row and column intersects is known as a cell. The cell is the basic element of
a worksheet and it is where you enter your data and/or perform various
operations on the data.
cell address
You
keep track of and reference a cell by its cell
address, which consists of the column letter followed by the row number.
E.g. the cell address D10 indicates the cell formed at the intersection of
column D and row 10 in the worksheet.
active cell
At
any time one cell in the worksheet is the Active
Cell. A dark border around it identifies the active cell. The active cell
is where anything you type will be entered. Its name or cell reference appears
in the name box.
You
can select any cell in the worksheet to be the active cell by clicking on it.
In an Excel worksheet the mouse pointer
appears as a cross symbol.
Procedure
1.
Select a cell within the visible area of the
worksheet to be the active cell by moving
the mouse pointer to that cell,
then
2.
Click on the cell you have placed the mouse pointer
in.
Moving within a
Worksheet
Procedure
1.
To move the visible part of the sheet up or down Click and drag on the vertical
scroll bar
2.
To move the visible part of the sheet to the right or left Click and
drag the horizontal scroll bar
A
small box appears which tells you the column or row you scroll to. However
scrolling does not change the active cell, it simply scrolls the visible area
of the worksheet. To change the active cell you must click on a new cell.
Entering and
Editing Data
types of
worksheet data
Excel
allows you to store and work with data in a variety of formats. The cells in
the worksheet are where you enter the data. Worksheet cells can contain three
distinct types of data - Text, Values and Formulas.
Text consists of any combination of alphanumeric characters i.e. letters,
numbers, and other symbols that you can type in at the keyboard. A cell can
contain upto 32,000 characters. Text entries are also referred to as labels.
All
formulas begin with an = sign
A
cell displays the value resulting from the formula entered.
The actual formula is displayed in the formula bar
when the cell containing the formula is selected.
Entering Data
Discussion
Entering
data is simple. Just select the cell you wish to enter data to and start
typing. When finished press Enter.
Procedure
1.
Select the
desired cell by clicking on it, i.e. making it the active cell
2.
Type
numbers, text or a combination of both to make the cell entry.
3.
To accept your entry press the Enter, the Tab key or click on the check
button shown on the Formula Bar.
Editing Data
modifying cell
contents
If
you make an error in an entry you can easily correct it. You can edit cell
contents or entries within the cell or on the Formula Bar.
Procedure
1.
Double click on
the cell whose contents you wish to modify or select it and press F2. The
insertion point is placed at the end of the cell contents.
2.
Select the cell
and then click on the formula bar. The insertion point is placed in the formula
bar.
3.
Edit the cell
contents as required using basic text editing techniques. Use the Backspace key to delete characters to
the left of the insertion point or the Delete
key to delete characters to the right of the insertion point.
4.
To accept your
entry press the Enter key, the Tab key or click on the check button
shown on the Formula Bar.
5.
To abandon your
entry press Esc or click on the
cross button shown on the formula Bar.
erasing/deleting
cell contents
To
erase the entry made in a cell, activate it and press Delete.
You
can also delete cell contents in the following way.
Procedure
1.
Select the
required cell.
2.
Select the Edit, Clear, content menu option.
3.
Select Delete.
replacing cell
contents
To
replace the contents of a cell with something else just activate the cell and
make your new entry. It replaces the previous entry. Formatting however, is not
removed.
undo
and repeat
If
you make an error during or after an entry and wish to change your entry you
can use the Undo feature to reverse it before proceeding.
Procedure
1.
To reverse your
last action Select the Edit menu
2.
Select the Undo
command.
On
the other hand if you would like to repeat the last action you performed, you
can use the Edit, Repeat command.
Procedure
1.
Select the Edit menu
2.
Select the Repeat option.
3.
Select F4 or
Click on the Redo button on the Standard
toolbar
using autofill
Saving a
Workbook
To
keep your workbook on disk for future use, you must save it by giving it a
unique file name.
save and save as
Procedure
§ Select the File,
Save menu option.
§ Press Ctrl+S, or click on the Save button on the Standard toolbar.
§ Excel will display the Save As dialogue box.
§ Enter the workbook file name into the File name text box.
§ You can set other options like the drive, the folder to save to the right location.
§ Once all save options have been selected, click OK.
If
the directory you require is not currently selected, move to the correct
directory (folder) by double clicking on the directory name shown in the Directories list box.
If the directory you are looking for is not in
the list, double click on the root directory c:\ to move to the top of the
directory tree. Then scroll through and double click on the required directory
name(s).
·
Enter a password into
the Protection Password text box if
you would like to prevent anyone from opening the worksheet unless they know
this password.
·
Enter a password
into the Write Reservation Password
text box if you would like to prevent anyone from saving changes to the
worksheet unless they know this password.
WORKING WITH WORKBOOKS
Opening Existing Workbooks
To
perform any operation on an existing workbook i.e. one that has been previously
saved to disk you must first open it.
·
You can open an
existing workbook by clicking on the Open
button on the Standard toolbar
Procedure
1.
Select the File menu.
2.
Select the Open command. This opens the Open dialog box
3.
Select the file
you want to open.
4.
Click OK.
The
open dialog box gives you many options most of which deal with the location of
files.
You
can also open your most recently used workbooks by selecting the file names
displayed at the bottom of the File
menu.
Using Workbooks
multiple workbooks/ windows
Excel
allows you to have multiple workbooks open
at the same time. This is useful if you want to work with or view data in
several workbooks simultaneously.
Excel
also allows you to open more than one
copy of a workbook at the same time. This is useful when you want to
simultaneously view different sets of data residing in one workbook
If
you have multiple workbooks or more than one copy of a workbook open each
workbook or copy of a workbook resides in an Excel window.
As
with other Windows applications, Excel windows can appear on the screen in the
following ways:
Maximized - only one workbook is displayed at a time.
Minimized - a small window displaying on the title bar appears
restored to a non maximized size
Navigating Workbooks and Windows
Discussion
When
you have more than one workbook open at any time, to work on any or all of them
you must be able to navigate between them. Excel allows you several ways of
navigating and controlling the open workbook windows. This includes moving,
resizing, and switching between windows.
To
practice navigating workbooks, first create some empty workbooks after starting
Excel.
Procedure
1.
Click once on the
New button to create Book 1
2.
Click twice more
on the New button to create Book 2 and Book 3.
the active workbook
When
you wish to work with any open workbook you must select it first and make it
the Active Workbook. You can do this
by selecting the desired workbook from the Window menu, which lists the names
of all open workbooks at the bottom.
Procedure
1.
Select the Window menu
2.
Click on the name of Book 1, Book 2 or Book 3 to
make it the active workbook.
If
more than one workbook is visible on the screen clicking anywhere in the
desired workbook will make it the active workbook.
This
procedure is used to move between windows
of a workbook.
Arranging Windows
Discussion
You
can arrange open workbooks on the screen in multiple windows so that you can
view them. The Arrange option on the Windows menu allows you several different
ways of doing this.
Procedure
1.
Select the Windows menu from the menu bar
2.
Select the Arrange option. The Arrange Windows dialog box with four different options of how windows can
be displayed appears.
3.
Select any option to see how the windows are
arranged under it.
Moving &
Resizing windows
Discussion
To
move a workbook window,
Procedure
1.
Click on the
Title bar of the workbook you want to move and drag it.
2. Once you have the workbook window in the desired
position release the mouse button
To
resize a workbook window use the mouse as follows. The mouse pointer changes
shape to a double head arrow when pointed at the window borders indicating that
you can click and drag to resize.
Procedure
1.
To resize a
window click and drag on any of its borders.
2.
To resize a
window both horizontally and vertically at the same time, click and drag on any
corner.
Closing Windows
When
you have finished working with a workbook you can close it to free memory it
uses.
·
You can close a
workbook from the Menu bar.
Procedure
1.
Select the File menu
2.
Select the Close command
Enter numbers, text, a date, or a time
¨ Click the cell where you want to enter data.
¨ Type the data and press ENTER or TAB.
¨ Use a slash or a hyphen to separate the parts of a
date; for example, type 9/5/96 or Jun-96.
¨ To enter a time based on the 12-hour clock, type a
space and then a or p after the time; for example, 9:00 p
Otherwise,
Microsoft Excel enters the time as AM.
Note:
To fill in rows of data in a list,
enter data in a cell in the first column, and then press TAB to move to the
next cell. At the end of the row, press ENTER to move to the beginning of the
next row. If the cell at the beginning of the next row doesn't become active,
click Options on the Tools menu, and then click the Edit tab. Under Settings,
select the Move selection after Enter check box, and then click down in the
Direction box.
¨ To enter today's date, press CTRL+; (semicolon).
¨ To enter the current time, press CTRL+SHIFT+: (colon).
Enter a formula
For
information about how formulas calculate values, the following are the rules.
1. Click the cell in which you want to
enter the formula.
2. Type = (an equal sign).
If
you click Edit Formula or Paste Function, Microsoft Excel inserts an equal sign
for you.
3. Enter the formula.
4. Press ENTER.
Note:
You can enter
the same formula into a range of cells by selecting the range first, typing the
formula, and then pressing CTRL+ENTER.
You
can also enter a formula into a range of cells by copying a formula from
another cell. For more information about copying a formula, the following are
the rules.
Enter the same data into several cells at once
·
Select the cells
where you want to enter data.
·
The cells can be
adjacent or nonadjacent.
·
Type the data and
press CTRL+ENTER.
· Enter or edit the same data on multiple worksheets
·
When you select a
group of sheets, changes you make to a selection on the active sheet are
reflected in the corresponding cells on all other selected sheets. Data on the
other sheets may be replaced.
·
Select the
worksheets where you want to enter data.
·
How?
·
Select the cell
or cell ranges where you want to enter data.
·
Type or edit the
data in the first selected cell.
·
Press ENTER or
TAB.
Microsoft Excel automatically enters the data on all selected sheets.
Note: If you've already entered data on one
worksheet, you can quickly copy the data to the corresponding cells on other
sheets. Select the sheet that contains the data and the sheets to which you
want to copy the data. Then select the cells that contain the data you want to
copy. On the Edit menu, point to Fill, and then click Across Worksheets.
Select sheets in a workbook
If
you select more than one sheet, Microsoft Excel repeats the changes you make to
the active sheet on all other selected sheets. These changes may replace data
on other sheets.
To select Do this
A single sheet Click the sheet tab.
Two or more adjacent
Sheets Click the tab for the first sheet,
and then hold down SHIFT and click the tab for the last sheet.
Two
or more
Nonadjacent
sheets Click the tab for the first sheet, and then hold down CTRL and click the
tabs for the other sheets.
·
All sheets in a
workbook
·
Right-click a
sheet tab, and then click Select All Sheets on the shortcut menu.
Quickly fill in repeated entries in a column
If
the first few character you type in a cell match an existing entry in that
column, Microsoft Excel fills in the remaining characters for you. Microsoft
Excel completes only those entries that contain text or a combination of text
and numbers; entries that contain only numbers, dates, or times are not
completed.
To accept the proposed entry, Press ENTER.
The completed
entry exactly matches the pattern of uppercase and lowercase letters of the
existing entries.
To replace the automatically entered characters, continue typing.
To delete the automatically entered characters, press BACKSPACE.
You
can also select from a list of entries already in the column. To display the
list, press ALT+DOWN ARROW to display the list, or right-click the cell, and
then click Pick from List on the shortcut menu.
Excel
is a spreadsheet program - a program designed to work with numbers (as opposed
to a word processor, such as word, which is designed to work with words of text
data) we need to work with data and enter it in cells arrayed into horizontal
rows and vertical columns on a worksheet, an arrangement some what reminiscent
of an accountant's ledger but far more flexible.
Copying and
moving the data
For
copying the data the following is the command:
Click
Edit
Then
select copy command
For
pasting the document
Click
Edit
Then
paste command.
For
cutting the document the same is the rule but the difference is that you will
have to select the cut command from the Edit menu.
N.B. Before pasting the document, you will have to select the area i.e.
insertion point to be placed there.
Keystrokes:
For copy : Ctrl+C
For paste : Ctrl+V
For cut : Ctrl+X
Find and
Replace
You
can find any document by selecting Find
command from the Edit menu and you
can replace it from the same menu. This command is helpful for updating the
document you prepared. A list of texts may be replaced by using this command.
Shortcut keys:
Ctrl+F
for finding the document and for
replacing the document you can press by using the keystroke Ctrl+H
Fill command
:
You
can fill a list of items by selecting the cell from the edit menu. You can fill
the text to the right, left, down, AutoFill and according to series in
different forms like linear and growth types. This command is helpful for
updating the document.
Keystrokes:
For
fill the document you can select the keystrokes: Ctrl +D
Clear the
documents
If
you feel difficult to delete the text, if the text area is formatted by Pivot
table or any graphic objects like object
linking and embedding ( OLE) or any complicate arises; you can delete the
formatted text by using Clear command. In clear command, you will find
contents, format and all.
Contents: specifies the text as selected
Format: Removes all formatted text, graphics/charts and
number.
All: removes all the contents in the text area
No comments:
Post a Comment