Tip of the Week: Creating a Database in Excel

When trying to keep a lot of data organized
concisely, while still keeping it all accessible when needed, a database is an
excellent tool to leverage. Furthermore, it can be fairly simple to generate
one of your own by using Microsoft Excel. We’ll offer a few steps and tips to
ensure you get the most out of this capability.

Step One: Data Entry

Naturally, in order to create a database, you
need data to fill it. However, you also need to make sure that you enter this
data into Excel correctly. If you want your database to have a title, the only
blank row in the document should be between the title of the document and the
data that is to be included. You also can’t have any empty cells, so you will
need to decide upon a standardized placeholder for your database to use. Blank
cells will cause issues with the function of your database - including the
labels for your records and fields.

Records and Fields

In order to properly compile your database,
you should devote a row to each record, each column providing a field to input
a specific piece of data.

  • Each record should detail an
    individual item that is organized in the database. Whatever it is you are
    organizing, whether it’s your equipment, each of your individual employees,
    what have you, each unit should have its own record.

  • Your fields should provide details
    for each record in your database, providing more in-depth information into each
    item. Each field should detail a specific variable, allowing separate items to
    be differentiated more easily.

In order for your database to be effective,
you need to be sure that your data is entered consistently - including the
format in which it is recorded. In other words, don’t start by writing out
numbers and end up writing them as digits. Furthermore, you need to be sure
that your records are as complete as they can be, the same variables identified
for each.

Once you have a way to organize this data,
commit to it. This will be easier if you establish a workable pattern quickly,
which may require some trial and error.

Step Two: Creating a Table

Now that you have your data and your
organization planned out, you’re ready to incorporate it into a workable
format. You’ll want to make sure all of your data is highlighted, except your optional title and placeholder space that
keeps the title from being mixed up with your data. Access the Home tab, and from there, select the
table you want to use by clicking Format
as Table
. Your field titles will have drop-down boxes added, which will
allow you to sort your data as you please. With this, you’ve created the
beginnings of a database!

Step Three: Putting Your Database
to Good Use

To continue adding to your database, all you
have to do is expand your table with the integrated click-and-drag
functionality that Excel includes. Hover
over the corner at the bottom-right of your table - a small dot should be there
to help you. Once there, your cursor should indicate your ability to change the
table by appearing as a double-ended arrow. Clicking and dragging will allow
you to add the additional records (or rows, as you might remember) that you
need to the table. All that’s left is to input the added data into the
appropriate fields.

If you find your database harder to read as it
continues to expand, Microsoft Excel offers a function to help with that, too.
You are able to filter your table based on the data that you need to see,
hiding any records that don’t apply to what you’re looking for. Take note, this
only hides the records… clearing your filters will allow them to once again

Filters can be used by using the drop-down arrow on the category that you want to base the
filter on. You’ll be given a few options, complete with each of that column’s
contents and a search bar to find any not displayed. Deselect the (Select All) option and select the specific option
that you want to view. To return to the complete view again, simply select the Clear Filter from option.

Granted, this database is very basic, but it
should do the trick. What are some other programs that you want to learn some
tips for? Let us know in the comments!