Using Excel As A Database

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!

by: Haggai Thomas

POST COMMENT

The Data Is In: Anti-tumor Activity Of Two Essential Oils Just what to Examine for in a Hollywood Rehab and Substance Recovery Center Top 3 Reasons Why Data Governance is Essential Restoring data from corrupt DBX files Never lose your important data with SIM backup Recovering data from inconsistent Zip file Rfid Driven Database Tracks Contamination Possibilities My 911, A Perfect System For Abduction Prevention And Recovery Assistance Commercial Fuel Cards Database 2010: Portugal What Data Cabling Provider In Toronto Offers? How To Recover Data From A Mac Os X System Crash? Benefits Of Genuine Data Entry Work Damaged System And Data Files Cause Data Loss In Mac Os X