Using Access or Excel to manage your data

Microsoft Office Access and Microsoft Office Excel possess many similarities, which can make it difficult to decide which program you should use. For example, both programs can store large amounts of data, run powerful queries and analysis tools to slice and dice that data, and perform sophisticated calculations that return the data that you need.

However, each program has clear advantages — depending on the type of data that you are managing and what you want to do with that data. For example, if it is your goal to maintain data integrity in a format that can be accessed by multiple users, Access is your best choice, whereas Excel is better suited for complex numerical data that you want to analyze in depth.

“If you store your data by using Access and analyze it by using Excel, you can gain the benefits of both programs.”

In many cases, you can use both programs, employing each for the purpose to which it is best suited. In general, Access is better for managing data: helping you keep it organized, easy to search, and available to multiple simultaneous users. Excel is generally better for analyzing data: performing complex calculations, exploring possible outcomes, and producing high quality charts. If you store your data by using Access and analyze it by using Excel, you can gain the benefits of both programs.

Before you decide which program to use, you may want to compare the benefits of each program, learn when it is best to use one or the other, and find out how to work with both programs to achieve exactly the results that you want.

 Note   All 2007 Microsoft Office suites include Office Excel 2007, but not all suites include Office Access 2007.

ShowHow to obtain the Microsoft Office System programs that are not installed on your computer

The following 2007 Microsoft Office suites include Office Access 2007:

  • Microsoft Office Professional 2007
  • Microsoft Office Professional Plus 2007
  • Microsoft Office Ultimate 2007
  • Microsoft Office Enterprise 2007

You can also purchase Office Access 2007 and Office Excel 2007 separately.


In this article


Comparing the benefits of each program

Choosing the right program is critical if you want to access and update your information with maximum performance and accuracy. To find out which program is best suited for the tasks that you want to accomplish, it may help to compare the benefits that each program has to offer regarding data storage, data analysis, multi-user collaboration, and security.

Data Storage

Flat versus relational data    To help decide which program is best for storing your data, ask yourself the following question: is the data relational or not? Data that can be efficiently contained in a single table or worksheet is called flat or nonrelational data. For example, if you want to create a simple list of customers, with only one address and contact person for each customer, Excel might be the better choice. However, if you want to store a more complex customer list that contains billing and shipping addresses for each customer, or multiple contact persons for each customer, Access is the better solution.

In a relational database (relational database: A type of database that stores information in tables. Uses matching values from two tables to relate data in one table to data in the other table. In a relational database, you typically store a specific type of data just once.), you organize your information into multiple tables. In a well designed relational database, each table is flat and contains information about only one type of data. For example, if you create a customer database, the names of the customers should be stored in one table, whereas those customers' billing and shipping addresses should be stored in a separate table. Storing addresses separately from names is a good idea because each customer can have more than one address, and you want to be able to enter multiple addresses for each customer without having to re-enter the customer name for each address.

Find a link to more information about database design basics in the See Also section.

Local versus external data    You can use Access to connect to data from a variety of external data sources so that you can view, query, and edit that data without having to import it. For example, Access provides commands to connect to existing data in a Microsoft SQL Server database, a dBASE file, or a Microsoft Office Outlook folder, along with many other data sources. You can use Excel to connect to a wide variety of data sources including Access, SQL Server and Analysis Services databases, text and XML files, and ODBC and OLE DB data sources. However, you cannot edit the data to change the source data through the Excel user interface.

Both Office Access 2007 and Office Excel 2007 provide commands to connect to data in Windows SharePoint Services lists. However, Excel 2007 provides just a read-only connection to SharePoint lists; whereas Access 2007 lets you read from and write data to SharePoint lists.

Data integrity versus flexibility    Unique identifiers help preserve the integrity of your data, and they ensure that no two rows (or records) contain exactly the same data. Unique identifiers also provide the quickest way to retrieve data when you search on or sort your data. In Access, you can use the AutoNumber data type to automatically generate a unique identifier for each record. You can then use these identifiers to relate records in one table to one or more records in another table.

The structure that Access applies to your data helps ensure data integrity. Access can require that new records in one table have an existing corresponding value in a different table, so that you cannot create "orphan" records. For example, you would not want to have an order that did not include customer information. Access can require that every new record in your Orders table has a corresponding customer value in your Customers table. This required correspondence of values is called referential integrity (referential integrity: Rules that you follow to preserve the defined relationships between tables when you add, update, or delete records.).

You can also impose your own constraints and rules to further ensure that data is entered correctly. Excel lets you enter data in a more free-form manner, but because Excel does not support relational data, it cannot support referential integrity. However, you can control data entry in Excel by using the Data Validation command.

Data analysis

Querying    If you often have to view your data in a variety of ways, depending on changing conditions or events, Access might be the better choice for storing and working with your data. Access lets you use Structured Query Language (SQL) (Structured Query Language (SQL): A database query and programming language widely used for accessing, querying, updating, and managing data in relational database systems.) queries to quickly retrieve just the rows and columns of data that you want, whether the data is contained in one table or many tables. You can also use expressions in queries to create calculated fields. Using an expression in Access is similar to the process of using formulas in Excel to calculate values. You can also use Access queries to summarize data and to present aggregate values, such as sums, averages, and counts.

Modeling    In Excel, you can use what-if analysis (what-if analysis: A process of changing the values in cells to see how those changes affect the outcome of formulas on the worksheet. For example, varying the interest rate that is used in an amortization table to determine the amount of the payments.) tools to forecast the outcome of a worksheet model. What-if analysis allows you to run different scenarios on your data, such as best case and worst case scenarios, and compare the resulting data of several scenarios in a summary report. No similar feature is available in Access.

Pivoting and charting    In both programs, you can create PivotTable reports and PivotTable charts. However, Office Excel 2007 provides more advanced PivotTable reporting and charting features than Office Access 2007. If you plan to create extensive PivotTable reports or provide professional looking charts regularly, you should use PivotTable reporting or PivotTable charting in Excel 2007 instead of the same features in Access 2007.

Multi-user collaboration

Both Access and Excel can be used in collaborative environments, such as Windows SharePoint Services and network file shares, but there are differences in the way the data can be accessed by multiple users.

Multiple user access to data    Under normal operation, Access lets multiple users open a single database at the same time; this works well because Access locks only the data that is being edited; as a result, other users can edit different records without conflicts. In Excel, you can share a workbook with other users, but multi-user collaboration functions best when users work on the data in that workbook at different times instead of simultaneously. In effect, users of an Access database collaborate on a set of data, and users of an Excel workbook collaborate on a document.

Collaboration by using Windows SharePoint Services    Both programs integrate with Microsoft Windows SharePoint Services technologies, such as SharePoint lists and document libraries.

Access provides a variety of ways to collaborate with multiple users on a SharePoint site. For example, you can upload a full database to a Windows SharePoint Services document library, make forms and reports available as Windows SharePoint Services views, and link a database to data that is stored in SharePoint lists.

Excel provides only one way to collaborate with multiple users on a SharePoint Services site. You can upload a workbook to Windows SharePoint Services document libraries, where individual users can check out the workbook to make changes, preventing other users from modifying the workbook at the same time. Users can edit a workbook without checking it out of the document library, in which case they must coordinate with other users to avoid data conflicts.

Collaboration by using shared network folders    If you store an Access database in a shared network folder, multiple users can open the database and work with its data simultaneously. Individual records are locked when a user edits them. If you store an Excel workbook in a shared network folder, only one user can edit the workbook at a time. For viewing purposes, multiple users can open the workbook while another user is editing it but those users cannot make any changes to the data until the user who is editing the workbook closes it.

Security

Both programs provide similar features — passwords and encryption — that can help you prevent data loss and protect your data from unauthorized access. However, there are some differences between Access and Excel in how user-level data protection works.

Data loss prevention    In Access, your work is continuously saved so that, in the event of an unexpected failure, you are unlikely to lose much work (if any). However, because Access saves your work continuously, it is also possible for you to make changes that you later decide you did not want to commit. To ensure that you can restore your database to the way you want, you should create a backup copy of the database file on a schedule that fits your needs. You can recover an entire database from a backup, or you can restore just the table or other database object that you need. If you use a file system backup utility, you can also use a copy of a database from a file system backup to restore your data. In Excel, you can save AutoRecover information at set intervals while you update your data.

User-level data protection    In Excel, you can remove critical or private data from view by hiding columns and rows of data, and then protect the whole worksheet to control user access to the hidden data. In addition to protecting a worksheet and its elements, you can also lock and unlock cells in a worksheet to prevent other users from unintentionally modifying important data.

File-level security    At the file level, you can use encryption (encryption: The process of converting readable data into a scrambled form (cipher text) to prevent it from being read and understood by an unauthorized party.) in both programs to prevent unauthorized users from seeing the data. You can also require that a password (password: A sequence of characters needed to access computer systems, files, and Internet services. Strong passwords combine uppercase and lowercase letters, numbers, and symbols.) be entered to open a database file or workbook. In addition, you can help secure a database file or workbook by employing a digital signature (digital signature: An electronic, encryption-based, secure stamp of authentication on a macro or document. This signature confirms that the macro or document originated from the signer and has not been altered.).

Restricted access to data    In Excel, you can specify user-based permissions to access the data or set read-only rights that prevent other users from making changes to the data that they have access to. Office Access 2007 does not provide user-level security features, but Access 2007 does support the user security model of any database server that it connects to. For example, if you link to a SharePoint list, Access 2007 heeds the user permissions for the SharePoint list. If you want to keep unauthorized users out of your Access data, you can encrypt your database by setting a password. Users must enter the password to read data from the database, even if they access it by using another program, such as Excel.

For more information about how to help protect your data, see Help secure an Access 2007 database and Protect worksheet or workbook elements.

Comparing Access and Excel

For a quick comparison to help you decide which product to choose, you can use the following table.

Legend:     Full support icon = Recommended Partial support icon = Acceptable No support icon = Doesn't Apply

Feature Access Excel Comments
Manage data
Amount of storage Full support icon Full support icon Both Office Access 2007 and Office Excel 2007 can work with millions of rows or records.
Variety of data types Full support icon Full support icon Both work well with text, numbers, dates, and other common data types.
Data validation Full support icon Partial support icon Both enable you to control data input. Access gives you more control and flexibility, such as specifying data input masking.
Security Full support icon Full support icon Both control user access to your data by using a variety of security features, such as encryption, passwords, digital signatures, and data protection.
Rich text and objects Full support icon No support icon Office Access 2007 has several useful features for working with rich text and for storing large objects, such as documents and images.
Multiple tables with relationships Full support icon No support icon Access provides tools for creating one-to-many (one-to-many relationship: An association between two tables in which the primary key value of each record in the primary table corresponds to the value in the matching field or fields of many records in the related table.) and many-to-many (many-to-many relationship: An association between two tables in which one record in either table can relate to many records in the other table. To establish one, create a third table and add the primary key fields from the other two tables to this table.) relationships between tables. You can establish rules based on these relationships to ensure the integrity of your data.
Data sharing Full support icon Partial support icon Access enables many people to work simultaneously with the same data and provides robust options for updating the data, such as locking and conflict resolution.
Gather and consolidate disparate data Full support icon Partial support icon
Import, export, and publish
  • Import and export data.
Full support icon Full support icon Both products import data from and export data to a variety of external data sources, such as text, XML, and other database and spreadsheet formats.
  • Connect to external data so that you can view, query, and edit that data.
Full support icon Partial support icon Access enables you to connect to external data sources by using linked tables, and can act as a friendly user interface to SQL Server by using Microsoft Access projects (Microsoft Access project: An Access file that connects to a Microsoft SQL Server database and is used to create client/server applications. A project file doesn't contain any data or data-definition-based objects, such as tables and views.).
  • Publish to Web pages
Full support icon Full support icon You can use both Access and Excel to create static and dynamic Web pages. In Excel, you can create dynamic, interactive Web pages by using Excel Services. You can use Access to read, create, update, and resolve conflicts with SharePoint lists.
Leverage SharePoint lists Full support icon No support icon
Use Forms and reports
Export to PDF and XPS format Full support icon Full support icon
Create and use reports Full support icon Full support icon You can use Access to quickly create reports that filter, sort, group, and summarize data the way you want.
Create and use forms Full support icon Partial support icon In Access, you can create forms that make it easier to add, change, delete, and navigate your data.
Create a mail merge to mass produce address labels Full support icon No support icon
Query data
Easily sort and filter data Full support icon Full support icon
Create different views and complex queries Full support icon Partial support icon
Visualize data
Use SmartArt graphics No support icon Full support icon
Create charts Partial support icon Full support icon
Conditionally format data Partial support icon Full support icon
Calculate data
Perform what-if analysis No support icon Full support icon
Use functions and formulas Partial support icon Full support icon
Use PivotTable views and PivotChart views to work with your data interactively. Partial support icon Full support icon
Run sophisticated calculations to derive the information that you want. Partial support icon Full support icon Use Excel if you primarily run calculations and statistical comparisons on your data. For example, you can show a cost/benefit analysis in your company's budget. Excel is frequently the best choice for performing calculations on data, especially if that data is mostly numeric; for example, if you want to maintain a financial budget for a given year.

Top of Page Top of Page

When to use Access

In very general terms, Access is the best choice when you have to track and record data regularly, and then display, export, or print subsets of that data. Access forms provide a more convenient interface than an Excel worksheet for working with your data. You can use Access to automate frequently performed actions, and Access reports let you summarize data in printed or electronic form. Access provides more structure for your data; for example, you can control what types of data can be entered, what values can be entered, and you can specify how data in one table is related to data in other tables. This structure helps you ensure that only the correct types of data are entered.

Access stores data in tables that look much the same as worksheets — but Access tables are designed for complex querying in relation to data stored in other tables.

Use Access when you:

  • Anticipate many people working in the database and you want robust options that safely handle updates to your data, such as record locking and conflict resolution.
  • Anticipate the need to add more tables to a data set that originated as a flat or nonrelational table.
  • Want to run complex queries.
  • Want to produce a variety of reports or mailing labels.

Common scenarios for using Access

  • Managing contacts    You can manage your contacts and mailing addresses, and then create reports in Access or merge the data with Microsoft Office Word to print form letters, envelopes, or mailing labels.
  • Inventory and Asset tracking    You can create an inventory of items in your home or business, and store photos or other related documents along with the data.
  • Order tracking    You can enter information about products, customers, and orders, and then create reports that show sales by employee, region, time period, or some other value.
  • Task tracking    You can track tasks for a group of people, and enter new tasks at the same time others are updating their existing tasks in the same database.
  • Organizing lending libraries    You can use Access to store data about your books and CDs, and keep track of whom you have lent them to.
  • Event Planning    You can enter information about event dates, locations, and participants, and then print schedules or summaries about the events.
  • Nutrition tracking    Keep track of recipes, and log diet and exercise activities.

For useful Access templates in any one of these categories, see Templates for Access on Microsoft Office Online.

Top of Page Top of Page

When to use Excel

As a spreadsheet program, Excel can store large amounts of data in workbooks that contain one or more worksheets (worksheet: The primary document that you use in Excel to store and work with data. Also called a spreadsheet. A worksheet consists of cells that are organized into columns and rows; a worksheet is always stored in a workbook.). However, instead of serving as a database management system, such as Access, Excel is optimized for data analysis and calculation. You can use this flexible program to build models for analyzing data, write simple and complex formulas to perform calculation on that data, pivot the data any way that you want, and present data in a variety of professional looking charts.

Use Excel when you:

Common scenarios for using Excel

  • Accounting    You can use the powerful calculation features of Excel in many financial accounting statements — for example, a cash flow statement, income statement, or profit and loss statement.
  • Budgeting    Whether your needs are personal or business related, you can create any type of budget in Excel — for example, a marketing budget plan, an event budget, or a retirement budget.
  • Billing and sales    Excel is also useful for managing billing and sales data, and you can easily create the forms that you need — for example, sales invoices, packing slips, or purchase orders.
  • Reporting    You can create various types of reports in Excel that reflect your data analysis or summarize your data — for example, reports that measure project performance, forecast data, summarize data, or present variance data.
  • Planning    Excel is a great tool for creating professional plans or useful planners — for example, a weekly class plan, a marketing research plan, a year-end tax plan, or planners that help you plan weekly meals, parties, or vacations.
  • Tracking    You can use Excel to keep track of data in a time sheet or list — for example, a time sheet for tracking work, or an inventory list that keeps track of equipment.
  • Using calendars    Because of its grid-like nature, Excel lends itself well to creating any type of calendar — for example, an academic calendar to keep track of activities during the school year, or a fiscal year calendar to track business events and milestones.

For useful Excel templates in any one of these categories, see Templates for Excel on Microsoft Office Online.

Top of Page Top of Page

Using Access and Excel in conjunction

There may be times that you would want to take advantage of the benefits that both programs have to offer. For example, you created a worksheet in Excel in which you can calculate and analyze the data, but the worksheet has become too large and complex, and many other users need to have access to the data. At this point, you might want to import or link your worksheet into Access and use it as a database instead of working with it in Excel. Or, perhaps you have data in an Access database for which you want to create some detailed Excel PivotTable reports and professional looking Excel charts.

No matter which program you use first, you can always transfer the data from one program to the other, where you can continue to work with it. With or without a data connection, you can bring data into Access from Excel (and vice versa) by copying, importing, or exporting it.

For more information about how to exchange data between both programs, see Exchange (copy, import, export) data between Excel and Access.

Top of Page Top of Page

 
 
Applies to:
Access 2007, Excel 2007