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.
For a complete video tutorial on this topic, please visit https://599cd.com/AccessvExcel?key=Blogger
No comments:
Post a Comment