Microsoft Excel is a powerful software. We can perform numerous operations on our datasets using excel tools and features. There are many default Excel Functions that we can use to create formulas. Many institutions use excel files to store important data. Sometimes, they need to repeat cell values and it’s easier to do that in excel. This article will show you 6 easy methods to Repeat Cell Value X Times in Excel.
Download Practice Workbook
Download the following workbook to practice by yourself.
6 Easy Methods to Repeat Cell Value X Times in Excel
Whenever we are working with datasets in Excel worksheets, it becomes essential to repeat cell values as many times as we need. This is for various reasons. We can do that manually. But that’s tiresome and can take a considerable amount of time. So, we can create simple formulas inserting built-in functions to perform the task. In this article, we’ll show you all the methods to repeat cell value X times. To illustrate, we’ll use the following dataset as an example. For instance, the dataset contains 3 Products. Here, we’ll repeat the products according to the Repeat Time.
1. Repeat Cell Value X Times with Helper Column and VLOOKUP Function in Excel
In our first method, we will take the help of helper columns and the VLOOKUP function. This function searches for a value in a range. Then, it returns a cell value from the specified column. Therefore, follow the steps below to perform the task.
- First, we’ll input 1 in cell B5 of the helper column.
- Then, select cell B6.
- Type the formula:
- Subsequently, press Enter.
- Use AutoFill to complete the rest.
- Again, take another helper column.
- Next, type 1 in cell E5 and apply AutoFill to cell E9.
- We’ll get 5 in cell E9 which is the sum of repeat time.
- Finally, choose cell F5.
- Insert the formula:
- Use AutoFill to return other products.
- In this way, you can easily repeat cell values.
2. Copy and Paste Cell Value to Repeat Multiple Times
We can manually repeat cell values by using copy and paste features. So learn the steps to carry out the operation.
- Firstly, right-click on row header 6.
- As a result, you’ll get the Context Menu.
- Press Insert.
- It’ll insert blank rows.
- Repeat the steps as many as needed.
- See the below figure to understand better.
- Now, click cell B5 and press the Ctrl and C keys simultaneously.
- This will copy the cell.
- Next, select cell B6 and press the Ctrl and V keys together to paste it.
- Repeat the steps for returning the cell values to your desired amount of time.
3. Use Excel Fill Feature for Repeating Cell Value
Moreover, we can apply the Fill feature for repeating cell values. Hence, follow the below process.
- First of all, select cell B7 and 2 other cells below it.
- Then, go to Home ➤ Editing ➤ Fill ➤ Down.
- Accordingly, it’ll return the TV in cells B8 and B9.
- How to Repeat Rows in Excel at Bottom (5 Easy Ways)
- How to Repeat Multiple Rows in Excel (4 Effective Ways)
- [Fixed!] Excel Rows to Repeat at Top Feature Greyed Out
- How to Find Repeated Numbers in Excel (5 Easy Methods)
- How to Repeat Rows in Excel When Printing (3 Effective Ways)
4. Return Cell Value X Times Using AutoFill Tool in Excel
However, we can also use the AutoFill tool to return cell values. Therefore, learn the process.
- Select cell B7 at first.
- At the bottom right corner of the selected cell, the cursor will turn into a + icon as shown below.
- Subsequently, click and drag the cell up to the desired cells below it to repeat.
- Look at the following figure where we repeat it 2 times.
5. Apply Excel VBA to Get Cell Value X Times
Code lovers will love this step. You can apply the VBA code to get cell values as many times as you want. So, go through the steps.
- In the beginning, go to Developer ➤ Visual Basic.
- Consequently, the VBA window will pop out.
- Now, click Insert ➤ Module.
- The Module window will appear.
- Copy the following code and paste it there.
Sub RepeatValue() Dim rg As Range Dim ir As Range, org As Range xTitleId = "ExcelDemy" Set ir = Application.Selection Set ir = Application.InputBox("Range :", xTitleId, ir.Address, Type:=8) Set org = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8) Set org = org.Range("A1") For Each rg In ir.Rows xValue = rg.Range("A1").Value xNum = rg.Range("B1").Value org.Resize(xNum, 1).Value = xValue Set org = org.Offset(xNum, 0) Next End Sub
- Next, save the file and press F5 to run the code.
- As a result, a dialog box will pop out.
- Select the desired range and press OK.
- Again, another dialog box will appear.
- Select the cell where you’ll spill the data.
- Press OK.
- Thus, you’ll see the cell values like it’s displayed below.
6. Return Cell Value Multiple Times with Power Query Editor
In our last method, we’ll apply the Excel Power Query Editor. We’ll show how to repeat the products as many times as the total number of months (3). Hence, follow the steps.
- In the beginning, select cell range B4:B7.
- Then, press the Ctrl and T keys to form a table.
- A dialog box will pop out.
- Press OK.
- Click any cell in the table.
- Next, go to Table Design ➤ Properties.
- Type Product in the Table Name.
- Repeat the same steps to form another table of Months.
- Now, we’ll take the data to the power query editor.
- For that purpose, select the month’s table.
- Next, select Data ➤ From Table/Range.
- Thus, you’ll get a power query.
- Press the Close & Load To option from the drop-down.
- In the dialog box, check the circle for Only Create Connection.
- Then, press OK.
- Similarly, bring the Product table into the power query editor by following the same steps.
- After that, go to the Add Column.
- Select Custom Column.
- Give the column name as Month.
- In the formula section, type Months.
- Press OK.
- Afterward, select the icon as shown in the below figure.
- Uncheck the box which is marked in the following picture.
- Press OK.
- As a result, it’ll return the repeated product lists.
- Subsequently, press Close & Load.
- At last, you’ll see each of the products for as many as months.
Henceforth, you will be able to Repeat Cell Value X Times in Excel following the above-described methods. Keep using them and let us know if you have more ways to do the task. Follow the ExcelDemy website for more articles like this. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.
- How to Repeat Formula Pattern in Excel (Easiest 8 ways)
- How to Autofill in Excel with Repeated Sequential Numbers
- Repeat Text in Excel Automatically (5 Easiest Ways)
- How to Repeat Number Pattern in Excel (5 Methods)
- Count Repeated Words in Excel (11 Methods)
- How to Repeat Rows in Excel When Scrolling (6 Suitable Ways)