Reaching Excel's Limits

[ExcelAtEverything]

Hello friends,

I am working on an Excel doc in a book with more data than I've ever worked with before. I have sheets in this WB that by year's end will be clipping near the 1,048,576 max for a worksheet. I also have other sheets in the same WB which will all be doing the same. The reason is that this Excel doc that I am working on will be pulling in new data each day to be analyzed, and it's a lot of data. My sheet is already starting to hang and "think" often when I hit the enter key.

My question is... what will happen once it reaches the limit? Also, any expert advice to try to alleviate the potential issue?

Thanks!

 

[Michael M]

Once the limit is reached it won't pull in anymore data....have you considered using multiple workbooks to address each worksheet ?
OR
deleting old data as new data is pulled in using macros ?
Would the data be better in a Access Database ?

 

[ExcelAtEverything]

Once the limit is reached it won't pull in anymore data....have you considered using multiple workbooks to address each worksheet ?
---> I have not considered that. In fact, I never even thought about that. My eyebrows lifted when I read this though!

deleting old data as new data is pulled in using macros ?
--->I can't, because I need itbecause that old data is part of the YTD totals which change as each new day passes.

Would the data be better in a Access Database ?
I don't know. I don't have it, and never used it before. I'd have to look into how it works & pricing.

 

[RoryA]

Why do you need all the data in a worksheet?

 

[ExcelAtEverything]

I don't. But it needs to be available to the worksheet. Daily each morning, the prior day's numbers (sales, returns, transactions, etc) will be added so that they can be squeezed to get summaries/averages/percentages, etc. And each day, the sheet reports ongoing Yesterday/Month to date/Year to date totals. So that's why the prior info can't be deleted, because the very next day it may not be needed for the "yesterday" portion any longer, but it will be needed for the month to dayte and year to date portions. As it stands, all of the needed info are residing in a folder in My Documents, which Power Query is pulling at.

 

[Tupe77]

It seems that the project has already failed. You are already within the limits of Excel performance even if you are just planning a project. You can change the application (for example, SQL would be better suited), or you can rethink about your goals. It’s hard to believe you need all that information in one workbook. Why can't data be split into different workbooks for months or weeks? Monthly summaries of other workbooks would be compiled in one workbook, but data would be processed in monthly periods, for example.

 

[Tupe77]

You misuse the forum by asking little things that can be easily found on the internet, but you don’t ask what you really need. There are people in this forum who could tell you how you could implement the project. I am completely the wrong person to it, but I would argue you should need help, especially in the design. You should tell us a lot more about the project and ask for help in planning it. Only then you should worry about formulas.

If the only purpose is to analyze (sales, Returns, transactions, etc) then I think it will work with Excel, as it can probably be broken down into smaller parts.

If they should be able to manage those events, then you probably have the wrong tool.

I don’t mean to criticize your questions in any way, I’m trying to tell You that the forum has people with a huge amount of experience with projects like yours and You should ask them for advice on the whole project, not about tiny little pieces.

I wrote this because I suspect I recognize myself from your writings. In usual, after solving problems with code and formulas, I find that the problems come from the design that caused unnecessary problems.

 

[RoryA]

I don't. But it needs to be available to the worksheet. Daily each morning, the prior day's numbers (sales, returns, transactions, etc) will be added so that they can be squeezed to get summaries/averages/percentages, etc. And each day, the sheet reports ongoing Yesterday/Month to date/Year to date totals. So that's why the prior info can't be deleted, because the very next day it may not be needed for the "yesterday" portion any longer, but it will be needed for the month to dayte and year to date portions. As it stands, all of the needed info are residing in a folder in My Documents, which Power Query is pulling at.

It sounds like you just need pívot tables for the analysis so you don’t need the data in a worksheet at all. If you’re loading the data with PQ already, you can use that data directly from the connection.

 

[ExcelAtEverything]

Tupe77, yeah I appreciate the help. But while I understood your clarification, things like "misusing the forum" and "things I you can easily look up on the internet" come off like it's on purpose or out of laziness. What you said later on is correct, but you don't understood the details of how this project ended up in my lap and how it came to be that I built it up incorrectly from the start. And I do look things up on the internet all day every day. But when I need additional help in understanding something because it's either not working correctly for me or because I'm just having trouble grasping it, I turn to this forum of all-stars. I've been guilty of asking a question here & there that's an easy one to many people and easily found on the internet. But that doesn't mean that I just do that with everything. Hindsight is 20/20, and you don't know what you don't know... you know? And I didn't ask how to frame it up correctly early on, because It wasn't an issue.... until it became an issue. But then I solved that issue. But then another... but then that too was solved. But then they started getting worse and more plentiful, and then grew into major problems... and here I am. There were parts of the data that I didn't even have access to for a while, so I started building the shell of the project out based on what I was told, which turned out to be not even close to the truth. So yes, now I am in deep on a project that I would never have agreed to do had I known (A) the extent of it (B) the poor quality of their currect reporting, and (C) that it required me to do things that I did not know how to do at all. Like PQ for instance. Never used it before, never saw it... it was foreign to me. But backed into a corner, I stayed up for 2 dyas straight learning as much as I could about it, then I rearranged my project for that. Now however, I'm seeing that it needs to be arranged in a different way. But had I known that before... etc. But again, you don't know what you don't know. And also you learn the what to do's & what not to do's as you grow. I'm obviously not at you r level of expertise with Exce, but I'm sure that if you & I were in a recording studio then things might be reversed. You wouldn't know the best way to do it until you became experienced at doing it. I used to be all into Excel. But I'm a music producer now. A producer who has not been able to work on music for 2 weeks now because of this project that I thought would certainly be done by now. So I'm just on here very humbly asking for help from people who are way smarter than me with Excel. If anyone doesn't want to help me, then I respect that decision. I'm in the situation that I'm in for a myriad of reasons. I'm just trying to find a way to get back in front of it.

 

[ExcelAtEverything]

Thx so much Rory. I'm glad you brought that up, as I've been reading up & watching videos on Pivot tables for a couple of days now on and off (never used them or knew how to use them before). I was thinking that might work.