Access Programmer Solutions That Save You Time & Money!

AccessProgrammer.org – Access vs. Excel

Confused About When To Use Microsoft Access or Excel?

You’re not alone! The experts at Access Programmer can help you make an informed decision. On the surface, both programs look alike. They both store data and you can enter the data in a grid of cells.

So how do you choose which one to use? One way to decide is to answer a few simple questions about how you want to organize your data, and about related issues such as the amount of data you want to store and manage. Your answers to those questions can help you get more done in less time, so keep an open mind.

Access – Relational Data Structure

If you’re having trouble choosing between Access and Excel, take a moment to answer an important question: Do you need to organize your data into a relational data structure or a flat data structure? Yes, those are geek terms, but don’t let them intimidate you. Deciding on a structure isn’t hard, and the next sections walk you through each type of data structure and show you how to choose between the two.

For now, just keep in mind that Access is designed to work with relational data, while Excel works best with flat data structures.

A relational data structure divides your information into logical pieces and places each piece in a separate table.

For example, a sales database typically puts information about customers — their names, addresses, and other key facts — in one table, and information about what those customers buy in another table.

Organizing your data that way can make it quite powerful.

A Relational Data Structure Has Several Advantages:

• It lets you answer important business questions, such as who bought the most (or least) of your product last week. The figure above shows you how this can work.
• What’s more, using separate tables can make your data easier to manage, because each table holds just part of your information.
• And finally, a relational structure helps keep your information accurate, because you can prevent users from entering data in the wrong table.

• In contrast to a relational data structure, a flat data structure is a simple list that isn’t related to other data. For example, a grocery list is a flat file, and so is a list of your friends and relatives. Flat data structures are easy to create, and they’re also easy to maintain, as long as you don’t have too much information.
• Simple lists work nicely in Excel. In fact, Excel is designed to create and maintain flat files.
• In case you’re wondering, each table in a relational structure is also a flat file. For example, in a typical sales database, one table holds a list of customers, and the other holds a list of orders. The only real difference between the types of structures is that with flat files, you don’t need to relate the data in one list to the data in another. Each list is useful by itself.

So how can you tell if you need a relational structure? By answering a few more questions:

• First, do you have a lot of repeated data? For example, do you constantly enter the names of cities or states? If so, you can put that repeated data into another table and create a relational structure. Doing so can save you time and effort because you don’t have to reenter the same information each time you create a new record.
• Second, do you want to track actions or events? For example, do you want to track sales or customer complaints? Any time you want to track an action, a relational data structure usually works best.

Excel Advantages

If you find yourself answering no to those types of questions, or if you only have a small amount of data and you don’t mind looking at repeated information, then feel free to use a flat structure.

While data structures are important, they aren’t the only reasons to choose Access or Excel. A few more questions can help you decide between the two programs:

• First, do you need to store and manage your data, or do you need to analyze that data? If storage is your primary goal, then use Access, but for analysis, use Excel.
• Second, do you have a lot of data? For example, is your Excel worksheet so large that it’s hard to use? If so, even if you have flat data, Access can make your information easier to find.
• Is your data mostly text, or is it numeric? Access can store a large amount of text — up to two gigabytes in a single database — while Excel is designed to store numbers and perform sophisticated calculations on them.

So here are a few more reasons to use Access:

• Do you need to help users enter data? For example, some users find it hard to enter data in a grid of cells. With Access, you can work around that problem by creating data entry forms that make it easier to enter data accurately.
• Do your users need reports? With Access, you can create reports that users can run at any time.
• Access is also a better choice when you need to have multiple users working on the data at the same time. Several users can work on the same data, because Access locks a record only while a user changes it. You can share an Excel workbook with several users, but the process works best when users change data in Excel at separate times.
• If you need to connect to several data sources and edit the data directly in those sources, then Access is your choice. You can view external data with Excel, but you can’t change that data.

• Analyses and numbers? Choose Excel.
• So where does Excel shine? With numbers! You can run sophisticated what-if models and cost-benefit analyses that you can’t in Access.
• Do you need PivotTable reports? These are interactive tables that allow you to rotate rows and columns and see different summaries of your data. Excel makes it easier to work with PivotTable reports and PivotChart reports — charts based on those tables.
• Do you want to convey information visually, by using charts or data bars? Excel is your tool.
• Finally, not everyone has Access, and not everyone knows how to use it. Your coworkers may prefer Excel.
• Those are just a few of the reasons for using Access or Excel.