5 handige Excel-hacks die uw productiviteit een ferme boost geven
Excel is bij uitstek één van de meest populaire softwarepakketten op de markt, maar veel professionals ‘struggelen’ nog altijd met de brede waaier aan mogelijkheden en functionaliteiten die deze tool biedt. Hoewel het programma op het eerste zicht intuïtief en éénvoudig lijkt, zijn er veel complexe functies en functionaliteiten verborgen onder het oppervlak. U hoeft alleen maar te weten waar u ze kunt vinden. Wij zetten alvast enkele handige Excel-hacks voor u op een rij:
1. Maak uw eigen sorteercriteria in Excel
Wat?
Gegevens sorteren is één van de meest gebruikte functies in Excel. De veelvoorkomende voorbeelden zijn alfabetisch sorteren, op waarde (van hoog naar laag of van laag naar hoog), op dag van de week of op maanden.
Hoewel dit enkele ingebouwde sorteercriteria zijn, kunt u nieuwe criteria maken op basis van uw eigen gegevens. Zoals in het voorbeeld hieronder waarin we willen sorteren van Small tot Xlarge. We laten u zien hoe u zelf eenvoudig sorteercriteria kunt maken.
Hoe?
Stap voor stap
- Ga in het lint naar het tabblad ‘File’ > Options
- In het Excel Options dialoogvenster, selecteert u in de linkse balk ‘Advanced’. Scroll naar beneden tot bij het blad ‘General’ en kies daar dan voor ‘Edit Custom Lists’
- Daarna opent een nieuw dialoogvenster, waar we de criteria kunnen toevoegen. U voegt de criteria toe in het venster van ‘List Entries’, gescheiden door een komma. Indien u een lange lijst heeft kunt u er ook voor kiezen om die te importeren van een aparte lijst in uw spreadsheet.
- Klik op ‘Add’ en tenslotte op ‘OK’ en daarna in het dialoogvenster van de ‘Options’ opnieuw op ‘OK’
- Ga opnieuw in uw lijst staan of selecteer uw lijst
- Ga in het lint naar het tabblad ‘Data’ > Sort (of ga via Home en kies daar voor ‘Sort & Filter’ en in de dropdown voor ‘Custom Sort’)
- Het volgende dialoogvenster opent
– Bij ‘Sort by’ kiest u voor de kolom die u wenst te sorteren. In ons voorbeeld is dat ‘Maat’
– Bij ‘Sort on’ laat u Cell Values staan
– Bij ‘Order’ kiest u voor ‘Custom List’ - Er opent opnieuw een ander venster met daarin de custom lijsten. U kiest daar voor de aangemaakte lijst ‘Small, Medium, Large, Xlarge’ en klikt op ‘OK’ en in het volgende venster ook voor ‘OK’
- De lijst is nu gesorteerd volgens de maat van klein naar groot
Resultaat
Kom alles te weten over Excel!
Neem deel aan de online Excel Experience Days op 28-29-30 november
2. Maak een afhankelijke keuzelijst (drop down) in Excel
Wat?
U wist al dat u in Excel éénvoudig een keuzelijst kunt maken. Maar soms wilt u misschien meer dan één keuzelijst gebruiken zodat de items die beschikbaar zijn in een tweede drop down (keuzelijst), afhankelijk zijn van de selectie in de eerste drop down (keuzelijst).
Dit noemen we ook wel eens afhankelijke keuzelijsten
U zult zien dat de keuzes in Drop Down 2 afhankelijk zijn van de selectie in Drop Down 1. Als ik ‘Fruit’ selecteer in Drop Down 1, krijg ik de fruitsoorten te zien, maar als ik ‘Groente’ in Drop Down 1 selecteer, dan krijg ik de groentesoorten in Drop Down 2 te zien.
Hoe?
Stap voor stap
- Ga in de cel staan waar u de eerste (hoofd) drop down wilt maken
- Ga in het lint naar het tabblad ‘Data’ > Data Validation
- Er opent een ‘Data Validation’ dialoogvenster. Op de settings tab bij ‘Allow’ kiest u in de lijst voor ‘List’. Bij ‘Source’ geeft u het bereik op dat de items bevat die in de eerste drop down moeten komen. Tenslotte klikt u op OK.
- U hebt nu uw eerste keuzelijst gemaakt
- Selecteer vervolgens de hele lijst (in ons voorbeeld van A1 tot B6)
- Ga in het lint naar tabblad ‘Formulas’ en kies bij ‘Defined Names’ voor ‘Create from Selection’
- In het volgende dialoogvenster zet je een vinkje bij ‘Top row’. Alle andere opties mogen afgevinkt worden. Hierdoor creëert u 2 keuzelijsten (‘Fruit’ en ‘Groente’).
- Klik op ‘OK’
- – Selecteer de cel waar u de afhankelijke drop down wilt maken (in ons geval is dat cel E4 onder Drop Down 2).
- Ga in het lint naar tabblad ‘Data’ > ‘Data Validation’
- Er opent een ‘Data Validation’ dialoogvenster. Op de settings tab bij ‘Allow’ kiest u in de lijst voor ‘List’. Bij ‘Source’ geeft u een formule op die verwijst naar de afhankelijke (hoofd) drop down. In ons voorbeeld is de formule =INDIRECT(D4). Want D4 bevat de hoofdkeuzelijst.
- Klik op OK
- Als u nu selecteert in de keuzelijst van Drop Down 1, worden de opties in keuzelijst van Drop Down 2 automatisch bijgewerkt.
Resultaat
3. GETPIVOTDATA-formules uitschakelen voor draaitabellen
Wat?
U hebt er zich ongetwijfeld ook al vaak aan geërgerd? De GETPIVOTDATA verrassing als u een formule in een Excel-werkblad maakt die verwijst naar een cel in een draaitabel. In plaats van een celverwijzing zoals = B4 te krijgen, krijgt u een lange lelijke GETPIVOTDATA-functie toegevoegd aan de formule.
Wist u dat u dit éénvoudig kunt uitzetten via de ‘settings’?
Hoe?
Stap voor stap
- Stel dat u een draaitabel hebt zoals in onderstaand voorbeeld en u wilt vanaf cel C2 graag een berekening maken op basis van cijfers in de draaitabel.
- Ga in een willekeurige cel staan in de draaitabel
- Ga in het lint naar het tabblad ‘Analyze’ in het lint en klik op de drop down bij ‘Options’. U ziet onderaan ‘Generate GetPivotData’ met een vinkje naast. Dit betekent dat de optie momenteel actief is. Als u hierop klikt, wordt deze optie afgevinkt.
- Als u nu terug in cel C2 gaat staan en een formule maakt met een verwijzing naar een cel uit de draaitabel, wordt er een relatieve verwijzing gemaakt. Dat betekent dat u zonder enig probleem kunt kopiëren en plakken in meerdere cellen.
Resultaat
BELANGRIJKE OPMERKING: De Generate GetPivotData-instelling is een instelling op toepassingsniveau. Dit betekent dat de instelling hetzelfde is voor alle bestanden op uw computer. U hoeft het maar één keer in te stellen. De instelling wordt de volgende keer onthouden wanneer u Excel sluit en opent.
Omdat het een instelling op toepassingsniveau is, reist deze NIET mee met de werkmap. Als u uw bestand naar collega’s verzendt, wordt NIET dezelfde instelling toegepast. Ze moeten de Generate GetPivotData-instelling op hun eigen computer wijzigen. De bestaande formules veranderen echter NIET in een werkmap wanneer u of iemand anders de instelling wijzigt. Generate GetPivotData is slechts een hulpmiddel voor het schrijven van NIEUWE formules. Het heeft GEEN invloed op bestaande formules.
4. VLOOKUP met meerdere criteria
Wat?
Eén van de vele beperkingen van VLOOKUP is dat u slechts één criterium kunt gebruiken om gegevens op te zoeken in een andere tabel. Maar wist u dat u door het samenvoegen van tekststrings u toch een unieke index kunt maken (door een CONCATENATE) en VLOOKUPS met meerdere criteria kunt uitvoeren?
Hoe?
Stap voor stap
- Stel dat u een lijst hebt zoals in het voorbeeld hieronder waar u in de 2e tabel de prijs wil opzoeken vanuit de 1e tabel. U zult zien dat de prijs afhankelijk is van zowel type als kleur, 2 criteria.
- Eerst gaan we naast de bron, meer bepaald de eerste prijzentabel (in kolom B) een concatenate (=C3&D3) maken van type en kleur, zodat dit ons uniek zoekcriteria wordt.
- Deze formule kopiëren we ook naast de 2e tabel (inventaris), in kolom H.
- Nu kunnen we in kolom K probleemloos de prijs opzoeken met een VLOOKUP door gebruik te maken van de samengevoegde tekstvelden.
- Als we de cellen verder kopiëren in kolom K, wordt de prijs overal opgevuld.
Resultaat
5. Verander positieve getallen in negatieve getallen met paste special
Wat?
Hebt u zich ook al afgevraagd hoe u gemakkelijk een lijst met positieve getallen kunt omzetten naar negatieve getallen? Dit kan immers handig zijn in een lijst met terugbetalingen of creditnota’s. Paste special kan u hierbij helpen.
Hoe?
Stap voor stap
- In het voorbeeld willen we graag dat de bedragen van de creditnota’s omgezet worden naar een negatieve waarde
- Typ in een nabijgelegen cel -1 (bijvoorbeeld cel E1)
- Kopieer de cel (Ctrl + C)
- Selecteer de bedragen van de creditnota’s. In het voorbeeld is dat van cel C10 tem C14
- Ga met de rechtermuisknop naar ‘Paste Special’
- In het dialoogvenster kiest u voor ‘Values’ en ‘Multiply’ en klik tenslotte op OK
- U zult zien dat de bedragen van het type Creditnota nu een negatieve waarde hebben.
Resultaat
TIP: Via de Format Cells kunt u er nog voor kiezen om de negatieve getallen in rood te zetten. Ga naar Format Cells > Numbers > Negative numbers (kiezen voor rode layout – laatste optie)
Bronnen:
https://trumpexcel.com/
http://www.iwillteachyouexcel.com/
https://www.excelcampus.com/
Zin in meer Exceltips?
Ontdek nog meer Excel tips & tricks tijdens de online Excel Experience Days. Voor occasionele, frequente of gevorderde gebruikers.