Paul Strauss

Information Management Applications

MS AccessMS ExcelWeb SitesMySQL

What Is MS Excel?

The simplest way to think of Excel is as an automated scratch-pad. You can use a scratch-pad to write down a column of numbers and write the total underneath the last number. If you change one or more of those numbers, you have to erase the current total, re-calculate the total, and write it down. Excel automates this process of keeping a total of a column of numbers. The first thing it does is, with one or two clicks, to allow you to total the column. Once you have created a total, then if you later change one or more of the numbers in the column, the total is automatically updated as soon as you change any of the numbers.
That alone makes Excel pretty useful but that is just the beginning of its power and usefulness. If that total is then used in some other calculation, it could be another total or some other more complex calculation, then whenever you change a number in the total, the other calculations that the total is used in are also automatically updated. There can be number of places that depend on that first total and they will all be automatically updated any time the total changes. The changes automatically ripple throughout the entire spreadsheet.
The calculations may be much more complex than simple arithmetic. Excel can do many common, complex calculations without you having to write any code for the calculation. For example, Excel can do financial calculations such as computing interest and payments. However, if Excel doesn't provide the calculation that you need, then you can write some code to do the calculation. If you can conceive of the calculation, then Excel can do it.

MS Excel As a Database

When you first open a new Excel spreadsheet, you will see a grid — columns and rows. Normally, you would just type your numbers in a column. However, the grid can also be understood to be fields and records. That is, you can use Excel as a simple database. A typical “Excel database” would be a name and address list. If you put the first and last names in separate columns, then, with a few clicks, you can sort the list alphabetically. This kind of list is very useful as a data source for MS Word mail merge. It is easy to maintain the list and it is easy for Word to use the spreadsheet for a mail merge. Excel used as a database has definite limitations but for simple situations, it is ideal. As the situation matures and the information becomes more complex and difficult to use and maintain, then it is easy to convert your Excel database into a MS Access database. If you want to start by keeping your data in Excel, I urge you to contact me first so that I can advise you on how to organize your spreadsheet so that when the inevitable happens — you need to migrate to MS Access — the process will be clean and efficient.

Powerful Features of MS Excel

Much like MS Access, there is a version of VBA available in Excel which makes it possible to go way beyond doing calculations. For example, if you do some repetitive operations with your spreadsheet, this operation can be automated in order to insure accuracy and save much time and effort. VBA can be used to validate your data to insure that the data is, for example, within a certain range. If you want to do some very complex calculations, this is best done in VBA. Your data may be entered and maintained in MS Access but Excel can be used to do some calculations on that MS Access data. Excel can literally reach into your MS Access database, read some data, and do some calculations on it. You don't have to decide whether to use Excel or MS Access — you can use both in order to take advantage of the strengths, efficiencies, and cost savings that each tool can best provide.