📊 Lesson 1.3: Entering, Editing & Formatting Data — Types, Fill & Number Formats

This is the "Data" stage of our journey, and it's the foundation everything else stands on: clean data in, trustworthy answers out. In this lesson you'll learn the data types Excel recognizes and why text, numbers, and dates behave differently; how to enter and edit efficiently; how to select ranges; the enormous time-savers AutoFill and Flash Fill; and how number formats change the way a value looks without changing what it actually is. You'll finish with a small, real, properly formatted dataset in your workbook.

📚 What You'll Learn

By the end of this lesson, you will be able to:

Recognize Excel's core data types — text, numbers, and dates — and read alignment as a clue to type
Enter and edit data efficiently with Enter, Tab, F2, and the formula bar, and select ranges confidently
Use AutoFill and the fill handle for series, dates, and copies — and try Flash Fill for pattern extraction
Apply number formats (General, Number, Currency, Percentage, Date, Accounting) and understand that formatting changes display, not the stored value
Apply basic cell formatting — bold, borders, fill color, alignment, wrap text — and know why to be careful with merged cells

⏱️ Estimated Time: 50 minutes

🎯 Project: Enter and format a week of expenses with correct data types and number formats — a clean, real dataset you'll analyze later in the course.

In This Lesson

Data Types: Text, Numbers & Dates

Here's a fact that quietly explains a huge share of spreadsheet trouble: Excel treats different kinds of data differently. When you type into a cell, Excel decides what type of value it is, and that decision affects whether you can do math with it, how it sorts, and how it can be formatted. The three types you'll meet constantly are text, numbers, and dates.

Text — labels, names, notes, anything Excel can't read as a number. Text is for reading, not calculating. By default it left-aligns in the cell.
Numbers — values you can do math on: 42, 3.14, -8. By default numbers right-align.
Dates and times — a special kind of number. Excel stores a date as a serial number counting days from a starting point, then displays it as a date. Because dates are really numbers underneath, you can subtract one date from another to get the number of days between them. Dates right-align too.

That alignment behavior is your free diagnostic tool. Type what you think is a number and it sits on the left? Excel read it as text — perhaps because of a stray space, a letter, or a symbol — and it won't add up correctly. Type a date that lands on the left? Excel didn't recognize your date format, and date math won't work. Learning to glance at alignment catches these problems in seconds, long before they poison a formula.

⚠️ Watch Out — "numbers" that are secretly text

This is the classic beginner trap. A value like 1,200 pasted from a website, a code like 00123, or a figure with a trailing space can land in a cell as text that only looks like a number. It will refuse to sum, or =SUM() will quietly skip it. The tell is left-alignment (and sometimes a little green triangle warning). This is exactly why the course's through-line is "clean data first" — a formula is only ever as trustworthy as the data feeding it.

📖 Why dates are numbers

Under the hood, Excel stores each date as a whole number of days counted from a fixed starting date. Because of that, =B2-A2 where both are dates gives you the number of days between them, and =TODAY() plus a number lands you that many days in the future. Times are stored as the fractional part of a day. You don't have to think about the serial number often — but knowing dates are numbers explains why date math "just works" and why a date that's secretly text does not.

Entering & Editing Data

Entering data well is mostly about a few keys that let your hands stay on the keyboard. Small habits here add up to real speed over thousands of cells.

Entering

Click a cell, type, and commit the entry with a key that also moves you:

Enter commits and moves down — great for typing a column of values.
Tab commits and moves right — great for filling a row across.
A handy combo: Tab across a row of entries, then press Enter — Excel returns to the column where you started, one row down. Perfect for typing a table row by row.
Esc cancels what you're typing and leaves the cell unchanged.

Editing an existing cell

There are three reliable ways to edit a cell that already has content:

Double-click the cell to put your cursor right inside it.
Press F2 to jump into edit mode on the selected cell (this is the keyboard way; on some Macs use Ctrl+U or just double-click).
Click into the formula bar and edit there — the most precise option, especially for long formulas.

Be aware of the difference between editing and overwriting: if you simply select a cell and start typing, you replace its entire contents. To change just part of what's there, enter edit mode first (double-click, F2, or the formula bar). And whenever you make a mistake, Ctrl+Z undoes it — you truly cannot get stuck.

💡 Autocomplete and pick-from-list

As you type in a column, Excel offers to autocomplete entries that match text already in that column — press Enter to accept. This keeps your labels consistent (so Groceries is always spelled the same way), which matters enormously later when you sort, filter, and summarize. Consistency in your labels is part of "clean data first."

Selecting Ranges

Almost everything you do — formatting, formulas, charts — starts by selecting the cells you mean. A range is any block of cells, written with a colon, like A1:C10 (from A1 to C10). Here are the selections you'll use daily:

To select…	Do this
One cell	Click it
A block of cells	Click the first cell and drag, or click the first and `Shift`+click the last
A whole column or row	Click its header letter (column) or number (row)
Non-adjacent cells	Select the first, then hold `Ctrl` and click the others
The entire sheet	Click the small box where the column and row headers meet (top-left corner)
To the edge of your data	Hold `Ctrl`+`Shift` and press an arrow key

Once cells are selected, glance at the status bar at the bottom of the window: Excel shows you a live Sum, Average, and Count of the selection without you writing any formula at all. It's a fast way to sanity-check numbers — highlight a column of amounts and read the total instantly.

AutoFill, the Fill Handle & Flash Fill

Two features here feel like magic the first time you see them, and both save enormous amounts of typing.

AutoFill and the fill handle

Select a cell (or cells) and look at the tiny square at the bottom-right corner of the selection — that's the fill handle. Drag it and Excel continues the pattern:

Type Jan and drag the handle down — Excel fills Feb, Mar, Apr…
Type Monday and drag — you get the days of the week.
Type a date like 1/1/2026 and drag — Excel steps the dates forward day by day. (This course uses the US month/day/year order; if your computer uses a day-first region such as the UK, type dates the way your region expects.)
Type 1 in one cell and 2 in the next, select both, and drag — Excel continues 3, 4, 5… (it learned the step from your two values).
Drag a single number or a piece of text and Excel simply copies it down.

When you release the drag, a small AutoFill Options button appears, letting you choose between Fill Series, Copy Cells, and filling formatting only. Double-clicking the fill handle is a lovely shortcut: it fills down as far as the neighboring column has data, so you don't have to drag to the bottom of a long list.

Flash Fill — pattern recognition

Flash Fill watches what you're typing and, when it spots a pattern relative to a neighboring column, offers to finish the whole column for you. The classic example: you have full names in column A, you type the first name of the first person in column B, and as you start the second, Excel previews the first name for every row — press Enter to accept. It's brilliant for splitting or combining text (first names out of full names, tidy phone formats, and so on). You can also trigger it from the Data tab or with Ctrl+E.

⚠️ Flash Fill is a one-time action, not a live formula

Flash Fill types values once based on the pattern it saw — it does not update if the source data later changes, the way a formula would. It's perfect for a quick, one-off cleanup. When you need results that stay in sync with changing data, reach for text functions instead (we cover those in Lesson 2.3). Know which tool fits the job.

graph TD A["Need to fill many cells fast"] --> B{"What are you filling?"} B -->|"A series or dates"| C["Fill handle
drag to extend"] B -->|"Copy one value down"| D["Fill handle
drag to copy"] B -->|"Extract from a pattern"| E["Flash Fill
Ctrl+E"] C --> F["Clean data, less typing"] D --> F E --> F

Number Formats — Display vs Stored Value

This is one of the most important ideas in all of Excel, and getting it now will save you endless confusion: number formatting changes how a value is displayed, not the value itself. Format the number 1234.5 as currency and it shows as a currency amount; format it as a percentage and it shows differently again — but underneath, the stored value is unchanged. Formulas always calculate on the true stored value, never on the pretty display.

You'll find number formats on the Home tab, in the Number group — a dropdown of format names plus quick buttons for currency, percent, and comma style, and buttons to add or remove decimal places. Here are the formats you'll use most:

Format	What it does	Example stored value	How it displays
General	The default — no special formatting	1234.5	1234.5
Number	Fixed decimals, optional thousands separator	1234.5	1,234.50
Currency	A currency symbol next to the number	1234.5	$1,234.50
Accounting	Like Currency but aligns symbols and decimals in a column; shows zeros as a dash	1234.5	$ 1,234.50
Percentage	Multiplies by 100 for display and adds a % sign	0.25	25%
Date	Shows the underlying date serial number as a date	46023	1/1/2026

⚠️ The Percentage gotcha

Percentage format multiplies the display by 100. So the stored value 0.25 shows as 25% — which is correct. The trap is applying Percentage to numbers that are already in the cell: a cell holding 25 becomes 2500%, because Excel multiplies the stored value 25 by 100. (Typing 25 into a cell that was already formatted as a percentage is usually fine — Excel's automatic percent entry turns it into 25%.) When you want 25%, the safe habits are to type 25% (with the sign) or 0.25. Remember: percent format is a display of the true decimal value underneath.

Two more practical notes. First, Accounting vs Currency: they look similar, but Accounting lines up the currency symbols and decimal points in a neat column and shows a dash for zero — it's the tidy choice for a column of money. Second, if a cell fills with #####, don't panic: the value is fine, the column is just too narrow to show it. Widen the column and it appears.

💡 Format the cell, don't type the symbols

Resist typing currency symbols, commas, or percent signs as part of the number. If you type $1,200 as text you may create a value Excel can't do math on. Instead, type the plain number 1200 and apply Currency format. Let formatting handle the appearance while the cell holds a clean, calculable number — this is "clean data first" in miniature.

Basic Cell Formatting (& the Merge Caution)

Beyond number formats, the Home tab gives you the everyday visual tools that make a sheet readable. Used with restraint, they help a reader's eye; overused, they turn a sheet into confetti. The essentials:

Bold / Italic / Underline — emphasize headings and totals (Ctrl+B for bold).
Borders — draw lines around cells to group and separate; a border under a header row is a classic, clean touch.
Fill color — shade cells to highlight, e.g. a light fill behind a header row.
Alignment — left, center, right, plus top/middle/bottom. Numbers usually read best right-aligned; text left.
Wrap Text — lets a long label display on multiple lines within a cell instead of spilling over or being cut off, so the row grows taller rather than the text vanishing.
Column width & row height — drag the boundary between two column letters (or double-click it to auto-fit to the widest entry); drag row-number boundaries to change height.

⚠️ Merge Cells — handle with care

Merge & Center combines several cells into one big cell — tempting for centering a title across a table. But merged cells cause real problems: they break selecting, sorting, and filling, and they confuse many formulas and PivotTables. As a rule, avoid merging inside your data. If you only want a heading centered over several columns without the side effects, select the cells and use Center Across Selection (in the alignment options) instead — it looks merged but keeps each cell independent. Keep merges, if any, to purely decorative title areas well away from the data you'll analyze.

💡 Excel Tables do the formatting for you (a preview)

You don't have to hand-format every dataset. In Lesson 3.1 you'll meet Excel Tables (Insert → Table), which instantly give you banded rows, a styled header, filter buttons, and formatting that grows as you add data. For now, hand-formatting builds your instincts — just know the automatic option is coming and it's the more powerful long-term habit.

🎯 Project: A Week of Expenses

Let's turn everything into a small, real dataset — a week of your expenses — entered with the right data types and formatted so it reads cleanly. This is genuine "Data" stage work: we're getting trustworthy numbers into the grid so that later, when we add formulas and analysis, the answers can be trusted. Use the workbook you created in Lesson 1.2.

🏋️ Enter and format a week of expenses

Objective: A clean, correctly typed, well-formatted table of a week's expenses, with dates as real dates and amounts as real currency numbers.

Instructions (about 15 minutes):

(1 min) Open your practice workbook and go to the Expenses sheet. In row 3, make sure you have the labels Date, Item, Category, Amount in A3:D3.
(4 min) Enter about 7 rows of real (or realistic) expenses starting in row 4. Type dates in column A, text descriptions in B, a category in C (reuse consistent words like Groceries, Transport), and plain numbers in D. Use Tab across and Enter to drop to the next row.
(2 min) Check alignment: dates and amounts should sit on the right (numbers). If anything is stuck on the left, fix the entry so Excel reads it correctly.
(2 min) Try AutoFill: if your dates are consecutive, type the first date and drag the fill handle down instead of typing each one.
(2 min) Select the Amount column values and apply Currency (or Accounting) format from the Home tab. Note the numbers didn't change — only how they look.
(2 min) Format the header row (row 3): make it bold, add a fill color, and a bottom border. Auto-fit the column widths by double-clicking the column boundaries.
(2 min) Select all your amounts and read the live Sum in the status bar — that's your week's total, with no formula yet.

💡 Hint — a sample layout

            A            B                 C            D
Row 3:    Date         Item              Category     Amount   (bold, filled, bordered)
Row 4:    1/15/2026    Coffee            Dining        4.50
Row 5:    1/15/2026    Bus fare          Transport     2.75
Row 6:    1/16/2026    Groceries         Groceries    38.20
Row 7:    1/17/2026    Phone top-up      Bills        15.00
Row 8:    1/18/2026    Cinema ticket     Leisure      12.00
Row 9:    1/19/2026    Groceries         Groceries    22.40
Row 10:   1/20/2026    Train ticket      Transport     9.80

Amount column: Currency/Accounting format.
Dates right-align; amounts right-align. Status bar shows the Sum.

Keep categories spelled consistently (autocomplete helps) — you'll thank yourself when we sort, filter, and summarize this exact data later in the course.

✅ Project Completion Checklist

You entered about a week of expenses with a proper header row
Dates are real dates and amounts are real numbers (both right-align)
The Amount column is formatted as Currency or Accounting
The header row is bold, filled, and bordered, and columns are auto-fit
You read the week's total live in the status bar

🎯 Quick Quiz

Question 1: You format a cell containing 1234.5 as Currency so it shows as $1,234.50. What is the cell's actual stored value?

Question 2: You type what should be a number, but it sits on the left side of the cell and won't add up in =SUM(). What does that most likely mean?

Best Practices for Clean Data

✅ Do's

Store clean values; format the appearance. Type 1200 and apply Currency — don't type $1,200 as text.
Glance at alignment to check type. Numbers and dates should right-align; a left-aligned "number" is a red flag.
Keep labels consistent. Let autocomplete keep Groceries spelled one way, so sorting and summarizing work later.
Use AutoFill for series and Flash Fill for one-off pattern cleanup. Both save real time.

❌ Don'ts

Don't merge cells inside your data. It breaks selecting, sorting, and filling — use Center Across Selection if you only want a centered heading.
Don't type symbols into number cells. Currency signs and commas typed by hand can turn numbers into text.
Don't panic at #####. The value's fine — the column is just too narrow. Widen it.
Don't rely on Flash Fill to stay updated. It's a one-time fill, not a live formula.

💡 Pro Tips

Double-click the fill handle to fill down as far as the neighboring column has data — no dragging to the bottom of a long list.
Double-click the boundary between two column headers to auto-fit the column to its widest entry.

📓 Learning Journal

Keep a learning journal as you work through this course — a separate document, a note, or the Notes worksheet in your practice workbook. After each lesson, take a few minutes to write down:

Key concepts you learned
Techniques that clicked for you
Questions or confusion points to revisit
Ideas you want to try with your own data
Your progress and feelings about learning this — including where your confidence grew

✍️ This lesson's prompt: What surprised you about the difference between how a value is stored and how it's displayed? Did you hit a "number that was secretly text," and how did you spot it? Which time-saver — AutoFill or Flash Fill — do you think you'll reach for most, and on what real data of your own?

📝 Lesson Summary

🎓 Key Takeaways

Excel recognizes data types — text (left-aligns), numbers (right-align), and dates (numbers underneath, right-align). Alignment is a free clue to a value's type.
Enter efficiently with Enter (down) and Tab (right); edit with double-click, F2, or the formula bar; select ranges like A1:C10 for everything that follows.
AutoFill and the fill handle continue series, dates, and copies; Flash Fill (Ctrl+E) extracts patterns — but as a one-time action, not a live formula.
Number formats (General, Number, Currency, Accounting, Percentage, Date) change display, not the stored value; formulas always use the true value. Store clean numbers and format the look.
Basic formatting — bold, borders, fill, alignment, wrap text, and column/row sizing — makes data readable; avoid merging cells inside data and prefer Center Across Selection.

🎉 What You've Accomplished

You've done real "Data" stage work: you understand the types Excel recognizes, you can enter and edit quickly, you've used AutoFill, you know that formatting changes appearance without touching the underlying value, and you've built a clean, formatted week of expenses in your own workbook. That dataset is trustworthy — which is exactly what makes the formulas we add next actually mean something.

❓ Common Questions at This Stage

My numbers won't add up in SUM — what's wrong?

Most often the "numbers" are actually text. Check their alignment: real numbers right-align, while text left-aligns. This happens with values pasted from the web, entries with a stray space, or numbers typed with symbols. Re-type the value as a plain number (no currency sign or commas) and apply a number format instead — =SUM() will then include it. This is the "clean data first" habit in action.

Why does my cell show ##### instead of the number?

Nothing is broken — the column is just too narrow to display the value at its current format. Widen the column (drag the boundary between the column letters, or double-click it to auto-fit) and the number appears. Excel shows ##### specifically so it never shows you a misleadingly truncated number.

When should I use Flash Fill vs a formula?

Use Flash Fill for a quick, one-time cleanup — splitting names, tidying formats — when the source data won't change. Use a formula (like the text functions in Lesson 2.3) when you need the result to update automatically as the source data changes. Flash Fill types values once; a formula stays live.

🔭 Looking Ahead

That's Module 1 complete — you understand Excel, you're set up, and you can get clean data into the grid. Next, in Lesson 2.1: References & Operators — Absolute vs Relative, we make the grid calculate. You'll write your first real formulas, learn how cell references work when you copy them, and meet the crucial difference between relative references and absolute references ($A$1). This is where the "Formulas" stage of our journey begins.

✅ Before the Next Lesson

Have your formatted week-of-expenses table saved in your workbook
Make sure every amount is a real number (right-aligned) and every date a real date
Write your Learning Journal entry for this lesson

📚 Additional Resources

🌟 Encouragement for the Journey

You just built something real — clean, typed, formatted data that Excel actually understands. It might look modest, but this is the bedrock every formula, chart, and dashboard in this course will stand on. "Clean data first" isn't a slogan; you just lived it. Now let's make that grid start doing the thinking for you. 📊