freePCtech (click here to return to the first page) Search the
    siteHelp on using this siteHome
Forums
Features
Articles
Guides/FAQs
Goodies
Downloads
About
Contact

    Articles / Reviews


An Introduction to Excel

by Tony Beckett
2003/03/10

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 CD 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
Excel Sheet
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 data.

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.
Sheet List

Worksheet "Adding Up"

Click in cell B14 and you will see in the "Formula Bar"
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 automatically.

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 line.

Before you do any more calculating give these short cuts a try:
CTRL HOME = go to first entry of the file or the top of the screen.
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 working area

If things go wrong, you can always "Undo", what you just did, using the undo icon Undo. 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 workbook.

Worksheet "Checking Account (Practice)"

The bank statement A1:E9 looks a real mess because only the information has been added, without any "formatting" being applied.

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 example).

With the 3 columns highlighted, use the mouse to click on the menu item Format/Cells, (the hotkey for this is CTRL 1)

Format Dialogue Box

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 .

  1. Widen column B
  2. Format columns C, D and E to 2 decimal places.
  3. Autofill cell E5 so that the calculations continue down the sheet.

"Extra" formatting

The formatting you have done so far was needed to make the sheet easily understandable.

The fill and font color buttons Fill and Font 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 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 other choices.
Borders drop down menu

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, Font Size will affect the whole sheet.

If you ever get a cell looking like Contents to large 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"
Sheet Tags
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

Delete SheetRight 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, http://www.freepctech.com/pcsoft.shtml .

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 C:\accounts.xls. 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 simply 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.

 

Articles / Reviews

Hardware
  Articles
  Reviews
Software
  Articles
  Reviews
Diagnostics
  Articles
  Reviews
Networking
  Articles
  Reviews
 
Search
 

 

Free PC Tech

Copyright The NOSPIN Group, Inc. 1991-2006.  All rights reserved.