What are some things you all do to handle really large datasets in Excel, and make them more manageable? I've got one I can barely open without my whole computer crashing.

like
Posting as :
works at
You are currently posting as works at

It's probably formulae making it crash. If it is just due to a lot of data I would use power query + power pivot.

like

Optimize for the number of tables you create and their associated formulas. You can always select the check performance button under the “Review” and see how many cells can be optimized.

like

If it's gotten to the point where it crashes:

First I'd disable the auto load of formulas. If the file js no longer slow then your issue is likely formulas that read a lot of cells and use a lot of inferences. Worst case you might have to debug by removing formulas until you find the formula that breaks your computer.

Then I go through the sheets and delete and rows or columns not used. Don't know if this is still a thing but on older versions having blank cells would affect the load speed as Excel would be trying to read empty cells. More of a old habit these days.

Next I check for cell/conditional formatting and remove them all for similar reasons. Also checking links and connections to see if the file is actively reading from another file (you can supersede this step using powerquery to load the table into the file using Excel.Workbook(File.Contents(filepath,null,true) IIRC).

I've had files with dozens of columns and thousands of rows run fairly easily. The issue may be a data modelling one and you'll have to think about how the data is brought into the file and how you use it. Powerquery can be a good alternative.

All fails it might just be your laptop is a bit crap and it's time to ask IT for an upgrade.

like

Save the file as .xlsb
It turns the file in a binary file that is more manageable, faster and takes up less space. It works just fine but might struggle a bit with if you use formulas connecting external data or so I was told. Never had issues so far.

like

Might be time to switch to coding

Related Posts

Can any healthcare bankers with experience in dealing with Canadian Healthcare system shed some light on why the system is inefficient and why it takes so long to see a specialist?

like

I got a remote L6 SWE offer at Google but the RSUs are too low for that level ($625k over 4 years), could not negotiate it higher. I’ve accepted the offer but trying to be hopeful that I could make it more equitable in the future if I perform well. Are stock refreshers based on the initial RSU grant or solely based on performance? Google

like

Rise and shine! Any recruiters for CVS 🥹☺️ in the building?

like

What is the best firm in NY for an associate interested in international tax planning (inbound and outbound), M&A, and transfer pricing?

likehelpful

What is the biggest perspective change you’ve had to make about your financial life in the last few years? Mine? I’m 49 and realized that making money for me is easy but the amount of money I’ve lost over the years makes me want to cry. Change is here and better late than never!

Post Photo
like

Bad idea to reneg on an internship offer for a better offer?

like

Hello team,

What should be good package for a 5yr old IAM consultant in UK? Thanks in advance!

like

Taking my shot in tech sales. Consulting was great experience but enjoyed the relationship and BD aspect more and didn’t see myself climbing to partner. Starting in a junior BD Rep role to learn prospecting and earn quota history. Took a big paycut for the potential future earnings as an Account Exec. Did I dun goof? Probably. But I’ll regret it forever if I didn’t take my chance now when I’m still single and young-ish.

like

More Americans died in Trump's Capitol assault than in Benghazi.

likesmart
like

Hi Guys, this is the platform to share your queies and experiences with various IT / IT related things. Please feel free to join and engage this bowl.

How much in hand will be required enough to have a quality life inside kerala with spouse,kids and parents? What are the various ways that you are following to achive it(to get the salary and/or live with whatever currently you are earning)? Do you have any source of second income? What are the various investments that you are following?
Please do share.

like

can someone suggest high paid product based companies in kerala ?

like
like

I have 3 years of Experience working as a fullstack developer using .NET Core, Angular, React, and Postgres.

I have also done certifications in Machine learning using Python and Tensorflow, but I am unable to land an interview with Microsoft, even if my profile matches the job description.

Any nudge in the right direction would be appreciated.

Thanks.

like

How is work life balance?

Hello, I am a junior business administrator working in advisory operations. I have been with the company for 6 months, and was employed along with 2 other colleagues. I have picked the job up really fast and am even doing some “extra curricula’s” I really love my job. However I have recently found out I am being paid the same as an apprentice on my team, I usually do at 3 x the work he does whilst also doing my Cert CII. It has really disheartened me and I feel I should be earning more money

like

Are we getting paid tomorrow since it’s a holiday weekend? I believe Juneteenth weekend we got paid on a Friday even though pay day was Tuesday

like

Is it still worth buying Tesla stock? Especially after rising so much since year start?

like

Any of you with kids chose NOT to open a 529 plan and why?

like

Additional Posts in Excel Genius

Is there a way for me to automate creating columns to do this so I can drag across and create my columns? For example--
1Q2021 2Q2021 3Q2021 4Q2021 2021 1Q2022?

likesmart

For a linear regression analysis… the equation of a line is Y=MX+B, where M is the slope. So if I’m measuring a line across 365 days and M=.2, can I say that I have increased production by 20% over a year. Or is production increasing 20% each day?

like

Anyone use power query before? My goal is I have multiple look up values in one cell (separated by comma) and I need it to look up those values and return values from another table which also may have multiple values (separated by comma) anyone know how to do that? I played around with power query today but it wasn’t giving me what I wanted

like

Still don't have xlookup. #fomo

like

How do I look up values from a column in a row? E.g. Values in A1:A6 in B2:F2?

like

Does anyone from PwC have a list of the favorite groups shortcuts for excel? Please provide. Much appreciated .

like

I’m verrrrrry new to excel and am going crazy because is there not a way to “clip” text in cells like you can in google sheets? I read about a “fill” option, but then noticed that cells that aren’t filled just repeat text. 😂🤯

like

Current IE here. What are some Time Motion Study Softwares that are out there and you use in your jobs?

like

I am trying to recreate this without the numbers at the end. Any advice?

Post Photo
likehelpful

What’s the keyboard shortcut to get into the “insert options” dialogue when you’ve inserted a new row with Ctrl+Shift+Plus?

like

What are some common reasons that a COUNTIF formula might not be working correctly? I'm using the correct range and value to count. I'm not seeing "ERROR" but the values aren't correct (it's not counting everything). Thx.

like

Anyone know what these badges are?

Post Photo
likefunny

How do I compare two sheets to make sure they are identical?

like

Is there anything similar to the INDIRECT function that I can use to build a formula reference to another workbook? INDIRECT creates errors when the referenced workbook is closed..

like

What are your top excel functions you use in finance / accounting - besides vlookup - Pivots - sumifs - count - index. I have an interview coming up which will have excel based technical questions. Not sure what other functions are considered important in the accounting finance world.

like

Best websites to get more of a handle on Excel as a beginner-intermediate?

like

Hi Group, seems like it should be basic format, but I just can not get it to work. a list of data and want to alternate shade (blue,white,blue,etc) each line but if I have multiple lines I want to group together and they all shade the same. I want to group by the same data in column A, and the number of rows that are the same could vary. So let’s say row 1& 2 same entry in column A(blue) row 3 different (white) row 4,5,6 same as each other different to other rows (blue) etc… help 🙏

like

Any book recommendations on excel modeling for real world business applications?

like

Has anyone had an issue with index match and lookups returning NA when trying to manipulate data from a password protected workbook?

This is dumb, but. How can I pull and average the numbers in column b associated with the 4 values in column a? So, (sum of all As/total number of As) without filtering?

Post Photo
like

New to Fishbowl?

Download the Fishbowl app to
unlock all discussions on Fishbowl.
That was just a preview…
Sign Up to see all discussions
  • Discover what it’s like to work at companies from real professionals
  • Get candid advice from people in your field in a safe space
  • Chat and network with other professionals in your field
Sign up in seconds to unlock all discussions on Fishbowl.

Already a user?
Login here

Share

Embed this post

Copy and paste embed code on your site

Preview

Download the
Fishbowl app

See what’s happening in your industry
from the palm of your hand.

A phone with Fishbowl app

Scan your QR code to download
Fishbowl app on your mobile

Download app

Sign up for free to view this conversation on Fishbowl

By continuing you agree to Terms of Use and Privacy Policy

Already have an account? Log in

Sign up for free to continue using Fishbowl

By continuing you agree to Terms of Use(New) and Privacy Policy(New)
Messaging rates may apply

Already have an account? Log in

For account settings, visit Fishbowl on Desktop Browser or

General

Legal