10 Trucs om trage Excel-spreadsheets te repareren (Excel versnellen)

Als er één ding is dat ons allemaal verenigt, dan is het wel de frustratie om een trage Excel-spreadsheet bij te houden.

Hoewel het effect op de prestaties verwaarloosbaar is wanneer er minder gegevens zijn, wordt het ingrijpender naarmate u meer en meer gegevens/berekeningen aan de werkmap toevoegt.

9 van de 10 keer zou een Excel-gebruiker klagen over de trage Excel-spreadsheets. En er is nauwelijks iets wat je eraan kunt doen.

Wel, dat is NIET helemaal waar.

De manier waarop Excel is gemaakt, wordt het inderdaad traag met grote gegevensverzamelingen. Er zijn echter veel versnellingstrucs die u kunt gebruiken om de prestaties van een trage Excel-spreadsheet te verbeteren.

10 tips om trage Excel-spreadsheets

Hier zijn 10 tips om uw trage Excel-spreadsheet een kleine snelheidsboost te geven, en u wat tijd en frustratie te besparen (klik om naar dat specifieke gedeelte te springen).

1. Vermijd Vluchtige Functies (je moet).
2. Gebruik Helper Kolommen.
3. Vermijd Array Formules (als je kunt).
4. Gebruik voorwaardelijke opmaak met voorzichtigheid.
5. Gebruik Excel-tabellen en naamreeksen.
6. Omzet ongebruikte formules in waarden.
7. Blijf alle gegevens waarnaar wordt verwezen op één blad bewaren.
8. Vermijd het gebruik van hele rijen/kolommen in verwijzingen.
9. Gebruik de handmatige berekeningsmodus.
10. Gebruik snellere formuletechnieken.

Vermijd vluchtige formules

Vluchtige formules worden niet voor niets zo genoemd. Functies zoals NOW, TODAY, INDIRECT, RAND, OFFSET enz. worden telkens opnieuw berekend als er een verandering in de werkmap optreedt.

Als u bijvoorbeeld de functie NOW in een cel gebruikt, wordt de formule telkens als er een verandering in het werkblad optreedt, opnieuw berekend en wordt de celwaarde bijgewerkt.

Dit kost extra verwerkingssnelheid en je eindigt met een trage Excel-werkmap.

Als vuistregel geldt: vermijd vluchtige formules. En als dat niet kan, probeer het gebruik ervan tot een minimum te beperken.

Gebruik Helper Kolommen

Helper kolommen zijn een van de meest ondergewaardeerde ontwerpconstructies in Excel. Ik heb gezien dat veel mensen terugdeinzen voor het maken van hulpkolommen.

Doe dat niet.

Het grootste voordeel van het gebruik van ‘Helper Columns’ is dat het u kan helpen arrayformules te vermijden.

Nu begrijp me niet verkeerd. Ik ben niet tegen array-formules. Ik denk eerder dat ze in sommige situaties geweldig kunnen zijn. Maar als je het allemaal met één lange formule probeert te doen, heeft dat wel invloed op de prestaties van je Excel werkmap. Een paar array formules hier en daar kan geen kwaad, maar in het geval dat je nodig hebt om het te gebruiken op veel plaatsen, overweeg dan het gebruik van helper kolommen.

Hier zijn enkele voorbeelden waar helper kolommen worden gebruikt:

• Automatisch sorteren van gegevens in alfabetische volgorde met behulp van formule.
• Dynamisch Excel Filter – gegevens extraheren als je typt.
• Meerdere vervolgkeuzelijsten maken in Excel zonder herhaling.

Vermijd array-formules

Array-formules hebben hun eigen verdiensten – maar snelheid is daar niet een van.

Zoals hierboven uitgelegd, kunnen array-formules veel gegevens (celverwijzingen) opnemen, analyseren, en u het resultaat geven. Maar dat kost tijd.

Als er een manier is om arrayformules te vermijden (zoals het gebruik van helper-kolommen), neem dan altijd die weg.

Gebruik voorwaardelijke opmaak met voorzichtigheid

Ik ben absoluut dol op voorwaardelijke opmaak. Het laat nietszeggende gegevens er zo mooi uitzien. In plaats van zelf de vergelijking te maken, kunt u nu eenvoudig naar de kleur of het pictogram van een cel kijken en weet u hoe deze zich verhoudt tot anderen.

Maar… hier is het probleem.

Niet veel Excel-gebruikers weten dat Excel Voorwaardelijke opmaak vluchtig is. Hoewel u het verschil met kleine gegevensverzamelingen misschien niet merkt, kan het resulteren in een trage Excel-spreadsheet als deze wordt toegepast op grote gegevensverzamelingen, of meerdere keren wordt toegepast.

Word of advice – Use it Cautiously.

Use Excel Tables and Named Ranges

Excel Table and Named Ranges zijn twee geweldige functies die uw gegevens vasthouden en verwijzingen super eenvoudig maken. Het kan even duren voordat u eraan gewend bent, maar als u het eenmaal begint te gebruiken, wordt het leven gemakkelijk en snel.

Bij het maken van gegevensgestuurde dashboards is het bijna altijd een goed idee om uw gegevens om te zetten in een Excel-tabel.

Het heeft ook het voordeel dat uw formules begrijpelijker worden.

Wat is bijvoorbeeld gemakkelijker te begrijpen?

=Verkoopprijs-Kostprijs OR =SUM(A1:A10)-SUM(G1:G10)

Omzetten van ongebruikte formules in statische waarden

Dit is een no-brainer. Als u ze niet nodig hebt, bewaar ze dan niet.

Veel formules resulteren in een trage Excel-werkmap. En als u formules hebt die niet eens worden gebruikt, weet u wie u de schuld kunt geven. Als een vuistregel, als je niet nodig formules, is het beter om ze om te zetten in een statische waarde (door te plakken als waarden).

Read More: How to quickly convert formulas to values.

Bewaar alle gegevens waarnaar wordt verwezen in een blad

Dit is misschien niet altijd mogelijk, maar als je dit kunt doen, ik garandeer dat uw Excel-sheet sneller zou worden.

De logica is eenvoudig – formules in uw werkblad hoeven niet ver te gaan om de gegevens te krijgen wanneer deze er vlak naast staan in hetzelfde blad.

Vermijd het gebruik van de hele rij/kolom als referentie (A:A)

De enige reden waarom ik deze op de lijst heb, is dat ik veel mensen de hele rij/kolom-referentie in formules zie gebruiken. Dit is een slechte praktijk en moet worden vermeden.

Hoewel je misschien denkt dat de rij/kolom slechts een paar cellen met gegevens bevat, denkt Excel niet op die manier. Wanneer je naar een hele rij/kolom verwijst, doet Excel dat als een goede knecht en controleert het toch. Dat kost meer tijd voor berekeningen.

9. Gebruik de handmatige berekeningsmodus

Ik herhaal alleen maar wat miljoenen mensen al hebben gezegd in diverse forums en blogs. Het gebruik van Handmatige berekening geeft u de flexibiliteit om Excel te vertellen wanneer te berekenen, in plaats van dat Excel zijn eigen beslissingen neemt. Dit is niet iets dat uw Excel-werkmap versnelt, maar als u een trage Excel-spreadsheet hebt, bespaart het zeker tijd door Excel niet steeds opnieuw te laten rekenen.

• Om over te schakelen op de handmatige modus, gaat u naar het tabblad Formule -> Berekeningsopties -> Handmatig (druk op de F9-toets om opnieuw te berekenen)

Gebruik snellere formuletechnieken

Excel geeft u een heleboel formules en formule-combo’s om hetzelfde te doen. Het is het beste om de snelste te identificeren en te gebruiken.

Hier zijn een paar voorbeelden:

• Gebruik IFERROR in plaats van IF en ISERROR combo (tenzij u Excel 2003 of eerder gebruikt, die geen IFERROR heeft).
• Gebruik MAX(A1,0) in plaats van IF(A1>0,A1,0) – Dit is een coole tip die ik heb geleerd van Mr. Excel aka Bill Jelen. Zijn onderzoek toont aan dat MAX optie is 40% sneller dan IF optie (en ik ben bereid om dit statuut te nemen op zijn nominale waarde).
• Gebruik de INDEX/MATCH combo, in plaats van VLOOKUP – Dit kan verhogen veel wenkbrauwen, maar de waarheid is, er is geen manier VLOOKUP kan sneller zijn als je 100’s van kolommen met gegevens. De wereld beweegt zich in de richting van INDEX/MATCH, en jij zou die verschuiving ook moeten maken.
  .
• Gebruik – (dubbele negatieven) om TRUE’s en FALSE’s om te zetten in 1’s en 0’s (in plaats van het te vermenigvuldigen met 1 of er 0 bij op te tellen). De snelheidsverbetering is merkbaar in grote gegevensverzamelingen.

Is dit een uitputtende lijst? Absoluut NIET. Dit zijn enkele goede waarvan ik denk dat ze de moeite waard zijn om te delen als een startpunt. Als u op zoek bent naar Excel-Speed-Up technieken onder de knie te krijgen, is er een aantal goed werk gedaan door een heleboel Excel-experts.

Hier zijn enkele bronnen die u nuttig kunt vinden:

• 75 Speed-up tips door Chandoo (slim gedaan door crowdsourcing).
• Decision Models Website.
• Mr. Excel Message Board (verken dit en je zou tonnen tips te vinden).

Ik weet zeker dat je ook veel tips die kunnen helpen aan te pakken trage Excel-spreadsheets. Deel het met ons hier in de commentaar sectie.

Ik heb ook een verzoek. De pijn van het werken met een trage excel spreadsheet is iets wat velen van ons ervaren op een dagelijkse basis. Als u deze technieken nuttig vindt, deel ze dan met anderen. Verlicht hun pijn, en verdien wat goeds 🙂

Je mag ook de volgende Excel Links:

• 24 Excel trucs om u te laten zeilen door dag-tot-dag werk.
• 10 Super nette manieren om gegevens op te schonen in Excel Spreadsheets.
• 10 Excel Data Entry Tips You Can’t Afford to Miss.
• Een vervolgkeuzelijst maken en gebruiken in Excel.
• De grootte van Excel-bestanden verkleinen.
• Hoe niet-opgeslagen Excel-bestanden herstellen.
• Gratis online Excel-training (7-delige videocursus)