How to Combine and Repeat Blocks of Text n Times in Excel (NO Copy Paste and NO Formulas)

Learn how to dynamically repeat cell values in Excel using Power Query, a powerful tool for data manipulation and automation. This tutorial is perfect for professionals who frequently work with lists and need to combine data from multiple ranges, such as generating a list of names for each month. Whether you're tasked with expanding datasets or managing constantly changing data ranges, our guide offers a dynamic and efficient solution.

In this video, you will discover:
⩥ The Limitations of Manual Data Manipulation: Understand the challenges of manually repeating cell values across ranges, especially with large or frequently changing datasets.
⩥ Setting Up Excel Tables: Learn how to convert your data into Excel tables for better management and integration with Power Query.
⩥ Leveraging Power Query for Automation: Step-by-step instructions on how to use Power Query to automatically repeat names for each month, creating a robust and flexible report without manual copying and pasting.
⩥ Expanding Data Ranges Dynamically: See how easily you can update your reports by simply refreshing Power Query when new data (e.g., additional months) is added, ensuring your dataset remains current with minimal effort.
⩥ Applying the Technique to Multiple Columns: Discover how this method can be adapted for more complex data combinations, allowing for extensive customization of your reports.

In this video I'll show you a DYNAMIC solution that's really EASY to setup in Microsoft Excel. There's no copy paste and no complicated Excel Formulas. Guess what the solution is?

We'll use Power Query to create this template. It's much easier than trying to come up with a dynamic Excel formula.

00:00 Repeat Cell Values X Times in Excel
00:45 Manual Solution
01:14 Dynamic Solution to Repeat Cell Values
03:36 Wrap Up


Let’s connect on social:

Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!

#excel

[DemetriPanici]

Another nice tip in Excel! Sometimes at work I feel like I know it all and then you and others show me how much more I have to learn in excel!

[senbagaganesh]

Mind blown 🤯 life long learning!! You make complex stuff look easier Leila. More importantly making it easier for almost everyone to follow your videos no matter what their prior experience is. Hats off!! 👏 👏 👏

[johnc]

Brilliance and beauty -- with the bonus of all the videos with tips this poor user can put to work immediately! You are a treasure. Thanks. 👍

[Kevink]

I was planning to register myself into an excel program course but that doesn't matter now. I found you and thank you for all the amazing contents you share with us! :)

[dontown]

I've been using Excel for almost 30 years but learn new things every day thanks to your YT channel.! Cheers

[vijayarjunwadkar]

"Repeat" after me, from "January to "December, *Leila is the Best Teacher!*" 😊👍 Very cool idea! and even Leila's T-Shirt says "Repeat"! 😁

[narendrapatil]

Love your content and love the way you explain and come with examples that are not covered any where ❤

[keshavc]

This is my first comment on youtube. I can't thank you enough. This was super helpful!!

[cenevspeed]

What can I say other than Thank You? The explanation was crisp, precise and short! 4 minutes 25 seconds of my life were well spent. And you don't have a boss, am I right, coz you are the boss? Never knew Power-Query was so useful. I am also loving the new rounded-corner UI of MS Office.
Suggestion: Can you make a video where you and Kevin collab? That would be fascinating to see.

[ahmedtareq]

Optimize and Automate, Power query is life.
Great as always Leila 👌

[sinwithin]

I always try to do unusual stuff in Excel and ultimately end up on your tutorial. You make me look good at my job. Thank you!

[grafikerinbiri]

I had a huge dataset and this video just rocked it all!!! Let me explain: 1000 rows, 10 columns! I had to repeat each 10 columns 1000 times but changing some data for each line 🤯 I put 3 days to finish it, but it took 1 hour research and test and finding this video! After this video it took 15 minutes. Because I had to watch it 3 times 😂

This video is more than THANK YOU 🤩

[michaelt]

This is a "why didn't I think of that?" moment. This is awesome for staff productivity reports.

[sonalividwans]

You are awesome Laila...this is a game changer..Thank you🙏

[amitkumarkoshti]

Hello Leila, i had learned power query from last few years watching your videos and it had helped me a lot in time saving for repetitive tasks. This was another one good example. Thanks for sharing it across.

[screentopper]

Thanks Leila! I’ve improved my excel skills to whole new level after watching your videos. I have a request for you. Can you please share the difference between “only create connection” vs “pivot table report” while importing data in reference to excel file size?

[veli-pekkanaumanen]

I was looking for a solution to a similar problem all sunday and didn't find this. I was frustrated with a big dataset and tried all kinds of complicated things and this was so simple and clear solution. Now i get it right. So thankful to you.

[ferozus]

Your powerquery course is brilliant! Thanks for that!

[byronstutorialspage]

OMG!!! I was trying to work out how to do exactly THIS last week!
Once again, you have saved my life!
Thanks Leila!

[lionmdd]

Great, You are Magician. Every time I watch new video, I feel that probably I have much more to learn. Thanks .