Overview

Excel Tables offer an easy way to create dynamic ranges that adjust when data changes. This makes tables perfect for pivot tables, charts, and dashboards that need to show the latest data. This course covers the key benefits of tables, including a detailed review of structured references, the special formula language for tables. Examples include VLOOKUP, INDEX and MATCH, and SUMIFS. The course also covers, table styles, slicers, filtering, sorting, and removing duplicates. What you'll learnThe basicsWhat is an Excel Table, and why would you want to use one?How to quickly create and name a tableHow to use the right terminology to describe tables and table partsHow to get rid of a table when you don't want one anymoreThe four key options that control table behavior, and their unlikely locationsHow calculated columns work, and how to manually override the automatic behaviorTable skillsHow to control table rows and columnsHow tables create dynamic ranges and how to use themHow table filters work, and how to quickly hide and display the filterHow to sort a table by one or more columnsThe special shortcuts you can use to work with tablesHow to add and customize the totals row in a tableHow to quickly remove duplicates in a tableHow to extract unique values from a tableStructured referencesWhat structured references are and how they work with tablesHow to use formulas to access different parts of a tableHow structured references behave inside a table (vs. outside a table)How to copy structured references and lock references when neededFormulas and TablesHow to use formulas to do a conditional sum with a table using SUMIFSHow to use VLOOKUP and MATCH with a table together for dynamic column referencingHow to use INDEX and MATCH with a table, and the key benefit this offersHow to set up a running total in a table using structured referencesHow you can use the INDEX function to get the first row in a columnHow to set up conditional formatting in a table with a formulaHow to use data validation in a table + how to use a table to create a dynamic listHow to use named ranges when Excel won't let you use structured referencesHow to set up a formula to display how many items in a table are visibleSlicersHow to quickly add (and remove) a slicer to a tableHow to add more than one slicer to to tableHow to stop slicer buttons from moving aroundWhat options are available for slicers, and how they workHow to define and apply a custom style to control how slicers lookTable stylesHow tables are formatted with styles, and what you can include in a styleHow to quickly apply a style and see what style is appliedHow to remove all formatting from a Table, and override local formattingHow to create your own custom styleHow to move a custom style from one workbook to anotherHow to set up Excel to use your custom style by defaultPractical table examplesHow to use a table to create a dynamic pivot tableHow to make a dynamic chart based on table data

Skills

Reviews