How to duplicate rows based on cell value in a column?
When working with Excel data tables, you may sometimes need to duplicate entire rows according to a value in a specific column. For example, imagine you have a table where column D indicates a number, and your goal is to copy or insert each row as many times as the corresponding cell’s value in column D. This can be useful for data expansion, preparing datasets for testing, simulating inventory, or generating repeated order lines. Efficiently achieving this without manual repetition helps maintain data integrity and greatly speeds up the workflow, especially for larger tables.
 |  |  |
Duplicate rows multiple times based on cell values with VBA code
Copy and insert rows based on a specified number of times with a handy tool - Kutools for Excel
Duplicate rows via Power Query transformation
Duplicate rows multiple times based on cell values with VBA code
If you need to quickly create duplicates of entire rows according to the values in a specific column (for example, in column D), VBA offers a direct, efficient solution. This approach is particularly valuable when handling large datasets, as it automates the process and helps ensure accuracy. However, working with VBA requires some basic familiarity with the Excel Developer tab and an understanding of how to execute macros. The main advantage is flexibility—the code can be adjusted to fit different ranges or target columns. On the other hand, if you're not comfortable with VBA or security policies block macros in your environment, please consider one of the alternative solutions below.
1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following code in the Module Window.
VBA code: Duplicate rows multiple times based on cell value:
Sub CopyData()
'Updateby Extendoffice
Dim xRow As Long
Dim VInSertNum As Variant
xRow = 1
Application.ScreenUpdating = False
Do While (Cells(xRow, "A") <> "")
VInSertNum = Cells(xRow, "D")
If ((VInSertNum > 1) And IsNumeric(VInSertNum)) Then
Range(Cells(xRow, "A"), Cells(xRow, "D")).Copy
Range(Cells(xRow + 1, "A"), Cells(xRow + VInSertNum - 1, "D")).Select
Selection.Insert Shift:=xlDown
xRow = xRow + VInSertNum - 1
End If
xRow = xRow + 1
Loop
Application.ScreenUpdating = False
End Sub
3. Then press F5 key to run this code. The entire rows will be duplicated multiple times based on the cell value in column D, matching your requirements.
Copy and insert rows based on a specified number of times with a handy tool - Kutools for Excel
If you prefer a method without using code, or you’d like a user-friendly interface to manage batch duplication, Kutools for Excel offers a practical solution. Its Duplicate Rows / Columns Based on Cell Value feature allows you to copy and insert rows quickly according to the number specified in the relevant cells. This is ideal for those who handle such repetitive row operations regularly and need a more visual, customizable process. Kutools maintains the layout and formatting of the original table during duplication, reducing the risk of accidental errors. This method works smoothly regardless of your VBA experience and significantly improves efficiency for medium to large data ranges.
- Click Kutools > Insert > Duplicate Rows/Columns Based on Cell Value to enable this feature;
- Then, choose Copy and insert rows option, and specify the cells of the Insert Range and Repeat Times separately in the dialog box. Confirm your choices and click OK.
Duplicate rows via Power Query transformation
Power Query provides a robust, repeatable solution for duplicating rows based on a column value—ideal for larger tables or when you need to refresh data frequently. By using this tool, you can expand rows according to numeric indicators without formulas or code. This method works well for users who want a visually explainable, step-by-step process and need repeatability with future data updates.
1. Add your data to an Excel Table (select range and press Ctrl+T). Go to Data > From Table/Range to load your table into Power Query.
2. In Power Query Editor, Select Add Column tab and click Custom Column.
3. Type the following formula to generate a list for each row according to the value in column D. ( Note: You should replace the [D] to your real column header. for example, here, I will select the Duplicate Times and click Insert button to replace the [D] argument.)
List.Repeat({1}, [D])
4. Click the Expand button beside the new custom column and click Expand to New Rows.
5. Then, you can see the data has been duplicated based on the values in column D, see screenshot:
6. Remove the helper columns if needed, and click Close & Load to return data to Excel.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in