Microsoft Excel provides numerous ways to autofill numbers with or without dragging. Filling down the column is the most common method to complete a series.
But if you don’t want to drag to autofill numbers, this article should help you through the following guidance with other useful methods.
Watch Video – AutoFill Numbers in Excel Without Dragging
How to Autofill Numbers in Excel Without Dragging: 5 Methods
Here, we can apply 5 separate methods to autofill numbers in Excel. These methods do not require any kind of dragging. For avoiding any version or compatibility issues, try to use the Excel 365 version.
1. Using the ‘Fill Series’
Option to Autofill Numbers
For example, you want the multiples of a number within a fixed range.
We’re going to find the multiples of 15 here up to 200.
Step 1:Â
- Go to the Home tab.
- Go to the Editing group of commands.
- Click on the Fill drop-down.
- From the drop-down select Series command.
Step 2:Â
- You’ll find a box with multiple options to choose from.
- Insert 15 as Step Value; it’ll count the steps or intervals to show the next value sequentially.
- Type 200 inside the box of Stop Value; it’s the final value of your range within which you want to see the results.
Step 3:Â
- Click OK and you’ll see all the multiples of 15 within 200 at once.
Read more: How to Repeat Formula Pattern in Excel
2. Using Flash Fill Feature to Autofill Numbers
This option is really interesting if you want to fill a series with a pattern. Suppose, you have a list of names and ID numbers of some students in a school. You only need their ID numbers.
Step 1:
- Type the first ID number from Cell B4Â in the column of ID (Column B) in cell C4.
Step 2:
- Like the previous process, select the Fill drop-down under the Home tab.
- Click on the Flash Fill command now.
- The ID numbers of all students in your list will be displayed right away.
Similar Readings:
- AutoFill Cell Based on Another Cell in Excel
- How to AutoFill from List in Excel
- How to Create a Custom AutoFill List in Excel
- How to Fill Down Blanks in Excel
- How to AutoFill Sequential Letters in Excel
3. Editing Name Box to Autofill Numbers in Excel
Now here you’re seeing a chart of some pizza items with their prices. You want to calculate the final prices with 7.5% VAT. Let’s do it for the first case manually.
Step 1:
- Multiply C5Â with 1.075(for 7.5% VAT the multiplier will be 1.075) at Cell D5.
- You’ll get the final price including VAT for the item Margherita.
Step 2:
- Now copy D5.
Step 3:
- Go to the Name Box and type your cell range D5:D14Â for calculation.
- Press Enter.
Step 4:
- Now press CTRL+V to paste finally.
- You’ll get all the final prices including VAT for all pizzas under Column D.
Read More: How to Auto Number Cells in Excel
You can evaluate the final prices with VAT in another method which won’t need to insert the range in the Name Box.
Step 1:
- Like the previous method, calculate the final price at D5Â for the first one.
Step 2:
- Â Now copy D5.
Step 3:
- Navigate to C2 by pressing the Left Arrow key.
- Press the CTRL+Down Arrow key to reach the last cell (C14) in column C.
Step 4:
- Navigate to D14Â with the Right Arrow key and press the CTRL+Shift+Up Arrow key.
- It’ll select the whole column range (D2:D14) where you need the overall calculated results.
Step 5:
- Press CTRL+V to paste and you’ll find your desired results in column C.
Similar Readings:
- How to AutoFill Numbers in Excel
- How to Auto Number or Renumber after Filter in Excel
- How to AutoFill Ascending Numbers in Excel
- How to AutoFill Numbers in Excel with Filter
- How to Autofill Dates in ExcelÂ
- How to Enter Sequential Dates Across Multiple Sheets in Excel
5. Applying SEQUENCE Function to Autofill Numbers
The SEQUENCE function is another useful function to execute the series by auto-filling.
For example, you want a series starting from 5 and with a common difference of 12.
You want to get the 1st 15 values in this series.
Step 1:
- In the Function Bar, type:
=SEQUENCE(15, 1, 5, 12)
- Here inside the bracket,
- 15 denotes the no. of rows you want to see.
- 1 is for column no.
- 5 is the initial value you want to start from.
- And 12 is for the common difference which you’ll find as Step Value in the Function Bar during typing.
Step 2:
⇒ Press Enter and you’ll get the full series with a common difference of 12.
Similar Readings
- How to Link PowerPoint Chart to Excel
- How to Add Sequence Number by Group in Excel
- How to Repeat Number Pattern in Excel
- Excel Formulas to Fill Down Sequence Numbers Skip Hidden Rows
- How to AutoFill Months in Excel
- How to Create Automatic Rolling Months in Excel
How to Autofill Formula in Excel Without Dragging
Previously, we had seen that we can autofill the numbers in a series. Now, we are going to demonstrate how you can autofill formulas in a series without dragging.
1. Double Clicking on Fill Handle
Double-clicking on the Fill Handle is the most effective and easy way to fill the series with Formula.
Steps
- We need to calculate the Price with 7.5% VAT of the product mentioned in the range of cells B5:B14.
- To do this, select cell D5 and enter the following formula:
=C5+C5*0.075
- Then notice the Fill Handle icon in the corner of cell D5.
- Double-click on the Fill Handle.
- After double-clicking the Fill Handle, you will notice that the range of cells D5:D14 is now filled with the price value with a 7.5% rise.
- This is how we autofill numbers in Excel without dragging.
Read more: How to Increment Month by 1 in Excel
2. Using Keyboard Shortcut to Fill Adjacent Cells with Formulas
Using the keyboard shortcuts, we can fill the adjacent cell with formulas with ease. The filling up of the series can be done in both horizontal direction and vertical direction
Steps
- We need to calculate the Price with 7.5% VAT of the product mentioned in the range of cells B5:B14.
- To do this, select cell D5 and enter the following formula:
=C5+C5*0.075
- Now select the cell with this cell, and also selects the range of cells that you are going to fill up with the values of price.
- Then press Ctrl+D from the Keyboard.
- This will fill the range of cells D5:D14 with the formula mentioned in cell D5.
Similar Readings:
- How to Autofill a Column in Excel
- How to Fill Column in Excel with Same Value
- How to Fill Down to Last Row with Data in Excel
- How to AutoFill Formula When Inserting Rows in Excel
- Filling a Certain Number of Rows in Excel Automatically
- How to Perform Predictive AutoFill in Excel
Download Practice Workbook
You can download the practice sheet here which was prepared while writing this article.
Conclusion
These are all basic and very effective methods and functions you can use to autofill a series of numbers without dragging or filling it down. If you have any suggestions or opinions on this article let me know in the comment box.
Feel free to ask any questions or feedback through the comment section.
Related Articles
- How to Use Autofill Formula in Excel
- How to Autofill Days of Week Based on Date in Excel
- How to Auto Generate Number Sequence in Excel
- How to Autofill Dates in Excel Without Dragging
- How to Apply AutoFill Shortcut in Excel
- How to Turn Off AutoFill in Excel
- How to Auto Populate from Another Worksheet in Excel
- Applications of Excel Fill Series
- [Fix] Excel Fill Series Not Working
- Fix: Excel Autofill Not Working
- [Fixed!] Auto Fill Options Not Showing in Excel
- [Solved:] Excel Double Click AutoFill Not Working
- Excel VBA: Autofill Method of Range Class Failed
- How to Use VBA AutoFill in Excel
- AutoFill Formula to Last Row with Excel VBA
- [Fixed!] AutoFill Formula Is Not Working in Excel Table
- Drag Number Increase Not Working in Excel
Awesome!!!