Werk efficiënter met Excel-2010 – gebruik Tables!

Dit artikel is een vervolg op een eerder artikel over Excel 2010 dat nader inging op het gebruik van Slicers.

Tables zijn nieuw sinds Excel-2007. Deze bieden een hoop extra functionaliteit en ze zijn zeer handig in combinatie met pivottables. Ik zal hieronder uitleggen wat de voordelen zijn van Tables en vervolgens hoe ze werken.

De grootste voordelen (maar lang niet alle) van het gebruik maken van een Table:

• Structured References: Formules maken automatisch gebruik van Table-names en Column-headers i.p.v. celverwijzingen. Dit maakt de formules veel beter leesbaar.

• Autoexpansion: Als je een onderaan een rij toevoegt of uiterst rechts een kolom toevoegt wordt deze automatisch toegevoegd aan de Table. Tevens worden automatisch de formatting styles, conditional formatting, formulas, data validation rules overgenomen. Omdat de Table automatisch wordt opgerekt betekent dat ook dat de basis voor je Pivottable of Chart automatisch wordt aangepast.

• Sorting: Het sorteren binnen een table is vele malen verbeterd. Nu mogelijk op een onbeperkt aantal criteria, maar tevens op Color of Font.

• Filtering: Veel meer mogelijkheden om te filteren o.a. op meerdere criteria, op Conditional Formatting, op Computed Criteria, et cetera.

• Formula Replication: Bij het invoeren van een formule in een rij van de tabel wordt deze automatisch ook naar de andere rijen gekopieerd.

• Removal of duplicate data: Rijen met dezelfde data kunnen op een eenvoudige manier verwijderd worden.

• Table styles: Het formatteren van de Table is erg simpel door het selecteren van een van de vele beschikbare Table styles met real live preview!

• Scrolling: In grote Tables zul je als je ver genoeg naar beneden scrollt de Column-headers niet meer zien. Op dat moment worden de Column-headers automatisch vervangen door de Table-headers.

Nog even voor de duidelijkheid; een Table is in principe een Database; een set van Records waarbij elk Record bestaat uit een aantal Velden.

Hoe zet je een Table op in Excel-2010?
Dat kan op 2 manieren:

•    je begint te bouwen vanaf het begin;
of
•    je zet een bestaande List om in een Table.

In het eerste geval begin je met het opzetten van de Column-headers. In het tweede geval heb je al iets in een soort van Database-format. Vervolgens ga je op één van de cellen binnen je toekomstige Table staan en druk je op de toetsen Ctrl en T. Vervolgens komt Excel met een voorstel voor de range van je Table. Zie voorbeeld hieronder:

Meestal klopt dit wel. Als je Table headers heeft, dan moet je wel ‘My table has headers’ aanvinken. Vervolgens klik je op OK en de Table wordt gecreëerd.

De andere mogelijkheid is om in de Ribbon naar de Insert Tab te gaan en aldaar te klikken op Table.

Nu dat je Table gecreëerd is kun je maar het beste gelijk ook de Table van een passende naam voorzien. Deze geeft duidelijkheid als je in je Table nog formules gaat zetten of bijvoorbeeld een Pivottable gaat maken op basis van je Table of een Macro maakt die gelinkt is aan je Table.
___________________________________________________________________________________

Ontdek meer verbluffende en eenvoudige mogelijkheden van Excel:
Cursus Excel 2010
Cursus Excel 2007
Cursus Financial Excel Update 2010
Cursus Effectieve liquiditeitsprognoses met Excel
Cursus VBA Excel
Cursus Investeringsanalyses met Excel
___________________________________________________________________________________

Dit doe je via je Ribbon. Als je cursor binnen de Table staat (alleen dan) verschijnt er in je Ribbon een extra Tab genaamd Table Tools – Design.  

Als je deze selecteert staat er linksboven onder Properties Tablename: overschrijf de naam die daar staat met een duidelijke naam voor jouw Table. Ik begin de mijne altijd met DB (van DataBase) zodat ik mijn databases altijd bij elkaar heb staan als er velen in één bestand zitten (zie ook Ctrl F3 = Name Manager).

PS    Om je Table weer terug te veranderen in een normale List, ga je naar de Ribbon Table Tools en click je op Convert to Range!

####

Hoe voeg je nieuwe rijen aan je Table toe?
Dit kan op verschillende manieren: ga naar de laatste rij van je Table naar de meest rechtse cel. Klik vervolgens op de TAB-key. Dit levert een nieuwe rij op.

(Let op: ik heb ondervonden dat de TAB toets niet werkt als je in File Options Advanced – Lotus Compatibility je Transition Navigation Keys aangeklikt hebt staan!)

De andere optie is om het kleine pijltje dat rechts onderin de meest rechtse cel van je laatste Row in je Table zit naar beneden te trekken. Tenslotte kan je ook rechtsklikken als je op bijvoorbeeld de laatste rij staat. Dan verschijnt er een heel menu met o.a. Insert. Daarbinnen kan je weer kiezen uit o.a. Table Rows Above en Table Rows Below. Ook als je in de eerst lege kolom rechts van de Table iets gaat toevoegen wordt deze kolom automatisch toegevoegd aan de Table (ook weer inclusief Formatting, et cetera).

Formule invoeren
het handigste is om eerst de eerste rij waar je je formule gaat invoeren te formatteren. Als je dat eerst doet en daarna de formule invoert dan wordt de formule incl. Formatting automatisch doorgevoerd naar de overige rijen in dezelfde kolom.

In mijn (volledig hypothetische) database heb ik 2 kolommen toegevoegd: 1 met Bonus-% en 1 voor de Max. Bonus. Hieronder kan je zien hoe een formule in een Table er uit ziet. Anders, maar wel beter leesbaar.

In elke rij staat er =[@[Bonus-%]]*[@Salaris] Dat laat aan duidelijkheid niets te wensen over, dunkt mij.

Verwijderen van “duplicate data”
Het kan voorkomen dat je in een Table data hebt die meerdere keren voorkomt. Als je de dubbele data wilt verwijderen dan kan dat op een hele snelle manier.

Ga ergens in je Table staan en klik op Remove Duplicates (onder Table Tools – Design).

Op dat moment verschijnt er een pop-up scherm. Daarin staan standaard alle kolommen van je Table aangevinkt, alsmede My data has headers. Normaliter zal je op slechts 1 kolom naar Duplicates zoeken, dus klik eerst op Unselect all en selecteer vervolgens die kolom waarbinnen je wilt zoeken naar Duplicates.
___________________________________________________________________________________

Ontdek meer verbluffende en eenvoudige mogelijkheden van Excel:
Cursus Excel 2010
Cursus Excel 2007
Cursus Financial Excel Update 2010
Cursus Effectieve liquiditeitsprognoses met Excel
Cursus VBA Excel
Cursus Investeringsanalyses met Excel
___________________________________________________________________________________

Let op: als je meerdere kolommen aanvinkt dan wordt de combinatie van de aangevinkte kolommen gezien als unieke combinatie en dus niet langer 1 veld binnen de geselecteerde kolommen.

TIP: Als je dus een lijst met data hebt en je wil hierin de Duplicates verwijderen dan is het dus een goede optie om de lijst even te veranderen in een Table (Ctrl T), de Duplicates verwijderen en vervolgens de Table weer terug vertalen naar een lijst!

Formatting Tables
Het formatteren van je Table gaat erg snel.

Ga ergens in je Table staan met je cursor. In je Ribbon verschijnt dan weer de extra Tab: Table Tools – Design. In het rechterblok zie je de verschillende Table Styles.

Door op het onderste pijltje te klikken ontvouwt het blad met alle Table Styles zich. Deze zijn onderverdeeld in Light, Medium en Dark. Als je met je muis over de Table Styles heen gaat zie je tegelijkertijd in je Table een real-life preview van hoe deze er dan uit komt te zien. Klik simpelweg op diegene die jou het meeste aanspreekt en “klaar is Kees!”

Vervolgens heb je nog verschillende Table Style Options waar je uit kan kiezen o.a. Header Row, Total Row, Banded Rows, Banded Columns, et cetera.

TIP: je kan zelfs je eigen Table Style creëren door een andere te kopiëren en aan te passen of vanaf nul te beginnen en deze helemaal op te bouwen.

####

Sorting
De Filterlabels verschijnen automatisch op het moment dat je de Table creëert.

Sorteren kan op vele manieren:
Meest gebruikelijke: op inhoud van de rijen:
•    op  1 enkele kolom;
•    op meerdere kolommen.

In het algemeen zijn er 2 manieren om te sorteren: ga in de Table staan en Rechts-klik op een cel OF ga naar Data – Sort in de Ribbon. Sorteren op 1 enkele kolom: ga op een van de cellen staan binnen de kolom waarop je wilt sorteren en rechtsklik. Vervolgens selecteer je Sort A to Z of Sort Z to A.

Sorteren op meerdere kolommen: ga naar de Data – Sort dialoog. Daar kan je opgeven op welke criteria je wilt sorteren en in welke volgorde.

Zie het voorbeeld hieronder:

Filtering
Filtering kan op vele manieren: Simpel en Advanced. De meeste zijn wel bekend met de normale manieren van Filtering, maar met de Advanced waarschijnlijk wat minder.

Laat me daarom een voorbeeld geven van Advanced Filtering. In mijn database wil ik filteren op alle salarissen boven 40.000 euro OF Woonplaats is Amsterdam. Dat gaat als volgt. Ik kopieer de Headers van die kolommen waarop ik wil filteren en plaats deze bijvoorbeeld boven mijn Table. In dit geval: Salaris en Woonplaats.
___________________________________________________________________________________

Ontdek meer verbluffende en eenvoudige mogelijkheden van Excel:
Cursus Excel 2010
Cursus Excel 2007
Cursus Financial Excel Update 2010
Cursus Effectieve liquiditeitsprognoses met Excel
Cursus VBA Excel
Cursus Investeringsanalyses met Excel
___________________________________________________________________________________

Daaronder vul ik in wat de criteria zijn. Omdat het een OR filter is dien ik ze op aparte rijen in te vullen. Vervolgens ga ik naar Data – Filter – Advanced. Dan verschijnt er een pop-up scherm waar je ingeeft wat de range is die je wilt filteren en wat de range is met de criteria.

Klik ik op OK dan worden all rijen getoond die aan de gestelde criteria voldoen. Op deze manier kun je heel selectief filteren.

LET OP: als je de criteria wijzigt worden NIET automatisch de gefilterde rijen aangepast. Daarvoor dien je wederom via Filter Advanced en OK te gaan.

Zoals je uit bovenstaand screenshot kan afleiden heb je bij Advanced Filtering 2 opties:
•    Laat de gefilterde rijen zien;
•    Kopieer de gefilterde rijen naar een andere lokatie.

Deze laatste optie kan uiteraard erg handig zijn.

Table als basis voor je Pivot-Table
Zoals je hierboven gelezen hebt is het handig om je Table gelijk een herkenbare naam te geven. In mijn voorbeeld hierboven was dat “DB_personnel”. Als ik vervolgens een Pivot-Table ga opzetten link ik deze gelijk aan de naam van mijn Table. Het meest efficiënt is in je Table te gaan staan en vervolgens op het Icoon “Insert Pivottable” te klikken (als deze niet in je Quick Access Toolbar zit raad ik je aan deze daar toe te voegen).

Zie hieronder:

De naam van de Table wordt gelijk herkend. Bij het uitbreiden van je Table zal de basis voor je Pivot-Table altijd automatisch aangepast worden (je Table is tenslotte een Dynamische Range). Het enige dat je dan nog rest is je Pivot-Table te refreshen!

Nawoord
Ik denk dat je na het lezen van dit artikel een goed idee hebt gekregen van al de voordelen en mogelijkheden van Tables. Nu nog een kwestie van oefenen en ondervinden. Tenslotte zou ik het uitermate op prijs stellen als je reacties achterlaat over hoe dit artikel geschreven is en het ook een rating geeft. Opbouwende kritiek is altijd welkom tenslotte!

Ook als je graag andere onderwerpen behandeld ziet worden, laat het dan weten.

Succes!!!

Frank Linssen is al meer dan 20 jaar werkzaam in verschillende financiële functies. Op dit moment als Finance Projekt Manager binnen het wereldwijde concern General Electric (waar hij nu al weer 14 jaar aktief is). Zijn uitgebreide kennis op het gebied van Excel heeft hij voornamelijk opgedaan door veel te lezen en proberen/oefenen hoe iets werkt. Dit is de beste manier om je Excel-skills te verhogen. Binnen GE verzendt hij ook regelmatig een Newsletter met Tips & Tricks aan een groep met op dit moment ca. 200 leden.

Bezoek ook hét eendaagse Excel event voor Financials:
Excel Gebruikersdag

Klassiekers uit het archief van Financieel-Management.nl:
10 veel voorkomende fouten in Excel
10 veel voorkomende fouten in Excel – deel 2

Bekijk ook de inspirerende video’s:
Excel Video #1: Winstmaximalisatie met de oplosser
Excel Video #2: What-if analyses voor een annuiteitsberekening
Excel Video #3: Frequentieverdeling maken met de invoegtoepassing histogram

Ontdek meer verbluffende en eenvoudige mogelijkheden van Excel:
Cursus Excel 2010
Cursus Excel 2007
Cursus Financial Excel Update 2010
Cursus Effectieve liquiditeitsprognoses met Excel
Cursus VBA Excel