Microsoft Office users often ask whether, why, and when they should use Microsoft Access versus Microsoft Excel. Especially, when they are very comfortable using MS Excel and do not understand the reasons why anyone would use MS Access or databases. Here is my perspective…
I do not believe it is an either/or situation. Experience with Access and Excel, so far, is that they both have strengths and naturally complement each other. Individuals who understand the differences, learn the products, and apply the appropriate tool for the appropriate situation to give themselves and their organisations a competitive advantage.
Advantages of Microsoft Excel Spreadsheets
The learning curve for Excel is very short, so it is easy to use Excel and become productive right away. Excel makes it easy to store data, perform numerical calculations, format cells, and adjust layouts to generate the output and reports to share with others. Advanced features such as subtotals, power pivot tables and pivot charts, analysis toolkit, and many templates make it easy to accomplish a wide range of tasks. It can even integrate with the Analytic Services (Business Intelligence) from SQL Server. Tweaking the results is also very easy to get the exact layout, fonts, colors, etc. that you want.
Disadvantages of Microsoft Excel Spreadsheets
Unfortunately, there is a price for the flexibility of spreadsheets; while it is easy to create formulas, reference cells, copy and paste data, and link worksheets and spreadsheets together, as the work gets more complex, spreadsheets become more difficult to change and manage. Spreadsheets are ideal for creating one time analysis, however, they become problematic as the data grows and evolves over time. As new rows and columns are added, summary ranges and formulas may need to be modified or new ones created, data and formulas are not consistently updated, and these mistakes lead to bad results and decisions.
For spreadsheet experts, it is often difficult to understand what databases offer that spreadsheets do not already have. There are several advantages of databases and some of them are as follows:
Table Structures and Validation
Databases make it easy to store information in one place and reference it in multiple places. For instance, customer information may be kept in a customer table where the name, address, phone number, email, etc. are kept. It is then referenced in other places such as queries, forms, and reports. If the customer’s information is changed, the new information is automatically updated in all the referenced places. The table designs also impose structure that lends itself to data types, validation and consistency for higher quality than what normally exists in spreadsheets. The basics of storing numeric, date, and text fields are just the beginning.
Records are Free in Databases
The biggest difference with spreadsheets is that in a database, records are free. If it is well designed, over time, new records are continually added without needing to add new fields. All the queries, forms, and reports continue to work without any changes. Different filters may be applied but the results are always consistent.
Queries and Reports
Microsoft Access queries and reports let you slice and dice your data and present it in detail or summary form regardless of how the data is stored or sorted in the underlying tables. It offers a great deal of power and flexibility to analyze and present results. Using groupings, it is easy to show aggregations as the data changes and groups get added or removed.
Disadvantages of Microsoft Access
The biggest disadvantage of Microsoft Access is that being a database, it takes more skill and training to use it well. Figuring out how to define normalized tables, link them together, and structure the information so it is easy to edit, query, view, and report can be quite challenging. Many of those issues are not unique to Access since they apply to all database designs. Additionally, some data analysis features such as power pivots exist in Excel but not Access.
Excel makes it easy to generate custom output with very flexible formatting and annotations that you can add anywhere. The payoff with Microsoft Access is how databases simplify things over time. A hybrid solution where data from an Access database is exported or copied to Excel often provides the best of both worlds. Having tools that allow for the flexibility to rapidly respond to changes empowers you and your organisation to meet its mission efficiently.