InfoAge

“Which is better, Access or Excel?” I am sometimes asked. Many people manage their data using Excel and use it like a database. Access is the leading database tool for small-medium business.

Databases and spreadsheets are different sets of tools, for different kind of jobs. They both serve an important purpose in business. The challenge is to understand what each tool is designed to be used for and make sure you are using the right one. Hammers are very good for driving nails, but terrible for tightening screws; screwdrivers are perfect for tightening screws, but not so much for pounding nails. One is not “better” than the other.

Take some time to evaluate your use of these Office tools. Part of my consulting services are in helping organisations sort or and rectify their data because they have picked the wrong tool.

In both Access and Excel, you can:

  • Run powerful queries to sort and filter your data
  • Run sophisticated calculations to derive the information you want
  • Generate reports on your data and view them in multiple formats
  • Use forms to add, change, delete, and navigate your data easily
  • Create a Microsoft Word mail merge — for example, to mass-produce address labels
  • Connect to external data and view, query, and edit it without having to import
  • Import data from external databases (Access, Microsoft SQL Server) and other file types (.txt or .htm)
  • Excel is great for number crunching and calculations

Use Access when you:

  • Requires multiple storage for your data (client personal details, client orders, etc)
  • If you want to keep track of customer information such as names, addresses, and telephone numbers, but that information may grow to include actions by customers such as orders, then consider an Access database
  • Have a very large amount of data (thousands of entries)
  • Have data that is mostly of the notes or descriptions (not numbers or calculations).
  • Need to maintain constant connectivity to a large external database such as one built with Microsoft SQL Server
  • Want to run complex queries or reports
  • Have people working in the database at the same time and you want to keep your data secure and accurate
  • Wish to control access to different parts of your data
  • Expect to grow your business and wish to have a stable and expandable system

Use Excel when you:

  • Want to analyse numbers such as financial data and perhaps create graphs
  • Know your dataset is manageable in size (no more than 15,000 rows)