Access v Excel
By Richard Rost
5 years ago
Access v Excel: Which is Better? When to Use Each.
In this video, I will cover the pros and cons of Microsoft Excel and Microsoft Access. Which one is better? When should you use each one? What's the final verdict?
Marc from Akron OH (a Gold Member) asks: I just started a small business and I can't decide whether I want to use Excel or Access to manage my customers, contact, orders, inventory, and so on. I don't want to use off-the-shelf accounting software like QuickBooks because I do have some unique record-keeping needs for my business. I've been using Excel for years, but I hear Access is a lot more powerful. What do you recommend?
Members
I'll show you how to import and link data from Excel into Access. This way your secretary can perform data entry in Excel and you can bring it into Access. I'll show you how to use an append query to automate the process of importing newly entered data in an Excel sheet. Then we'll go the other way. I'll show you how to link Excel to an Access table so your secretary can see your customer list, itinerary, or whatever else you have in your Access database. I'll show you a trick to refresh that link every time he opens the sheet.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
 Links
Export to Access to Excel: https://599cd.com/acx19
Export to Excel from Access: https://599cd.com/acd20
Microsoft's Access v Excel: https://tinyurl.com/y2udgwha
FREE Access AND Excel Level 1: https://599cd.com/Free1
Subscribe to Access v Excel
Get notifications when this page is updated
Transcript
Welcome to another TechHelp video brought to you by AccessLearningZone.com and ExcelLearningZone.com. This is our first ever joint TechHelp video. I am Richard Rost, your instructor. In today's video, I'm going to talk about Access versus Excel. It's a question I get asked all the time: which is better, what do I use, and when.
Today's question comes from Mark from Akron, Ohio, one of my Gold members, but I get asked something similar to this all the time. Mark says, "I just started a small business and I can't decide whether I want to use Excel or Access to manage my customers, contacts, orders, inventory, and so on. I don't want to use off-the-shelf accounting software like QuickBooks because I do have some unique record-keeping needs for my business. I've been using Excel for years, but I hear Access is a lot more powerful. What do you recommend?"
Well, first Mark, let me start off by saying that I love Access and Excel. Over the past couple of years, I've spent most of my time teaching Access lessons simply because that's more of my niche market. There's a lot less competition for good Access training materials.
With Excel, there's a lot of good people that are putting out books and tutorials like MrExcel. He's got a lot of great stuff. So there's a lot more competition there. Access, not so much. So that's why I've been focusing my business. So I love them both, and I'm going to give you an unbiased accounting of which one is better to use and when.
Access and Excel really complement each other well. Let's talk about the primary role of each application. Access is great for data storage and management. When you've got a large volume of data that you need to store and you need to be able to manage it, make additions, do update queries, deletes, and stuff like that on a large scale, and you want to automate stuff, that's Access's primary function.
Access is also very good at creating a robust user interface. You want to create menus and buttons for non-computer users to be able to go in and easily do what they have to do. That's what you want Access for. Also, if you're dealing with multiple simultaneous users, if you've got 5, 10, or 20 people in your office that need to work from the same customer table, again, that's a job for Access.
Excel is much better with smaller amounts of data, and it's really great at data analysis and visualization. In other words, if you want to take this year's sales and do analysis on them, what-if scenarios, pivot tables, you want to see trends and things like that in your data, that's what Excel is good at.
Excel is much better with things like advanced charting. It's got better conditional formatting. We'll talk about these things as we go along today in the video.
Now, each application also has its primary disadvantages. Access, for example, has a steeper learning curve. Like my buddy Alex says, it has a higher barrier to entry. It does take a little more training to get up to speed for building Access databases. It's not quite as easy to use as Excel. Excel is a lot more intuitive. You can just sit down and start typing information in. With Access, you have to set up a structure first.
Also with Access, there's a longer development time. If you're going to build a database to run your business, it's going to take longer than just sitting down and plugging information into a spreadsheet. But that disadvantage is actually a benefit in the long run because you'll be a lot more productive with a properly built Access database.
Excel's problems? Essentially, a lack of structure. Anyone can just type in anything, anywhere they want on the sheet. There's no rules. Yes, Excel does have some basic data validation, I know that, but by and large, you can pretty much get away with whatever you want. That's okay for you, but if you've got 15 people working off an Excel sheet, it becomes a problem.
Another primary problem for Excel is that it's not relational. Each sheet stands on its own. So if you've got customers and orders and products, they're not related together at all. You have separate information in separate sheets, and it can be hard to try and track information down. That's what Access does really well.
So let's talk about how both are alike. Both can store large amounts of data. The maximum size of a single file is 2 gigabytes, and that's a lot of information. That's, I believe, like a million rows in Excel, and I forget how many columns. Access, a single table can only be 2 gigabytes which again is pretty big, but in Access, you can chain them together. You can have multiple tables stored in multiple files, so your size is pretty much irrelevant.
Both can search, sort, and filter data, although searching, sorting, and filtering very, very large spreadsheets can get cumbersome sometimes. Both do have data analysis tools, although Excel's are much more robust. Both can create basic charts. There is some simple charting in Access. It's getting better. Excel is by far a better option for charting, though.
Both have sophisticated functions for Max, Min, and lookups and all that kind of stuff. Both can perform advanced calculations. Both can be secured with a password. If all you care about for security is putting a simple password on your file, you can do that in both Access and Excel. Both support macros and the full Visual Basic for Applications programming language. And both can access data from outside sources, although I will argue that Access does it better.
So that's what they both have in common. Now let's take a look at where each is better than the other. Now these are in no particular order, and the one in bold is the one that I suggest is a benefit.
Access does have a longer learning curve, whereas Excel is relatively easy to learn. It's a lot more intuitive to sit down and just start typing data into Excel, although that can sometimes be a downside because there's less structure. Access does have a longer setup development time, whereas with Excel, you can just start working right away. You can just start sitting down and typing in your customers, your products, and so on.
With Access, it's easier to maintain data integrity with validation rules, formatting, things like input masks, and so on, indexing with no duplicates. Excel does have some basic validation rules, but these are harder to enforce.
Collaboration. Access is better for multiple simultaneous users. With Excel, it's not quite so easy to work in teams. Yes, you can share a file on the network, but you really shouldn't edit it at the same time. Excel Online does have some multiple simultaneous user capabilities. So does Google Sheets, in fact. You can see each other editing a spreadsheet at the same time. But again, Access does this much, much better.
Excel is much better at analyzing small amounts of data, whereas Access does lack rich data analysis tools. With Access, you can build a high-quality, long-term application. I've been running my business off the same database, granted with some updates, since 2002, and it's now 2020, so that's 18 years. I'm using basically the same core database.
With Excel, your sheets can get unmanageable as your data needs grow. I've got 40-some thousand customers in my database. An Excel sheet with 40,000 rows is tough to work with. With Access, you can create a custom interface for your users with menus for different groups. You can have an admin menu, you can have a menu for your accounting people, your secretary can have his or her own screen, and so on. So you can custom tailor your Access application for whoever is using it.
You can lock them out of certain areas and give them rights to certain other things, and so on. With Excel, it is possible to create some kind of a menu interface, but it's nowhere near as easy. And it's harder to manage workflow if you're using just a bunch of different Excel sheets.
Data entry and lookup can be made simple. Your developer can set things up, and then your users can just use them. Whereas with Excel, everyone who's using it pretty much has to know how to use Excel.
With Access, simple charting is available, but it's nowhere near as good as with Excel. Excel has much more robust charting options available. In fact, I will often take data out of Access, export what I need, and then import it into Excel, and I'll run my charting in Excel.
With Access, you can do some simple like sales charts. It's got bar charts, line charts, and some simple stuff, but Excel has things like trend lines and stuff that Access just doesn't have. Plus, the formatting options are a whole lot better in Excel charts. I tried to do a pie chart in Access the other day using the new metro charts, and you can't even change the colors of the pie slices yet. They're working on it. They're adding more stuff to it, but right now, Excel is way better with charting.
With Access, your data must be structured. Each field has to have rules. This is a date. This is a yes/no value. This is text, and so on. And I think that's a benefit if you're storing information in a database. With Excel, you can literally put data anywhere you want on the sheet. Now, that might be a benefit to you as well depending on what kind of information you're storing. So this one can go either way.
If you just want to use Excel for a collection of loose notes, and I've done that before. Like I used to do my personal checkbook in an Excel sheet because I could make notes and put stuff wherever I wanted, and that's fine. But eventually, it got big enough where I moved it over to Access.
Alright, I've touched on this one before. Access is better at managing large amounts of data, searching, sorting, and filtering, whereas large sheets can be very cumbersome to work with in Excel. If you've got an Excel sheet that's more than a couple hundred rows, I think that's starting to get unmanageable.
We've talked about the longer setup and development time, whereas with Excel, you can start working immediately. Data in Access is relational. Customers are related to orders, orders are related to products, and so on. In Excel, you don't have that; everything is stored in a flat file spreadsheet. So if you want a list of what products your customers in New York have purchased in the last year, it's so much easier to generate that in Access than it is with Excel data.
In Access, records can have unique auto number IDs. Access automatically maintains numbering your customers and numbering your products, whereas Excel doesn't do that. Access has indexing, so it speeds up searches and sorts. Excel doesn't have indexing. So if you want to take a list of 10,000 customers and sort them by any particular field, last name, first name, whatever, that's a lot faster in Access.
Access has referential integrity, and that means that you can have situations in Excel where you put in an order that doesn't have a customer. You can't do that in Access. You can set up referential integrity so that if an order doesn't have a customer associated with it, you can't enter it in the system.
This is what I'm guilty of a lot. In Access, calculations are bound to fields in queries and are easier to enforce. So when you put calculations in a query in Access, that record is the one that is always calculated on. In Excel, you can have a situation where you put in a formula, and you got the wrong reference in there. So it's referring to a different row. And I do that all the time. I copy and paste stuff, and I forgot the auto-fill, and I'm having my calculations pointing to different records, different rows. That can't happen in Access.
In Access, you can set up parameter queries where the user can type in. Let's say you're looking for a list of customers from a particular state, they can type in the state when you run the query like New York or Florida, and boom, there's your list. You can't easily do that in Excel.
A big benefit of Access is action queries. There's update queries, delete queries, append queries that can change data in huge batches when you import new orders or something like that. With Excel, you can do something similar. You can do mass updates and stuff, but it requires either a macro or some scripting or some VB programming.
I've kind of already talked about this. In Access, complex queries can be saved for novice users to run. So you can set up a query, again, that shows all the products between two dates from customers from a particular state for a particular sales rep. You can put all that in one query and save it, and then Joe the secretary needs to run that report to give it to someone, he just clicks one button and boom, there's the report.
Excel doesn't have something like that available. You have to know how to use Excel and its filtering options to get the data that you want. And it's even harder if it's in multiple spreadsheets.
Now I know a lot of these pros have been on the Access side so far. I'm kind of picking out different features that Access has, but Excel does really have some areas in which it shines. For example, what-if analysis doesn't exist in Access. Data modeling, so much better in Excel. Pivot tables and pivot charts, they used to have pivot tables in Access 2010, but they removed them in 2013.
Because again, pivot tables, it's something better to just take the data that you want to pivot, take it out of Access, just export it and then import it into Excel quickly, and you can run your pivot table then.
You can set up user-level security in Access. Now they don't have user-level security built into Access like they used to have back in like Access 2000, 2003. They removed that. But like I show in my security seminar, it is still possible to set up your own user-level security and lock down your database. It's not built into Access, but there's a way to do it if you really want to take care to make sure that your database is secured.
With Excel, there's simple protection, there's sheet-level protection, and you can put a password on the entire workbook, but security isn't as easy to implement or as robust to implement in Excel as it is in Access.
Now, once you have that security set up, you can audit your user activity. You can see who's doing what, who's deleting records, adding records, and so on, whereas with Excel, that's just not possible.
Access is much better for printing out reports. Any kind of report you can make, you can print it. Mailing labels, different kinds of correspondence, all kinds of stuff. You can print out anything. I can do pretty much anything in Access that you can do in Word, for the most part. With Excel, you can format your sheets to look good when you print them, but it's nowhere near as powerful as Access. Excel is much better with conditional formatting. You've got icons, data bars, color scales.That's where you go from one color to another one, you know, like hot red to light green. Okay. Access has real simple conditional formatting with basic colors. It's useful, but it's not as good as Excel. So if you're looking for data visualization, Excel is where you want to be.
I've actually got this next one backwards. I didn't bold it correctly. Let me fix it real quick. There we go. It's a benefit for Access that only the developer in your office needs a paid copy of Access. If you've got 20 people in your office, only one person, the person who's making the database, who's making design changes, needs a paid copy of Office. Everybody else can run the database using the free runtime edition that you can download from Microsoft's website.
Whereas with Excel, everybody has to have a paid copy of Microsoft Office to use Excel, the desktop Excel. However, to flip that over, you can give everyone a free copy of Excel online if they log on to office.com and set up a Microsoft account. They can use Excel online, which has 80% of the features that people are going to need from Excel right in the free version. Whereas Access has no such free version.
But, you know, Access with a Microsoft 365 subscription is like $12 to $13 a month. It's not expensive, and you only have to pay that for one person in your office or whoever is going to be developing.
Okay, so in summary, to run down these real quick, when to use each. Use Access when you want a more robust user interface, you don't mind taking some more time to learn how to build it, and you've got a little more time for application development. You don't mind spending time doing that.
You've got to store large volumes of data, thousands and thousands and thousands of records. Definitely use Access. You need relationships between your tables. All right, definitely use Access for that. You have employees who aren't computer people who don't really even know the basics of Excel, and you don't want to bother training them. You can build them a bulletproof application in Access they can use to do their work.
If you need multiple simultaneous users, then Access is pretty much your only choice. If you want to be able to import and export or modify large amounts of data automatically with action queries, again, Access. And if you want a variety of different printable reports and labels, Access is your man.
Now use Excel if you don't want to take the time to learn Access. All right, it's got a real small learning curve. You can just sit down and start entering your data. If you've got smaller sets of data to store, feel free to use Excel. I don't take everything that I have and put it into a database right away. If I've got something that I know is going to only be 20, 30 rows of data, sure, I'll throw it in a spreadsheet.
There's no sense taking the time to set up a table and do all that stuff and put it in Access if it's just a small set of data. I keep my list of domain names that I own, for example, in a spreadsheet. I've got like 20 different domains, and I want to make sure that I've got the domain name and the password and when it renews and all that stuff. And that's just in a spreadsheet. I don't need to build a database for that.
If you need the adaptability to put information anywhere on the sheet, use Excel. If you want to be able to put notes in the corner and this and that over there, Excel is just fine for that. Performing any kind of statistical analysis, what-if scenarios data modeling, use Excel.
I will often export information from Access into Excel to do that or for data visualization. Better charting, pivot tables, better conditional formatting.
Okay, so after going through all of that, what's the final verdict on which is better. Which one should I use? Drum roll please. Cue the drum roll. Oh, I don't have a drum roll. Okay. Forget that. All right. So which should I use? Use both.
Of course, they complement each other. They work well together. Excel is quicker and easier to set up initially, but a well-built Access database will definitely save you time and money in the long run. It'll increase your productivity to have a good Access database. Managing large, complex spreadsheets is cumbersome, especially if you need relational data.
Right? If you've only got a couple hundred customers, feel free to keep them in your Excel spreadsheet. As soon as you get up to a couple thousand, you might want to really consider moving them over to Access, especially if you're dealing with situations where each customer might have multiple addresses, multiple phone numbers. Now we're talking relational databases.
I will often start out entering my data in Excel. This gives me a way to get up and running quickly. Then when my data grows to over a few hundred rows, I didn't mean to rhyme that, it's time to start moving over to Access. After you've built your Access database, continue to export your data to Excel for rich charting, pivot tables, what-if analysis, and so on. Each application has its place and they complement each other nicely.
So that is my verdict on which is better and when to use each, Excel versus Access. They each have their own benefits, they have their own drawbacks, and they make a great team for your office data management.
Want to learn more about Access and Excel and how to get them to work together? Well, in the extended cut, I'm going to show you a few tricks.
First, I'm going to show you how to import or link to Excel spreadsheets from Access. That's handy if you've got someone using an Excel sheet somewhere on your network, maybe doing data entry for you. I'll show you how to link to that sheet so you can see what information is in there, and you can even pull it into your database. Then with an append query, you can run a little append query and have it automatically brought into your customer table with one click, well, double click.
Then I'm going to show you how to go the other way. So if you've got someone on your network that is just using Excel and they want to have access to, let's say, your customer list or your itinerary or whatever that you've got stored in Access, you can have it so that they can see that information inside of Excel. And there's a trick to have it so it automatically refreshes when you open up that spreadsheet file, and I'll show you how to do that in the Extended Cut for Members.
---
Access vs Excel comparison
Data storage in Access
Data management with Access
User interface creation in Access
Multiple simultaneous users in Access
Data analysis in Excel
Data visualization in Excel
Advanced charting in Excel
Conditional formatting in Excel
Learning curve for Access
Development time for Access databases
Data integrity and validation in Access
Collaboration with Access vs Excel
Large data handling with Access
Relational databases in Access
Unique auto number IDs in Access
Indexing in Access
Referential integrity in Access
Parameter queries in Access
Action queries in Access
Complex queries in Access
What-if analysis in Excel
Data modeling in Excel
Pivot tables and pivot charts in Excel
User security in Access
Audit user activity in Access
Report printing in Access
Using free runtime edition of Access
Excel online vs desktop Excel
Importing and linking Excel spreadsheets in Access
Exporting Access data to Excel
---
In today's video, we tackle a common question: Access or Excel? Which is better and when should you use each? We'll break down their primary roles. Access excels at data storage, handling large data volumes, and creating user-friendly interfaces for multiple users. Excel shines with smaller datasets, powerful data analysis, and visualizations. We'll discuss their pros and cons, from Access's higher learning curve to Excel's lack of structure for complex data management. By the end, you'll know when to leverage each tool for your business needs. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.
Quiz
Q1. What is the primary function of Microsoft Access?
A. Advanced charting and visualization
B. Data storage and management
C. Creating complex mathematical models
D. Programming and scripting
Q2. For what type of tasks is Excel particularly suited?
A. Handling large volumes of relational data
B. Managing multiple simultaneous users
C. Data analysis and visualization
D. Building robust user interfaces with menus
Q3. What is a significant advantage of using Access for database management?
A. Easier to perform statistical analysis
B. Requires no setup or development time
C. Better data integrity with validation rules and formatting
D. Best choice for managing small sets of data
Q4. Which is a primary drawback of Excel in terms of data management?
A. Requires complex setup before starting
B. Struggles with data analysis and visualization
C. Often lacks structure, making it hard to manage large data sets
D. Has limited support for macros and VBA
Q5. How does Access handle data in a way that Excel does not?
A. By offering advanced charting options
B. Through relational database structure
C. With simpler password protection
D. With more intuitive user interface design
Q6. When is it advisable to use Excel over Access?
A. When dealing with small data sets and needing quick setup
B. To create a multi-user environment for data entry
C. For managing thousands of customer records
D. For generating complex, long-term business applications
Q7. What is a key feature of Access in terms of multi-user collaboration?
A. Shared editing capabilities with automatic updates
B. Free online version available for all users
C. Detailed user-level security and auditing of activities
D. Advanced conditional formatting for data visualization
Q8. Why might someone choose to develop a database in Access despite its longer setup time?
A. Because it requires less training and is more intuitive
B. Because it allows highly tailored user interfaces and enhanced productivity
C. Because it offers more sophisticated charting options
D. Because it's a quicker and easier initial setup
Q9. Which tool is more suitable for generating pivot tables and performing what-if analysis?
A. Access
B. Excel
C. Both are equally suitable
D. Neither tool can perform these tasks
Q10. How can Access and Excel complement each other effectively?
A. Use Access for initial data entry and Excel for data import
B. Use Access for managing relational data and Excel for complex data visualization
C. Use Excel for robust user interfaces and Access for quick setup
D. Use Excel for storing large volumes of data and Access for chart generation
Answers: 1-B; 2-C; 3-C; 4-C; 5-B; 6-A; 7-C; 8-B; 9-B; 10-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.
|
