Do's and Don'ts of Entering Data in Excel

Learn what to do and what to avoid when entering data in Excel

Businessman on Computer
stevecoleimages / Getty Images

Entering data correctly in a spreadsheet the first time avoids problems later on and makes it easier to use many of Excel's tools and features such as formulas and charts. This tutorial covers the basic dos and don'ts of entering data into spreadsheet programs such as Excel, Google Sheets, and Open Office Calc.

Instructions in this article apply to Excel 2019, 2016, 2013, 2010; Excel for Mac, Excel for Office 365, and Excel Online.

Excel Data Entry Overview

Excel worksheets are powerful analytics tools that store large amounts of data. Make sure that your data is displayed and analyzed correctly by using Excel best practices for data entry.

7 DO's and DON'Ts of Data Entry

These are the top dos and don'ts to keep in mind when using Excel:

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

Do Plan Your Spreadsheet

When entering data into Excel, it's a good idea to plan before you begin to type. Decide how the worksheet will be used, the data it will contain, and what will be done with that data. These decisions significantly affect the final layout of the worksheet.

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

Here are some points to consider when planning your Excel workbooks:

  • What is the purpose of the spreadsheet?
  • How much data will the spreadsheet hold?
  • Are charts necessary?
  • Will the spreadsheet be printed?

What Is the Purpose of the Spreadsheet?

The purpose of the spreadsheet determines the location of the data, as the most critical information needs to be visible and easily accessible. If your data requires calculations, the calculations determine which formulas and functions are needed and where the formulas should be entered.

How Much Data Will the Spreadsheet Hold?

The amount of data the spreadsheet will initially hold and how much will be added to it later determines the number of worksheets you'll need for the workbook.

Here are a few tips:

  • Don't spread your data out too much. When data is contained in several worksheets, specific information may be hard to find. Also, calculations that span multiple worksheets or workbooks affect Excel's performance.
  • For large worksheets, display significant column totals above the column headings, rather than at the bottom of the worksheet to to find summary information quickly.

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.

Follow these best practices when using charts:

  • Depending on the type of chart, the data may need to be grouped differently. For example, create a summary area for pie charts.
  • For large spreadsheets, move charts to separate pages so that worksheets aren't crowded with data and images.

Will the Spreadsheet Be Printed?

How you organize the data depends on how the data will be printed. Will all of the data be printed or just some of the data? Will the data be printed in portrait or landscape layout?

When printing worksheets, keep these tips in mind:

  • If the worksheet is printed using portrait view on letter size paper (8.5" x 11"), place most of the data in rows under a few columns. This way, the headings are visible on one sheet.
  • If the worksheet is printed on multiple sheets, print column headings at the top of each page. Column headings provide an overview of the data and make the data more accessible and easy to read.
  • If several columns of data are printed, use landscape layout to keep all headings on one page. Again, if multiple sheets are needed, print the column headings at the top of each page.
  • If the worksheet contains charts, move the charts to a separate sheet before printing the workbook.

Don't Leave Blank Rows or Columns When Entering Related Data

Blank rows or columns in data tables or related ranges of data make it 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 below.

Don't Leave Blank Rows or Columns in Data Areas

The absence of empty spaces helps Excel detect and select related data when using a range of features such as sorting, filtering, or AutoSum.

When you want to break up data to make it easier to read, use borders or format headings and labels. Use visual cues such as bold text, lines, and cell color to separate and highlight the data.

Follow these tips when working with data in rows and columns:

  • Place a descriptive heading at the top of the first column of the table with the data below it.
  • 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, it's also useful to separate unrelated ranges of data. Leave blank columns or rows between different data ranges or other data on the worksheet so that Excel selects 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. If you use a web-based spreadsheet such as Google Sheets or Excel Online, saving is not an issue. These programs don't have a save option. Instead, spreadsheets are saved automatically to your cloud account with the Autosave feature.

For desktop spreadsheet programs, save your work after two or three changes. For example, save after you've added data, formated column headings, or entered a formula. 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 accidents happen, the loss of of data increases your workload as you 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

You don't have to move the mouse to the ribbon and click on icons to save your work. Instead, get in the habit of saving using the keyboard shortcut combination. When you want to save your worksheet, press Ctrl+S.

Do Save in Two Places

It's also important to save your data in two different locations. The second location is a backup. The best backup is one that is in a different physical location from the original.

When saving a backup copy of your workbooks, consider saving files to your cloud account. When workbooks are stored on your computer and in the cloud, you'll not only have a backup copy in the cloud, but the cloud is designed with redundant backups meaning that your data is stored on multiple servers.

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.

In the case of online spreadsheets, 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

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

Don't use numbers for column or row headings.

As shown in the image above, column and row headings that are 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 and then look to the left for a row of numbers. Any headings that are numbers will be included in the selected range.

Numbers that are used as row headings can 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 chance that Excel or Google Sheets will view all of your data as text.

Instead, place units in the headings at the top of the column to ensure that those headings are in text format and won't create a problem.

Text to the Left, Numbers to the Right

A quick way to tell if you have 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 Sheets, and number data is aligned to the right in a cell.

Although this default alignment can be changed, formatting should be applied after all data and formulas are entered. The default alignment gives you a clue if data is formatted correctly in the worksheet.

Percent and Currency Symbols

The best practice for inputting data into a worksheet is to enter the plain number and then format the cell to display the number correctly, for example as a percentage or as currency.

Excel and Google Sheets recognize percent symbols that are typed into a cell along with the number. These apps also accept 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), is interpreted as text.

To avoid potential problems, enter the amount and then format the cell for currency rather than typing the currency symbol.

Do Use Cell References and Named Ranges in Formulas

Both cell references and named ranges can be and should be used in formulas to keep formulas and the entire worksheet free of errors and up-to-date.

Using Named Ranges and Cell References in Formulas

Cell references are a combination of the column letter and row number of a cell, such as A1, B23, and W987. Cell references 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 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'll change the contents of cells A1 and A2, but keep the formula as it is. Excel automatically updates the formula results.

This method is useful when the worksheet contains complicated formulas and when multiple formulas reference the same data. Then you'll only change the data in one location, which in turn updates the formulas that reference it.

Cell references and named ranges also make worksheets safer by protecting the formulas from accidental changes while leaving data cells that change accessible.

Point at the Data

Another feature of Excel and Google Sheets is that cell references or range names can be entered 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 the wrong cell reference or misspelling a range name.

Use Named Ranges to Select Data

Giving an area of related data a name makes it easier to select the data that will be sorted or filtered.

If the size of a data area changes, edit the named range with the Name Manager.

Don't Leave Cells Containing Formulas Unlocked

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.

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

Locking Cells and Protecting Worksheet Formulas in Excel

At the same time, leave the cells containing the data unlocked so that users can enter changes and 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. Protect the worksheet by adding a password.

Do Sort Your Data

Do sort your data after you have finished entering it. Working with small amounts of unsorted data in Excel or Google Sheets is not usually a problem, but as the spreadsheet gets bigger it becomes difficult to work with it efficiently.

Sort Data After it Has Been Entered in Excel

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

Also, sort your data in different ways to spot trends that are not obvious at first.

Select the Data to Be Sorted

Excel needs to know the exact range that you need to sort, and identifies areas of related data if 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 also determines if the data area has field names and excludes 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.

Use 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 sort a named range of data, type the name in the Name Box, or select it from the associated dropdown list. Excel automatically highlights the correct range of data in the worksheet.

Sorting Hidden Rows and Columns

Excel doesn't include hidden rows and columns of data when sorting, so these rows and columns must be unhidden before the sort takes place. For example, if row 7 is hidden within a range of data that will be sorted, it will remain as row 7 and won't be moved to its correct location in the sorted data.

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.

Store Numbers 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 data is quickly sorted with the A-Z or Z-A button, things may go wrong. If there is a blank row or blank columns in the data, some of the data will sort and some will not. The easiest way to ensure that you've selected the correct range of data before sorting is to give it a name.

If a single cell is 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.

Column headers must meet strict guidelines before Excel recognizes it as a header. For instance, if there are 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 Excel may not recognize the header. Additionally, if a data table consists of text and the header row contains nothing but text, Excel won't recognize the header row; the header row looks just like another data row to Excel.

Excel sorts only after the range is selected and Excel determines if there is a header row. The sort results depend on whether Excel got both the range selection and the header row determination right. For instance, if Excel doesn't think that there's a header row, then the header is sorted into the body of the data.

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 either modify the character of the data in your table, or select the data range before using the Sort dialog box.

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

If your data table uses multi-row headers, Excel may have a hard time recognizing them. The problem is compounded when you expect Excel to include blank rows in that header; it just can't do it automatically. You can, however, select the rows to be sorted 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.

Store Dates and Times 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 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

When using the Sort dialog box, even for sorts on one column, Excel may display a warning that it has encountered data stored as text and gives these options:

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

The first option places the text data in the correct location of the sort results. The second option places the records containing text data at the bottom of the sort results just as it does with quick sorts.