Excel Tables: A Complete Guide for Creating, Using and Automating Lists and Tables
300
Excel Tables: A Complete Guide for Creating, Using and Automating Lists and Tables
300eBook
Available on Compatible NOOK devices, the free NOOK App and in My Digital Library.
Related collections and offers
Overview
Product Details
| ISBN-13: | 9781615473403 |
|---|---|
| Publisher: | Holy Macro! Books |
| Publication date: | 08/01/2014 |
| Sold by: | INDEPENDENT PUB GROUP - EPUB - EBKS |
| Format: | eBook |
| Pages: | 300 |
| File size: | 6 MB |
About the Author
Read an Excerpt
Excel Tables
A Complete Guide for Creating, Using and Automating Lists and Tables
By Zack Barresse, Kevin Jones, Kitty Wilson
Holy Macro! Books
Copyright © 2014 Holy Macro Books!All rights reserved.
ISBN: 978-1-61547-340-3
CHAPTER 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.
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.
CHAPTER 2Table 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. If a header can be changed in the course of working with that Table, you can use a validation list to present the user with a list of possible header values. When a header is changed, all formula references to that column are automatically adjusted.
In addition to defining the column or field names, the header row is used to display a filter button (a downward-pointing black triangle) over each column. When you click the filter button, Excel opens a dialog that presents many options for filtering and sorting the Table using that column. The next figure highlights the header row and filter buttons in a Table.
Table header values must be unique within a Table. When you enter a header value that is the same as another header in the same Table, Excel appends a number to the end of the rightmost header value to make it unique. For example, if there is a column with the header "ID", if you enter "ID" into another column to the left of the existing value, the rightmost header value, the original "ID", changes to "ID2". If instead you enter the new "ID" to the right of the existing "ID" header, Excel changes the new header to "ID2".
The Data Body Range
The data body range is the area of a Table between the header row and the total row, where data is displayed, entered, and maintained. If there is no data, the Table displays one empty row for entering data. The number of rows in a Table's data body range is limited only by the number of rows in a worksheet. The next figure highlights the data body range in a Table.
The Total Row
The total row is the bottom row of a Table. You can optionally show this row, but the default is to hide the total row. When you select a cell in the total row, a drop-down menu control appears; you can click this dropdown to see a list of possible built-in aggregate functions. The functions aggregate only the visible cells in the column directly above the total row. In addition to using the built-in functions, you can enter into any total row cell custom functions that reference any cells inside or outside the Table. The next figure highlights the total row in a Table.
Calculated Columns
A calculated column is a column that contains a single formula applied to the entire data area part of that column. A column that contains different formulas in different cells is not a calculated column.
In a calculated column, any formula entered is automatically assumed to be the new default formula and is applied to all cells in the data area range of the column. In a column that contains formulas or values but is not a calculated column, entering a formula in any cell changes only that cell's formula. An AutoCorrect Options button appears in the bottom-right corner of the cell, with an option to apply the formula to the entire column; you can select this option to enable the calculated column and overwrite everything in the column with the formula you enter.
Excel maintains the formulas in calculated columns and uses them in new rows.
The Sizing Handle
In the bottom-right corner of a Table is a small icon called the sizing handle. This icon both shows where the Table ends and allows you to increase or decrease the Table's range: Just drag the handle.
Table Behavior
Before you create a Table, it is important to understand what works with a Table and what does not. Several limitations affect how Tables appear and how they function. These limitations are in place to support the functionality that Excel's Table functions provide. For example, Excel does not allow you to give two Tables in a workbook the same name, as doing so would prevent you from referencing the Tables within formulas.
Table Limitations
When a list of data is defined as an Excel Table, Excel does not allow that Table to:
• Have headers in more than one row
• Have totals in more than one row
• Include duplicate column header values
• Include multiple-cell array formulas (but single-cell entered array formulas are allowed)
• Have any cells overlap any other Table
• Be named the same as any other Table in the workbook
Excel also does not allow you to save a workbook with any Tables as a shared workbook. However, you can share an Excel Table by using SharePoint.
As long as you follow these rules, Tables behave as any normal range, with a few subtle exceptions that are discussed in this book.
Basic Table Behavior
A Table is basically just a list of data that has been designated as an Excel Table. With that designation you get some additional functionality designed to make working with the list of data easier and more productive. The following are some examples of the differences between a list of data and an Excel Table:
[TABLE OMITTED]
How Tables Interact with Other Worksheet Objects
Unlike with a list of data, Excel confines actions taken with a Table to that Table's range. Cells outside the Table's range are left intact as much as possible. Excel never overwrites existing data when inserting new rows and columns, nor does Excel resize a Table to overlap other cells that contain values or formulas. When new rows are inserted into a Table, Excel shifts down only cells in the Table (below the row[s] being inserted) and cells below the Table. When you add a new row to the bottom of a Table (by pressing TAB while the last cell of the last row is selected), Excel adds the new row without shifting anything below unless there is no room, in which case it shifts down everything below by one row.
The next figure shows three Tables stacked vertically. These Tables are separated by two blank rows. If you append a new row to the bottom of the topmost Table by using the TAB key, Excel increases the Table size by a single row, without shifting the Tables below. The topmost Table takes up one of the rows below it that was originally blank, increasing the Table size by one row. It can do that one more time because there is another blank row below the Table. Then, when there are no more empty cells for appending rows to the Table, the selection moves to the next row in the first column, but the Table is not resized and extended down any more rows.
Excel manages Table header and total rows in a similar manner. When a Table's header row is turned on, Excel shifts down cells only if there is no room for the header row. When the header row is turned off, Excel does not shift any cells, and a row of empty cells remains in its place. When the total row is turned on, Excel always shifts the following cells down one row. When the total row is turned off, Excel always shifts the following cells up one row.
Awareness and Visibility
Sometimes it may be difficult to tell whether a range of cells or a single cell is part of a Table. Selecting any cell within a Table results in the display of the contextual {TABLE TOOLS} DESIGN tab. If this tab is present on the ribbon, the active cell resides in a Table.
Determining Column Names
Excel Tables can become very large. When you're working with a large Table, you may have trouble identifying columns when the Table's header row is not visible. One way to solve this issue is to freeze the Table's header row (by selecting VIEW | Window | Freeze Panes | Freeze Panes) so that it doesn't scroll. If the worksheet's Freeze Panes option is not enabled, Excel displays the Table's headers in the worksheet header row (where normally it displays the column letters A, B, C, etc.) as long as the active cell is inside that Table, as shown in the next figure.
With Freeze Panes disabled, Table headers are visible while scrolling through a large Table.
When the Table's headers are displayed this way, the filter buttons are available.
Accessibility
For users who have accessibility needs and tools, such as screen readers, many Excel objects support alternative text to help those users understand the content. You edit a Table's alternative text by right-clicking any cell in the Table and selecting Table | Alternative Text.
Accessing the Alternative Text option by right-clicking a cell.
You may also want to use alternative text when publishing content to a web page or DAISY-formatted file. When a user hovers the mouse over an object that has alternative text, that text displays.
Table Names
Every Table has a name. As you already know, within a workbook, every Table name must be unique. Excel automatically assigns the name "Table" with a number appended for each new Table — for example, "Table1", "Table2", etc. When you copy a Table to another location, Excel uses the name of the original Table with another number appended to ensure that the name is unique within the scope of the workbook; for example, Excel might change "Table2" to "Table21". When you import data from an external database such as SQL Server or Access, Table names default to "Table_QueryName" — for example, "Table_Employees".
Changing Table Names
You can change a Table's name at any time. Besides being unique within the scope of the workbook, a Table name must:
• Not contain special characters other than an underscore
• Not contain spaces
• Always start with a letter or an underscore
You change a Table's name by editing the name displayed in the {TABLE TOOLS} DESIGN | Properties | Table Name text box, as illustrated in the next figure.
Editing a Table name in the Properties ribbon group.
As shown in the next figure, Excel displays an error message if the name you enter is not unique or does not follow one or more of these rules.
An error message for an invalid name.
When you change the name of a Table generated as the result of a query, the name does not revert back to its default name when you refresh the query. However, if you delete the Table and reconnect the query, the new Table gets the default name.
When you change the name of a Table, Excel automatically applies the change to any and all formulas that reference the Table by name.
Naming Conventions
A traditional naming convention is called Hungarian notation. Hungarian notation uses the first few letters of a name to identify the type of object (called the type prefix). These letters are set in lowercase. They are usually a shortened form of the object type, such as "wkb" for workbook or "wks" for worksheet. For Tables, it's generally accepted to use either "tbl" (for Table) or "lo" (for List object). Programmers and Excel users have used type prefixes for many years. Although most modern standards no longer use them, they can make object names more intuitive.
However, you really don't need to use a type prefix for Table names , and doing so can be confusing. Meaningful names such as "Data", "Assets", "Expenditures", etc. can be just as helpful or more so to those using and maintaining workbooks that contain Tables. We recommend using meaningful names over prefixed or Hungarian notation.
Keep in mind two things when naming Tables:
• With formulas using structured references that reference a Table but are located outside that Table, you need to use Table names.
• You can and should use Table names for referencing Tables when automating Tables using VBA, as discussed in Chapter 9.
Forward and Backward Compatibility
Microsoft works very hard to ensure that files will be forward compatible — that is, that files created in older versions of Excel still work with newer versions. Microsoft also tries for backward compatibility — that is, ensuring that files created in newer versions work with older versions. With backward compatibility, for example, a file created in Excel 2013, using the full feature set and tools that may not be available in previous versions, will still be able to open, without errors, in previous versions of Excel. Although some of the newer features aren't available in previous versions of the application, the file won't break or become corrupted when it's opened in the older version. In this section we describe how Microsoft has worked to ensure backward and forward compatibility with Excel Tables.
Formulas
When Excel 2007 introduced Tables as a reinvented version of lists, one of the major additions was the introduction of structured references. A structured reference accomplishes the same result as a traditional R1C1 or A1 reference: It references a range of one or more cells. However, it uses the names of the Table parts to create the reference. (Chapter 4 covers structured references in detail.)
(Continues...)
Excerpted from Excel Tables by Zack Barresse, Kevin Jones, Kitty Wilson. Copyright © 2014 Holy Macro Books!. Excerpted by permission of Holy Macro! Books.
All rights reserved. No part of this excerpt may be reproduced or reprinted without permission in writing from the publisher.
Excerpts are provided by Dial-A-Book Inc. solely for the personal use of visitors to this web site.
Table of Contents
Contents
Excel Tables: A Complete Guide for Creating, Using and Automating Lists and Tables, i,Foreword, v,
Introduction, 1,
1 What Are Tables?, 5,
2 Table Behavior and Anatomy, 7,
3 Working with Tables, 19,
4 Table Formulas, 35,
5 Pivoting with Tables, 49,
6 Sorting, Filtering, and Using Slicers, 65,
7 Table Formatting, 75,
8 Working with External Data, 87,
9 Automating Tables with VBA, 93,
10 Tables in Excel Online, 131,
11 Tables on Mac, iPad, and Office Mobile, 141,