Section: Excel Basics Tutorial: Autofill Row or Column - Same Data Show We often want to copy and paste, or replicate, cell data into other worksheet cells, but how can we do it quickly? By using a very powerful feature of Microsoft Excel called autofill. This comprehensive tutorial shows the Excel user how to autofill adjacent cells, selected cells that are not adjacent, or a cell range. For related tutorials, see the Excel Autofill Information Center. ○ This tutorial contains affiliate links. Read our disclosure policy to learn more. ○ A KeynoteSupport.com Tutorial There are two fast ways that an Excel user can fill adjacent cells in a column or row with the same numerical or text data. This section shows both methods. Method #1: Ctrl + D
Method #2: Using the Fill HandleThe Fill Handle is a powerful Excel tool for autofilling a linear series, a growth series, and many other types of data. The Fill Handle can also be used to autofill the same value AS LONG AS the value isn't a series starter. If so, Method #1 must be used. Examples of series include days of the week, month names, series involving dates, and time.
Sometimes we want to autofill non-contiguous cells in a row or column. For example, we may have an empty row between each data row for readability, but now want to autofill part of a column. Use the technique below to autofill individually selected worksheet cells.
A KeynoteSupport.com Tutorial The methods in the first section of this tutorial do not work if you wish to autofill a range of cells that covers multiple columns and rows (2-dimensional). Use the technique described below.
↑ Return to the top
This short tutorial shows how to copy formatting in Excel using Format Painter, Fill Handle and Paste Special options. These techniques work in all versions of Excel, from 2007 through Excel 365. After you've put a lot of effort in calculating a worksheet, you would usually want to add some finishing touches to make it look nice and presentable. Whether you are creating a repot for your head office or building a summary worksheet for the board of directors, proper formatting is what makes important data stand out and convey the relevant information more effectively. Fortunately, Microsoft Excel has an amazingly simple way to copy formatting, which is often overlooked or underestimated. As you have probably guessed, I am talking about the Excel Format Painter that makes it really easy to take the formatting of one cell and apply it to another. Further on in this tutorial, you will find the most efficient ways to use Format Painter in Excel, and learn a couple of other techniques to copy formatting in your sheets. When it comes to copying formatting in Excel, Format Painter is one of the most helpful and underused features. It works by copying the formatting of one cell and applying it to other cells. With just a couple of clicks, it can help you reproduce most, if not all of the formatting settings, including:
In all Excel versions, the Format Painter button is located on the Home tab, in the Clipboard group, right next to the Paste button: How to use Format Painter in ExcelTo copy cell formatting with the Excel Format Painter, just do the following:
Done! The new formatting is copied to your target cell. Excel Format Painter tipsIf you need to change formatting of more than one cell, clicking each cell individually would be tedious and time consuming. The following tips will speed things up. 1. How to copy formatting to a range of cells.To copy formatting to several adjacent cells, select the sample cell with the desired format, click the Format Painter button, and then drag the brush cursor across the cells that you want to format. 2. How to copy format to non-adjacent cells.To copy formatting to non-contiguous cells, double-click the Format Painter button instead of single-clicking it. This will "lock" the Excel Format Painter on, and the copied formatting will be applied to all cells and ranges that you click/select until you press Esc or click on the Format Painter button one final time. 3. How to copy the formatting of one column to another column row-by-rowTo quickly copy the format of the entire column, select the heading of the column whose formatting you want to copy, click Format Painter, and then click the heading of the target column. As demonstrated in the following screenshot, the new formatting is applied to the target column row-by-row, including the column width: In a similar manner, you can copy the format of the entire row, column-by-column. For this, click the sample row heading, click Format Painter, and then click the heading of the target row. As you have just seen, the Format Painter makes copying format as easy as it can possibly be. However, as is often the case with Microsoft Excel, there is more than one way to do the same thing. Below, you will find two more methods to copy formats in Excel. We often use the fill handle to copy formulas or auto fill cells with data. But did you know that it can also copy Excel formats with just a few clicks? Here's how:
That's it! The cell values revert to the original values, and the desired format is applied to other cells in the column:
Tip. To copy the formatting down the column until the first empty cell, double-click the fill handle instead of dragging it, then click AutoFill Options, and select Fill Formatting Only. Excel Format Painter and Fill Handle work great with small selections. But how do you copy the format of a specific cell to an entire column or row so that the new format is applied to absolutely all cells in a column/row including blank cells? The solution is using the Formats option of Excel Paste Special.
Alternatively, select the Formatting option from the Paste Special pop-up menu. This will display a live preview of the new format, as shown in the screenshot below: Shortcuts to copy formatting in ExcelRegrettably, Microsoft Excel doesn't provide a single shortcut that you could use to copy cell formats. However, this can be done by using a sequence of shortcuts. So, if you prefer working form the keyboard most of the time, you can copy format in Excel in one of the following ways. Excel Format Painter shortcutInstead of clicking the Format Painter button on the ribbon, do the following:
Please note, the shortcut keys for Format Painter in Excel should be pressed one by one, not all at once:
Paste Special formatting shortcutAnother quick way to copy format in Excel is by using the keyboard shortcut for Paste Special > Formats:
If someone still uses Excel 2007, press Shift + F10, S, T, Enter. This key sequence does the following:
These are the fastest ways to copy formatting in Excel. If you've accidentally copied a wrong format, no problem, our next article will teach you how to clear it :) I thank you for reading and hope to see you on our blog soon! You may also be interested in |