Automatic copy training in Excel (the easiest way)

Read this article to be able to enter data automatically in worksheet cells. (For Office 365 and Excel 2010, 2013, 2016, 2019)

Use the Auto Fill feature to fill Excel cells with data that follows a pattern or is based on data in other cells .

Note: This article describes how to automatically enter values ​​into other cells. It does not explain how to enter data manually or in multiple worksheets at the same time.

1. Select one or more cells that you want to use as a base to fill extra cells.

For series such as 1, 2, 3, 4, 5 and…, type the numbers 1 and 2 in the first two cells. For numbers 2, 4, 6, 8 and…, enter the numbers 2 and 4.

For series 2, 2, 2, 2 and…, just type 2 in the first cell.

2- Select the desired cells and click and drag on the small square on the right. 9597dcff-83e7-4954-896c-cced4cfc9fae

3- If necessary, click on the Auto Fill Options button 2fd4f5cb-718d-42a8-b461-6570267903c8 Click and select the option you want.

Copy Excel Formulas Even Quicker using Auto-fill

Ask anyone who takes an Excel Basic class and they’ll tell you one of the best tools in Excel is called the auto-fill handle. You might know it more commonly as the black plus sign you get when hovering over the lower-right corner of a selected cell. If you drag the fill handle down the spreadsheet, your formula copies down to each row you select.

Excel Auto-fill

But what if you have hundreds or thousands of rows? That’s a lot of dragging.

Next time, write your formula, then when you see the auto-fill handle double-click on it. Your formula automatically copies down to every row that has data next to it. And you know what ? It’s still handy if you only have 3 rows.


Automatically Copying Formatting

One of the foundational features of Excel is to allow one cell to be equal to another cell. For instance, you could use the most simple of formulas in a cell:


This copies the contents from cell C7 to the current cell, and updates whenever the contents of cell C7 change. What if you are not just interested in copying cell values, but also want to copy formatting from one cell to another?

Unfortunately, there is no intrinsic way to do this in Excel. There are two workarounds you can try, however. First, you can create a macro that will find out whenever cell C7 changes, and if it does, the macro copies the contents of the cell (including formatting) to the target cell. For instance, the following macro will run every time there are changes in the worksheet.

When the change is in cell C7, then the contents of C7 are copied to cell E3 on Sheet1.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Not Intersect(Target, Range("C7")) Is Nothing Then
        Range("C7").Copy (Worksheets("Sheet1").Range("E3"))
    End If
End Sub

There are some downsides to this approach. First, it can be slow, particularly if you have quite a few cells that you want to copy in this manner. In addition, the macro only runs if the contents of cell C7 are actually changed, not if the formatting alone of C7 is changed. (There is no way to trigger an automatic event whenever formatting is changed.)

An alternative to the macro approach is to use the Camera tool in Excel. This has been covered in other issues of ExcelTips, but essentially the camera is a way to copy a dynamic image of a range of cells from one place to another. It is the image of the source cells that is shown, and it is shown as a graphic, not as the contents of any target cells. Since the graphic is dynamic, whenever the source cells are changed (including formatting), the image is also updated to reflect the change.

To use the Camera tool, you must customize your toolbar so that the tool is available; it is not available by default. When you are doing your customizing, the Camera tool is available on the Commands tab in the Tools section. It is near the bottom of the list of commands and looks—oddly enough—like a small camera.

With the Camera tool in place, follow these steps to use it:

  1. Select the cells or range of which you want a picture taken.
  2. Click on the Camera tool. The mouse pointer changes to a large plus sign.
  3. Change to a different worksheet.
  4. Click where you want the top left-hand corner of the picture to appear. The picture is inserted as a graphic on the worksheet.

Leave a Reply

Your email address will not be published. Required fields are marked *