Consolidate & Clean Multiple Excel Sheets in One Pivot Table

Рет қаралды 1,017,678

Күн бұрын

Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
In this video you'll learn how to dynamically combine data from multiple Excel sheets into a single PivotTable using Power Query! This guide is perfect for those who need to consolidate information from various sources in Excel. We'll demonstrate how to append data from multiple sheets, apply necessary transformations, and create a PivotTable report that updates automatically with new data.
⬇️ DOWNLOAD the workbook here: pages.xelplus.com/pivottable-...
Excel's Power Query can easily solve a common problem in Excel: Consolidating information from multiple Excel worksheets into one Pivot Table. This also works if you'd like to append data from multiple sheets into a single Excel table.
🔑 Key Concepts Explained:
▪️ Sending data from multiple sheets into Power Query in one step.
▪️ Excluding irrelevant data, like grand totals, and filling gaps in datasets.
▪️ Dynamically pulling in data from all sheets named with a specific pattern.
▪️ Creating a PivotTable directly from the Power Query Editor.
▪️ Ensuring the final report excludes itself as a data source to avoid duplication.
▪️ Updating the PivotTable report automatically when new data or sheets are added.
In this video we'll combine the data from multiple Excel sheets into a single Pivot table in the same workbook. And we'll do it in a dynamic way so that when new sheets are added, we just need to press Refresh on our report. We will also apply Power Query's convenient transformations to clean and prepare the data for our report (fill up empty cells in a table)
Power Query Playlist: • Excel Power Query (Get...
00:00 Combine Data from Multiple Sheets in one PivotTable
01:48 Send data to Power Query
02:25 Send data from all sheets in one go
03:45 Apply Power Query's Data Transformations
04:58 Create Excel Pivot Table on Appended Data
05:38 Exclude End-Report as Data Source in Power Query
07:46 Refresh Report with New Data
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
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 #powerquery

Пікірлер: 550

@LeilaGharani 4 ай бұрын

Grab the file I used in the video from here 👉 pages.xelplus.com/pivottable-multiple-sheets-file

@eyitayoeyitayo-lawal2331 3 жыл бұрын

The absence of skill makes a man sweat when faced with tasks. Thank you for adding value Leila.

@TylerStanczak 3 жыл бұрын

This is perfect! I have always needed to be better about this when working in excel thank you for the amazing tutorial Leila :)

@jacquelines3093 3 жыл бұрын

You are a terrific teacher, thank you. The content is always relevant and you make it easy to understand with just the right amount of details and explanation.

@michebernier5298 3 жыл бұрын

So often using the old ways becomes habit, wasting time and energy, these tips honestly have been an incredible eye opener, and are very appreciated. Thank you for making the instruction so easy to understand, and meaningful. I am looking forward to some time off, where I can purchase a course or two and finding out what new things I've missed. Thank you again.

@GirishNayyar 3 жыл бұрын

You are an excellent teacher with a real good understanding of what we would like to learn and you cover your topics very comprehensively. Thank you for your hard work on this.

@Locomaid 3 жыл бұрын

Leila, your examples are so realistic. Thank you so much!

@LeslieTzy 3 жыл бұрын

I took your full powerquery course and it was very well done and helpful to my career. Thanks for another amazing course!

@bjhastings4681 3 жыл бұрын

What great information! I'm an experienced SQL and Powershell developer, Power Query has changed everything about the way I manipulate data. Your class is without a doubt the best $55 I have ever spent on education, it helped me to go from 0--100 in record time. Thanks a bunch!

@miltonnyamayaro1829 3 жыл бұрын

I Always learn something new whenever I watch your videos. Endless lessons. Thank you Leila

@kiwirafael1985 3 жыл бұрын

Hello Leila! I'm from Brazil and I'm very happy to have access to your videos, which are always very good. Thank you so much for the smart content!

@wayneedmondson1065 3 жыл бұрын

Hi Leila. Thanks for this great lesson. The way you explain it.. so easy to understand and follow along. Al always, thanks and Thumbs up!!

@DrDamoStrikesBack 3 жыл бұрын

Very good explanation. You've covered a number of interesting features in Power Query in a succinct video. Thank you.

@jahbyke Жыл бұрын

Hi Leila, Your tutorials have always been a go to for me recently. Thanks for the knowledge you share. ❤❤

@brennahughes95 3 жыл бұрын

I’m repeatedly impressed by your tutorials, so I’m heading over to the site to commit to the Power Course. (Even though I’m still stuck in 2016... Hopefully, my office will catch up someday!)

@luissilvacolindres4359 Жыл бұрын

This helped me so much on a report that I was breaking my head with and not being able to get around to what I was trying to accomplish. But since I learning and watching this short video, I managed and resolved my issue and nailed what I was trying to complete. Thank you so much for this!

@sybamunki Жыл бұрын

Brilliant! Thanks Leila. I needed to pull data from several tabs into a front sheet to meet different team needs and this coding did the trick!

@kaprisonept 3 жыл бұрын

Thank you very much! Your explanations are always clear and right to the point, one of the best channels to subscribe for sure!!

@Juliesongbirdjournals 3 жыл бұрын

I have purchased all of Leila's courses on Udemy. She is an amazing teacher and teaches with scenarios that I can use in my work.

@SakthivelS77 Жыл бұрын

Madam, thank you very much for explaining in details the steps involved to create an automated report. After watching the above video, I started using the above method and I got very appreciated by our colleagues for my speed in reporting. All thank to you only. Thank you once again.

@shaunwthompson 2 жыл бұрын

Leila Gharani is the absolute best. 50% of my KZfaq suggestions are her videos at this point and I watch almost all of them.

@titoutitouh 3 жыл бұрын

Leila strikes back again...You just answered a ton of questions I had in one simple video. I am starting to think that you're somehow reading my mind! Thank you very much, you are the best!

@LeilaGharani 3 жыл бұрын

You are so welcome!

@dogwal7822 Жыл бұрын

I keep learning something New Every Day with you. You are making me change the way the World looks Daily by data. You deserve the 5 🌟 starts. You are Awesome

@bigbusinessanalytics9458 3 жыл бұрын

This is Amazing ,Leila great work, i always learn something new from your videos & your videos are always unique ,thankyou so much leila, keep supporting us with ur knowledge & we will support your videos by sharing with others :) Have a great Day.

@barbo1948 3 жыл бұрын

This is just what I needed, amazing tutorials :-)

@martinoreilly9076 3 жыл бұрын

Brilliant as ever Leila. Please keep doing what you do. It’s really kind and helpful.

@LeilaGharani 3 жыл бұрын

Thanks Martin!

@Remars808 3 жыл бұрын

it never fails.. The Queen make it looks easy

@nikiasf Жыл бұрын

Hi! I have been watching your videos since 2020 and it's amazing how my excel skills advanced since!

@iainmacdonald7034 2 жыл бұрын

This would have samed me a lot of time a few weeks ago! But I have it for next time. Thank you for all your very clear and relevant content. Much appreciated 🙏

@mohammedhassan2808 3 жыл бұрын

Amazing Trick Leila, Every time you bring advanced tricks and solutions, keep it up

@prioritymaleman Жыл бұрын

I've watched half a dozen pivot table videos, and yours is by far the easiest and most complete. Great job thank you! Jim in FL

@LeilaGharani Жыл бұрын

Wow, thanks!

@lmayhead 3 жыл бұрын

Yet again - really clear and informative - thank you and well done.... Quick question though - can you use a pivot table as the source rather than a table ?

@MatoMagerko 3 жыл бұрын

Amazing. Thank you Leila. You've save me a lot of time.

@Muuip 3 жыл бұрын

Excellent! Thank you! I have been looking for this for a while now.

@darrylmorgan 3 жыл бұрын

Hi Leila!Great Tutorial With The Awesome Power Query..Thank You :)

@Seowyokelee840505 4 ай бұрын

thanks for teacher Leila Gharani, the power query useful to compile all the database together. much appreciate to you

@MrNuranga 3 жыл бұрын

This is just perfect! Just what I would need for some of my work. Clearly explained. Thanks Leila.

@relaxjap 2 жыл бұрын

Ohh de de los

@relaxjap 2 жыл бұрын

Ohh de is an

@relaxjap 2 жыл бұрын

Ohh

@YTOS86 2 жыл бұрын

This is what I only do in work. I even merge queries as a dynamic filter and as a Vlookup alternative.

@GP-ty8ix 3 жыл бұрын

Been looking for this! Thanks going to take the Power Query course in August.

@andreashermle2716 Жыл бұрын

Wow, I am deeply impressed with your expertise. This knowledge will help me a lot in my projects. Thank you very much!!

@jmartin9865 2 жыл бұрын

You are nothing short of a wizard, thank you!!

@mattschoular8844 3 жыл бұрын

Thanks Leila. That was explained very well.

@praveenk1672 3 жыл бұрын

Thank you so much Mam 😊👏👏 I am going to implement this right now in my report. We have a report with multiple departments and I will build a consolidated report with this method.

@IvanCortinas_ES 3 жыл бұрын

Excellent masterclass. Thank you very much Leila for touching so many points of interest.

@LeilaGharani 3 жыл бұрын

Glad you enjoyed it, Iván!

@hisomraj 3 жыл бұрын

Your videos are very informative and helpful. Really appreciate and thankful to you. However kindly post more about MS power point as it is a very important skill. Most data representation happens in powerpoint although calculation happens in excel.

@s.o.7568 3 жыл бұрын

Thank you Leila! Not having a lot of knowledge in Excel, and English not being my mother tongue, I can easily follow your explanations using the “pause” button (thank God 😊!). Thanks to you, Excel doesn’t seem inaccessible. So, thank you very very much, Leila 😊!! S. 🌸

@LeilaGharani 3 жыл бұрын

Happy to hear that!

@sedarathnadcd 2 жыл бұрын

Thank You. very clear explanation. Your effort is highly appreciated.

@abdallah.kandiel 3 жыл бұрын

Amazing as always your highness Happy Thursday

@vincentmorissette6532 3 жыл бұрын

I'm following the complete Power Query Course and it litterally changed everything. Life is so much easier at work when I have to manipulate data !

@LeilaGharani 3 жыл бұрын

Glad you like the course, Vincent!

@melbrags 3 жыл бұрын

Awesome. Have been waiting for such a solution for long.

@2ush 3 жыл бұрын

Great !! Muchas gracias! Es de mucha ayuda !! Thanks for the tips! greetings from Mexico!

@emilianopineda8009 2 жыл бұрын

I love the energy of explaining!

@adishmukler6050 Жыл бұрын

Thank you for making it simple to understand! 10/10

@sriharim1596 3 жыл бұрын

Excellent explanation ..... powerful knowledge on PowerQuery

@MatthewTsang 3 жыл бұрын

These videos (excel, accounting etc) are probably the best training videos I have come across. It's easy to understand and follow and it's really useful. Big thumbs up. Keep up the good work. By the way, any chances for videos about Power Pivot? 😄

@LeilaGharani 3 жыл бұрын

Wow, thanks Matthew!

@sd7285 3 жыл бұрын

This channel deserves more than a million subs

@asetbilibaev6519 3 жыл бұрын

I’ve been heavily using power queries for two years in my job as data analyst. In 9 minutes i have just learned several things which has already drastically changed forever my ways of doing excel.

@LeilaGharani 3 жыл бұрын

That's great. I'm glad it was helpful.

@staronebroadband5282 Жыл бұрын

Great tutorial. learned at 0 cost. Thank You so much.🤩

@mdtechpk739 3 жыл бұрын

That is amazing Leila. You always bring something new, mostly excel users are unaware of this. Powerquery 👍

@zahirpaki 3 жыл бұрын

You're right, I have been following Leila and improving my advanced-level skills. Thanks Leila

@b-positve5398 3 жыл бұрын

Wow, I wasn't familiar with PowerQuery.

@LeilaGharani 3 жыл бұрын

Thank you! 😃

@mr.mrs.b.3800 11 ай бұрын

Your instruction are so easy to follow. Thank you for taking the time to make these videos. You have made my job so easy. I need to make time to take your Power Query class.

@LeilaGharani 11 ай бұрын

Great to hear! Power Query is the best :)

@wfpbgigi6365 3 жыл бұрын

OMG! Just what I was looking for! I don’t know how I landed to your site but you’ve got yourself a new subscriber! Thanks for this, I was pulling my hair dealing with so much data... live saver! 🎉🤗🥳🥰

@LeilaGharani 3 жыл бұрын

That's great. Welcome aboard!

@sabermmirza 3 жыл бұрын

Thank you for sharing your knowledge with us :)

@magdahassib8659 Жыл бұрын

Thank you so much, you're explaining in a very easy way.

@HamidurRahman 3 жыл бұрын

Thanks Leila, for sharing this concept, and huge congratulations 🎉 to reaching 500 k plus subs.

@LeilaGharani 3 жыл бұрын

Thank you! 😃

@engrmonirulislammcips4753 2 жыл бұрын

Your teaching techniques are always excellent. I've learnt a lot in excel watching your lectures. Could you please help how can I learn Power BI Desktop effectively? Have you any course on it?

@AndrewBennettScience 3 жыл бұрын

I felt like I knew Excel really well, then I started watching your videos about PowerQuery. How did I miss this!? Thanks, as always - terrific video! I'm enjoying your VBA course, and I think I'll need to do the PowerQuery course next.

@AndrewBennettScience 3 жыл бұрын

Update - I used this to simplify a task at work yesterday. I was planning on a couple hours to format things the way I needed, but with what I learned here, it took more like 15 minutes. Thank you!

@cyrilologe 2 жыл бұрын

Perfect...exactly what I was looking for. Kudos

@elizabethnwachukwu6431 2 жыл бұрын

I love you leila. This just saved me a truckload of work

@amit4329 3 жыл бұрын

Hello Mam your all learning videos are very useful... Thanks 👍 : From India 🇮🇳

@vikaasb2016 2 жыл бұрын

Super awesome !! Leila you're an academic philanthropist !!:)

@Luciano_mp 3 жыл бұрын

Power query is very useful. Thanks Leila...

@eduardomunoz2764 3 жыл бұрын

Very good explanation. Thank you.

@rksudera 3 жыл бұрын

Concise & Crisp... As Always! ❤

@coolrecursion 3 жыл бұрын

You're absolutely amazing! I have been adding each table manually to append, until now. Many thanks

@LeilaGharani 3 жыл бұрын

My pleasure :)

@nibrasfarook4382 2 жыл бұрын

@@LeilaGharani hi Leila

@darcyg852 3 жыл бұрын

Always amazing Ms. Leila! Thank you! Good stuff!

@LeilaGharani 3 жыл бұрын

Glad you enjoyed it!

@SolutionsAbroad 3 жыл бұрын

Great content as always, will definitely steal the blank query trick for next time!

@314jph 3 жыл бұрын

wonderful and timely. The find/replace does change ALL 6/ to 7/ so at 8:15 of video the date 6/6/2020 becomes 7/7/2020 (row 33 of data). So any interation of 6/ (i.e. 16/, 26/ 36, etc.. will change). Still this is just wonderful. Thank you.

@osamasalah341 3 жыл бұрын

Very informative Video, Thanks Leila

@stevemulisa Жыл бұрын

In the 25 first second of video , you just described my issue. OMG I’m going to have such a blast watching you 🙈

@mcd22630 3 жыл бұрын

Thank you for another informative video regarding the power query feature. Power query seems to be an evolutionarily Excel feature. By the way, if you ever visit the Virginia, Washington DC area, I'd be happy to recommend a couple of vineyards to visit.

@aeromechsagar Жыл бұрын

Thank you Leila, it's an amazing session.

@vida1719 3 жыл бұрын

You have great skills to explain various Excel topics in a very clear way

@LeilaGharani 3 жыл бұрын

Glad you think so!

@LearnExcelVBA 3 жыл бұрын

This is an excellent video Leila. I learned and got inspired from Leila so much that I have started my own channel where I teach Excel VBA. 👍👍👍

@vikrambajrani3118 3 жыл бұрын

Hey, great to hear this. I just subscribed to your channel and also watched few videos. They are really good. Looking forward to learn more from you.

@hattemghanoom9557 3 жыл бұрын

Wow Thank you Leila for sharing this video You are a MS Excel Genius

@LeilaGharani 3 жыл бұрын

Wow, thank you

@vacilando86 3 жыл бұрын

This video is awesome. You are #1 in the world when it comes to PQ.

@LeilaGharani 3 жыл бұрын

Wow, thank you!

@rafaelandrespalaciosvillam8767 3 жыл бұрын

One of the best tutorials I have ever seen.

@LeilaGharani 3 жыл бұрын

Thank you, Rafael!

@pardhuch8213 2 жыл бұрын

Thank you leila. You just made my life really easy in office

@vijayarjunwadkar 3 жыл бұрын

Nice video as usual! Thanks for sharing your knowledge and enriching us! :-)

@sonialorenzo9223 2 жыл бұрын

This is beautiful, exactly what I needed

@GeertDelmulle 3 жыл бұрын

Oh, yes! Our beloved Power Query (or PQ-M as I like to call it. The best invention since sliced bread! :-)

@cor351 3 жыл бұрын

Cant't wait for the company I work for to install Power Query, it is making things so easy.

@yemisiadeoluwa7907 3 жыл бұрын

Yep, I agree

@khalidmajeed2886 3 жыл бұрын

WAOO---QUEEN IS ALWAYS QUEEN---distribute your knowledge to others get the result..

@intimeontime 3 жыл бұрын

This is great, will save me so much time and open up new opportunities :)

@sisirakumara726 3 жыл бұрын

Dear Teacher Thank You millions.......save my life

@chrism9037 3 жыл бұрын

Great video Leila!

@kylestephen9061 3 жыл бұрын

You are completely amazing. Thank you for all you do!!!

@LeilaGharani 3 жыл бұрын

Wow, thank you!

@MrAdanquah 3 жыл бұрын

First time seeing this. Super amazing .Thank you.

@LeilaGharani 3 жыл бұрын

You’re welcome 😊

@simonscrubber 3 жыл бұрын

Perfect, just what I needed, thank you. Subscribed!

@LeilaGharani 3 жыл бұрын

Thanks for the sub, Simon!

@kingZeuz 3 жыл бұрын

thank you so much i learned alot from your tutorial

@MrDhunpagla 3 жыл бұрын

Magical ........................Thanks Leila 👌😃

@sajadmk6618 3 жыл бұрын

Very very informative.. Thanks a lot