An Introduction to Excel
A spreadsheet is such a universal tool I can't imagine anyone not having
a need for one. The UK computer magazine, PCW, once made a joke about
spreadsheet enthusiasts being grey and boring, and I guess I will have to
accept the grey and boring bit, as I am definitely an enthusiast!
Every version of Microsoft Office has some version of Excel installed. Most
people, if they load the program at all, typically use it to keep lists, or
organize data. Everything from a list, to large listing of a
collection, much like a light weight database. But while Excel can be a
great place to make your grocery list, it has far more powerful features,
and it is not
that difficult to acquire a working knowledge of the basics.. Load any
spreadsheet type program, and the first thing you see is a grid.
Don't be put of by that big grid
or the description of
a spreadsheet as "accountants ruled paper that does the adding up for you". You
don't need to be an accountant, nor use all the grid, to make your spreadsheet as simple as you
like, or as complicated as you like. You can make
Excel work for you, by presenting your data, your way. Typically, a programmer predetermines how they want you to organize your data.
But with a spreadsheet program, you
get to set things up the way you want to view them.
Think of each cell, whether it is A1 (in red), G5 (in blue) or D15 (in
yellow) or any of the others, as a note pad, where you type information
specific to that cell. You don't work in paragraphs,, going down the sheet,
as you would in a word processor. But in cells which are in certain fixed
places, as if you were working on a piece of graph paper.
I am going to be using this Excel Worksheet,(please click here>>)
Introduction to Excel , to walk you though an introductory
tutorial. If you do not have Excel installed on your computer, you can
follow along looking at this web page. Of course you won't be able to
perform any of the examples.
However, if you
have Excel installed on your computer then
click here to download the actual worksheet
in a self extracting exe file. Once you have downloaded
the file, double click on it, to extract to the path of your choice. It will
extract by default to the root directory of c:\ , but if you want to put it somewhere else, then
just point it to the correct path. Once the file is extracted, then just
double click on it, and Excel should open and load it. Now you can follow
along with this tutorial, while actually working with the worksheet.
The workbook will open on your machine at :- Worksheet "Sheet 1"
On the worksheet there are a number of very basic lists, make some
changes by clicking in a cell and just typing the new information over the top
of the old. A tap on the delete key will clear a cell completely. Try to
create a simple list of your own so you can get a feel of how to input
Work of this sort, can be done in a Word processor, but the underlying
grid makes it easier to do in a spreadsheet, and does a far better job of
organizing the data. Even with the simplest of lists, Excel can help makes things easier. If there is a
pattern to the list you can use "AutoFill" to complete the pattern. Try the examples that start at cell A25, there are directions alongside
to help. Once you have tried the examples type in your own words and figures.
Try "Week 3" and then "Month 8" as well as "Dept 1".
Once you have finished with simple lists, and "AutoFill", it is time to
move to the next worksheet in the workbook - "Adding Up"
The entire saved file is the workbook, and the
pages in the workbook, which can be accessed by the tabs at the bottom of
the page are called worksheets. Each workbook, can have as many worksheets as you want, this example has five. With
the sheet "Adding Up" the one in use - it is shown as white, whilst the other
sheets have a grey background to their tab.
Worksheet "Adding Up"
Click in cell B14 and you will see in the "Formula Bar"
the information =SUM(B7:B13), don't be worried by what looks like algebra, it
is an Excel function and Excel can do the work for you. It just adds up every
cell between B7 and B13. Change any of those numbers and see the answer change
Select cell D14 and use the Autosum button. Excel makes a guess at what
you want and gets it right. =SUM(D7:D13) adds all the cells between D7 and D13
- tap the Enter key for the result to appear in the cell. Then try the other
examples on the worksheet. This adding up of columns, or rows, can cover a lot of
work in Excel. However =SUM() isn't always the way to do simple addition.
Click in cell B24, what appears in the Formula Bar this time is
=B14+D14+F14+H11+J11. The calculation MUST start with an = to tell Excel you
want a calculation done. Then each of the cells that you wish added are listed.
=SUM() wouldn't work here, as the cells you want added are not in a straight
Before you do any more calculating give these short cuts a try:
= go to first entry of the file or the top of the
CTRL END = go to the last entry
in the file or the bottom of the screen. This can be very useful when you look at another persons
worksheet for the first time, and you want to see
how big the range is. By executing a CTRL END, you will quickly know the size of the
If things go wrong, you can always "Undo",
what you just did, using the undo icon
. This is the same
icon that you will find in Word. CTRL Z is the
hotkey for undo.
A useful small job for Excel, is a personal banking sheet. Click on the
worksheet tag "Checking Account (Practice)" at the bottom left of the Excel
screen, and you move to a different worksheet, really just another page in your
Worksheet "Checking Account (Practice)"
The bank statement A1:E9 looks a real mess because only the information
has been added, without any "formatting"
First move your mouse to the point between the B and the C at the tops
of the columns. Hold down the left button and drag the column
to make it larger. Instead
of dragging with the mouse, you can "double click" the button, and the column
widens automatically. You can now see the full item descriptions.
Even though I typed in 500.00 for the "from Savings Account credit",
Excel ignored me. It never does more that it has to, in order for it to be mathematically
correct. You do want to see the extra zeros though.
Click actually on the C, at the top of column C, then hold the left button
down, whilst dragging across to select D and E. That will mean anything you do
whilst those 3 columns are selected, will cause
the same effect to all three columns.
Excel is good at patterns, and the pattern here is that figures in
columns C,D, and E, all need two decimal places shown. (500.00 not 500 for
With the 3 columns highlighted, use the mouse to click on the menu item
Format/Cells, (the hotkey for this is CTRL 1)
Then choose "Number", and on the right make sure that the -1234.10 at
the bottom of the group is selected. The minus sign, with the red font, has
the effect of making a negative number appear as red. You can instantly
tell if your in the back, or in the red.
And if your account goes
overdrawn, you will see not only the - sign, but the figure will appear in red as well. Decimal places
are listed as 2 spaces for currency.
Now that we have our worksheet formatted for the
way we want to use it, lets go ahead and do some calculations.
Doing the Calculation
The =SUM() that you looked at previously, is not appropriate here, as you
don't want to add up columns, or rows of figures.
Click in E5 and note the calculation =E4+D5-C5 in the Formula Bar.
To practice, click in E6 and type =E5+D6-C6. You must type the = so that
Excel knows it is doing a calculation. E5 is for the balance at the end of the
previous transaction. +D6 for a credit on the current line if there is one, and
-D6 for a debit if there is one.
The figures are simple enough for you to check.
The calculation "pattern" you have typed will work for each row where
there is a transaction. It would be very time consuming to type the calculation
for each row. So make sure E6 is selected. Get the mouse into position for the
AutoFill cross (you practiced this on the previous worksheet). Then AutoFill
down to row 12. You have three spare lines at the end. Invent 3 more items and
type them in. Watch the balance change as each new amount is added. Add some
more items, and this time you will have to AutoFill the last balance cell, until
it is level with the new items for the balance to update.
When AutoFilling, or copying a calculation, Excel is working with the
"pattern" of that calculation, so when you AutoFill, or copy to another cell, it
is the "pattern" that is copied.
You now have a very basic but useful Excel application, however, its
appearance can be improved.
Worksheet "Checking Account"
Click on the sheet "Checking Account". Have another go at what you did
on the "Checking Account (Practice)" sheet .
- Widen column B
- Format columns C, D and E to 2 decimal places.
- Autofill cell E5 so that the calculations continue down the
The formatting you have done so far was needed to make the sheet easily
The fill and font color buttons
can be used to color cells (fill - paint pot) or the contents of
the cell (font - letter A). You MUST highlight the cell you want colored first
and then click on the "Fill Color" or "Font Color" button.
Formatting one cell at a time is not efficient, it is best to highlight
a "range" and then apply the formatting to the whole range. The smallest
possible range is a single cell, the largest range is the whole worksheet.
You need some thing in between. Select A4, hold down the left mouse button
and drag across to D4, this will give you a A4:D4. Click on the "Font
Color" and the text will be turned red. Leave the range selected and click
on the tiny downward pointing triangle just to the right of the "Font
Color" button. Choose a different color for your text
this time and try it again.
If you would like a whole row to appear with the same background color,
then click on
the 5 of row 5, and the whole row is selected. Now click on the "Fill Color"
(paint pot), and the background color of the whole row is now yellow.
Another useful formatting tool for appearances is the "Borders" button
Just to the left of "Fill Color" and "Font Color". The borders button works on
those cells SELECTED when the button is pressed. Experiment to see what
happens, also try the little triangle to the right of the button to be offered
Borders put a line around the cell or cells. You
can change the way those lines look using this tool.
The top left choice
(empty) is very useful, as
that one REMOVES any borders that have been put on the selected cells.
Worksheet "Checking Account Colored"
So far each worksheet has used the standard layout and formatting
offered by Excel, and you have changed individual cells, rows, or columns. To
make your change affect the whole worksheet, do a CTRL A (A for All) and the
whole sheet is selected. Then any change you make, such as changing the font
size from 10 to 12,
will affect the whole sheet.
If you ever get a cell looking like
don't worry, it just means that the column is not wide enough for
this piece of information. Use the mouse to widen the column, as you did on the
worksheet "Checking Account (Practice)".
Once you have completed the sheet "Check Account Colored", and saved the
workbook, then go to File >> Save As, and change the name of the workbook from
"Introduction1.xls" to "Checking Account.xls".
Then on the click on the tab "Sheet 1"
That makes that sheet "live". If you then RIGHT click with the
mouse on the "Sheet 1" tag you will be offered this set of options
Right click on the delete option, and Excel will
confirm with you that you want to delete this sheet. Do the same with all the
sheets except "Checking Account Colored". With this last sheet choose "Rename"
, not "Delete" and give it a suitable name. Then save the workbook.
You now have a workbook with the beginnings of a checking account that
you can use. You have also covered a fair amount of basic work in Excel, which
you can use for other projects.
Now that you know the basics, I have provided a
download of a simple Personal Accounts Workbook. You can change this
to suit yourself. It has a number of worksheets including an Overall, Bills, Checking account, Savings
account, Credit Card, and
Insurance information. When you input data into one of the sheets, the
overall worksheet will automatically reflect the change. The workbook is formatted
with lots of colors, but now that you
know how to change things to suit yourself, you can set things up your way. And if you need help making
changes, adding formulas, or tweaking it, you can always post your
questions to PCSOFT,
To download Accounts.xls please click here >> Accounts
You will then download a self extracting zip file called Accounts.exe . If you
double click on the file, it will auto extract to this path
Or, you can point it to extract to the folder of your choice. Once it is
extracted, you can rename the accounts.xls, to whatever you wish, and then
double click it, to have Excel open and load the worksheet. Of course, you must have
Excel installed on your computer, to be able to work with this file.