Kan ik formules kopiëren in Excel?

Miranda schrijft me op Facebook dat ze leuk geploeterd heeft met het kopiëren van een formule in Microsoft Excel. Haar probleem was dat ze een formule uit een cel moest kopiëren naar een hele rij. Ze probeerde het met formule plakken “en van alles en nog wat” maar ze moest koppeling plakken kiezen. Of het de meest logische manier is, wist ze niet maar het werkte wel. Daarop beloofde ik haar wat meer over het kopiëren van formules uit te leggen.

Werken met celverwijzingen

Een formule bestaat vaak uit een of meer celverwijzingen. De meest simpele =A5 verwijst naar kolom A, rij 5. Op de plaats waar je dat typt komt dan de inhoud uit cel A5. Dus als ik in cel D2 typ =B2*C2 dan verwijs ik in de formule naar de inhoud van de cellen in B2 en C2.

Bij het kopiëren van formules worden die celverwijzingen meegenomen naar links/rechts en naar boven/beneden. Stel ik kopieer die formule uit D2 naar D3 dan komt daar automatisch te staan =B3*C3.

Dat heet relatieve celverwijzing. Dat kan wel eens lastig zijn als je naar een vaste cel wilt verwijzen. Bijvoorbeeld als je in een cel het btw-percentage hebt staan waarmee je wilt rekenen.

Er zijn 3 soorten celverwijzingen mogelijk: de relatieve (standaard), de absolute (verwijzen naar een vaste cel) of een mix van beide (waarbij óf de rij óf de kolom vast staat in de verwijzing).

Relatieve celverwijzing

In onderstaand voorbeeld zijn de formules in de cellen D3 en D4 gekopieerd vanuit D2. Dat betekent dat automatisch meebewogen wordt naar beneden: B2 wordt B3, B4…
Bij het naar rechts kopiëren worden de kolomletters opgeschoven zijn (A wordt B en B wordt C). Voor de duidelijkheid heb ik de formules in de kolommen F en G zichtbaar uitgeschreven.

Absolute celverwijzing

Door zowel voor de kolomletter als voor het rijcijfer een dollarteken te typen, maak je een absolute celverwijzing. Wanneer je gaat rekenen met $A$2 zul je dus altijd rekenen met de gegevens uit cel A2. In kolom D worden de getallen uit kolom B steeds vermenigvuldigd met de inhoud van cel A2. In kolom E worden alle getallen uit kolom C nog steeds vermenigvuldigd met het getal in cel A2. Je kunt je voorstellen dat het geen zin heeft om 2 absolute verwijzingen met elkaar te vermenigvuldigen en te kopiëren.

Moet je die dollartekens altijd handmatig typen? Met de functietoets F4 kun je ook dollartekens toevoegen.

Gemixte celverwijzing

Een gemixte celverwijzing verwijst naar een deel van de cel als absoluut. In onderstaand voorbeeld heb ik de rij ‘vastgezet’ – de uitkomsten rekenen dus steevast met rij 2 en omdat naar beneden wordt gekopieerd reken je in dit geval dus steeds met de inhoud van cel A2. Daarna heb ik dezelfde formule naar rechts gekopieerd. De kolom verspringt dan wel maar het rijcijfer blijft 2.

Voor de duidelijkheid heb ik de formules in de kolommen F en G weer zichtbaar uitgeschreven.

Plakopties

Welke plakopties kent Excel? Wanneer je een cel kopieert heb je verschillende mogelijkheden om eender waar te plakken.

• Als eerste zie je de “gewone” plakoptie. Precies wat er in de cel staat wordt geplakt. Daarbij worden absolute en relatieve verwijzingen gebruikt. Met deze optie plak je ook alle opmaak die je aan een cel gegeven hebt mee.
• De “123” optie plakt alleen de waarde uit de cel. Excel plakt dus géén formule maar de uitkomst van de formule.
• De Fx plakt alléén de formule uit de cel, niet de opmaak.
• De 4e optie maakt van kolommen rijen en omgekeerd. Dit proces heet transponeren.
• De optie met de kwast kopieert alléén de opmaak, niet de inhoud.
• De laatste optie is koppeling plakken, deze leg ik apart uit.

Wat doet koppeling plakken?

Een koppeling verwijst naar de uitkomst in die ene cel. Ik ga het uitleggen aan de hand van een kleine tabel. In celadres C2 staat de formule om de uitkomst te berekenen van het aantal producten in celadres B2 maal de prijs in celadres A2. De formule is uitgeschreven in celadres D2.

In celadres C3 is de formule uit celadres C2 als koppeling geplakt. Er komt nu een absolute celverwijzing tevoorschijn. Dit zie je aan het dollarteken vóór de kolomverwijzing ($C) en het dollarteken vóór de rijverwijzing ($2). Waar op het rekenblad ik deze koppeling ook plak, overal zal deze uitkomst komen te staan, de inhoud van celadres C2.

Succes met het plakken van formules!

Vond je deze tip handig?

Wil je meer tips ontvangen? Volg me dan op LinkedIn. Daar schrijf ik vaak ook korte tips. Wil je meer weten of misschien wel een training volgen? Bel 06-125 63 799 of stuur het contactformulier in. Dan bel ik jou.