[Excel Shortcut] Drag and Fill Formula (Windows & Mac)
Written by Kasper Langmann
Microsoft Excel is a powerful tool that can simplify and automate many tasks. One of the most useful features is the ability to drag and fill formulas across cells. This feature can save you a lot of time and effort, especially when dealing with large data sets. In this guide, we will explore the drag and fill formula feature in Excel, covering both Windows and Mac operating systems.
Understanding the Drag and Fill Feature
The drag and fill feature in Excel is a time-saving tool that allows you to automatically fill cells with data or formulas. Instead of manually entering data or formulas into each cell, you can simply enter the data or formula into one cell, then use the drag and fill feature to automatically fill the remaining cells.
This feature is particularly useful when you need to apply the same formula to a range of cells. For example, if you need to calculate the total sales for each month in a year, you can enter the formula into the cell for January, then use the drag and fill feature to automatically fill the cells for the remaining months.
The drag and fill feature works with both numerical data and text data. For numerical data, Excel will automatically increment the numbers as you drag and fill. For text data, Excel will simply repeat the text in each cell.
How to Use the Drag and Fill Feature in Excel
Windows
To use the drag and fill feature in Excel on a Windows computer, follow these steps:
- Enter the data or formula into the first cell.
- Click on the cell to select it.
- Move your cursor to the bottom-right corner of the cell. The cursor should change to a small black cross.
- Click and hold the left mouse button, then drag the cursor across the cells you want to fill.
- Release the mouse button to fill the cells with the data or formula.
Note that if you are dragging and filling a formula, Excel will automatically adjust the cell references in the formula as you drag and fill. This means that if your formula references other cells, the references will be updated to match the row or column of the filled cell.
For example, if you have a formula in cell A1 that references cell B1, and you drag and fill the formula down to cell A2, the formula in cell A2 will automatically update to reference cell B2.
Mac
The process for using the drag and fill feature in Excel on a Mac is similar to the process on a Windows computer. Here are the steps:
- Enter the data or formula into the first cell.
- Click on the cell to select it.
- Move your cursor to the bottom-right corner of the cell. The cursor should change to a small black cross.
- Click and hold the left mouse button, then drag the cursor across the cells you want to fill.
- Release the mouse button to fill the cells with the data or formula.
As with Windows, Excel on a Mac will automatically adjust the cell references in the formula as you drag and fill. This means that if your formula references other cells, the references will be updated to match the row or column of the filled cell.
For example, if you have a formula in cell A1 that references cell B1, and you drag and fill the formula down to cell A2, the formula in cell A2 will automatically update to reference cell B2.
Advanced Drag and Fill Techniques
While the basic drag and fill feature is quite straightforward, there are some advanced techniques that can help you get even more out of this feature.
Dragging and Filling Non-Sequential Data
By default, when you drag and fill numerical data, Excel will automatically increment the numbers. However, there may be times when you want to fill cells with non-sequential data. For example, you might want to fill cells with the same number, or with a series of numbers that increases by a certain increment.
To do this, you can use the Fill Handle Options button that appears after you drag and fill. This button allows you to choose how Excel fills the cells. You can choose to fill cells with the same number, with a series of numbers that increases or decreases by a certain increment, or with a custom list of numbers.
Dragging and Filling Formulas with Absolute References
As mentioned earlier, when you drag and fill a formula, Excel will automatically adjust the cell references in the formula. However, there may be times when you want to keep the cell references constant. For example, you might have a formula that references a specific cell, and you want that reference to remain the same as you drag and fill the formula.
To do this, you can use absolute references in your formula. An absolute reference is a cell reference that remains constant, even when the formula is copied to other cells. To create an absolute reference, you simply add a dollar sign ($) before the column letter and row number in the cell reference.
For example, if you have a formula in cell A1 that references cell B1, and you want the reference to cell B1 to remain constant as you drag and fill the formula, you would enter the reference as $B$1 in the formula.
Conclusion
The drag and fill feature in Excel is a powerful tool that can save you a lot of time and effort. Whether you’re working with numerical data, text data, or formulas, this feature can help you quickly and easily fill cells with the data you need.
By understanding how to use this feature, and by learning some of the advanced techniques, you can get even more out of Excel and make your work more efficient and productive.