When working with data in Excel it surprises me how many people don’t know how to setup a database (also known as a list or table) correctly. This can cause many problems when trying to sort, filter, subtotal, build pivot tables or utilising the table tool. I’ve seen first hand the devastating results of a user sorting only part of a database because the database wasn’t set up correctly and they didn’t know how to safely manage the data. Whether you enter the data directly into your database or you download the data from an external source I’m going to go through the design considerations that you need to apply when setting up your database. There are not many considerations however they are very important.
Setup your database header row
There are 2 things you need to do to get Excel to identify the headings in your database.
- Every column must have a heading. Headings are important, so if a column doesn’t have a heading, Excel will not be able to identify that row as a header row. Excel will treat the row as a normal record and sort it with the rest of the data. Not good!
- Format your headings differently from the rest of the database. A simple bold format applied to the headings will be enough for Excel to pick up that this is a header row. Any formatting change will work. You can change the font size, colour, bold, italic, underline, set borders, change background colour. Again, any formatting change will work. The application is smart enough to know where the list starts and stops. It will compare the formatting of the top row, with the formatting of the second row in the database. If the formatting is different Excel will identify the first row as the header row.
Blank rows or columns
No full blank rows or columns within the database. This can happen when an Excel user deletes the data in a column or row and leaves the column or row intact. If the data is no longer required, delete the entire row or column. We can also use this rule to our advantage. When setting up a database you may want to have a title at the top on the page. This could be the subject matter or even a company name. I see a lot a database headings that look like this:
Setup your database heading like this:
When you want to add calculations to the bottom of the database you can also apply the same methodology. Leave a blank row in between the database and the calculations.
Golden Rule
Only ever select one cell. Do not try and select the database. As long as the database is setup correctly you should not need to select the database. Excel will do this for you. It’s time consuming and potentially dangerous. In my early days of using Excel, I’ve been guilty of only selecting part of the database, sorting it, and splitting up all the records (rows of data) literally trashing the data. I’ve also seen this many times myself with other peoples databases. Without a backup the data is useless. All you need to do is select one cell in the column by which you want to sort the data. For example, select a single first name in the first name column if you want to sort the data by their first name. Definitely do not select the whole first name column. Select one cell only. With lots of blank cells in the database it’s safer to just select the first name column heading. Then you sort ascending or descending from the ribbon which is located on the data tab or via the data menu for earlier versions of Excel.
Alternatively you can apply the table tool from the ribbon. For basic users, this would be a safer way of doing a sort.
Tip! If you need to select the database to format the data or move/copy the database, click anywhere into the database and press Ctrl + A. Technically, this selects the current region which is what excel does when you sort, filter, subtotal, create pivot tables or use the table tool. I use current region in VBA programming quite a lot when automating tasks using macros
