Excel Tables

A Complete Guide for Creating, Using and Automating Lists and Tables

Zack Barresse, Kevin Jones

• Publisher: Holy Macro! Books
• Year: 2014
• Print ISBN: 9781615470280
• eBook ISBN: 9781615473403
• Edition: 1
• Topic: Computer Science
• Subtopic: Data Warehousing
• Index: Computer Science

Creating tables in Excel allows for easier formatting and reporting, but the new syntax that it implies can be intimidating to the uninitiated. In this guide, one of the developers of the official Microsoft Excel 2013 templates—all of which employ tables—helps introduce readers to the multiple benefits of tables. The book begins by explaining what tables are, how to create them, and how they can be used in reporting before moving on to slightly more advanced topics, including slicers and filtering, working with VBA macros, and using tables in the Excel web app. Novice Excel users and experts alike will find relevant, useful, and authoritative information in this one-of-a-kind resource.

1 What Are Tables?

Microsoft Excel is a versatile application that millions use to solve many different kinds of problems and perform everyday tasks. We've seen it used to calculate math problems, evaluate complex choices, and analyze data. It's been used to create grocery lists and manage checkbooks. Some use it to create and manage business plans, generate forecasts, and present performance reports.

One of the most common uses of Excel is to create, maintain, and analyze a simple two-dimensional list—anything from a list of financial transactions to the standings and statistics of the sports teams in a league. You typically create, manage, and analyze data by using a database such as Access or FileMaker, but Excel makes it much easier to handle these tasks. Unlike more database-oriented applications, Excel exposes functionality to analyze a list's data by using sorting, filtering, graphing, and other tools in a more intuitive and flexible fashion, thus making your experience more dynamic and, ultimately, more productive with less effort.

If you have a list of data that has a header row and one or more data rows, you can turn that data into an Excel Table that has additional functionality and rules that help you better manage and analyze the data in the Table. When you identify a list of data as an Excel Table, Excel provides tools to, among other things, format, sort, and filter the rows of data more easily than if the list of data were not identified as a Table.

NOTE It is important to note here that the only real difference between a simple list of data and an Excel Table is the designation of that list as being an Excel Table versus not being an Excel Table. Designating a list of data as a Table makes additional Table functionality available. You can convert a list of data into an Excel Table and back to a simple list of data without any loss of data. A simple list and an Excel Table are essentially synonymous from a data values perspective.

Who Uses Tables?

Any list of data that has multiple columns, a header row, and an optional total row at the bottom is an excellent candidate for Excel Table designation. Excel Tables are versatile and useful to anyone using spreadsheets to maintain lists of data. Identifying a list of data on a worksheet as an Excel Table instantly adds both additional functionality for maintenance and analysis as well as rules to help keep the data organized and clean. The fact that you can convert a list of data into an Excel Table and back into a simple list of data without any loss of data makes trying out Tables a very easy proposition.

If you're importing data from an external source such as an online analytical processing (OLAP) cube, the default destination is an Excel Table. If you're using one of the newer business intelligence (BI) tools like Power Query, the results of queries are placed in Excel Tables. In essence, Microsoft realizes the universal appeal of a robust table function and is leveraging Excel Tables as much as possible and wherever that kind of functionality makes sense.

Why Use Tables?

Tables don't overwrite existing data or create new data. An Excel Table encapsulates data, extends functionality and visual appearance, and enables faster implementation of solutions. Some of the advantages include:

• Data is structured in rows and columns, without spaces.
• New rows inserted into the Table are automatically formatted the same as the other rows.
• Changes to formulas and formatting in a cell are automatically applied to the entire column.
• An optional total row can be displayed, and each column's "total" value can be any of a number of different aggregate functions.
• Special Table formatting styles instantly format an entire Table with banded rows and/or columns.

These dynamic features and many others are readily available with Excel Tables and are covered in this book.

History of Tables

Excel 2003 introduced the concept of "Lists," which had limited functionality. With Lists, even though a lot of the currently available Excel Tables functionality was available in one form or another (sorting, filtering, formatting), it was not presented as part of the List itself and therefore was rather elusive for the average Excel user.

Excel 2007 redefined Lists as Excel Tables. Microsoft rethought how Tables could be most effectively used and put significant effort into this redesign. Not only did Tables become more user friendly, they became much more prominent in Excel's user experience model: They became the default downstream presentation form for OLAP cube queries and Power Query.

Microsoft continues to provide new functionality and refinements in Tables. Excel 2013 includes a number of changes and additions to Tables, as described in this book. Tables have become a mainstay in many versions of Excel, from the desktop version to Excel Online (the browser-based, free version of Excel). Excel Tables are here for the long term.

2 Table Behavior and Anatomy

In this chapter we discuss Table anatomy and behavior—the cornerstones in working with Tables and the rules every Table adheres to. This chapter also covers naming conventions and compatibility issues. Not all versions of Excel are the same, so this chapter explains things you should be aware of in the various versions.

Table Anatomy

There are three main parts to an Excel Table: the header row, the data body range, and the total row. Together they represent the entire Table. The following sections discuss these three parts. The following sections also cover calculated columns and the sizing handle.

The Header Row

The header row is the topmost row of a Table. You can optionally hide it from view, but the default is for the header row to be visible. The value in the header row for a column defines that column's name, which is also called the field name. To maintain consistency and allow advanced formula references (structured references), all header row values must be constants and not formulas. ...

Table of contents

• Excel Tables: A Complete Guide for Creating, Using and Automating Lists and Tables
• Foreword
• Introduction
• 1 What Are Tables?
• 2 Table Behavior and Anatomy
• 3 Working with Tables
• 4 Table Formulas
• 5 Pivoting with Tables
• 6 Sorting, Filtering, and Using Slicers
• 7 Table Formatting
• 8 Working with External Data
• 9 Automating Tables with VBA
• 10 Tables in Excel Online
• 11 Tables on Mac, iPad, and Office Mobile