3.1 Formatting a database

TABLE OF CONTENTS

• 3.1.1 Turning a sheet into a database
• 3.1.2 Making the database easier to read
• 3.1.3 Renaming the variables
• 3.1.4 Display formats

---

When you enter data in a spreadsheet (text, value or formula), it is displayed in a standard format. However, it is possible to modify the display of these elements and to reorganize the database as needed: sort, filter, modify the formats, the alignment of the data, the font, the borders, as well as the width and height of the rows and columns.

3.1.1 Turning a sheet into a database

Once the column headers are defined in the first row of the worksheet, you can transform the worksheet into a database table by clicking Insert and then Table. The headers you specified on line 1 are now recognised by Excel as fields in your database and flanked by a drop-down icon that will be useful for you later to sort and filter your data.

To limit input errors in your database, you can create cell content validation rules. For instance, you can use such validation rules to limit the input to items in a given list, prohibit entry of numbers, dates, or times outside a given range of values, or limit the length of the text you enter. To apply a validation rule to a column, select the column, then in the Data tools tab, under Data Validation, select Data Validation.

3.1.2 Making the database easier to read

In Excel, it is possible to merge cells, that is, to group several cells into one. Simply select all the cells you want to group and click Merge & Center.

In this example, cells C1 and D1 were merged.

You can also manually change the column display width or row height. To do this, place the cursor between 2 columns and then drag the separator until the column (or row) has the width (height) you want.

When the database contains a lot of variables or observations, it is sometimes useful to hide certain columns or rows in order to navigate faster. This is easily done by positioning yourself on the column you want to hide, then right-click on the column header and select Hide. If the columns you want to hide follow one another, you can do this at once, by selecting all the columns and then right-clicking the column header and selecting *Hide.

Another very useful feature when browsing the database is to freeze the panes. Freezing the panes allows you to freeze rows and/or columns so that they remain visible at all times when browsing the Excel sheet. It is common to freeze the first line of the file usually comprising the names of the variables, which will make it possible to continue to display the names of the latter as we descend into the database.

To return to the original state simply unfreeze the panes.

3.1.3 Renaming the variables

Variables often have unnecessarily long names, usually associated with the question in the questionnaire. However, a short or truncated name is much more appropriate for parsing: creating truncated names for variables is a two-step process.

First, insert a line under the line with the variable name in its expanded form. Next, create a short name following the logic below:

• Each truncated name must be specific to a variable (that is, two variables must not have the same truncated name),
• Truncated names must not contain spaces or special characters (+,”,*,ç,%,&,#, etc.),
• The truncated name must be on the last line before the first data record.

In the example above, the full names appear on the first line and the truncated names appear on the second line.

3.1.4 Display formats

You can format the observations in your database according to standard formats predefined in Excel.

For example, it is possible to change the display of numeric data by applying a currency or percentage format.

It is also possible to change the display format of observations stored as dates. You most probably have observations in your databases posted in the form of dates, such as 14/03/2021 or 14-03-2021 or March 14, 2021. It is important to keep in mind that this is a display format used by Excel, but that Excel actually stores this data as a number, where the value 1 corresponds to 01/01/1900 (first date in Excel), the value 2 corresponds to 02/01/1900 and so on. Thus, the value stored by Excel for the date of 14/03/2021 corresponds to the 44,269th value. Because dates are values, they can be added, subtracted, and included in other calculations.

You can, if you wish, change the date display format of your database. Right-click the date you want to format, and then in Format Cells, go to Date. The different types of date format are displayed with a preview in the Sample box.