Academic Consulting

Regardless of the software package you eventually intend to use for data analysis, MS Excel is a great package for your initial data entry. This is thanks to the range of features it contains that are especially suited to data entry purposes, such as Auto Fill, Data Forms, Data Validation etc. Note however that while we endorse MS Excel for data entry, we do not recommend its use as a data analysis package. Providing that you enter your data in a manner that suits your data analysis package however (typically columns for variables and rows for cases), you will be able to easily transfer your MS Excel file into the package of your choice for later analysis. There are tips on how to go about this for both PASW (SPSS) and NVivo in our Knowledge Base.

Auto Fill

Auto Fill can fill a range of cells with consecutive numbers, dates, times, and strings. This can be particularly useful when entering variables such as 'ID' (e.g. 001, 002, 003 ...) or if you have a range of cells that contain the same value. To use the Auto Fill feature, type in the first 2 entries of your list and then select both cells. Take your mouse cursor over the fill handle (the small black square in the bottom right-hand corner), left-click and hold while dragging your mouse in the direction you wish to fill. A screen tip will appear, telling you what value the list is generating - release the mouse when you have reached the last value you require.

Data Forms

Data Forms can be a quick and easy method for entering your data. They allow you to enter your data via a dialog box for each respondent/case in your sample rather than on the spreadsheet itself. They are also incredibly simple to set up. To use Data Forms, enter your variable names (i.e. your column headers) into row 1 of your spreadsheet. Next, from the MS Excel 2003 menu bar, choose Data > Form (if a warning box appears following this, click OK). A dialog box will appear - use this to enter your data, pressing the Tab key to move down the variables and the Enter key to add the data to the worksheet.

Excel 2007 also has a Data Form option but it is not available on the Ribbon by default. For instructions on how to add it into the ‘Quick Access Toolbar’ search the help file for ‘data form’ and click the ‘What happened to the data form’ option.

Data Validation

Data Validation allows you to specify criteria that user input must meet in order for the entry to be accepted. For example, if you're entering 5 point Likert Scale entries, you can set up Data Validation so that only values between 1 and 5 are accepted. This is a great way to reduce possible errors in your worksheet. Before setting up Data Validation, ensure you enter your variable names into row 1 of your spreadsheet (this is important as you won't be able to enter them later!). Next, select the range of cells you wish to set up validation for, and from the MS Excel 2003 menu bar, choose Data > Validation (or in 2007, select the ‘Data’ tab on the Ribbon followed by the Data Validation button). In the dialog box that appears, set the validation you require, and click the OK button to return to your worksheet. Now, if you try to enter an 'invalid' value, MS Excel will not allow it.

'Locking' Rows and Columns

If you are working with a large data file, you may find it useful to 'lock' the first row and/or column (usually your variable names and ID numbers) so that these can always be viewed no matter where you currently are in your worksheet. Both the 'Split Window' and 'Freeze Panes' features will allow you to do this. Before activating either of these features it is important to select the location you want the split or freeze to appear. If you wish to lock the first row, select cell A2; to lock the first column, select cell B1; and to lock your first column and row select cell B2. Next, from the MS Excel 2003 menu bar choose Window > Split or Window > Freeze Panes as required. To turn off the split/freeze, choose Window > Remove Split or Window > Unfreeze Panes as applicable. If you’re using Excel 2007 these equivalent features are located on the ‘View’ tab of the Ribbon.

Shortcut Keys

Shortcut keys can be especially useful to speed up data entry - most experienced MS Excel users avoid using their mouse whenever possible! Some useful shortcut keys for data entry follow: