|
|
|
|
 |
Starting with Excel Iain Laskey explains how to get started with Microsoft Excel Microsoft Excel can be purchased on its own but is more commonly bought as part of Microsoft Office. Excel is a type of spreadsheet program. Spreadsheets are a very flexible way of storing and calculating numbers and data. Excel files are called workbooks and each workbook can contain one or more worksheets. An example might be having a workbook to store your banking information with each month’s data being held in a separate worksheet.
Rows & Columns The key to understanding spreadsheets is to know how they are laid out. Excel has a number of rows and columns. Different versions have slight variations on the maximum sizes but as an example, Excel 2000 has 65536 rows and 256 columns. The rows are easy to work with as they are numbered 1, 2, 3 etc. but the columns are slightly different. The first column is called A then B and so on. However, there are more columns than letters of the alphabet so after Z you get AA then AB until AZ then BA, BB etc. The final column is IV. This might seem odd but the first spreadsheets only had A-Z and when people needed more than 26 columns, they had to find a way to do this without confusing people who were used to using letters of the alphabet for columns.
Entering Information You can type anything in to a cell. Excel allows you to format different cells in different ways so that numbers can be displayed one way, text another and money yet another. Excel is very good at guessing what you want when you type things in so that of you entered 34.2 it would format the cell as numeric. If you entered 21/3/01 it would assume you meant this to be a date and would format it accordingly. Sometimes this might not be what you want though. What if you wanted to store an account number as 00012789? Excel would see numbers and format it accordingly resulting in the leading zeros being lost leaving you with 12789. What to do? If you put a ‘ in front of the number, Excel treats it as text instead so entering ‘00012789 would leave the zeros intact. If you don’t like what excel has done with the automatic formatting of a cell, you can override it by clicking on the cell and selecting Format/Cells from the menu. Here you can choose from a wide variety of formats. There are a huge range of options available with customised formatting but for now it is best to stick with the basic types in the list. Formulae OK, now you’re worried! This is not as hard as it sounds though and rapidly becomes automatic after the first few. Formulae are what make spreadsheets fantastically powerful. A simple example might be if you want to have one cell showing the price of an item with a second cell showing the price plus VAT (17.5%). Assuming the cell with the basic price is A1, you could put the following in B1 (the next cell to the right): =A1*1.175 Let’s break this down. All formulae start with = to say it is a calculation of some sort as opposed to a bit of data. Without it, Excel would think you wanted a piece of text saying ‘A1*1.175’. Next up is the A1 that means ‘take the contents of cell A1’. Then there is the * which means multiply then the 1.175 is what you want it multiplied by. So we end up with something that means ‘take the contents of cell A1 and multiply it by 1.175’. This gives you the original amount plus 17.5% which is what we wanted. The best bit is that formulae recalculate automatically so if we types 100 in cell A1, A2 would instantly show 117.5. If we then typed 200, A2 would show 235. When working with formulae you should note the following:
Another example is if you wanted cell C1 to show the total of A1 plus B1. In this case you would use the following formula: =+A1+B1 (=A1+B1 also works)
Excel will take a guess at the size of the column and outline it. Assuming this is correct, just hit return. Job done!
|
If
you start up Excel, you’ll normally be shown a new blank workbook
with three sheets. By default, these are called sheet1, sheet2 and
sheet3. You can see this by looking at the bottom left hand side of
your workbook where you’ll see three tabs. You can select different
worksheets by clicking on the tabs. To change the name on a tab to
something more useful, right-click on a tab name and select rename
from the pop-up menu. Type in a new name such as ‘January’ and press
enter.
Each
sheet is thus divided up in to a number of cells in a grid. The
location of each cell is described in a row and column format. As an
example, the fourth cell down in the third column is C4. The next
cell down is C5.
A
final example is if you have a column of numbers and you want to put
the total at the bottom. There are two ways to do this. Rather than
having to type a formula quoting every cell in the column e.g.
=+A1+A2+A3+A4 etc, you can put in a range by entering =SUM(A1:A10).
The word ‘SUM’ is a spreadsheet function that adds up all the cells
in a range. This range is described by A1:A10 where the use of :
means ‘all the cells between A1 and A10’. For those keen on
shortcuts, there is a toolbar button that does this automatically.
Click in the cell at the bottom of a column then click on the symbol
that looks like a 