 |
| <<Convert Excel Spreadsheets to Web Pages | Trading Software That Operates Within Excel | Convert Excel, Access & Other Databases | Merge Excel Files>> |
|---|
|
Spreadsheets have been in use on personal computers now for some years. They are used in nearly every office environment in the world for any number of reasons. In the past they were used only to perform basic math, such as adding, multiplying, subtracting and dividing. Today's spreadsheets can be significantly different and far more advanced. The leader in the spreadsheet field today is without doubt Microsoft Excel. The reasons for this will become apparent the more you learn about this very powerful program.
You will find that by following a few basic fundamental rules when starting a spreadsheet that you will be able to extract, change, modify, report, add and manipulate data in a way you never thought possible. Sadly most users of Excel never realise the full potential of what they are using. In fact all too many users simply use Excel as no more than a personal whiteboard, with the occasional calculation (formula) thrown in. This is a great shame as it is designed to do so much more than this.
We will show you how you can use Excel to achieve all your wants and need. As we progress you will also find yourself wanting to know more and it can become an unquenchable thirst if you allow it to be (believe me we know!). By the completion of these lessons you will be able achieve a lot more than you can now, but even more importantly you will have the knowledge to be able help yourself.
You will notice throughout the course that we do NOT use screen shots to aid you. This is done intentionally, as we believe that by doing so forces the student to think a lot more about what they are doing.
Excel is spreadsheet program that hinges entirely on three very important aspects.
The Workbook (the spreadsheet in its entirety)
- Worksheets (pages or sheets within the Workbook)
Cells (contained on all Worksheets)
Remove anyone of these aspects and Excel can no longer function. Lets look at each of these is turn.
This is like the outer shell of the spreadsheet and contained within it will be Worksheet(s) and cells. There are many formats that Excel can save itself as, but by far the most common is the default *.xls. Some of the other popular formats are the *.xlt (Template), the *.xml (Extensible Markup Language) and the *.txt (Tab delimited). When you save a Workbook as a *.xls. you may notice that there are different choices of the .xls types. These different types are for backward compatibility with previous versions of Excel. You can also save the Workbook as multiple versions eg; 2000 and 97. I strongly recommend doing this ONLY if you need to as it will increase the file size by 10-50%.
Worksheets within a Workbook are like pages in a book. The default for the amount of Worksheets within a new Workbook is three. We can change this by going to Tool>Options/General and changing the Sheets in new Workbook: to a number of our choice. The number of Worksheets within a Workbook is limited only by the amount of available memory on your computer. Adding Worksheets to a Workbook can be done in one of four ways.
Go to Insert>Worksheet/OK
- Right click on another Sheet name tab and select Insert, then Worksheet
By pushing F10 the I then W
The fourth way is not exactly inserting a Worksheet, but rather making a copy of an existing Worksheet. To do this select the Sheet you want to copy then left click on the name tab and holding down the Ctrl key drag it to one side, then release. This is a great time saver when you have spent hours setting up a Worksheet with all the formatting you want.
These are without doubt the very backbone of Excel. On each Worksheet there are 16777216 cells. These are divided into 65536 Rows and 256 Columns. The number of Rows and Columns in a Worksheet are fixed, which means we cannot add more and we cannot take any away. So as a consequence the number of cells are also fixed. This should never become a problem as we can add a lot of Workheets each with 16777216 cells - this should give us plenty!
The method that Excel uses to reference these cells is called the A1 style reference. It also supports R1C1 style reference ("R"=Row, "C"= Column) for backward compatibility. These two styles of references are very simple and effective and are the same method you would use to locate a Street on a road map ie; grid pattern. The default for Excel is now the A1 style reference, but we can change this by going to Tools>Options\General and checking the R1C1 reference style box. Personally we much prefer the A1 style (I guess most do that's why they changed it). When we refer to particular cell we use it's Address to nominate the cell.
There are two methods we can use to reference a single cell or a range of cells, these are Absolute Reference ($A$1) and Relative Reference (A1). The dollar symbol is used to Absolute the Column ($A) and to Absolute the Row ($1). What this means is if we put: =$A$1 in cell B1 and then copied this to cell C2, the cell C2 would contain the exact same ie; =$A$1. If we did the same with the Relative reference cell C2 would contain =B2.
To rotate through changing a cell reference from Absolute to Relative, type =A1 in any cell and hit Enter. Then click back on your cell and place your mouse insertion point in the Formula bar inside the cell address (or press F2) and push F4. This will rotate the cell through Relative to Absolute.
When we refer to a range of cells we are talking about more than one cell. There are two types of ranges in Excel, these are a Contiguous ranges and a Non-contiguous ranges. A contiguous range is an area of cells whose cells borders connect. A contiguous range of cells is also known as an Area. While a Non-contiguous range is more than one area whose cell borders do not connect.
While it is true that we can refer to cells using the cell address there is another method we can use (and should often) this is naming a range. We can do this in a lot of different ways. The easiest is to select the cell or range want to name then type a one word name in the Name box (this is the box to the left of the Formula bar). Naming ranges is a good idea as it can make our formulas much easier to read.
Everything we have discussed so far is the least you should know to be able to use Excel effectively. While it is true many users can get by knowing a lot less, that is all they are doing "getting by"!
Now that we know about the foundations of Excel we can now move on to learning how we should set up a spreadsheet. This becomes very important as you learn more about Excel, because you will find yourself delving deeper and deeper into it's capabilities. If you have not set up your data correctly initially you will either not be able to do as much as you want or give up trying.