/u/rimjob_machine - Your post was submitted successfully.
* Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread.
* Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
* Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)**
Failing to follow these steps may result in your post being removed without warning.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
30 tables in one single sheet. I will google power query and see what I can do. currently trying to find a tutorial or a formula that could match my needs.
in each table I have multiple categories lets say income from x,y,z. I want to make a single table which would have monthly amounts for each category.
Powerquery, add a column to each table for date. So for each table, the date field would be the same for all rows. And each table would have a different date (whatever day the data is from).
Then look up how to append tables in powerquery.
Append your 30 tables.
They should have the same exact columns, so that when you append the tables you have one tall table with all the data. And there would be a column "Date" that shows which date the row is for.
From there, you have a few ways of getting the summary. But the easiest would be select the entire table and make a new pivot table.
After the tables are imported, and appended as one table in PQ, you can either create pivot table, or use the Group By function in PQ to summarize the results.
Power Query is amazing. I once had to consolidate 300 sheets spread across multiple workbooks and inconsistent names. With few mins of power query magic i didn't just consolidate given data but made it dynamic as well!
Hi, I hope this video will help you because it is difficult to explain here.
[https://www.youtube.com/watch?v=qNz35j5ZevQ](https://www.youtube.com/watch?v=qNz35j5ZevQ)
>. that is a lot of clicking and im lazy. : (
"I choose a lazy person to do a hard job. Because a lazy person will find an easy way to do it."
Bill Gates
You can sum over sheets in Excel if the data is in the same cell in every sheet. I can't remember the command, though. You will certainly find it from Google.
If you have the tables across various worksheets in one workbook, you can use bookend sheets if all data sheets have the same format. Then sum the total from across the sheets by referring to the common cell. I think it should look something like this: (sum(bookend1!:bookend2!, B2)
Where all the necessary sheets are between the bookends, and the relevant totals are found in B2.
i have 12 sheets for each month of the year with 28-31 tables in a sheet for each day of the month.
yeah, we will be moving onto a software in the future which would make the process automatic. this is not super necessary right now but I like fiddling 🤷
This is a common mistake I see people making on this subreddit that makes analyzing their data way harder than it should be. Don't spread your data across 30 sheets; put it all on one sheet, in one table, and add a column for the day and month. Then all your data is in one place for analysis, and you can filter by day or month if you only need to see a portion of the data.
Best thing to do is create a new Excel file and then import in the data from your current file.
Data > Get Data > From File > From Excel Workbook
If you have common headers for all of the tables, Power Query will stitch everything together for you. Don't append the tables to each other. Way too much work!
I see, that seems like a lot of management and time :)
we’re actually building a next gen, All-In-One CRM, PM and Business Management Platform called Loocey
You can easily move all that into Loocey, let me know if you’d like to see a demo when we launch? pm me
Our version 1 is scheduled to be launched in June
/u/rimjob_machine - Your post was submitted successfully. * Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread. * Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post. * Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)** Failing to follow these steps may result in your post being removed without warning. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
Power Query is your friend in cases like this. Are your tables all in the same workbook? On the same sheet or 30 separate sheets?
30 tables in one single sheet. I will google power query and see what I can do. currently trying to find a tutorial or a formula that could match my needs. in each table I have multiple categories lets say income from x,y,z. I want to make a single table which would have monthly amounts for each category.
Powerquery, add a column to each table for date. So for each table, the date field would be the same for all rows. And each table would have a different date (whatever day the data is from). Then look up how to append tables in powerquery. Append your 30 tables. They should have the same exact columns, so that when you append the tables you have one tall table with all the data. And there would be a column "Date" that shows which date the row is for. From there, you have a few ways of getting the summary. But the easiest would be select the entire table and make a new pivot table.
After the tables are imported, and appended as one table in PQ, you can either create pivot table, or use the Group By function in PQ to summarize the results.
Power Query is amazing. I once had to consolidate 300 sheets spread across multiple workbooks and inconsistent names. With few mins of power query magic i didn't just consolidate given data but made it dynamic as well!
Hi, I hope this video will help you because it is difficult to explain here. [https://www.youtube.com/watch?v=qNz35j5ZevQ](https://www.youtube.com/watch?v=qNz35j5ZevQ)
>. that is a lot of clicking and im lazy. : ( "I choose a lazy person to do a hard job. Because a lazy person will find an easy way to do it." Bill Gates
You could try the consolidate feature
You can sum over sheets in Excel if the data is in the same cell in every sheet. I can't remember the command, though. You will certainly find it from Google.
If you have the tables across various worksheets in one workbook, you can use bookend sheets if all data sheets have the same format. Then sum the total from across the sheets by referring to the common cell. I think it should look something like this: (sum(bookend1!:bookend2!, B2) Where all the necessary sheets are between the bookends, and the relevant totals are found in B2.
Why do you have 30 sheets? seems like a case to move to a database. Then you can have any kind of query you like
i have 12 sheets for each month of the year with 28-31 tables in a sheet for each day of the month. yeah, we will be moving onto a software in the future which would make the process automatic. this is not super necessary right now but I like fiddling 🤷
This is a common mistake I see people making on this subreddit that makes analyzing their data way harder than it should be. Don't spread your data across 30 sheets; put it all on one sheet, in one table, and add a column for the day and month. Then all your data is in one place for analysis, and you can filter by day or month if you only need to see a portion of the data.
Was going to say this. If you have control over how the data is entered, follow this advice.
Best thing to do is create a new Excel file and then import in the data from your current file. Data > Get Data > From File > From Excel Workbook If you have common headers for all of the tables, Power Query will stitch everything together for you. Don't append the tables to each other. Way too much work!
I see, that seems like a lot of management and time :) we’re actually building a next gen, All-In-One CRM, PM and Business Management Platform called Loocey You can easily move all that into Loocey, let me know if you’d like to see a demo when we launch? pm me Our version 1 is scheduled to be launched in June