0

When using an add in function in Excel to compute values for hundreds of thousands of rows of data, Excel will start freezing or showing "0" values.

enter image description here

The add in function allows me to calculate the mileage of an origin and destination pair for each row.

I've tried filtering by year, but am having issues with that even.

Are there any solutions to this?

Improve this question
6
  • Please show samples of the workbook and choose to show formulas so we can see how it is setup.
    – Brian
    Commented Jul 20, 2023 at 15:06
  • I assume your running a 64-bit version of Excel and have as much system memory as the system can support?
    – Ramhound
    Commented Jul 20, 2023 at 15:09
  • 3
    you are pushing up against the limits of desktop productivity software. at some point, you need a real DBMS.
    – Frank Thomas
    Commented Jul 20, 2023 at 15:13
  • 3
    @FrankThomas - I suggest that when a user is talking about "hundreds of thousands" of rows of data, they're well past "pushing the limits of desktop productivity software", and deep into "need a real DBMS".
    – Jeff Zeitlin
    Commented Jul 20, 2023 at 15:46
  • Excel should be fine with hundreds of thousands if you're using Power Query. If you're using spreadsheet formulas, or an add-in of unknown efficiency, you're probably gonna have a bad time. If you edit your post with more details about where the calculations are happening, we may be able to help.
    – FlexYourData
    Commented Jul 20, 2023 at 20:13

2 Answers 2

Reset to default
1

You need a relational database to deal with large amounts of data. Most of these DB's use the powerful and easy-to-learn Structured Query Language (SQL).

Microsoft has the (hobbled) Access DB in Office, free LibreOffice has Base, and there are numerous others. Even the huge commercial Oracle database is free for personal use. Base probably has the smallest learning curve, includes both back-end (DB engine) and front-end (GUI), and might be a good tool to learn with.

Using Excel with large data sets is like trying to move Aconcagua with a Tonka Toy.

Improve this answer
0

You should consider using another tool to manipulate such a large dataset. Consider looking into R (https://posit.co/download/rstudio-desktop/)

Improve this answer
1
  • As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.
    – Community Bot
    Commented Jul 20, 2023 at 21:35

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .