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.
We now know that Excel has far more Rows than Columns (65280 more to be exact) and this something we should keep in mind when we set up any spreadsheet. You will also find as you delve deeper into Excels functions that they are designed for spreadsheets that have been set up with columns as headings and rows holding the data. There are, however, a few rules that you should try to adhere to.
When putting in headings bold the font. This will help Excel recognise them as headings when you use one of its functions such as Data>Sort.
When putting data into the data area of your spreadsheet try to avoid blank rows and columns if possible. This is because most of Excels built in features will assume a blank row or column is the end of your data. It also helps with a lot of Excels formulas.
Have your data sorted if possible. Excel is very rich in what is known in Lookup & Reference formulas and many of these rely on your data being sorted in a logical order.
Use real dates for headings and format them appropriately. By this we mean if you want the names of the months as headings type them in as 1/1/2001 , 1/2/2001 , 1/3/2001 etc then format them as "mmmm". This is a very simple procedure that is all too often overlooked by many. If you have real dates as headings life will be much easier further down the road when you need to use them in formulas.
Avoid merging cells if possible. Merging cells, although convenient and visually appealing, can cause all sorts of problems later on that that will have you scratching your head. Use Centre across selection instead. This can be found by going to Format>Cells/Alignment and select it from the Horizontal: box.
Don't put in one cell what could go in more than one cell. Say you have the names of 100 people to put in your spreadsheet don’t put their full name in one cell. Instead put the first name in one cell and then their surname in the next cell to the right. If you need to place them into one single cell at a later stage, this can be done very easily - the same cannot always be said for the reverse.
Just by following these six rules you will find that you will be able to easily reference, manipulate and make use of Excels built in features. While you are building your spreadsheet keep in mind that you will need to change it as time goes on. This is because while formatting a well-constructed spreadsheet can make it easier to read, the formatting should be one of the last things we do. We have seen people spend hours formatting their spreadsheet until it looks perfect, only to find they need to make a small change that renders their hours of work useless. All the above rules can be broken and we can possibly still end up with the results we want, but the road to get there will most likely be very long and winding.
We mentioned previously about naming ranges to make your formulas easier to read. We can take this to yet another step in Excel and also give names to any constants. A constant in this context is a numeric value that does not change.
Assume you need to set up a spreadsheet that constantly refers to a specific tax rate eg; 36%. You could type the rate (36% or 0.36) in all cells that need to use this value or we could give this value a name eg; TaxRate. To do this we need to use the Define Name dialogue box. We access this by going to Insert>Name>Define, or by pushing Ctrl + F3. In the Names in workbook box type the word TaxRate. In the Refers to box type: 0.36 or 36%. Click Add then click Ok. Now in any cell enter =TaxRate and hit Enter. You should get the result: 3.6.
Not only does this make our formulas easier to read, but it will also allow us to change the value of TaxRate in one spot. This can save a lot of time and is much easier than trying to locate all cells that use the 36% had we simply just used the number.
Dates and times are a very important part of a lot of spreadsheets and as such it is very important you understand how Excel interprets them. Excel for Windows uses the 1900 date system. Excel for the Macintosh uses the 1904 date system. The 1900 system can be changed by going to Tools>Options\Calculation and checking the 1904 date system. If you are using Windows there is no need to change this.
The 1900 date system starts from 1 January 1900 and has a numeric value of 1. This is how Excel sees dates, as numbers. You can see this by typing the date 19-Dec-2004 in any cell. Now format the cell as a General number format, it will display the value 38340. This simply means that 19-Dec-2004 is exactly 38340 days from 1-Jan-1900. By using this method Excel can perform calculations on dates. The term that is used to describe these numbers is serial numbers.
We now know that Excel uses what is known as serial numbers to store dates. It also uses a very similar system to store times. Instead of using serial numbers, Excel uses what is known as decimal fractions. In Excel 24 hours is equal to the whole number one. 12 hours is equal to 0.5 and six hours equal to 0.25. So Excel stores times as a portion of one, with one being equal to 24 hours. Again you can see the decimal fraction of a time by typing any time in any cell and formatting it as General.
To now combine this we can see how Excel would interpret a date and time in a cell. Lets stick with the 19-Dec-2004 example and modify it to also include a time, let's say 12:00. We would enter this into a cell as: 19-Dec-2004 12:00. If we now format this cell as General we will see: 38340.5 with .5 representing the time portion.
Once you understand Excels method of storing dates and times it should no longer be a mystery on how Excel uses dates and times in calculations. While it is a very simple method it is also very effective.
A common problem that people run into when working with times is when they need to use hours greater than 24. Let's say in cells A1:A5 you have the hours 8:00 , 8:00, 10:00, 7:00, 7:30 respectively. These hours represent the hours worked in one week for an employee. We need to know the total hours worked for that week, so in cell A6 we put: =SUM(A1:A5). You will see that we do not get the result we expected, we end up with: 16:30. Why does Excel do this? Well when you use a formula that references other cells our result cell will automatically take on the format of the cell(s) it is referencing. Excel sees that we have times in cells A1:A5 and so assumes we want the same in our result or total cell and so formats it as h:mm. More often than not Excel gets it right, but as you can see in this instance it hasn't. But why the result of 16:30? Lets step through this to explain why.
If you add A1 and A2 (8:00+8:00) together you get 16:00.
If you now add this 16:00 to cell A3 (10:00) you don't get 26:00 you actually get 2:00.
What happens is, if you add 8:00 of the 10:00 hours to 16:00 you would get 0:00 (Midnight). If you then add the remaining two hours you of course end up with 2:00, the result we got initially.
So we now know that when Excel adds 8:00+8:00+10:00 we get 2:00
If we now add the remaining times 7:00 + 7:30 we get 14:30.
Finally we add 2:00 to 14:30 and we get the result that Excel got, ie; 16:30
Ok this is all fine, but we don't want that, we want to force Excel to keep going once it reaches 24:00 hours. We can do this by using a Time format of 37:30:55 or a Custom format of [h]:mm. We then get our expected result of 40:30:00.
No matter how proficient a person is in using Excel, there will be times when you need to get help. Excel has a huge help file, it offers help on each and every aspect of Excel. While this is needed, the size of the help file can often seem overwhelming or make it very hard to find help on the topic you want. Most people often snub their nose at using that annoying paper clip (Clippit). Don't become one of them as this help is always on hand and once you learn more it becomes easier to get straight to where you want to go.
There are other methods to get help, such as typing a question for help in the Type a question for help box at the top right hand side of your screen (this is for newer versions). Or you could use the option Help>Microsoft Excel Help to bring up a Task Pane (from 2002 onwards) with options to help you such as a Search facility to search the Microsoft Database on-line or a Table of Contents.
Once you get into the habit of using this help system in your preferred way, you will be able to find exactly what it is you want every time with minimal effort.
While Excel is without doubt the number 1 spreadsheet package in the World today it can do a lot more than just crunch numbers it's can easily manipulate text just as well. Most users of Excel unfortunately never come to realise anywhere near its full potential. Even though Excel can be found in nearly every office worldwide most are using it for no more than a big notepad with a few basic formulas in it.
Always keep in mind the three vital aspects of Excel, the Workbook, Worksheet and the Cell. Once you are in Excel, the cell becomes the very backbone of all that you do. Always keep in mind that there are many more rows than there are columns and set up all spreadsheets accordingly.
When starting a spreadsheet take the time to think it through and plan, if you get the foundations correct everything else will flow on. Adhere as much as possible to the 6 rules, it may mean a bit of short-term pain, but believe me this will be outweighed by the long-term gain.
Use range names where possible and define constant values. This may seem a bit more time consuming initially, but it will make life a lot easier further down the road.
Familiarise yourself with how Excel interprets dates and times. While not every spreadsheet uses them, most do. Excel itself recognises the fact a lot of spreadsheets will involve dates and times and so has numerous date and time functions available to the user. All of these functions rely on the serial numbers of dates and the decimal fractions of times and quite often the combination of the two.
Last but by no means least, use the help often so you will understand the way in which it works. Once you have found the answer to a problem that is complex, make use of the Annotate feature and you will thank yourself later.
OzGrid.com accepts no responsibility for any adverse affect that may result from undertaking our training.