Excel has a limit of 1,048,576 rows and 16,384 columns. Here are a few options to explore if your data exceeds those limitations.
Opening a CSV in Excel that contains more than 1,048,576 rows will result in data loss. Excel will cut off the data that exceeds the limit.
Try using Power Query
Excel has a way to import data from a CSV to get around the limit. Check out Power Query to learn more about how to do that.
My experience with Power Query is that it does work for large files but it’s really cumbersome and can be really slow depending on how big your file is. It’s also only limited to PC users.
Try Google Sheets
Google Sheets has recently increased its sheet limit to five million cells. This is a good option if your data has only a few columns.
Use Database to Summarize the Results
I highly recommend using DB Browser for SQLite. It’s a quick and free way to create a local database. You can summarize your data then copy the results to Excel for visualizations.
The caveat is that you do have to know basic SQL for this option.
Consider Learning Python
This is especially relevant for Mac users. The Mac version of Excel is extremely limited and can feel like driving a Model T. Python is completely free is much more flexible than Excel. However, it will take a few months at least to learn how to code in Python.
Knowing Excel definitely reduces the learning curve for Python. I found that using Codecademy is a great way to start learning, however there are a ton of great resources available to start learning.
Thanks for reading!