Dos and Dont's of Entering Data in Excel

of 08

Excel Data Entry Overview

7 DO's and DON'Ts of Data Entry
7 DO's and DON'Ts of Data Entry. © Ted French

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 a number of Excel's tools and features such as formulas and charts.

The DOs and DON'Ts 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 what the worksheet will be used for, the data it will contain, and what will be done with that data could greatly affect the final layout of the worksheet.

Planning before typing could save time later if the spreadsheet needs to be reorganized to make it more efficient and easier to work with.

Points to Consider

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

What Is the Purpose of the Spreadsheet?

  • The purpose of the spreadsheet will help determine the location of data, as the most important information needs to be visible and easily accessible.
  • What calculations are required? The calculations affect what formulas and functions are needed and where they will be located.

How Much Data Will the Spreadsheet Hold?

The amount of data the spreadsheet will initially hold and how much will be added 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 important 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 is to be displayed in a chart or charts, it could affect to 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 data is arranged may be affected if all or some of the data will be printed, depending on whether portrait or landscape layout is chosen and the number of sheets needed.

  • If portrait view on regular letter size paper (8 1/2 X 11) is to be used, 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 easier.
  • If many columns of data need to be printed, 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 charts are to be printed, moving them to a separate sheet can simplify the task.

of 08

Don't Leave Blank Rows or Columns in Related Data

Don't Leave Blank Rows or Columns in Data Areas
Don't Leave Blank Rows or Columns. © Ted French

Leaving blank rows or columns in data tables or related ranges of data can make it very difficult to properly use a number of Excel's features such as charts, pivot tables, and certain 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 in a range if features such as sorting, filtering, or AutoSum are used.

Rather than leave blank rows or columns, use borders or format headings and labels using bold or underline in order 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 important, at the same time, it can be useful to keep unrelated ranges of data separate.

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

of 08

Do Save Frequently
Save Your Data Frequently. © Ted French

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 auto save 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.

If that seems too much, save at least every two or three minutes.

Even though the stability of computers and computer software has improved drastically over the last few years, the software still crashes, power failures still 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 auto save feature, which usually works very well, but it shouldn't be relied on. Get in the habit of securing your own data with frequent saves.

Shortcut to Saving

Saving does not have to be an onerous task of moving the mouse to the ribbon and clicking on icons, get in the habit of saving using the keyboard shortcut combination of:

Ctrl + S

Do Save in Two Places

Another aspect of saving that can't be overstated is the importance of saving your data in two different locations.

The second location is, of course, a backup, and it has been said many times,"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?
  • on the same computer, that gets stolen?
  • in the same building that burns to the ground?

Web Based Backups

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

If security is not an issue - the worksheet is a list of your DVD's - emailing yourself a copy using web mail so that a copy stays on the server is probably sufficient.

If security is an issue, web storage is still an option - albeit with a company that specializes in that sort of thing and charges a fee for doing so.

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

of 08

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

Don't Use Numbers for Column or Row Headings
Don't Use Numbers for Column or Row Headings. © Ted French

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 much easier, 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 such as:

that automatically select the range of data for the function's argument.

Typically, such functions first look up for columns of numbers above where they are located 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 at least are text 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 this default alignment can easily be changed, formatting is not normally applied until after all data and formulas have been 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 entering all data into a worksheet is to enter just the plain number and then format the cell to display the number correctly - and this includes 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 they are typed 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.

of 08

Do Use Cell References and Named Ranges in Formulas

Using Named Ranges and Cell References in Formulas
Using Named Ranges and Cell References in Formulas. © Ted French

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 included in formulas - such as:

= 5 + 3

Every time that the data changes- say to 7 and 6, the formula needs to be edited and the numbers changed so the formula becomes:

= 7 + 6

If, instead, the data is entered into cells in the worksheet, the cell references - or range names - can be used in the formula rather than the numbers.

If the number 5 is entered into cell A 1 and 3 into cell A2, the formula becomes:

= A1 + A2

To update the data, change the contents of cells A1 and A2, but the formula stays the same - Excel automatically updates the formula results.

The savings in time and effort are increased if the worksheet contains more complicated formulas and if multiple formulas reference the same data since the data need only be changed in one location and all formulas that reference it will be updated.

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.

of 08

Don't Leave Cells Containing Formulas Unprotected

Locking Cells and Protecting Worksheet Formulas in Excel
Locking Cells and Protecting Worksheet Formulas. © Ted French

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

By placing data in cells in the worksheet and then referencing that data in formulas, allows the cells containing the formulas to be locked and, if necessary, password protected to keep them safe.

At the same time, the cells containing the data can be left unlocked so that changes can easily be entered in order 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

of 08

Do Sort Your Data

Sort Data After it Has Been Entered in Excel
Sort Data After it Has Been Entered. © Ted French

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 as a number of data increases so does the difficulty in working with it efficiently.

Sorted data is easier to understand and analysis and some functions and tools, such as VLOOKUP and SUBTOTAL require sorted data in order to return the correct 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

Before data can be sorted, Excel needs to know the exact range that is to be sorted, and usually, Excel is pretty good at selecting areas of related data - so long as when it was entered,

  1. no blank rows or columns were left within an area of related data;
  2. and blank rows and columns were left between areas of related data.

Excel will even determine, fairly accurately, if the data area has field names and exclude this row from the records to be sorted.

However, allowing Excel to select the range to be sorted 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 the same range is to be sorted repeatedly, the best approach is to give it aName.

If a name is defined for the range to be sorted, 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

Hidden rows and columns of data are not moved during sorting, so they need to be unhidden before the sort takes place.

For example, if row 7 is hidden, and it is part of a range of data that is sorted, 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 and not be reordered with the other columns in the sorted range.

of 08

All Numbers Should Be Stores as Numbers

Issue: Check that all numbers are stored 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 horribly wrong. If there is a blank row or blank columns within the data, part of the data might be sorted, while other data is ignored. Imagine the mess you'll have, if names and phone number no longer match, or if orders go to the wrong customers!

The easiest way to ensure that the correct range of data is selected before sorting is to give it a Name.

The second assumption affects exactly what Excel sorts. If you have a single cell selected, Excel extends the selection to select 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.

This is where sorting with the toolbar tools can become tricky—your header (assuming you have one) must meet some rather strict guidelines in order for Excel to recognize it as a header. For instance, if there are any blank cells in the header row, Excel may 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. As well, if your data table consists entirely of text and your header row contains nothing but text, Excel will—virtually all the time—fail to 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; this 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; this is what will be sorted. 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 make sure 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 have more than one header row selected, then Excel assumes you have no header row at all. To correct this, make changes in your header row to make sure it is recognized properly by Excel.

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, simply 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 -, the record should have been placed above the record 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 of records containing text and number data are mixed together, 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 the Sort dialog box is used, even for sorts on one column, Excel displays a message warning you that it has encountered data stored as text and gives you the choice to:

    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.