MS Excel - Fill in Excel

In Microsoft Excel, the Fill feature helps you quickly copy data, create sequences, or apply patterns across cells without manually typing. It’s a time-saving tool that works for numbers, dates, text, formulas, and custom lists. Here’s a complete guide:

1. Types of Fill in Excel

A. Fill Down & Fill Right

Used to copy data or formulas to adjacent cells.

  • Shortcut Keys:

    • Fill Down → Ctrl + D (copies content/formulas down)

    • Fill Right → Ctrl + R (copies content/formulas right)

  • Steps:

    1. Enter data in the first cell.

    2. Select the cell along with the range where you want to fill.

    3. Use the shortcut or Home → Fill → Down/Right.

B. AutoFill Using the Fill Handle

AutoFill uses the small square handle at the bottom-right corner of a selected cell.

  • Steps:

    1. Type the value in the first cell.

    2. Hover over the cell’s bottom-right corner until you see a small black “+” sign.

    3. Drag down, right, up, or left to fill the cells.

  • Uses:

    • Copy data: Drag the fill handle directly.

    • Create number sequences: Type 1 and 2, select both, then drag.

    • Fill months/days: Type “January” or “Mon” and drag to auto-complete.

    • Extend formulas: Excel adjusts cell references automatically.

C. Fill Series

For more control over patterns, use the Fill Series option.

  • Steps:

    1. Select the starting cell.

    2. Go to Home → Fill → Series.

    3. Choose from these options:

      • Series in: Rows or Columns

      • Type:

        • Linear → Adds a fixed value (e.g., 1, 2, 3…)

        • Growth → Multiplies by a fixed factor (e.g., 2, 4, 8…)

        • Date → Fills days, weekdays, months, or years

        • AutoFill → Uses Excel’s built-in pattern recognition

      • Step Value: Defines the increment (e.g., +2).

      • Stop Value: Sets where the series should end.

D. Flash Fill (Excel 2013 and Later)

Flash Fill automatically detects patterns and fills data instantly.

  • Shortcut: Ctrl + E

  • Steps:

    1. Start typing the desired pattern in the next column.

    2. Excel predicts the rest based on your input.

    3. Press Ctrl + E or Data → Flash Fill to accept.

Example:
If you have “Rahul Sharma” in column A and type “Rahul” in column B, Flash Fill can auto-extract all first names.

E. Fill Formatting Only

If you want to copy only formats and not the data:

  1. Drag the fill handle as usual.

  2. After releasing, click the AutoFill Options icon.

  3. Select Fill Formatting Only.

2. Where to Find the Fill Options

  • Ribbon Path: Home → Editing → Fill

  • Options available:

    • Down

    • Right

    • Up

    • Left

    • Series

    • Justify

    • Flash Fill

3. Tips and Tricks

  • Double-Click Fill Handle → Auto-fills down based on adjacent data length.

  • Custom Lists → Create your own lists (e.g., Team A, Team B, Team C) via
    File → Options → Advanced → Edit Custom Lists.

  • Undo Wrong Fill → Press Ctrl + Z to revert.