How to use the consolidate function in Excel
Вставка
- Опубліковано 5 лют 2025
- How to use the consolidate function in Excel.
Combine data from different sheets. Combine data from different files. Free templates and templates with code are available for purchase for $50 USD
www.easyexcela...
Addition Templates may be available on request
Click this link to check out my one-on-one training www.calendly.co...
For more help visit my website www.easyexcelanswers.com or email me at easyexcelanswers@gmail.com.
Contact me regarding customizing this template for your needs.
Click for online Excel Consulting www.calendly.co...
Are you struggling to update your Microsoft SQL Server data?
I recommend using well-known Excel to update and manage your data in SQL Server using the SQL Spreads Excel add-in.
Click sqlspreads.com... to find out how SQL Spreads works best for non-technical end users and download our free 14-day trial. You will get two months free when purchasing an annual subscription using our link.
I am able to provide online help on your computer at a reasonable rate.
Check out Crowdcast for creating your webinars
app.linkmink.c...
I use Tube Buddy to help promote my videos
Check them out
www.Tubebuddy....
Follow me on Facebook
/ easyexcel.answers
IG @barbhendersonconsulting
You can help and generate a translation to you own language
www.youtube.com...
*this description may contain affiliate links. When you click them, I may receive a small commission at no extra cost to you. I only recommend products and services that I've used or have experience with.
How to insert VBA code in Excel • How to insert VBA code...
IG @barbhendersonconsulting
You can help and generate a translation to you own language
www.youtube.com...
Thank you!
7 years later and you are still helping people, great video, short and to the point, and explained everything with ease. Thank you
I appreciate that!
True😊
I am preparing for the MO211 Exam. This covers the Point Summarize data from multiple ranges by using the Consolidate function. Nice, clear and concise.
Happy to help
Miss Henderson thank you very much for making this tutorial, this really helped me prepare for my practical tomorrow
Glad it was helpful!
Thank you for making this so simple. I wish you all the best in life.
Thank you! You too!
Barb,
Tomorrow is my practical exam...
I'm blessed that I got your video at last minute
Thank you 😊
Wonderful!
Thank you, Barb! This really saved me tonight! Also, I really appreciate your pleasant and warm delivery. :)
You are so welcome!
Perfect….straight and to the point.
Thanks!
very welcome
wait what happened between 1:47 and 1:52 when you added the 3 horses.
what did you do to add the other 3 sheets?
can you do a tutorial on how to consolidate data on file sheets into one file sheet in excel Mac
pleeeeease help
I need to grab range from a different file sheet and consolidated to a new sheet.
but excel in Mac turned everything grey and I can't click any tab or switch windows to loo choose the other file sheet.
sorry, I have a limited knowledge of Excel on a Mac
Mam please describe all functions of consolidate command stddev and all
stddev is standard deviation. You are able to pull that up in the functions and click on help with this function to get an explanation.
Found this very helpful. Thanks, Barb!
You are so welcome!
Thankkk you soo much you have no idea how big are the reports I need to do a consolidation for😻😻😻and I used to do it manually cell by cell ❤️ Thanks God im so grateful
Glad I could help!
@@BarbHendersonconsulting I'm so grateful! It only took less than 2 days to do the reports. If I did it manually, I swear it would have taken weeks, maybe even over a month!
Thank you so much! I am creating a spreadsheet with monthly totals, which will consolidate to each of the 4 quarterly report sheets, then to an annual sheet. My consolidate is not automatically adding as I enter new numbers into the monthly sheet unless I highlight the sheet and consolidate it again. The Refresh button does not work. I am sure I did not do something correctly when I created the sheets. Any suggestions? I'd be happy to send my workbook.
It does not work automatically, sorry
Hello, i am following same as you have told but i'm still getting an issue stating that - source reference overlaps destination area.
You seem to be combine sheets with the summay sheet?
How do I removed the + signs on the left of row A. I deleted the info for some consolidation but still have the + simple.
The plus sign indicates that you have grouped data. Click on the plus sign to display the grouped data and then select the rows. Go to the data tab and select ungroup
@@BarbHendersonconsulting thank you very much for your help. Fix
Want to consolidate monthly bank ledgers into one file, which shows ALL transactions. Is this the best approach??
I think that would work!
How do I add other jobs on too an already sheet that have got jobs consolidated?
You are not able to add to sheets that are already consolidated
Thank you mam! You just saved me two hours!
Happy to help!
thank you for making the time to share it.
My pleasure!
It's very helpful man, thanks a lot for posting videos like that. i got a question how did you select all these sheets in one touch
I selected them individually
Hi Barb,
I am wondering about the export to CRM feature that is on your ribbon.
Is that custom?
And if so how do you accomplish this?
Thank you for your time.
That was when I was on a contract with a company that had a CRM. It was a custom add on
@@BarbHendersonconsulting oooh
Hi Barb, Are you still available for Excel tutoring? Please let me know when you get a chance.
yes, click this link calendly.com/easyexcelanswers
Hi Barb, where did that QuickBooks menu come from, in your Excel menu?
When I installed Quickbooks on my Computer it came up automatically
Thank you for this amazing tutorial.
You're very welcome!
Each time new data coming after refresh. I don't wnt to manually consolidate each time. Tell me some other way
Looking at your example, how will I know which horse it came from?
there is no easy way that I can think of
can I just combine data not getting sum by using consolidate function?
No, You have to use one of the functions,sum,avg etc
how to consolidate from a different workbook or excel file into a new and empty excel workbook?
I have never tried that and am not sure it is possible.
Thank you! My boss got impressed😂❤
Happy to hear that!
how do I isolate repeated names from 2 different spreadsheets?
How do you consolidate a cell with words? I have multiple cells in the same column with an "item description" ? I have tried using this function and it is not working. Any recommendations are welcome!! Thank you
the only way I can think is using the linking sheets ua-cam.com/video/TVNSb6Dsb94/v-deo.html
Can we consolidate a string instead of data ?
I have never tried it with a string
@@BarbHendersonconsulting Oh, ok. Thank you for your reply!
Seeing as this video is pretty old, I'm not sure if you still answer questions, but I have one. I followed your instructions and it worked perfectly so thank you, however, my sheet has both %'s and $'s in it. The dollars are summed, but I don't want the %'s to be summed. For example, $1000 on each of 3 sheets is totaling to $3000, but that's also showing at 300% and it's really 100%. Can this be done where you have multiple functions? Also, can you eliminate the groupings on the side of the sheet so that one can't see what makes up the total unless they look at the individual sheet?
The only way that you could get around the percent thing is if you showed your percent as decimals i.e. 30% would be .30
The grouping is part of the function
Why after consolidating did the A column shrink and a line appear In it
it is indicating that it has been consolidated
@@BarbHendersonconsulting thanks, I subscribed. Can’t wait to dig in and learn more.
Great review of the function, thank you!
My pleasure!
Thankyou so very much! This was so helpful!
I'm so glad!
oh ! thank yoou so much i gotta get marks by u !!!
Your welecome
well explained
thank you
I am happy that it made sense!
how to sum the amount with multiple duplicate orders? Example; duplicate orders and has different total and would like to sum it so the duplicate order can be delete.
Thank you!!! This was SOOOOO helpful!!!
Why are they all named Book 3 though? I'm guessing it's because we can consolidate different files as you said, but can we automatically change them to their sheet names?
That is exactly why they are named book1 and so on. I have never tried to change the name but there should not be a problem.
Hi, also interesred in this. Do you know how to change the names of the subrows in the consolidation sheet to match the worksheet names? Now they are all names according the file name and when all sheets are from the same file, all subrows are called same.
Barb, simply thank you for your kind help online. Major respect coming your way for sure!!!!!! :)
You are so welcome!
Hello
I have like two columns with PIN and Serial number respectively. I want to consolidate them into one cell where it will be like one on top the other below for like hundreds of data. How do I do it?
you could simply do a formula =A2 &" "&B2
Thanks for this quite helpful.
You're welcome!
awesome vid, helped me a ton! :D
Glad to hear it!
I have a question about figuring out average trip times. I have a spreadsheet with a bunch of trucks and there begin and end journey times are in same column and I want to find a way to auto calc the average time. I could send you my sheet
Please send it to easyexcelanswers@gmail.com
Thank You! This helped me.
Glad it helped!
Cute and useful example. Ty!
Glad you like it!
Barb
I am interested in combining real estate client contact information (names, phone, comments, etc.) from several separate Excel files into one Master Contact list. They are separate Excel files, not separate sheets within one Excel file. Would you be able to help guide me on that?
Thanks
Bill
Bill, I have a pretty old video that explains that ua-cam.com/video/3TrpIehzG6c/v-deo.html
Thank you very much very helpful
Glad it helped
Great tutorial
Thank you! Cheers!
Quick, easy , & to the point.
thanks
Thanks! I forgot how to do this!
Glad I could help!
Hi, how can we unlink source data?
I think that you would have to go to previous version
Or how do I collect data that is in a column with blank cells between them? To go from a column with blank spaces to a new column identical data with no spaces
Blanks are a problem with data, watch my video on how to delete blanks ua-cam.com/video/PBb7tRunL7U/v-deo.html
Help me a lot thank you
Happy to help
Hi, can I use consolidate to find same information on multiple spreadsheets? I'm looking for a way to compare data in multiple spreadsheets using a unique identifier so that I can see who is on these lists and who isn't.
it is not really designed to compare data rather to combine
Thanks Barb
You are so welcome!
Thanks for it ✨
No problem 😊
When I select Create Links to Source Data, the data is not automatically updating. Can you tell me how to get the data to update automatically when I update a sheet?
You could try on the data sheet refresh all
@@BarbHendersonconsulting It's okay, I found my answer. There needs to be a value in the cell in order for it to update. I just put zeros in every cell and it fixed the problem. Thank you for the response.
@@danielroos5426 Did the consolidated sheet have a bunch of 0's?
@@deneramccullough3763 I've fixed this issue. Thank you.
@@danielroos5426 I was asking for myself. I don't work with Barb.
How to change the name of worksheets from book1 to horse1?
right click on the sheet tab and select rename
@@BarbHendersonconsulting how to do it after using the consolidate function?
Thanks for this! ❤
You're so welcome!
nice and awesome pr short n sweet
Thanks for watching
Nice one. Thanks
glad you liked it
thank you, good summary!
Glad you enjoyed it!
Great video. Ty
you are vey welcome
Thank you !
You're welcome!
Thanks. It helped.
Glad to hear it!
You saved me🥰
glad to help
Can you help me please…..I need help in excel urgently.
email me at easyexcelanswers@gmail.com
How to subtract..?
I do not think it would work. If you used negative numbers they would all be negative.
Thank you
Welcome!
Perfect
thank you
Awesome 👏
Thanks
Welcome
Thank You !!
wow i love you!!!
nice
Thanks
What you say
welcome?
Cheers
thanks
Damn you have a really attractive accent
O.K. thanks
Appreciate this🩷
Happy to help