Entering and Managing Data in Excel

Last updated: November 2026 · Works in Excel for Microsoft 365, Excel 2021 and Excel 2019

Good analysis starts with clean data. If dates are typed as text, names have extra spaces or the same customer appears three times, every total, chart and pivot table built on top will be wrong. This guide covers the tools that help you enter data quickly and keep it tidy: data types, AutoFill, Flash Fill, Excel Tables, Data Validation, Remove Duplicates, Text to Columns and Find & Replace.

Understand Excel's data types

Excel treats what you type as one of a few types, and it matters which one it picks:

If a number or date sits on the left of the cell, Excel probably sees it as text and will not add it up. Select the cells, click the small warning icon and choose Convert to Number, or use Data > Text to Columns > Finish to force a conversion.

Enter data faster with AutoFill and Flash Fill

AutoFill

Type Jan in a cell, then drag the small square at the bottom-right corner (the fill handle) across or down. Excel continues the series: Feb, Mar, Apr. It works for weekdays, dates, numbers (type 1 and 2, select both, then drag) and formulas. Double-clicking the fill handle copies a formula down as far as the data in the next column goes.

Flash Fill

Flash Fill spots a pattern from an example and repeats it. Suppose column A holds full names like Ahmed Khan. In B2 type Ahmed, move to B3 and press Ctrl + E. Excel fills every first name in one step. It also works for joining text, reformatting phone numbers and extracting codes. Flash Fill produces fixed values, so run it again if the source data changes.

Turn ranges into Excel Tables

Click anywhere in your list and press Ctrl + T, confirm that My table has headers is ticked, and click OK. An Excel Table gives you:

• Banded rows and filter buttons on every header.
• Automatic growth: new rows typed underneath join the table and inherit formats and formulas.
• Readable formulas such as =[@Qty]*[@Price] instead of =B2*C2.
• A Total Row (Table Design tab) with one-click SUM, AVERAGE or COUNT.
• Charts and pivot tables that update when the table grows.

Rename the table on the Table Design tab, for example to tblSales, so formulas elsewhere can refer to tblSales[Amount].

Prevent mistakes with Data Validation

Data Validation controls what can be typed into a cell. To create a drop-down list of branches:

1. Type the allowed values (Dubai, Abu Dhabi, Sharjah, Ajman) in a range on a separate sheet.
2. Select the cells where users will choose a branch.
3. Go to Data > Data Validation, set Allow to List and select your range as the Source.
4. On the Error Alert tab, write a friendly message such as "Please choose a branch from the list."

You can also restrict entries to whole numbers between limits, dates within a range or text of a set length. Use Data > Data Validation > Circle Invalid Data to find existing entries that break the rules.

Clean up messy data

Remove duplicates

Select the data, choose Data > Remove Duplicates, tick the columns that define a duplicate (for example Invoice No.) and click OK. Excel reports how many rows were removed. Work on a copy if you are unsure.

Split one column into several

If a column holds "Khan, Ahmed", select it and choose Data > Text to Columns. Pick Delimited, tick Comma, and finish. Make sure the columns to the right are empty first, or they will be overwritten.

Remove extra spaces and fix capitals

Imported data often has stray spaces that break lookups. In a helper column use =TRIM(A2) to remove them, =PROPER(A2) to capitalise each word, or =UPPER(A2) for codes. Copy the results and use Paste Special > Values to replace the originals.

Find & Replace

Press Ctrl + H to replace text across a sheet or workbook, for example changing "Abu dhabi" to "Abu Dhabi". Click Options to match case or the entire cell contents.

Five layout rules for data that works

1. One header row, with a unique, short name in each column.
2. One record per row: one sale, one employee, one transaction.
3. No blank rows or columns inside the list.
4. No merged cells inside the data.
5. Keep each kind of information in its own column (put the city and the country in separate columns).

Follow these rules and sorting, filtering, charts and pivot tables will all work first time.

Next: make your data easy to read with Formatting.