Storing Lists in Excel

Using Excel to Store Lists

Working with Excel as a database is a very common use for the application – the tabular nature of the worksheets makes it an ideal tool for storing lists of customers, vendors, students, contacts, products, payments, transactions, inventory – just about anything.

When you store such a list in an Excel worksheet, you’re creating what’s known as a flat file database, in that it’s one table of data with no related tables. There may be other worksheets that you work with as though they’re related, and you may have formulas or functions in place that create connections between data in two or more sheets, but that’s not the same as an actual relationaldatabase. For that, you need Microsoft Access, designed to create a full-fledged database system.

Assuming you need only to store a simple table of items – be it data pertaining to your clients, your inventory, your employees – there are some rules for how you set up your table for maximum efficiency and functionality as a data table:

As shown in this table of cities – with location and population data – you can have a title, as I have in Row 1, and that can be separated from the row that contains the column labels, which in this case is Row 3. Those labels become the field names for each piece of information in the list, and each row of data is a record.That list of records is what makes the worksheet a data table.

Notice that there’s no blank row between the field names and the first row of data, and there are no blank rows within the data. This is for good reason – a blank row between the field names and the first record might prevent Excel seeing that row of labels as the field names.

When you name your fields, choose simple, short names. Avoid abbreviations that aren’t easy to decipher, but if you have a very long word or phrase, abbreviate it for simplicity’s sake. Field names that consist of more than one word can be combined into a single word, which can make it easier to export your data into real database application later, so that there’s no chance of Time Zone being seen as two different fields.

And here’s a tip! To keep your field names onscreen no matter how far down you scroll through your records, select the first record – the row right below your field names – and then from the View tab, choose Freeze Panes. As you can see, now my field names remain visible, so I can see what’s what, no matter how far down I scroll through my data.

Other things to consider include keeping people’s names in two fields – so don’t have a Name field, have a First Name field and a Last Name field. And then, if you need it, a Middle Name or Middle initial field. Why all that separation? If you ever want to sort by last names, having those names in a separate field makes that much easier to create a quick alphabetical list.

You’ll also want to think about the order of the fields you add to your table. If you will be doing data entry from manual forms or data pulled from a website, matching the order of that source data will make data entry much easier.

Also, keep your data consistent, by making rules you’ll stick to for the formatting of data in your fields. In a State field, don’t type some states as the full name of the state and then have others using the 2-character abbreviation. If you want both, create 2 fields – one that’s the full name of the state, the other the abbreviations. Same with phone numbers – always include the area code, even if you don’t need it in your state or county to dial the number.

If your tables are set up properly, you’ll get lots of useful information from them, whether they consist of just a few fields or hundreds of fields. Given that an Excel worksheet can hold millions of rows, that means you can store a great deal of data – and you’ve got lots of sorting and filtering tools at your disposal for finding and using it. You’ll learn more about those tools in another video!