Data Entry in Excel (Basic, Form Creation & Error Handling)

Get FREE Advanced Excel Exercises with Solutions!

In this article, we are going to learn about data entry in Excel. We will learn about the basics of data entry, form creation, and error handling.

Data entry stands for simply entering the data in an Excel worksheet. This is important if we need to keep proper track of data and want to manage our data efficiently.

The data entry form is a game changer in this regard. This form has made the data entry task butter smooth and added useful functionalities like adding, editing, or searching data in the worksheet in an organized way.

Image1-Overview of Data Entry in Excel

Download Practice Workbook

How to Enter Text or Numbers in Excel

  • Click on cell B12 to type number type data.

Image2-Enter no

  • Type the number in cell B12 and press the Tab key to move to cell C12.

Image3-No entered

  • Type text type data in cell C12.

Image4-Type Text

  • The text type data is inserted in cell C12.

Image5-Text data inserted

  • Similarly, enter data in cells B12:F12.

Image6-Cells containing text and no data

  • Press ENTER to move to cell B13.

Image7-Hit ENTER to move to the cell below

  • As a result, B13 is the active cell now.

Image8-B13 is the active cell

  • We need to keep the old ID on a new line in cell B13.

Image9-New line insertion

  • Press the ALT + ENTER keys together to add a new line in cell B13.

Image10-Press ALT+ENTER

  • Type the old ID in the newly inserted new line in cell B13.

Image11-New line inserted

How to Enter Numbers with Fixed Decimal Points in Excel

  • Click on File.

Image12-File Menu Selection

  • Navigate to Home >> Options.

Image13-Option selection

  • In the Advanced menu, check the settings as shown in the following image.

Image14-Decimal point insertion

  • If we enter a number anywhere in the worksheet now, a decimal point will be followed by the last two digits.
  • Type 17568 in cell F12 and press ENTER.

Image15-Decimal Point Insertion step1

  • As a result, we can see that cell F12 contains the number 175.68.

Image16-Decimal Point is added

  • If we replace the 2 with -2, two zeros will be automatically added after the number we will type in the worksheet.
  • Do accordingly as shown in the following image to check this feature.

Image17--2 is typed

  • Now type 1 in cell F13 and press ENTER.

Image18-Zero will be added after 1

  • As we can see, there are two zeros after 1, hence the number becomes 100.

Image19-Two zeros are added

How to Enter Date or Time in Excel

  • By default, the date format in Excel is MM/DD/YYYY. We need to use the “/” sign as the separator.
  • Type 6/6/2023 in cell D13 and press ENTER. We can see the date is added in the cell.

Image20-Date format

  • We need to type a for AM and p for PM at the end of 12 hours based on the HH: MM time format in Excel.
  • Type 9:10 a in cell E13 and press ENTER.

Image21-Writing time format

  • As a result, we can see the time as 9:10 AM in cell E13.

Image22-TIme in HHMM format

How to Enter Data Simultaneously in Multiple Cells

  • We can enter data simultaneously in multiple cells.
  • For example, we need to enter 100 in cells F12 and F13 simultaneously.

Image23-Writing in multiple cells

  • Select cell F12 first, press and hold the Ctrl key, and click on cell F13 to select both cells.

Image24-Selecting Multiple keys

  • Type 100 in cell F13 and press Ctrl + ENTER together.

Image25-Typing 100 in cell F13

  • As a result, 100 is entered simultaneously in cells F12 and F13.

Image26-Writing in multiple cells simultaneously

How to Enter Data Simultaneously in Multiple Worksheets

  • We can enter data simultaneously in multiple worksheets too.
  • For example, Backup2 and Backup3 are two back-ups of the worksheet named Multiple Worksheets and we need to enter data simultaneously in these worksheets.

Image27-F13 is blank in three worksheets

  • When Multiple Worksheets is the active worksheet, press and hold the Ctrl key and select worksheets named Backup2 and Backup3.

Image28-Selecting worksheets

  • As a result, all three worksheets are selected now.

Image29-Three selected worksheets

  • Type 175 in cell F13 of the worksheet Multiple Worksheets and press ENTER to enter the value simultaneously in the selected worksheets.

Image30-Entering Data Simultaneously in multiple worksheets

  • As we are done entering the value simultaneously in the selected worksheets, we can now ungroup the selected cells.
  • Right-click on the worksheet named Multiple Worksheets and select Ungroup Sheets to do so.

Image31-Ungrouping Sheets

  • Now we can check the worksheet named Backup2 and see that cell F13 has 175. The same goes for the Backup3 worksheet.

Image32-Value is Entered simultaneously in multiple worksheets

How to Change Worksheet Settings and Cell Formats in Excel

We need to change the default worksheet settings and cell formats according to our use cases. This includes changing the direction of the ENTER key and changing the cell’s width. Also, we need to wrap text and format numbers sometimes.

1. Change Direction for ENTER Key

  • Select File.

Image33-File Menu Selection

  • Go to Options.

Image34-Option selection

  • Go to Advanced, click on the drop-down option, and select the direction of the ENTER key accordingly.
  • Finally, click on OK.

Image35-Direction of ENTER key

2. Change the Width of a Column

  • Values are displayed as “####” in cells where the width of the cell is not enough to display the content of the cell. Cell F13 is such an example.

Image36-Column width 1

  • We need to apply Autofit Column Width to display the full content of the cell.
  • Select cell F13, navigate to Home >> Format and select Autofit Column Width.

Image37-Autofit Column Width

  • Cell F13 displays the full content as the column width is autofitted.

Image38-Autofit Column Width

3. Wrap Text in a Cell

  • We need to wrap a text to display the full text in the cell when the text is longer than the cell dimensions.
  • We will use this feature in cell F13.

Image39-Long Text

  • Select cell C13 and select Wrap Text.

Image40-Wrap text selection

  • As a result, we can see the full name in cell C13.

Image41-Wrap Text output

4. Format a Number as Text

  • Sometimes we need to format numbers as text. In the following case, our Employee ID should be treated as text although it looks like a number.
  • Select cell B13 and click on the drop-down as shown in the following image.

Image42-Selecting Number format

  • Select Text as the data type.

Image43-Text selected

  • Now if we type 1009 in cell B13, it will be formatted as text.

Image44-No as Text

How to Create an Excel Data Entry Form

We need to use a data entry form for a smooth data entry experience. This enables faster data entry and significantly reduces data entry time, especially if we are working with a large dataset.

What Is Excel Data Entry Form?

The data entry form is a type of form that Excel automatically generates to ease the data entry task. We can automate data entry in Excel to increase productivity. In this case, all the column headers appear as labels in a single dialogue box. We will learn the step-by-step procedure here.

1. Add Excel Data Entry Form to Quick Access Toolbar

  • If the data entry form is not added to the quick access toolbar, we need to click on the Customize Quick Access Toolbar and select More Commands.

Image45-More Command

  • Add Form to Quick Access Toolbar as shown in the following image and click on OK.

Image46-Adding Form

  • As a result, a data entry form is added to the Quick Access Toolbar.

Image47.1-Data Entry Icon is Added

2. Create a Data Entry Form

  • To create a data entry form, we need to create a table first. Select any cell in the dataset (B12 in this case).

Image47-Create table

  • Click on OK as the Create Table window pops up.

Image48-Click ok

  • As a result, a table will be created.

Image49-Table is created

  • Click on any cell of the table (cell B12 in this case) and click on the Form icon as shown in the following image.

Image50-Form Creation

  • As a result, a data entry form will be created with column headers as the labels of the form.

Image51-Data Entry form is created

3. Add, Search, Update, or Restore Records in Excel

  • Click on New in the data entry form to add a new entry.

Image52-Add new record

  • Type the necessary information in the appropriate fields and finally press ENTER to add the new record to the worksheet.

Image53-New Record

  • We can see, the new record is added to the worksheet using the data entry form.

Image54-New Record is added

  • We can search in the worksheet using the data entry form.
  • Click on Criteria to open a new search field.


  • Type the search keyword (1007 in this case) and click on Find Next.


  • As a result, we can see the search result in the data form which is an exact match with the worksheet dataset.

Image57-Search Output

  • We can update the cell values of the worksheet using the data entry form.
  • For example, we need to update the Total Sales value against the Employee ID 1007.

Image58-Update cell value

  • Select the old value, type the new value and press ENTER to update the value in the worksheet.
  • We can see that the value is updated in the worksheet.

Image59-Value is updated

  • We can also restore the previous value if we need unless we have pressed the ENTER key.


  • Click on Restore to restore the initial value.


  • As a result, the initial value is restored.

Image62-Value is restored

4. Data Validation in Data Entry Form

We can use data validation in a data entry form. But to do so, we need to add the data validation before even turning the dataset into a table.

  • Select the column where we need to add the data validation (column D in this case), navigate to Data, and select Data Validation as shown in the following image.

Image63-Adding Data validation1

  • We need to restrict the date to the year 2023 only.
  • So, select Settings accordingly as shown in the following image, and finally navigate to Input Message.

Image64-Data validation2

  • Type the Title and the Input message properly and go to Error Alert.

Image65-Data validation3

  • Type the Title and Error message here too and click on OK to add the data validation.

Image66-Data validation4

  • Now create a data entry form following the steps we have described before.
  • After that, to check whether the data validation is working or not, change the year of a record to 2022 and press ENTER.

Image67-Data Validation5

  • As the year is not 2023, so an error message popped up.
  • This means the data validation is working properly.

Image68-Data Validation Error Message

5. Formulas in Data Entry Form

We can use formulas in the data entry form. For example, the sales target is greater than 100 and we want to check  whether the employees reached the sales target or not.

  • Type the following formula in cell G5 and press ENTER.
=IF([@[Total Sales]]>100,"Yes","No")

Image69-Formula in Data Entry

  • As a result, the whole “Target Reached” column will have the same formula.

Image70-Formula is applied to the entire table

  • Now select cell B12 and click on Form to open the data entry form.

Image71-Data Entry form for formula

  • Click on New so that we can insert new data.

Image72-New Data Entry

  • Type the data for the new entry and press ENTER.

Image73-New entry

  • As a result, the new data is inserted in the worksheet, and Target Reached is calculated automatically using the formula.

Image74-Formula in new data

6. Open the Data Entry Form Using a Button

To open the data entry form, we need to click on any cell of the dataset and then select the Form option. This process works, but it will be more convenient if we assign a button so that whenever we click on that button, the data entry form opens.

  • Right-click on the worksheet named Button and select View Code to open the code window.

Image75-Using VBA to add button

  • Type the following code in the code window to open the data entry form.
Sub DataEntryFormOpening()
Dim namedRange As Range
Dim nameObj As Name
Set namedRange = Range("B4").CurrentRegion
ThisWorkbook.Names.Add Name:="Database", RefersTo:=namedRange
For Each nameObj In ThisWorkbook.Names
If nameObj.Name = "Database" Then
Exit For
End If
Next nameObj
End Sub

Image76-VBA Code for data entry form

  • Note this macro works when we assign Database as the namedRange.
  • Now go to Insert >> Shapes and select a shape as shown in the following image to insert a shape in the worksheet.

Image77-Shape Insertion

  • Right-click on the shape and select Assign Macro.

Image78-Assigning macro

  • Select the macro name and click on OK so that whenever we click on the shape, the data entry form opens.

Image79-Assigning macro

  • Click on the shape now to check if it works properly.
  • We can see from the following image that it is working perfectly and the data entry form is opened.

Image80-Data entry form is open

7. Issues Regarding Data Entry Form

There are some issues regarding the data entry form. We will discuss how to check for data entry errors and the fixes here.

  • Too Many Fields in Data Form

If there are more than 32 column headers in the dataset, then when we try to add a data entry form, this error message pops up.

Image81-Too many fields

Solution: We can use the dataset with a maximum of 32 column headers to fix this issue.

  • Cannot Extend Table or Database

If there is data immediately where the table ends, and if we try to add new data using the data entry form, this error message shows up.

Image82-Cannnot Extend

Solution: We need to keep at least one blank row after the table ends to fix this issue.

  • Wrong Cursor Positioning

If we select a cell outside the table and click on Form, then this error happens.


Solution: We need to select a cell of the table and then click on the Form to avoid this error.

  • Database or Table Range is Not Valid

This error happens if there is a named range in the workbook named database.


Solution: We need to change the named range name to anything but the database to fix this error.

How to Create Microsoft Forms

We can create Microsoft Forms for a seamless data entry experience. If we need to collect data from a group of people, we can create a Microsoft Form and share the form link with the set of people.

  • In OneDrive, go to New >> Forms survey.

Image85-Microsoft Form

  • Click on Add new to add fields.

Image86-MS Form2

  • Create the forms accordingly. The form looks like this.

Image87-Form Look

  • Click on the Collect responses option and Copy link to copy the link so that you can share them with the concerned group of people.

Image88-Link Sharing

  • Click on Responses to view the responses.

Image89-Check Response

  • We can see the response details. Click on Open in Excel to open the responses in an Excel file.

Image90-Open in Excel

  • We can see the collected response in the Excel file.

Image91-Collected Data output

In this manner, we can create and use Microsoft Forms for data entry purposes.

Things to Remember

  • Create a table including all the column headers of the dataset.
  • Apply formulas before creating the table.
  • Use “database” or “Database” as the named range in the VBA code.

Frequently Asked Questions

1. What are the types of entries in Excel?
There are four types of data entry in Excel. They are text, number, logic, and error.
2. How many entries are in Excel?
You can use the COUNT function to get the count of entries in a dataset.
3. How many cells are in Excel?
There are 17,179,869,184 cells per worksheet in Excel.


In this article, we have discussed everything about data entry in Excel. We have covered the popular data types and various kinds of formatting. We also discussed the Data Entry Form in detail here. The discussion also includes the fixes of issues with the data entry form. With a little bit of practice, we believe, you can master data entry in Excel if you have properly followed this article. If you face any difficulties implementing this, please let us know in the comment section. Team Exceldemy will be there to help you out. Have a good day!

Data Entry in Excel: Knowledge Hub

<< Go Back to Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.


Hadi Ul Bashar
Hadi Ul Bashar

Hadi Ul Bashar holds a BSc degree in Biomedical Engineering from Bangladesh University of Engineering and Technology.  In January 2023, Bashar joined SOFTEKO as an Excel and VBA content developer. Since then, he has authored over 50 articles. He was engaged in two specialized training programs focused on VBA and Chart & Dashboard designing in Excel. Currently he is creating video content on Excel and VBA and has created over 70 videos. Reading books and traveling are his... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF