Making the most out of your Microsoft Office suite depends on understanding all of your options for storing information. Both Microsoft Excel and Access provide datasheets with rows and columns which allow you to store, sort and format records. Excel spreadsheets contain powerful tools for storing information and using formulas. Access tables are the foundations of a database and provide a starting point for inputting and viewing information. Both programs provide many of the same basic features.
Video of the Day
Microsoft Excel spreadsheets and Access tables both provide a format to store information in a single set or in multiple sets. In Excel, multiple sets of data can be placed in separate tabs of the same Excel file. Similarly, you can separate data into multiple Access tables in an Access database. Excel spreadsheets and Access tables have the same recognizable features, including cells organized in columns and rows, column headings, and row selections.
Sort and Filter
Excel spreadsheets and Access tables allow you to customize the way your information is listed. You can sort alphabetically or numerically by one or more columns. For example, you can sort a product listing by product ID number, then by name and then by distributor in both programs. Also, you can view a limited amount of information by filtering your table or spreadsheet. This doesn't remove any records, but it temporarily displays records based on criteria.
You can set specific data types for cells and columns in both Excel spreadsheets and Access tables. Setting the type of data improves the readability of your data set, as the user can distinguish different types of information. The default data type in both programs is text, which doesn't impose any particular formatting. But you may want a column listed "Cost" to contain currency-based numbers. If you set the data type to currency, the appropriate format is displayed.
Text and Cell Formats
Your Excel spreadsheets and Access tables will benefit from custom text and cell formatting. In both programs, you can set text formatting, cell height and column width. You can also set the text alignment to the left, center or right. Grid lines can help distinguish cells from each other and can be set to be displayed vertically, horizontally or both. Customizing these formats improves the usability of spreadsheets and tables and provides a professional appearance to your spreadsheet.