 |
| <<Convert Excel Spreadsheets to Web Pages | Trading Software That Operates Within Excel | Convert Excel, Access & Other Databases | Merge Excel Files>> |
|---|
|
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.