Pinterest Stumbleupon Whatsapp
Ads by Google

Or should you be using both? Access and Excel both feature data filtering, collation and querying, but which program is suited to your work requirements, and how do you get the best of both worlds?

Understanding What Each Tool Is For

Excel vs Access Outline clip

The above table should provide you with an outline to the key comparisons between using MS Access or MS Excel for your data requirements. In an ideal world we all use Excel and Access interchangeably, one for its advanced mathematical calculations and statistical comparisons, the other for its ability to organise and display rafts of data in a structured manner.

Key Questions Before Your Choice

You should ask yourself these key questions before embarking with one piece of software over the other:

  • How do you want to organise your data?
    • Repetitive? Duplication?
    • Action tracking/event management?
  • Are you storing and managing, or storing and analysing?
  • How much data do you have?
    • Text?
    • Numerical?
  • Do you require formatting for sharable output?

Excel Is for Data Analysis

Excel excels at numerical data, its storage and the many permutations of analysis you might require to uncover any patterns, trends or misnomers in your data. The learning curve for Excel is quite short – in that it may seem extremely daunting to begin with, but you’ll quickly be bending pivot tables How to Use an Excel Pivot Table for Data Analysis How to Use an Excel Pivot Table for Data Analysis The pivot table is one of the single most powerful tools in the Excel 2013 repertoire. It is frequently used for large data analysis. Follow our step-by-step demonstration to learn all about it. Read More for your own use in no time at all.

The latest versions come absolutely jammed full of templates 15 Useful Excel Templates for Project Management & Tracking 15 Useful Excel Templates for Project Management & Tracking Read More , handy shortcuts and a very friendly GUI, but there are considerations to make. Excel is a superb tool when utilising a few spreadsheets at a time, but when you begin to scale up the size of your database, for instance with a government dataset, Excel begins to creak under the strain.

Ads by Google

Maintaining a massive Excel spreadsheet with thousands, if not tens of thousands of entries becomes increasingly difficult and as your data evolves the need to update your formulas, summary ranges and macros may lead to mistakes appearing in your data.

Excel Resources

Here are some excellent online resources, tutorials and templates to give you the upper-hand when getting to grips with Excel:

  • Learn Excel Without Spending a Penny – reference list for Excel tutorials, covering literally everything you need through one of the links
  • Templates for Excel – the MS Office store for Excel templates. Excel 2013 has an extended list when you open the software – give it a browse for more options!
  • Chandoo.org – tons of free information covering all learning levels
  • MrExcel – similar to Chandoo, has thousands of hands-on how-to videos and tutorials
  • YouTube: Howcast Tech – great introductory series, moving toward some intermediate Excel tools

Chandoo, MrExcel and the YouTube series from Howcast Tech are particularly good, each providing outstanding tutorials covering pretty much every tool you might encounter in Excel, brilliant resources for humble beginners right through to the Excel savants amongst us.

Access Excels at Data Management

Access is a slightly different beast to Excel. Where Excel maintains a focus on numerical, potentially one-off data sequences, Access provides a storage solution for information that can be recalled and referenced across multiple places. I will admit the learning curve for Access A Quick Guide To Get Started With Microsoft Access 2007 A Quick Guide To Get Started With Microsoft Access 2007 Read More  is problematic in places, but once you have learned the basics of the software, you will find those skills relatively interchangeable with almost any database software.

MS Access/MS Excel Screencap

Perhaps the single largest difference between Access and Excel is the method for record retention. Access records are free for modification at any time. The above image shows Access (top) and Excel (bottom) for comparison. Note, when using Access each individual record is given a specific ID number – the first column – to allow you to sort, filter and query your entire database So What Is a Database, Anyway? [MakeUseOf Explains] So What Is a Database, Anyway? [MakeUseOf Explains] For a programmer or a technology enthusiast, the concept of a database is something that can really be taken for granted. However, for many people the concept of a database itself is a bit foreign.... Read More . And your database is just that – a digital store of information. Adding more information, modifying, removing, filtering and querying it have no impact on the existing record, nor the formulas, summary ranges, tables and reporting you have already set up. When using a massive dataset in Excel, there is a possibility that performing any of the aforementioned tasks could break something, somewhere.

One Step Further Than Excel

Access goes a step further than Excel in terms of pure data storage, featuring specific internal tools to ensure data quality:

Aggregations of enormous databases are relatively simple in Access, too. Querying and filtering can take place regardless of your mode of storage with Access providing powerful data analysis solutions – but no pivot table!

Access Resources

Similar to our Excel offerings, here are some great online resources, tutorials and templates to give you the upper-hand when getting to grips with Access:

And the Winner is…

Neither!

Oh, what a cop-out, sorry…but I hope that we have illustrated each piece of software so your next data storage/analysis decision will be easier. You can import Access data into Excel and vice-versa in a number of ways, infinitely expanding the potential of both pieces of software. The solutions provided by Access and Excel provide a spectrum of data management techniques that evolve alongside our data requirement.

Access External Data Tab

 

The top bar illustrates the import and export options available via the Access > External Data tab. The second shows similar export options via the Excel > Data tab. Both data tabs provide users with an expanded cross-functionality that allows you the best of both worlds: supreme mathematical calculations, formatting and tables, along with excellent data management, analysis and reportage.Excel External Data Tab

Keen-eyed readers will note the lack of an Excel export tab. Data-sharing between Excel and Access is usually a one-way street, that is, there is no permanent data connection set between the two MS Office platforms when moving from Excel into Access. Using the Access “Import & Link” tab, specific tables or frames within Excel can be selected, linked to and referenced within your database, but the numerical data must be updated at source i.e. within Excel. Your table within Access will consistently update with your Excel input, but once you close Excel, you close the data-link (but keep your data!). For everything else there is always CTRL+C Windows Shortcuts Windows Shortcuts Read More or CMD+C! Everything You Need To Know About Mac OS X Keyboard Shortcuts Everything You Need To Know About Mac OS X Keyboard Shortcuts No matter what operating system or program you're using, keyboard shortcuts are a tool you can use to make things quite a bit easier for yourself. Simply not having to take your hands off the... Read More

Understanding just what each tool can do for you, and for your data can make a massive difference, so use both to your advantage by reading and watching some of the tutorials to gain some hands on experience with these powerful data tools.

Did we miss anything? What are your favourite Access and Excel alternatives? Let us know below!

  1. Eric Au
    October 27, 2016 at 1:07 pm

    A database and a spreadsheet both have its advantages and disadvantages. A database is difficult to start with for most of the people, while a spreadsheet, although it's easy to use, will be troublesome and difficult to handle as the data size become larger and larger.

    Instead of choosing between using Excel and Access, why not using both of them together. BAU DB is a database tool designed for this purpose which links up Access with Excel. It solved the difficulty of using Access and can get straight to work right away without the need to build anything. You can also use the nice formatting features of Excel to create document and report layout just on an Excel spreadsheet.

  2. Stuart Taylor
    October 13, 2015 at 3:28 pm

    Gavin, i need your help, how to export inventory database from excel to this access template:

    http://access-templates.com/access+2010/access+database+inventory+management-572.html

    Really appreciate your help, if you'd like to communicate through email, i will very happy.

    Many thanks

  3. Felipe Angel
    July 10, 2015 at 2:35 am

    I personally don't like the kind of complexity Access puts at, say, everything you want to do. Excel provides great form tools and some data management bridges through VBA and SQL using ADO, this combination has worked great for me, I've built project management systems and a bunch of apps easily, just great. I wonder if I'm missing something here for not using Access at all... SQL server + VBA do the work together... and better in my opinion than Access itself.

  4. Emiel Nijhuis
    April 23, 2015 at 1:28 pm

    Great article.
    As a professional MS Office developer I often have to choose what type of application I should use: MS Excel or MS Access. In that regard I find this article very helpful:
    https://support.office.com/en-us/article/Using-Access-or-Excel-to-manage-your-data-09576147-47d1-4c6f-9312-e825227fcaea#bm1

    I also use this Connexa add-in to combine these two applications: it allows you to write back changes from Excel to MS Access:
    http://www.redcirclesoftware.com/connexa-xs/

  5. Andrew Purvis
    February 7, 2015 at 5:15 pm

    Of course, Access is a dog of a database. Try, as an alternative, about anything else. Building an interface takes ages compared to other solutions on the market, and the filtering is more annoyance than feature.

    Excel, at least, is best-of-breed.

  6. Jeff
    January 16, 2015 at 7:16 am

    Disclaimer: I work for Ragic (http://www.ragic.com)

    I believe the best way to deal with the fact that Excel is the world's most popular choice of Data Management, is to make true data management tools behave more like spreadsheets. The reason is what Gavin said, the learning curve for this type of spreadsheet software is extremely short, as opposed to database builder software like Access. We can take advantage of this existing behavior and make database builders extremely easy to learn, and even more powerful.

    This is exactly what Ragic did. Ragic makes the process of designing a database just like editing a spreadsheet. This significantly shortens the learning curve for database builder applications like Access.

    There's also an article on Ragic website that discuss the why this type of user-driven spreadsheet-like database can be so important for business in the future as we go from Bring Your Own Device to Bring Your Own Application. Most of the time enterprise IT is just too slow and unresponsive to the fast paced business need for various applications. Non-technical business users can really benefit from having a tool that they can pick up quickly and cook up a working database application for their team.

    The article:
    http://www.ragic.com/intl/en/about

  7. Ben Shirley
    January 2, 2015 at 6:14 pm

    Excel gets upset with over 30K lines of data and starts getting a bit buggy, we record and track information in Excel as Access is just not user friendly when it comes to daily updates of that information. But the features of access in handling data would be such a powerful tool, but its limitations in the most basic of data manipulation in the table view renders it useless. Its such a shame. Cant drag down the cell above, does not bunch dates like excel for filtering, very hard to paste data into the table. We have to use Excel for Data Management as Access is just unmanageable when daily updates are made by a large team.

    If these 2 programs where better linked, or shared more functions then we could use them correctly i guess.

  8. Qasem
    December 24, 2014 at 6:36 pm

    Can excel manage easily records that have big text for 2000 row?

  9. Joe B
    November 12, 2014 at 11:06 pm

    PowerPoint and the Fuzzy matching addins for Excel allow those fearing Access to work with larger tables, use more slicers (in place of queries) and even perform joins (including joins of dirty data).

    I think the best source for Excel tutorials and not mentioned is excelisfun:
    http://people.highline.edu/mgirvin/excelisfun.htm

  10. Jackson Pollack
    November 12, 2014 at 2:22 pm

    There is an entire group at our company that produces reports in SQL.

    Until five years ago, the manager of that group didn't even know what a relational database was. And to this day only two of the staff of twelve even uses SQL out of the box. The rest of them are too lazy to learn SQL and import their data to Access and Excel...all the while using the very code from SQL to run their quiries and reports.

    And both of these tools (Access and Excel) have awesome data capabilities...if your data is in SQL then you should also learn SQL to get the most out of that database.

  11. Geoff
    November 11, 2014 at 5:51 pm

    I have been an Access user from the day it was introduced and Excel for even longer and I have spent thousands of hours writing complex applications in both, for both personal and commercial use. In my opinion, asking if Excel can do the job of Access is akin to asking the question if Excel can do the job of MS Word. Sure you can write a letter in Excel if you really want to, or set up a table in Word, just like you can hammer in a nail with a wrench if you really want to.

    And so it is with Excel and Access. As true database tools the overlap betwen Excel and Access is extremely small but I guess that comes down to how the word "database" is used. The so called "database" tools and functions in Excel are more correctly "data table" tools. It has some powerful tools and functions for handling a small amount of records in flat tables but nothing comes even close to creating a true relational database as Access does. I have a couple of Access databases with over half a million records.

    For pure number crunching of a few hundred records in a few linked worksheets Excel does the job but beyond that there are better tools for the data handling side and Access is one of them. Like some other users here, sometimes I will extract or link a subset of data from Access into Excel for specialised analysis or charting. Excel certainly is more flexible for that.

    For many users I guess one of the key differences between Access and Excel (and Word for that matter) is that Access (like any other database software) requires an amount of setup before you can just start plugging in data. Sure you can use wizards and templates to do some of the groundwork for you, but my advice is DON'T! Whichever database software you use get in there and learn it along with the fundamentals of relational database tools and you won't be sorry.

    Access has its knockers may not have the data sharing capabilities and data integrity of some of the client/server databases out there but it is still an extremely powerful tool that is largely underestimated and misunderstood and it is still my "go to" tool for may and varied applications. I suppose one of the reasons for that is that I have so many hours invested and thousands of lines of code that I could never possibly rewrite into somthing else ..lol..

    By the way, these days at home I run Linux and run Windows in a virtual machine to handle my Access apps.

    So I agree with the article, but really when I hear people comparing Excel to Access my first thoughts are always that they do not understand what Access really is and what it is intended for.

    • steve Callaghan
      February 25, 2015 at 12:08 am

      Geoff
      Did you do some work for me a few years ago using Microsoft access to extract records from the Companies House database from which unique domain names were produced?

    • Geoff
      February 27, 2015 at 3:05 am

      Hi Steve, no that was not me. Unless of course you have an unpaid account in which case it definitely was! ..lol.. Best wishes,

  12. Murray
    November 11, 2014 at 8:45 am

    Question: Can anyone recommend the best db program that can take excel cvs files and filter the data into a pre-designed graphical template where the data would of course be changeable while keeping the same graphical under-layout? This is to be used for generating performance seating reports for by the waitstaff in a 700 seat dinner theater which can only work with the combo of data and the theater layout as opposed to the columnar data directly from the ticketing system or an excel alone export.

    • Andrew Purvis
      February 7, 2015 at 5:26 pm

      I would suggest Filemaker Pro. You can get up and running quickly, import with no effort, and easily customize it to suit your needs.

  13. Evert van Dijken
    November 11, 2014 at 7:48 am

    For serious business, you cannot use either Excel or Access. There is no version control possible and you cannot prove with which version you made a calculation. Program and data should be separated files. And the program should be compiled with a version number and the version number should be mentioned in the results. Only in this way you can reproduce your findings. Programs which users can alter on the fly, like Excel and Access, have no meaning for serious quality managers.

    • Andrea
      November 11, 2014 at 9:40 am

      You can actually lock pretty much anything you don't want modified in both Excel and Access. But I agree with you on the rest.

  14. Chris
    November 11, 2014 at 12:28 am

    Excel has it's place for it's ease of use, but Access is just plain awful. Access isn't any easier to use than a more traditional database like MySql and it isn't conducive to proper database design. Sure, it works for throwing something together, but once the database grows, someone gets the nightmare job of moving a poorly designed Access DB with no foreign keys to a database that actually scales well with volume.

    Excel wins hands down for actually being good at its job.

    • Gavin
      November 13, 2014 at 2:17 pm

      I didn't cover this in the article to much, but you are right about scaling up. Access has the odd 2gb limit still and once you start reaching that, things can get messy!

  15. jim
    November 10, 2014 at 10:16 pm

    There's only one current version of the Excel document and it's on the laptop of someone who had to leave town for a funeral.

    Just drop the Excel file on the Filemaker icon and you have an instant multi-user database.

  16. Doc
    November 8, 2014 at 10:32 pm

    One of Ars Technica's Hallowee'en IT Horror Stories was from a manager who wanted his head of IT to move everything from a shared database to Excel...simply because he knew Excel. (It takes very little expertise to know you can't move a shared database to a single Excel file, or set of files, that are single-user only...only one person can have each Excel file open at a time, while everyone can access a properly designed database simultaneously.)

    • Mark
      November 10, 2014 at 4:08 am

      I used to see this all time in photography. I used to use Filemaker (similar to Access but Mac-only) to file contact sheets and their details from jobs, keywording them to make retrieval by subject easy. Apart from one or two others who used Access, it amazed me that virtually everyone else used Excel simply because, as Doc suggests above, they vaguely knew how it worked and it is superficially easier to use. No amount of pointing out what a rod they were making for their own backs seemed to help because they all seemed so intimidated by the idea of setting up a database. I assume the same is true in any number of other lines of work. Horrendous!

    • Gavin
      November 10, 2014 at 11:41 am

      Hi there.

      Thank you both for your responses. You are both right - people are instantaneously put off at the thought of 'managing' a database, when in reality it isn't outrageously difficult and the benefits can be massive. The database is a versatile platform that many of us are not introduced to until later in our computing lives (my understanding!) and in that time Excel becomes the go-to for anything involving storing data records.

      There are so many resources and tutorials online now that gaining knowledge is relatively easy and querying mistakes only takes a matter of seconds. Moving people set in their ways or too 'scared' to change can be difficult, but when they see the results of a small amount of applied learning can usually be encouraged to learn more.

      Unless as in Doc's case - that will never change!

    • Doc
      November 10, 2014 at 2:07 pm

      @Mark: Filemaker Pro isn't Mac only - there are versions for Windows and iPad. (I know because a local historical association loves their copies - they use it for genealogy - and can't live without it).

    • Gavin
      November 11, 2014 at 11:00 am

      I didn't realise there was an iPad version - have you used it?

    • Doc
      November 11, 2014 at 6:04 pm

      @Gavin: Haven't used *any* version of FileMaker, as I grew up on dBase (played with it in college) and FoxPro (started with 2.6a for DOS, and worked my way up through 5.0, 6.0, 8.0 and 9 with my current employer). Now developing for PHP/MySQL with the Symfony and Magento frameworks.
      Nothing beats Excel for fancy tables and quick recalculations...but multiuser it ain't. I prefer LibreOffice (it's free!) but my boss loves Excel for its ability to make nifty slanted header rows, which LibreOffice/OpenOffice Calc lacked at the time I brought OOo up (LOo didn't exist yet).

    • Andrew Purvis
      February 7, 2015 at 5:24 pm

      dBase was a fine solution in its day, and I would still prefer it to Access. FoxPro was another fine solution, but MS bought it, released Access, and killed it.

      Mark's note that Filemaker is Mac-only has, to my knowledge, never been true (perhaps before 2.5). Filemaker Go (free on iPad and iPhone) can handle the databases locally or from a server. Web publishing allows Filemaker to serve almost everything as a web page that precisely replicates layouts created in the software; only a few limitations on script steps stand between web and local functionality.

      The problem with people who use Excel to store data that should be in a database is that Excel does not provide the level of data integrity of a proper database, as noted in the article, and I have seen this destroy content.

  17. Jeanne Thelwell
    November 7, 2014 at 9:32 pm

    Is there ever going to be the tutorial on Access Forms that was promised in the one for Queries?

    • Gavin
      November 10, 2014 at 11:47 am

      HI Jeanne,

      Is this the article you are referring too:

      http://www.makeuseof.com/tag/quick-tutorial-queries-microsoft-access-2007/

      I wasn't on the team at that point, but I'm sure could work something out if it something you would be interested in reading?

    • Jeanne Thelwell
      November 10, 2014 at 8:45 pm

      ?Thanks for replying!

      I would definitely be interested in reading it. I'm using Access 2010, but the concepts are the same and the previous 3 articles were very helpful.

Leave a Reply

Your email address will not be published. Required fields are marked *