Choosing the Right Tool: FileMaker or Excel?

The first step is to acknowledge that there is a time and place for both FileMaker and Excel, and leveraging the strengths of each will benefit your workload and maintain the highest level of accuracy in your data. FileMaker is a true database, which means it has the capacity to capture data and the way it changes in order to tell a story about it later. On the other hand, it is equally valuable to allow your clients to export the data into Excel spreadsheets and perform validations and analyses without interfering with the data in FileMaker. Excel is familiar, powerful, and flexible right out of the box for static, self-contained reporting and analysis. FileMaker is the incubator for the evolution of your existing or future app. Preferring one application over the other is natural, but treating them as mutually exclusive options is unnecessary and inefficient. The more open we are to using every tool in our toolbox, the greater our confidence will be in the integrity of our data and the conclusions we draw from it.

Use FileMaker for… complex conditional logic and data management

If you’ve ever tried to create an entry form in Excel, you would have undoubtedly encountered the need to write a 50-line, nested formula in order to calculate the unit price for a line item. Cells with these formulas contain a lot of conditional logic to account for many possible variations, but ultimately perform the same operation, like calculating the price per unit. You were likely very proud of writing that nested formula, inserting the right comma inside of the right-colored parenthesis – but mostly because it’s such a pain in the trunk (…of the elephant in the room).

 In FileMaker, you can implement conditional logic in a variety of ways, choosing the method best suited for the situation. FileMaker allows you the ability to consolidate all the logic of an operation into a single calculation, just like you can in Excel. However, the calculation engine in FileMaker allows you to add spaces, returns, and comments within your formula, so that it’s easier to read, understand, and edit in the future. The power and flexibility of the calculation engine is accessible in almost all environments – the field definition itself, the hide condition or conditional formatting of an object or field, a tooltip or placeholder text, a button bar, and – in a more recent release – in a text box using a layout calculation.

The widespread access to the calculation engine allows you to organize logic according to the variation it’s referencing, and show or hide the field, variable, or button conditionally. For example, if you had different methods of deriving the unit price of a product depending on its manufacturer, you could create separate fields for each manufacturer’s pricing logic and stack them all on one layout. When a product is selected, the hide condition formula for each of those stacked fields would determine which fields to hide, showing only the unit price corresponding to that product’s manufacturer. If the formula is complex or references many related tables, the conditional logic can be shifted to a script, which is triggered when the product is first selected. The script can hold as much or as little of the conditional logic as you’d like. It allows you to store values in fields or variables, and it affords you the ability to annotate your thought process in comments throughout the script. Moreover, when the conditional logic is executed through a script, it is easier to make updates to it (you won’t need to lock the database schema in order to update field definitions). Between the flexibility of employing conditional logic aesthetically or through data, and the convenience of being able to annotate each branch of the workflow, FileMaker beats Excel in a landslide as the best tool to manage conditional logic for workflow. 

Use Excel… to add notes and comments for review and collaboration

Finding a discrepancy in your data should fill you with feelings of relief and accomplishment – problem solved! However, if your data drives your budget, inventory, and revenue estimates, you will probably be left wondering if there are any other discrepancies to find and resolve. After all, you don’t know what you don’t know. You may want someone from your team to do a deeper dive into your full sales history, do an updated inventory count, and then compare the two to find any other inconsistencies.

The best way to markup the data and add notes and instructions for another person (or for yourself to do later) is to export the data into Excel and add comments there. Whether it’s actually using the comment function or just adding a note to a cell next to a particular value, Excel makes it incredibly easy to insert instruction, highlight it so that the eye goes to that cell first, or otherwise leave an explanation about data expectations and/or exceptions. While Excel was not built to be an annotation tool, it is, ultimately, digital graph paper as well, which is what makes it so easy to enter notes in between columns or highlight cells to review later.

Use FileMaker… to manage how much of your data each user can access

If you have data which you don’t want accessed, edited, or deleted by just anyone, FileMaker can safeguard it through a permissions hierarchy. While Excel offers password-protection for viewing and editing workbooks, it doesn’t offer much nuance for levels of access or restriction. Excel also offers methods of security through macros, but they are relatively complicated to set up and fragile to maintain long-term.

FileMaker is extremely flexible when it comes to setting up your security model, and it offers a variety of options for how to protect your data. If you’d like only specific users or groups of users to see particular layouts, reports, or entire modules of your solution, you can assign access to only those users. For each user or user group, you can select default access options or you can customize them individually. If a group of users, such as cashiers, need to be able to ring up customers and retrieve product information and pricing, but should not be able to delete transactions or view monthly sales reports, you have the option to create a customized privilege set with those security parameters. You can even assign logic to specific tables and layouts to dictate whether someone can create, view, edit, or delete a record in a particular circumstance.

Use Excel… because your contacts use it

This is simultaneously the best and the worst reason to use Excel: because your clients, vendors, and partners use it, too. However, it is usually unavoidable, and not too big a sacrifice to support successful collaboration regarding your business. If you have any kind of business relationship with banks, they will ask for recurring financial reports in Excel. If you sell your product to stores with multiple locations, the buyers from these stores will often place orders with you using their own Excel templates because they contain lists of the store locations in their territory, and that’s the information that is consistently important to them.

If your business associates use Excel, then you will use Excel.

Keeping an Open Mind

There are many factors involved in choosing the best software to identify a problem, present a report, or run your business, and many of those factors may be specific to your industry or situation. You are best equipped to evaluate your options and make that call because you know your business better than anyone selling you software. As long as you understand your goals for your data – or the questions you want your data to answer – you can decide whether the job at hand is best delegated to a powerhouse relational database with reporting capability – FileMaker – or a popular, ready-to-use analytical tool with exceptional flexibility to transform, filter, and aggregate data from a single point in time – Excel. Every time you have a question to ask your data, you should evaluate the tools at your disposal, because the same tool you use day to day may not be the best – or fastest or cheapest – way to answer this question you’re asking today. Keep an open mind, and keep FileMaker and Excel in your toolbox, because each one boasts unique strengths in every situation. After all, an elephant is no match against a noble thoroughbred’s speed and endurance – but, man, does he know how to network.