A spreadsheet can work as a flat file database IF one row holds all the data for one record.
Other rows on the worksheet can be used for text or calculations or other data, but each database record must have its fields in a single row.
Limits: A spreadsheet has a maximum number of rows and columns. Excel 2007, 2010, 2013, and 2016 allow 1,048,576 rows by 16,384 columns which is a lot more than the 65,536 rows and 256 columns allowed in Excel 2003. Either one sounds like a lot! But, Excel will get rather slow long before reaching the maximum number of records.
Some programs, including Excel 2007, 2010, 2013, and 2016, call such a database a table. Earlier versions of Excel called it a list.
Your spreadsheet program probably will not treat the data as a database until you tell it to do so.
We will now look at the example Excel spreadsheet to see how a spreadsheet database can work. The illustrations and directions are for Excel 2010. Older versions of Excel work differently, as do other spreadsheet programs.
This page will assume that you are already familiar with the style of interface that uses ribbons, which are used in Excel 2007, 2010, 2013, and 2016.
An Excel table has some handy features. If you have some experience with Excel, you will appreciate the changes.
You can sort the table based on values in only one column at a time. Pick alphabetical or numerical order (Sort Smallest to Largest) or reverse order (Sort Largest to Smallest). Any cells on the sheet that are NOT in the table will not be sorted.
How to tell that data is sorted:
All of the fields in a record in the table move together. We don't want to move some fields and leave behind others from that same record!
Only the cells you included into the table move. Data in other cells on the worksheet or even other tables on the same worksheet will not be affected by what you do to the active table.
Example: Choosing Sort Smallest to Largest from the menu for the column YearMade will reorder the rows in the table so that the values in this column are in numerical order.
multiple columns with Sort dialog:
Use the Sort button on the Data ribbon tab to open the Sort dialog to do a custom sort using multiple columns. You can sort with up to 64 columns! The table column headings can only sort on one column at a time.
You can filter the records to show just certain records, by choosing one or more values from the menu of options.
For example, in the menu of options for Year Made, if you click on (Select All) to deselect the choices and then click on 1999, only the records that have 1999 in this column will show. But the others are not really gone! They are just hiding.
How to tell data is filtered:
The Status Bar shows a message like "6 of 16 records found" or "Filter Mode".
Filters add together: After you have filtered once, choosing a value from another column's list of values does not remove the previous filter. It filters the current, previously filtered records. You are filtering what has already been filtered.
Marking what's included in the table
Formatting from a table style.
Most table styles show alternating row background colors and a thin border around the table.
Table Tools: Design ribbon tab
The Table Tools: Design ribbon tab appears when the table is active because a table cell is selected. However, the tab does not automatically become the active tab.
Convert to Range button changes your table back
to normal cells.
Show a Total Row: Check or uncheck the box to show or hide a row for column Totals.
The 'total' does not have to be a sum. You can also average or count or use any other Excel function. Each cell in the Total row shows an arrow for this list when the cell is selected.
Would you like to see a spreadsheet database at work?
If you have installed software that will open an Excel spreadsheet, click the image below to open the example spreadsheet. Depending on your browser and security software settings, you will may need to save the file first. Then you can open the file in an appropriate program.
Experiment! Play around with it. We will not explore Excel
in depth here but it is good to see what can be done with a plain spreadsheet.
Try the following activities in the spreadsheet:
(The directions below are also on the sheet named Experiment in the spreadsheet.)
Remove all the filters: On the Data ribbon tab, click the Clear Filters button .
[The sum of Price Paid column does not change! The prices that were hidden by the filter are still text.]
Note: If you click the Filter button on the Data ribbon tab, the filters are all removed and the arrows in the headings vanish, too. The cells are still in a table and any sorting you have done remains.
How easy is it to work with a spreadsheet database?
Is it easy to create a new record?
To view a whole record?
If you try to work with a spreadsheet as a database for very long or with one that has a large number of fields, you will want to be able to do things that are hard or impossible to do.
Forms: Excel 2007, 2010, 2013 and 2016 allow you to create, view, and edit records using a form. This form puts all of the fields for one record in view at once as a single column of text boxes. Too many fields means that Excel will refuse to create the form. I was able to get a form that had 31 fields. You cannot edit the form's layout at all. The scroll bar moves you through the various records.
Unhappily, the command for a form is not on any of the default ribbon tabs. You can add it to the Quick Access toolbar from the Commands Not in the Ribbon. In Excel 2010, 2013, and 2016 you can also add the command to a custom tab group or a custom ribbon tab. Apparently Microsoft found that forms for spreadsheet tables were not being used by very many people.
Reports: If you wanted to print a neat report from a spreadsheet database, you have to export the data to another program. This is not a priority for spreadsheets!
Queries: It is common to want to filter and sort your record in a fairly complex way based on comparing fields to each other or to particular values. Excel cannot handle these easily.