Entering and Formatting Data
Master professional data entry techniques in Excel — entering different data types correctly, using AutoFill and Flash Fill, applying number formatting, and making spreadsheets look polished with cell formatting and conditional highlighting.
Video
Watch the lesson video, then complete the reading and challenge.
Presentation Slides
Review the slides below, then complete the reading and challenge.
1 / 9
Lesson Notes
Read through the key concepts before you try the challenge.
Real-World Scenario
You have been asked to enter this month's office supply purchase data into Lakeside Medical Associates' supply tracker — 25 rows of items including product names, quantities, unit costs, and purchase dates. If you enter dates as text instead of date values, date-based sorting will not work. If you enter costs without currency formatting, the spreadsheet will look unprofessional in the monthly report. If you format costs as numbers when they should be dates, calculations will fail. This lesson teaches you how to enter data correctly the first time, format it professionally, and use Excel's productivity features to work faster.
Data Types: Text, Numbers, and Dates
Excel distinguishes between three fundamental data types — text, numbers (including currency and percentages), and dates. Entering data in the correct type is critical because Excel's formulas, sorting, filtering, and calculations work differently depending on whether a cell contains a number or text:
- Text values (labels) are strings of characters — item names like 'Exam Gloves Medium,' category labels like 'Medical,' and status values like 'In Stock' are all text. Text is automatically left-aligned in cells and cannot be used in arithmetic calculations. When Excel detects that an entry is not a valid number or date, it stores it as text. If a number is stored as text (a common data quality issue), SUM formulas will ignore it — always confirm that numeric columns contain actual numbers, not text-formatted numbers.
- Number values are numeric quantities that Excel can calculate — quantities (50), costs (12.99), and counts (200) are all numbers. Numbers are automatically right-aligned in cells. You can apply number formats to display the same underlying number in different ways: 12.99 displayed as $12.99 (currency), 0.15 displayed as 15% (percentage), or 12.99 displayed as 13 (no decimals) — the underlying value never changes, only the display.
- Date values are actually serial numbers that Excel stores internally (January 1, 1900 is serial number 1, today's date is approximately serial number 46000+) but displays as recognizable dates. Because dates are numbers, you can subtract one date from another to calculate elapsed days, use dates in IF formulas, and sort date columns chronologically. Always enter dates in a format Excel recognizes (5/5/2025, May 5 2025, or 2025-05-05) — if you type '5th of May' or 'May 5th' Excel may store it as text, breaking date-based calculations.
- Detecting data type issues: select a cell and look at the horizontal alignment (left = text, right = number or date). Check the formula bar for any text entry markers. Select a numeric column and look at the Sum display in the Status Bar at the bottom of the screen — if the Status Bar shows Sum: 0 for a column you expect to total, your numbers are stored as text. Select the column, use Data > Text to Columns, and click Finish to convert text-formatted numbers to actual numbers.
Efficient Data Entry Techniques
Excel provides several tools that dramatically speed up data entry — using them correctly means you spend less time typing repetitive data and more time doing productive work:
- Tab moves right across a row, Enter moves down a column — when entering data in a table, press Tab after each cell in a row to move right across all columns, then press Enter to jump to the first column of the next row (back to the starting column of your tab sequence). This is significantly faster than reaching for the mouse after every entry. Shift+Tab moves left, Shift+Enter moves up.
- AutoFill extends a pattern down a column — type the first two values in a series (January, February OR 1, 2 OR Monday, Tuesday), select both cells, then drag the small green square (the fill handle) at the bottom-right corner of the selection down as many rows as you need. Excel recognizes the pattern and continues it automatically. AutoFill also works for dates, fiscal quarters, and custom lists you define in File > Options > Advanced > Edit Custom Lists.
- Flash Fill is one of the most impressive Excel productivity features — it recognizes the pattern of what you are trying to create and fills the rest of the column automatically. For example: if your column A contains full names like 'Maria Rodriguez' and you want column B to contain only first names, type 'Maria' in B2. When you start typing 'Dr' in B3 for 'Dr. James Whitfield,' Flash Fill recognizes the first-name pattern and offers to fill B3:B100 instantly. Press Enter to accept. Flash Fill is accessed via Data > Flash Fill or by pressing Ctrl+E.
- AutoComplete suggests completions as you type — if you type 'Ex' in a column that already contains 'Exam Gloves,' Excel suggests 'Exam Gloves' as the completion. Press Enter to accept the suggestion, or keep typing to override it. AutoComplete is helpful for maintaining consistent text values (so 'Medical' is never entered as 'medical' or 'MEDICAL') but can slow you down if you are entering entirely new values — press Delete to clear the suggestion without accepting it.
Number Formatting and Cell Appearance
Number formatting makes spreadsheet data readable, professional, and appropriate for its purpose. The underlying numbers never change — only how they appear in the cell changes. All formatting is applied through the Home tab > Number group or the Format Cells dialog (Ctrl+1):
- Currency format displays numbers with a dollar sign (or other currency symbol) and two decimal places — for example, 12.5 displays as $12.50. Use Accounting format for financial reports (aligns currency symbols and decimal points for cleaner column alignment) or Currency format for standalone cost values. Apply via the '$' dropdown in the Number group or Ctrl+1 > Number > Currency.
- Percentage format multiplies the cell value by 100 and appends a percent sign — the value 0.15 displays as 15%. Always enter percentages as their decimal form (0.15) before applying percentage format, not as '15%' typed directly (Excel handles both, but the decimal form is more predictable). Apply via the '%' button in the Number group.
- Date format controls how a date serial number is displayed — 'MM/DD/YYYY' shows 05/05/2025, 'MMMM D, YYYY' shows May 5, 2025, and 'MMM-YY' shows May-25. Choose the format appropriate for your audience — internal trackers often use short dates (M/D/YY), while patient-facing documents use full written dates.
- Conditional formatting automatically applies cell colors, font colors, or icons based on cell values — without you having to manually review each row. Select a range, click Home > Conditional Formatting > Highlight Cells Rules, and set your rule: for example, 'Less Than' 10 with a red fill highlights any quantity below the reorder threshold. This is how supply tracker status columns automatically turn red when stock is low at Lakeside Medical Associates — the formatting updates dynamically as values change.
- Format as Table converts a plain data range into an Excel table with automatic alternating row colors, filter arrows in the header, and automatic formula expansion as you add rows — click anywhere in your data, press Ctrl+T, and click OK. Table formatting is the professional standard for data ranges that will be regularly updated, sorted, or filtered.
Quick Reference: Data Entry and Formatting
Data Entry and Formatting Quick Reference — building clean, professional spreadsheets
Responsible Use
Formatting errors in financial spreadsheets can create misleading information with real consequences. If a cost column is formatted to show no decimal places but the underlying data has decimals, the displayed total will look correct while the actual calculated total may differ significantly. Always use 'Show decimal places' (two places minimum for financial data) in any spreadsheet that feeds into financial reporting at Lakeside Medical Associates. Before sharing any financial data with the office manager or physician owner, have a second person verify that all totals match what would be expected — a formatting error that makes $1,234.50 appear as $1,234 may seem minor but can compound into significant discrepancies across a full year of reporting.
AI Assist
💡 AI Task: Ask ChatGPT — 'I need to set up conditional formatting rules in Excel for a medical office supply tracker. The tracker has a Quantity column and a Reorder Level column. What conditional formatting rules should I create to automatically highlight: (1) items at or below the reorder level in red, (2) items between 1–5 units above the reorder level in yellow, and (3) items well-stocked (more than 5 units above reorder level) in green? Give me the exact steps for creating each rule in Excel 2019.' Follow the steps to apply all three conditional formatting rules to your supply tracker.
Knowledge Check
You have a column of cost values (12.5, 34.75, 8.0) stored as numbers. You want them to display as $12.50, $34.75, and $8.00. What should you do?
Challenge
Apply what you've learned in this lesson.
Build a professionally formatted supply data entry sheet for Lakeside Medical Associates with 20 rows of realistic supply data, correct data types, number formatting, conditional formatting, and a table format. Every element must be done correctly — no text-formatted numbers, no manual color fills instead of conditional formatting.
- Create a new Excel worksheet named 'Supply Log.' Enter column headers in row 1: Item Name, Category, Quantity, Unit Cost, Total Cost, Reorder Level, Status, Date Ordered. Enter 20 rows of realistic medical office supply data across all columns. Use at least 4 different categories (Medical, Office, Cleaning, Break Room). Include a mix of items where some quantities are below their reorder level.
- Apply correct data types: ensure Quantity and Reorder Level columns contain numbers (not text), Unit Cost and Total Cost contain numbers (not text), Date Ordered contains actual date values (formatted as MM/DD/YYYY), and Item Name and Category columns contain text.
- Apply number formatting: format Unit Cost and Total Cost as Currency with 2 decimal places, format Quantity and Reorder Level as Number with 0 decimal places, and format Date Ordered as a short date (MM/DD/YY).
- Apply conditional formatting to the Quantity column: red fill for any quantity at or below the Reorder Level value in the same row (use a formula-based rule: =C2<=F2 applied to the Quantity column), and green fill for quantities greater than double the reorder level. Verify the colors appear correctly for at least 3 items in each category.
- Convert the data range to an Excel Table (Ctrl+T) and apply a professional table style. Use AutoFill to fill a 'Date Ordered' pattern for rows 11–20 (continue a weekly sequence from row 10). Verify Filter arrows appear in all column headers.