Copy formulas and data with the Excel fill handle

Content
  1. Copy data, formulas, formatting and more
  2. Working with the Excel fountain pen
  3. Copy data without formatting
  4. Copy Formulas
  5. Automatic cell filling
  6. Add preset content to cells
  7. Add a custom list to a fill marker

Copy data, formulas, formatting and more

The Excel fill handle is a multipurpose little black dot or square in the lower-right corner of an active cell that can save you time and effort when used to copy the contents of one or more cells to adjacent cells on a worksheet. Using the fill handle includes:

  • Copying and formatting data

  • Copy Formulas

  • Fill cells with a range of numbers, such as odd or even numbers

  • Add the names of the days of the week or month to a sheet

  • Add custom lists of frequently used data, such as department names or report titles, to a sheet

These instructions apply to versions of Excel 2019, 2016, 2013, 2010, and Excel for Office 365.

Working with the Excel fountain pen

The fill handle works in tandem with the mouse. Try this simple example in your own Excel spreadsheet:

  1. highlight the cells containing the data to be copied or, in the case of a range, expanded.
  2. Hover over the fill handle – the pointer changes to a small black plus sign (

  3. Press and hold left mouse button and then drag the fill handle to the target cells.

  1. highlight the cells containing the data to be copied or, in the case of a range, expanded.

  2. Hover over the fill handle – the pointer changes to a small black plus sign (

  3. Press and hold left mouse button and then drag the fill handle to the target cells.

Copy data without formatting

By default, when data is copied using a fill handle, any formatting applied to the data is also copied. To copy data without copying the formatting, after copying the data using the fill handle, Excel displays a button: Autofill options below and to the right of the newly filled cells.

By pressing the button Autofill options a list of options opens, including:

  • Copy cells

  • Just fill in the format

  • Fill without formatting

  • Flash Fill

on . to press Fill without formatting copy the data with the fill handle, but not with the original formatting.

Copy Formulas

Formulas copied using the fill handle are updated to use the data in the new location if it was created using cell references.

Cell references are the column letter and row number of the cell containing, for example, the data used in the formula A1 or D23 † As an example:

Instead of entering actual numbers in a formula in H1 to make this formula,

 = 11 + 21 

cell references are used instead and the formula becomes:

 = F1 + G1 

In both formulas, the answer is in the cell H1 is 32, but the second formula, since it was created using cell references, can be copied to the cells using the fill handle H2 and H3 and it returns the correct result for the data in those rows.

Automatic cell filling

When Excel recognizes the contents of a cell as part of a series, autofill the other selected cells are the next items in the series. To do this, you need to enter enough data to show Excel the template, such as the number of two you want to use.

Here is a good example of the AutoComplete feature in Excel:

  1. Enter the number 2 in a cell D1 and press the key Input

  2. Enter the number 4 in a cell D2 and press Enter

  3. Select cells D1 and D2 nasty Highlight their.

  4. Press and hold mouse pointer the fill handle in the lower-right corner of the cell D2

  5. Drag the fill handle to a cell D6

  6. Cells of D1 before D6 must contain numbers: 2, 4, 6, 8, 10, 12.

Add preset content to cells

Excel also has predefined lists of names, days of the week, and months of the year that you can add to a worksheet using the fill handle. For example, you can add the days of the week to your worksheet.

Excel also includes a predefined list of short forms for the days of the week, such as Sun, Mon, etc., as well as the full and short names of the months – January, February, March and January, February, March, which can be be added to the worksheet using the steps listed.

  1. Enter Sunday in a cell A1

  2. Press key Enter on keyboard.

  3. Click the cell again A1 to make it active.

  4. Venue mouse pointer about fill mark in the lower right corner of the active cell.

  5. mouse pointer turns into a little black plus sign () when placed over the fill lever.

  6. When the mouse pointer changes to a plus sign, hold mouse button.

  7. Drag the fill handle to a cell G1 to automatically fill in the days of the week from Monday to Saturday.

Add a custom list to a fill marker

Excel also allows you to add your own lists of names, such as department names or worksheet titles, to use with the fill handle. The list can be added to the pour handle by entering the names manually or by copying them from an existing list on the sheet.

Enter a new auto-complete list

  1. Click on the tab File on the tape.

  2. Click parameters: to open the Excel Options dialog box.

  3. Go to the tab Additionally in the left panel.

  4. Go to section Occur often list of options in the right pane

  5. Press the button Edit Custom List in the right pane to open the dialog box Edited list

  6. Enter a new list in the window List items

  7. Click To add to add a new list to the window Custom Lists on the left panel.

  8. Click Okay twice to close all dialog boxes and return to the worksheet.

Import a custom autocomplete list from a spreadsheet

To import custom autocomplete lists based on the contents of your spreadsheets, follow these steps for a more hands-on approach:

  1. highlight a range of cells on a worksheet containing list items, such as from A1 before A5

  2. Completely steps 1-5 above to open the dialog box Edited list

  3. The range of previously selected cells should be displayed as absolute cell references, such as: $A$1: $A$5 in the field Import a list from cells At the bottom. dialog box.

  4. Press the button Import

  5. A new autocomplete list will appear in the window Custom Lists

  6. Click Okay twice to close all dialog boxes and return to the worksheet.

Leave a Reply

Your email address will not be published.