Guide: Create a Database in Excel

An Excel database is perfect for organizing — and clearly seeing — financial data. A database includes a series of series of records in rows with fields of data entered in columns.

Here are three steps to use Excel as a database:

1. Get Started by Organizing Data

To use Excel as a database, your data must be structured in a specific way:

• each column has to contain the same category of data in every row of that column, and
• each row must contain all of the data for the same specific thing — person, group, organization, object, et al.

To indicate this thing that the row represents, the first column of each row must contain a unique name. The top row must contain a unique column heading for each column to indicate which data is stored in that column. This top row containing the column headings should be formatted differently than the rest of the data to make the headings stand out.

If you have a long database, you can freeze that top row, so that you can always see the column headings even as you scroll down. To do this, go to the “View” tab and click on “Freeze Panes.” From there, click on “Freeze Top Row.” When entering your data, make sure to keep your data formatted in the same way throughout your spreadsheet.

You also want to make sure that you didn’t leave any rows blank. You can have blank cells throughout the database, but you can’t have blank rows or it will screw up your data when you try to sort or filter it.

2. Sort Data and Remove Duplicates

To sort your data, go to the “Data” tab and click on the “Sort” button. When the dialog box pops up, choose which column you want the data to be sorted by. Then choose what you want the sorting to be based upon (examples: values, cell font, cell color, etc.). And lastly, choose how you want the data to be sorted, such as smallest to largest.

To remove duplicates in your database, select the database area, go to the “Data” tab, and under “Data Tools” click on “Remove Duplicates.” A dialog box will appear and prompt you to mark the columns that you want to examine for duplicate values. The duplicates will be removed and the dialog box will display the results.

Be carfeful: This command can’t be undone, so make sure you select the correct columns to examine for duplicate values. To verify that you are selecting the correct columns, you can highlight the duplicate values by going to “Conditional Formatting.” Then select “Highlight Cell Rules” and select “Duplicate Values.” The duplicate values will be highlighted and you can verify that those are the values that you want removed.

3. Use AutoFilter to Focus In On Data

Excel’s AutoFilter feature provides a partial list of data based on a certain criteria. To set up this filter, go to the “Home” tab and click on “Sort & Filter.” You can also go to the “Data” tab and click on “Sort & Filter” and select “Filter.”

Then you’ll be able to use the drop-down arrow to select a subgroup of records that you want shown. To do this, remove the checkmark from “Select All” and check only the items you want to display. To clear the filter, click on “Select All” from the drop-down list or click on “Clear Filter.”

For more helpful Excel tips, check out learnexcel.now