| The benefit of an Excel training course is not just | | | | Mark Blank Formulas |
| learning new things but also learning new ways to | | | | There are a lot of reasons you might have blank |
| do old things. An experienced instructor provides | | | | cells that actually contain formulas. They could be |
| hints and shortcuts that have you designing | | | | examples of the above, formulas that won't |
| spreadsheets in new ways. | | | | calculate until all data is present. Another reason, |
| For example, let's look at tips from Excel training | | | | and another trick you might pick up in an Excel |
| courses that can help in maintaining a database. | | | | training course, is that cells used for intermediate |
| Excel is an easy way to keep a simple database | | | | calculations can be formatted to be white text on |
| and a few design steps can save you huge | | | | a white background so they don't clutter up the |
| headaches later. | | | | sheet. |
| Hide Blank Formulas | | | | These blank cells are easily overwritten by a |
| The simplest databases are a collection of data | | | | careless user so need to be marked in some |
| records but some have formulas off to the side | | | | way. Using a light gray or other colored |
| providing analysis of each entry. However if any | | | | background is a good way to indicate that there |
| data is missing from the record then these | | | | is something there without detracting from the |
| formulas clutter up the sheet with "#NUM!" and | | | | main information on the sheet. |
| "#DIV/0!" and other errors. | | | | Use Border Rows |
| Consider a database of payments from a client, a | | | | Databases outside of Excel training courses are |
| typical example in an Excel training course. Each | | | | seldom static. As you add new records to them |
| line contains, among other information, the date of | | | | you don't want to update every formula that |
| invoice and the date of payment. You have a | | | | refers to the old range. By using border rows, the |
| formula on each line that calculates the time | | | | formulas all update automatically. |
| between invoice and payment using a formula like | | | | Take one row above and below your database |
| "=G2-F2" to subtract the invoice date (F2) from | | | | and mark it in some way. You might put symbols |
| the payment date (G2). Until you have a payment | | | | such as asterisks or dashes, or you might color |
| date, this field will read as a very large negative | | | | the background of the cells to create borders. In |
| number. | | | | any formula that refers to the database, be sure |
| Instead, enter this formula: =IF(G2="","",G2-F2). | | | | to include these border rows in the range |
| This says "If there is no payment date, leave this | | | | designation. The range automatically adjusts as |
| field blank otherwise calculate the payment delay". | | | | you insert new lines even if you add them before |
| Even better, try "=IF(OR(F2="",G2=""),"",G2-F2) | | | | the first row or after the last because you are |
| which leaves the payment time field blank if either | | | | still adding them within the two border rows. |
| date field is blank. | | | | |