Excel Data Entry Overview
Excel worksheets are powerful analytics tools that store large amounts of data.
confirm that your data is displayed and analyzed correctly by using Excel best practices for data entry.
These decisions significantly affect the final layout of the worksheet.
stevecoleimages / Getty Images
Planning before typing saves time later if the spreadsheet needs to be reorganized to make it more efficient.
How Much Data Will the Spreadsheet Hold?
Here are a few tips:
Are Charts Needed?
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?
Even blank cells in a row or column containing data can cause problems as shown in the image below.
Use visual cues such as bold text, lines, and cell color to separate and highlight the data.
If you use aweb-based spreadsheetsuch 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, back up your work after two or three changes.
For example, save after you’ve added data, formatted column headings, or entered a formula.
At the very least, back up your work every two or three minutes.
Excel has an AutoSavefeature, which usually works very well, but you shouldn’t fully rely on it.
Get in the habit of securing your data with frequent saves.
Instead, get in the habit ofsaving using the keyboard shortcutcombination.
When you want to back up your worksheet, pressCtrl+S.
Do Save in Two Places
It’s also important tosave your datain 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.
Cloud-Based Backups
Again, making a backup doesn’t have to be a time-consuming task.
If security is an issue, cloud storage is an option.
But to be safe, download a copy of the file to your gear.
But don’t use numbers such as 2012, 2013, and so on, as headings.
Any headings that are numbers will be included in the selected range.
The apostrophe doesn’t show in the cell, but it changes the number to text data.
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.
Excel and Google Sheets recognize percent symbols that are typed into a cell along with the number.
To avoid potential problems, enter the amount and thenformat the cell for currencyrather than typing the currency symbol.
Cell references identify the location of data in a worksheet.
Referencing Data in Formulas
Formulas are used in Excel to perform calculations such as addition or subtraction.
Excel automatically updates the formula results.
This method is useful when the worksheet contains complicated formulas and when multiple formulas reference the same data.
Pointing reduces the possibility of errors brought on by typing the wrong cell reference or misspelling a range name.
If the size of a data area changes,edit the named range with the Name Manager.
Lock the cells containing your formulas if you use cell references or named ranges.
If necessary, password protect cells to keep them safe.
Sorted data is easier to understand and analyze.
Some functions and tools, such asVLOOKUPandSUBTOTAL, require sorted data to return accurate results.
Also,sort your data in different waysto spot trends that are not obvious at first.
Excel automatically highlights the correct range of data in the worksheet.
Store Numbers as Numbers
Check that all numbers are formatted as numbers.
When data is quickly sorted with theA-ZorZ-Abutton, things may go wrong.
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.
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.
To check that that your data range is recognized correctly, use theCtrl+Shift+8shortcut to see what Excel selects.
To correct this, make changes in the header row to confirm Excel recognizes them.
If your data table uses multi-row headers, Excel may have a hard time recognizing them.
