ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

How to Decide if You Should Use MS Access or MS Excel

Updated on October 4, 2012
Source

As a massive geek who loves creating databases, I know that sometimes it’s easy to decide to use Access when Excel is more appropriate…and I also know that some people aren’t as comfortable in Access, and so they might use Excel, even when Access would help them a lot more. So, how do you decide which is the appropriate program to use to store, access, and manipulate your data?

What do you have to input?

Here’s the big decision to start with – is the data you have relational or not? Not sure? Relational data is data that has a relationship. For example, in Access, you can make a relational database. You can create two tables; one will contain the names and addresses of customers. Another will contain orders that the customers make. This database has a relationship in the data – the customers will appear in the orders, and to be more specific, you have a one-to-many relationship, assuming that one customer can place multiple orders.

In Excel, you cannot create relationships. You have a static table (also called a “flat file”). If one customer makes multiple orders, you may need to have multiple lines for that same customer, each one repeating the information. This gives you a greater chance of having inconsistent data. You can easily change a customer’s address in one line but not in another, making your data unreliable.

How often will it change?

If your data isn’t going to change very often, or not at all, then you may be looking at Excel as a good option. If you’re going to need to constantly pull new information from other sources, do a lot of updates, or maintain a connection with another database, then Access is probably going to be better for your needs. While both of them allow you to easily change and access your data, if you’re going to constantly need to make changes (and potentially also need to keep increasing the size or the file or add more tables), then Access is better. Excel’s main purpose is to make and maintain lists. If that’s not what you want to do, then it might not work for you.

Spreadsheet Example

Source

What do you want as an outcome?

Are you tracking sales figures and need to do a lot of math? Complex formulas? Excel is your buddy! Sure, you can do a lot of the same work in Access, but if your data doesn’t require those relational tables, then why go through the additional work of creating a full database?

You can create queries and run reports from both Access and Excel; however, you have more options in Access because of the relational nature of the database. You can create more complex queries and reports than you can with Excel. But if your reports are going to all be financial or statistical, then Excel might be best. Excel is really meant to deal with numeric data above all else; it is capable of dealing with other data, but it loves its numbers.

Relational Database Example

Source

Who do you have to share it with?

Excel can be shared, but not as easily as Access can be shared. Access lets you build numerous forms, reports, and queries, all of which can be locked down or open, allowing you the control over what data can be seen, shared, and exported. While Excel may also be locked down and its data can also be controlled, it is not as simple to create forms and queries with it, and there is more danger in letting others into your data. Simply put, Access can be made “friendly” to users who are not necessarily comfortable with spreadsheets or other non-WYSIWYG programs.

How much data are you going to track?

Excel is not meant to handle much, about 15,000 rows of data. Sure, that might sound like a lot, but what if you’re working at a university and you want to track all the students who’ve ever applied or attended? You might quickly hit that limit.

Access, on the other hand, can connect to Microsoft Servers and handle a much larger quantity of data. It is not limited, and the use of the relational tables means that you can handle and combine much more data than a simple flat file.

So, overall, while Access is great anytime you want to build a relationship between your data, Excel is there for you when you want to manage money, numbers, or statistics. The choice is yours!

Watch a series of videos on how to create an Access database

Microsoft Excel for Beginners – Overview

Comments

    0 of 8192 characters used
    Post Comment

    No comments yet.

    working

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, hubpages.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://hubpages.com/privacy-policy#gdpr

    Show Details
    Necessary
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Features
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Marketing
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Statistics
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)