Dos and Don'ts of Entering Data in Excel

Learn what to avoid when entering data in Excel

Businessman on Computer
stevecoleimages / Getty Images

This tutorial covers some of the basic dos and don'ts of entering data into spreadsheet programs such as Excel, Google Spreadsheets, and Open Office Calc.

Entering data correctly the first time can avoid problems later on and make it easier to use many of Excel's tools and features such as formulas and charts.

Excel Data Entry Overview

7 DO's and DON'Ts of Data Entry

The top dos and don'ts that you should always keep in mind when using Excel are:

  1. Do plan your spreadsheet.
  2. Don't leave blank rows or columns when entering related data.
  3. Do save frequently and save in two places.
  4. Don't use numbers as column headings and don't include units with the data.
  5. Do use cell references and named ranges in formulas.
  6. Don't leave cells containing formulas unlocked.
  7. Do sort your data.

Do Plan Your Spreadsheet

When it comes to entering data into Excel, it is a good idea to do a bit of planning before you begin to type.

Knowing how you'll use the worksheet, the data it will contain, and what you'll do with that data could significantly affect the final layout of the worksheet.

Planning before typing can save time later if the spreadsheet needs to be reorganized to make it more efficient.

Points to Consider

  • What is the purpose of the spreadsheet?
  • How much data will the spreadsheet hold?
  • Are charts necessary?
  • Will you print out the spreadsheet?

What Is the Purpose of the Spreadsheet?

  • The purpose of the spreadsheet will help determine the location of data, as the most critical information needs to be visible and easily accessible.
  • What calculations are required? The calculations affect which formulas and functions are needed and where you should enter them.

How Much Data Will the Spreadsheet Hold?

The amount of data the spreadsheet will initially hold and how much users will add to it later will affect the number of worksheets used.

  • Don't spread your data out too much since it can make specific information hard to find and calculations spanning multiple sheets or workbooks can start to affect Excel's performance.
  • For large worksheets, it is sometimes useful to display significant column totals above the column headings, rather than forcing users to scroll to the bottom of the worksheet to see the information.

Are Charts Needed?

If all or part of the data will be used in a chart or charts, it could affect the layout of information.

  • Depending on the type of chart, the data may need to be grouped differently -- such as employing a summary area for pie charts.
  • For large spreadsheets, charts can be moved to separate pages to ease crowding.

Will the Spreadsheet Be Printed?

How you organize the data depends in part if you or others plan to print out all or some of the data, depending on whether you use the portrait or landscape layout.

  • If using portrait view on regular letter size paper (8.5 x 11), consider placing most of the data in rows under a few columns, so that all of the headings are visible on one sheet.
  • If multiple sheets are needed, consider printing column headings at the top of each page to make reading the data more accessible.
  • If you're printing many columns of data, the landscape layout might keep all headings on one page. Again, if multiple sheets are needed, print the column headings at the top of each page.
  • If you're printing charts, moving them to a separate sheet can simplify the task.

Don't Leave Blank Rows or Columns in Related Data

Don't Leave Blank Rows or Columns in Data Areas

Leaving blank rows or columns in data tables or related ranges of data can make it very difficult to properly use several of Excel's features such as charts, pivot tables, and specific functions.

Even blank cells in a row or column containing data can cause problems as shown in the image above.

The absence of empty spaces will also make it easier for Excel to detect and select all related data when using a range of features such as sorting, filtering, or AutoSum.

Rather than leave blank rows or columns, use borders or format headings and labels using bold or underline to break up the data and make it easier to read.

Enter your data column-wise when possible.

  • When laying out your spreadsheet, place a descriptive heading at the top of the first column of the table with the data below.
  • If there is more than one data series, list them one after the other in columns (left to right) with the title for each data series at the top.

Keep Unrelated Data Separate

While keeping related data together is essential, at the same time, it's also useful to separate unrelated ranges of data.

Leaving blank columns or rows between different data ranges or other data on the worksheet will again make it easier for Excel to select the correct related ranges or tables of data.

Do Save Frequently

The importance of saving your work frequently can't be overstated - or stated too often.

Of course, if you are using a web-based spreadsheet such as Google Spreadsheets or Excel Online then saving is not an issue, since neither program has a save option but, instead, work with an Autosave feature.

For computer-based programs though, after two or three changes whether it's adding data, making a formatting change, or entering a formula, save the worksheet.

At the very least, save your work every two or three minutes.

Even though the stability of computers and computer software has improved drastically over time, software still crashes, power failures happen, and other people sometimes trip over your power cord and pull it out of the wall socket.

And when it happens, the loss of any amount of data, large or small, only increases your workload as you try to reconstruct what you've already done.

Excel has an AutoSave feature, which usually works very well, but you shouldn't fully rely on it. Get in the habit of securing your data with frequent saves.

Shortcut to Saving

Saving doesn't even require moving the mouse to the ribbon and clicking on icons. Instead, get in the habit of saving using the keyboard shortcut combination of:

Ctrl + S

Do Save in Two Places

It's also important to save your data in two different locations.

The second location is, of course, a backup, and as the saying goes: "Backups are like insurance: have one, and you probably won't need it; don't have one and you probably will."

The best backup is one that is in a different physical location from the original. After all, what is the point of having two copies of a file if they:

  • Are on the same hard drive that fails?
  • Are on the same computer, that gets stolen?
  • Are in the same building that burns to the ground?

Cloud-Based Backups

Again, making a backup doesn't have to be a time-consuming task.

If security is not an issue, such as if the worksheet is a list of your DVD's, it's probably sufficient to email yourself a copy.

If security is an issue, cloud storage is an option, albeit with a company that specializes in that sort of thing.

In the case of online spreadsheets, presumably, the program's owners back up their servers, and this includes all user data. But to be safe, download a copy of the file to your computer.

Don't Use Numbers as Column Headings and Don't Include Units with the Data

Don't use numbers for column or row headings.

Do use headings at the top of columns and at the beginning of rows to identify your data, as they make operations such as sorting simpler, but don't use numbers such as 2012, 2013, and so on, to do it.

As shown in the image above, column and row headings that are just numbers may inadvertently be included in calculations. If your formulas contain functions that automatically select the range of data for the function's argument, such as AutoSum, numbers in the column headings will cause an issue.

Typically, such functions, which also include AutoMax and AutoAverage, first look up for columns of numbers above them and then to the left for a row of numbers, and any headings that are just numbers will be included in the selected range.

Numbers used as row headings can also be mistaken as another data series if selected as part of a range for a chart rather than as axes labels.

Format numbers in the heading cells as text or create text labels by preceding each number with an apostrophe ( ' ) such as '2012 and '2013. The apostrophe doesn't show in the cell, but it changes the number to text data.

Keep Units in the Headings

Don't enter currency, temperature, distance or other units into each cell with the number data.

If you do, there is a good chance that Excel or Google Spreadsheets will view all of your data as text.

Instead, place units in the headings at the top of the column, which, as it happens, will ensure that those headings are in text format and won't create the problem discussed above.

Text to the Left, Numbers to the Right

A quick way to tell if you have either text or number data is to check the alignment of the data in a cell. By default, text data is aligned to the left in Excel and Google Spreadsheets, and number data is aligned to the right in a cell.

Although you can change this default alignment, formatting is not generally applied until after all data and formulas is entered, so the default alignment may give you a clue early on that something is amiss in the worksheet.

Percent and Currency Symbols

The best practice for inputting data into a worksheet is to enter just the plain number and then format the cell to display the number correctly including percentages and currency amounts.

Excel and Google Spreadsheets, however, accept percent symbols that are typed into a cell along with the number and both also recognize common currency symbols, such as the dollar sign ($) or the British pound symbol (£) if you type them into a cell along with number data, but other currency symbols, such as the South African Rand (R), will likely be interpreted as text.

To avoid potential problems, follow the above mentioned best practice and enter the amount and then format the cell for currency rather than typing in the currency symbol.

Do Use Cell References and Named Ranges in Formulas

Using Named Ranges and Cell References in Formulas

Both cell references and named ranges can be and should be used in formulas to make it quicker and easier to keep the formulas and by extension, the entire worksheet, free of errors and up to date.

  • Cell references are a combination of the column letter and row number of a cell, such as A1, B23, and W987, and are used to identify the location of data in a worksheet.
  • A named range or defined name is similar to a cell reference in that it is used to identify a cell or range of cells in a worksheet.

Referencing Data in Formulas

Formulas are used in Excel to perform calculations - such as addition or subtraction.

If actual numbers are in formulas such as:

= 5 + 3

Every time the data changes, you have to edit the formula. So if the new numbers are 7 and 6, the formula becomes:

= 7 + 6

Alternatively, if you enter the data into cells in the worksheet, then you can use cell references or range names in the formula rather than the numbers.

If you enter the number 5 into cell A1 and 3 into cell A2, the formula becomes:

= A1 + A2

To update the data, you change the contents of cells A1 and A2, but keep the formula as it is since Excel will automatically update the formula results.

Using this methodology especially pays off if the worksheet contains more complicated formulas and if multiple formulas reference the same data. Then you'll only need to change the data in one location, which in turn will update all the formulas that reference it.

Using cell references or named ranges also makes your worksheet safer, since it allows you to protect the formulas from accidental changes while leaving data cells that change accessible.

Pointing at the Data

Another feature of Excel and Google Spreadsheets is that they allow you to enter cell references or range names into formulas using pointing which involves clicking on a cell to enter the reference into the formula.

Pointing reduces the possibility of errors caused by typing in the wrong cell reference or misspelling a range name.

Use Named Ranges to Select Data

Giving an area of related data a name can make it much easier to select the data when carrying out sorts or filtering operations.

If the size of a data area changes, the range of a name can easily be edited using the Name Manager.

Don't Leave Cells Containing Formulas Unprotected

Locking Cells and Protecting Worksheet Formulas in Excel

After spending so much time getting their formulas correct and using the proper cell references, many people make the mistake of leaving those formulas vulnerable to accidental or unintentional changes.

You can lock the cells containing your formulas if you use cell references or named ranges. If necessary, you can password protect them to keep them safe.

At the same time, you can leave the cells containing the data unlocked so that users can enter changes to keep the spreadsheet up to date.

Protecting a worksheet or workbook is a two-step process:

  1. Make sure that the correct cells are locked.
  2. Apply the protect sheet option and if desired, add a password.

Do Sort Your Data

Sort Data After it Has Been Entered in Excel

Do sort your data after you have finished entering it.

Working with small amounts of unsorted data in Excel or Google Spreadsheets is not usually a problem, but the spreadsheet gets bigger so does the difficulty in working with it efficiently.

Sorted data is easier to understand and analyze and some functions and tools, such as VLOOKUP and SUBTOTAL, require sorted data to return the accurate results.

Also, sorting your data in different ways can make it easier to spot trends that are not obvious at first.

Selecting Data to Be Sorted

Excel needs to know the exact range that you need to sort, and is usually pretty good at identifying areas of related data so long as there are:

  1. No blank rows or columns within an area of related data;
  2. Blank rows and columns between areas of related data.

Excel will even determine if the data area has field names and exclude that row from sorting.

However, allowing Excel to select the range for sorting can be risky especially with large amounts of data that are hard to check.

Using Names to Select Data

To ensure that the correct data is selected, highlight the range before starting the sort.

If you plan to sort the same range of data repeatedly, the best approach is to give it a name.

When you want to sort a named range of data, type the name in the Name Box, or select it from the associated drop-down list and Excel will automatically highlight the correct range of data in the worksheet.

Hidden Rows and Columns and Sorting

Excel doesn't include hidden rows and columns of data when sorting, so they need to be unhidden before the sort takes place.

For example, if row 7 is hidden within a range of data you're sorting, it will remain as row 7 rather than be moved to its correct location as a result of the sort.

The same goes for columns of data. Sorting by rows involves reordering columns of data, but if Column B is hidden before the sort, it will remain as Column B after the sort.

All Numbers Should Be Stored as Numbers

Check that all numbers are formatted as numbers. If the results are not what you expected, the column might contain numbers stored as text and not as numbers. For example, negative numbers imported from some accounting systems or a number entered with a leading apostrophe (' ) are stored as text.

When you quickly sort data with the A-Z or Z-A button, things can go wrong. If there is a blank row or blank columns within the data, some of the data will sort and some will not. Imagine the mess you'd have, if names and phone number no longer match, or if orders go to the wrong customers.

The easiest way to ensure that you've selected the correct range of data before sorting is to give it a name.

If you have a single cell selected, Excel extends the selection to a range (much like pressing Ctrl+Shift+8) bounded by one or more blank columns and rows. It then examines the first row in the selected range to determine if it contains header information or not.

Your header (assuming you have one) must meet some rather strict guidelines for Excel to recognize it as a header. For instance, if there are any blank cells in the header row, Excel might think it isn't a header. Likewise, if the header row is formatted the same as the other rows in the data range, then it may not recognize it. Additionally, if your data table consists entirely of text and your header row contains nothing but text, Excel will likely not recognize the header row. (The row looks just like another data row to Excel.)

Only after selecting the range and determining if there is a header row will Excel do the actual sorting. How pleased you are with the results depends on whether Excel got both the range selection and the header row determination right. For instance, if Excel doesn't think you have a header row, and you do, then your header is sorted into the body of the data; which is generally a bad thing.

To make sure that your data range is recognized correctly, use the Ctrl+Shift+8 shortcut to see what Excel selects. If it doesn't match your expectations, then you need to either modify the character of the data in your table, or you need to select the data range before using the Sort dialog box.

To ensure that your heading is recognized correctly, use the Ctrl+Shift+8 shortcut to select the data range, then look at the first row. If your header has blank cells among those selected in the first row or the first row is formatted just like the second row, or you've selected more than one header row, then Excel assumes you have no header row at all. To correct this, make changes in your header row to make sure Excel recognizes them.

Finally, all bets could be off if your data table uses multi-row headers. Excel has a hard time recognizing them. You compound the problem when you expect it to include blank rows in that header; it just can't do it automatically. You can, however, just select all the rows you want to sort before doing the sort. In other words, be specific in what you want Excel to sort; don't let Excel make the assumptions for you.

Dates and Times Stored as Text

If the results of sorting by date do not turn out as expected, the data in the column containing the sort key might contain dates or times stored as text data rather than as numbers (dates and times are just formatted number data).

In the image above, the record for A. Peterson ended up at the bottom of the list, when, based on the borrowing date November 5, 2014, it should be above the one for A. Wilson, which also has a borrowing date of November 5.

The reason for the unexpected results is that the borrowing date for A. Peterson has been stored as text, rather than as a number

Mixed Data and Quick Sorts

When using the quick sort method with records containing text and number data, Excel sorts the number and text data separately, placing the records with text data at the bottom of the sorted list.

Excel might also include the column headings in the sort results, interpreting them as just another row of text data rather than as the field names for the data table.

Sort Warnings - Sort Dialog Box

As shown in the image above, if you use the Sort dialog box, even for sorts on one column, Excel displays a message warning you that it has encountered data stored as text and gives you these options:

  • Sort anything that looks like a number as a number.
  • Sort numbers and numbers stored as text separately.

If you choose the first option, Excel will attempt to place the text data in the correct location of the sort results.

Choose the second option and Excel will place the records containing text data at the bottom of the sort results just as it does with quick sorts.