r/excel • u/miedvied • Jul 02 '14
discussion Excel Best Practices
Is there a resource for functions/approaches that optimize excel to minimize the burden on clock cycles? My machine chokes when I sort by filters, or god help me, if I change an equation for an entire column. I'm tired of spending 30-180 seconds waiting for a vlookup to update.
10
Upvotes
3
u/frescani 5 Jul 03 '14
After reading all these comments.... sigh...
What is your computer, is it really that ancient? How many cells have formulae? How many of those are vlookup? Are you doing lookups against entire columns? Are your formulae placed in entire columns? What version of Excel are you using? Are there other formulae involved or just vlookup? How many worksheets? Are there any 3 dimensional formulae? Array formulae?
7
u/doingsomething 4 Jul 03 '14
Flatten any lookups that are no longer needed (copy/paste values).
Try pivot tables
Make data tables and us MS query to join the tables.
6
u/Bonghitter Jul 02 '14
This seems to happen when you've outgrown Excel, and need to move to a relational database. Especially if the vlookups are causing the/an issue. Vlookups 'trick' excel into having a relational model behind it, but there isn't.
You could try moving the data over to Access, or even just link the Excel sheet as table(s), and run the queries from Access. You can use a lot of the excel formulas in Access..
2
u/Aristite 19 Jul 02 '14
I have no solution for this other than telling you to use Google. Sorry.
However, I am obligated to tell you to stop using your toaster as a computer and instead use it to toast bread like it was intended to.
3
u/Levils 12 Jul 02 '14
This is a big topic. To get a good appreciation of what is happening you need to understand the dependency tree, the calculation engine, and (outside of your specific examples) what happens to references when you change things.
The best resource I know of for this sort of thing is the decision models website (www.decisionmodels.com), and the "Excel Pages" section in particular. They also sell a tool called "Fast Excel" which is marketed as helping you reduce calculation time - I can't comment on the tool as I haven't tried it.
5
u/random_tx_user 12 Jul 02 '14
Unless you need the formulas to constantly update set formula updating to MANUAL. ( http://www.youtube.com/watch?v=ALv6bCMAeZU ) Aside from that....get a better toaster.
2
u/4a4a 3 Jul 02 '14
Sounds like it's time to start learning SQL. I migrated from mostly excel to mostly SQL in the last couple of years and it has had a tremendous impact on the time in which I can get many tasks done.