Excel & Filemaker Pro Similarities

At first glance, Excel and FileMaker Pro are in two entirely different software categories: Excel is spreadsheet software, while FileMaker is database software. However, both applications have similar approaches to tables of data, and can be used in similar ways for simple database functionality. If you have both applications, their similarities and differences can help you determine which tool is best for your task.

Tabular Data

In Excel, all data is entered in tabular format. Columns run vertically across the top of the spreadsheet, with rows running horizontally down the left edge. In database terms, each row is a database record, and each column is a field spanning all records. When using FileMaker Pro, the "View as Table" option shows you a similar table layout for your fields and records. Excel has the advantage when it comes to building a simple list database on the fly, as you do not need to explicitly create new fields. Add a new field to your spreadsheet simply by clicking in an unused column and adding the new data.

Calculations

Both Excel and FileMaker support the creation of calculations from the contents of other fields. In FileMaker, you have to explicitly define a calculation field in the Define Fields dialog, which brings up a window allowing you to enter the calculation. In Excel, any cell of the spreadsheet can hold a calculation, which you indicate by starting the cell contents with the "=" sign, followed by a legal Excel expression. For example, FileMaker may calculate sales tax with this calculation:

SalesTax = Price * 0.06

In Excel, a sales tax expression may read as follows, if the B column holds the prices:

=B2*0.06

One difference between Excel and FileMaker: while the FileMaker calculation will automatically appear in all records, you have to manually copy the Excel calculation into all of the rows where you want it to appear.

Relational Data

FileMaker is a relational database, which means that you can create multiple tables of data and interlink them based on calculations you specify. For example, a database could look up a sales tax from a secondary table based upon the location where a sale has been made. Excel is less powerful than FileMaker for creating these relations, but provides you with similar capabilities through the ability to create multiple sheets in a single worksheet. In the above example, a secondary sheet could contain the tax rates, which is then accessible in the primary spreadsheet using Excel's Lookup function.

Choosing Your Tool

Excel and FileMaker Pro are interchangeable for simple lists of data, so when creating this kind of database, use the application with which you are most comfortable. For more complex tasks, Excel lends itself to presentation of tables of data with extensive formatting. This is possible in FileMaker Pro, but requires more work creating custom layouts. On the other hand, FileMaker Pro is better for complex interrelated data functions with many lookups and calculations.

References

• Microsoft: Using Access or Excel to manage your data
• FileMaker: Defining calculation fields