Using Excel As A Database

By Author: Haggai Thomas
Total Articles: 1
Comment this article

Access is a great database application, but let's face it - sometimes it's just a "bit"

complicated! There are a lot of times when it would be nice to have some of the capabilities of a database without all the hassles. This is where Excel really comes in handy!

If you design your Excel workbook correctly, you can use Excel to look at your data in many of the same ways you would use a database application - you can sort data in many different ways, filter it so you see only data that matches specific criteria, and you can reorganize your data in any way you want it "on the fly" as your needs change.

To use an Excel workbook as a database, your data needs to be structured in the list format. The techniques we'll talk about in this session won't work on data that's not structured as an Excel list, but don't worry - this is not hard to do!

An Excel list consists of columns and rows of data structured in a specific way. Data can be sorted alphabetically or numerically in Excel using the Quick Sort buttons on the Standard Toolbar. Sorts can be in Ascending (A-Z) or Descending (Z-A) order.

If ...
... your data wasn't originally sorted alphabetically or numerically, once you've performed a sort on the data you won't be able to return the list to its original order; however, you CAN add an additional column to your list, and number each row sequentially so that you can resort that column numerically.

When your worksheet is structured as an Excel list, you can use the Data command to filter items that meet criteria you specify. When you've filtered a list, rows of data that don't match your criteria are temporarily hidden from view, but not deleted, making it easier to find exactly what you're looking for and do different kinds of data analysis.

There are two methods of filtering an Excel list - AutoFilter and Advanced Filter. AutoFilter is easier to use than the Advanced Filter method, but the AutoFilter method doesn't always have the power you need. Try AutoFiltering first - if that doesn't meet your filter needs, you can always remove the AutoFilter, and try building an Advanced filter.

You can also use the Form feature to quickly locate a specific set of data by clicking the Criteria button, then specifying the criteria to search for in the appropriate field. When a set of data matches your criteria, that data is displayed in the form window.

Now that you've seen how useful and powerful an Excel list can be, you may want to structure all of your Excel worksheets as lists - it can be quite a time-saver when you need it!

We offer excel manuals to help you understand the 31 most important excel formulas. Understand excel fast and easy. Visit us: http://www.31bestofexcel.com .