Excel Tables
eBook - ePub

Excel Tables

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

Zack Barresse, Kevin Jones

Share book
  1. 300 pages
  2. English
  3. ePUB (mobile friendly)
  4. Available on iOS & Android
eBook - ePub

Excel Tables

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

Zack Barresse, Kevin Jones

Book details
Book preview
Table of contents
Citations

About This Book

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.

Frequently asked questions

How do I cancel my subscription?
Simply head over to the account section in settings and click on “Cancel Subscription” - it’s as simple as that. After you cancel, your membership will stay active for the remainder of the time you’ve paid for. Learn more here.
Can/how do I download books?
At the moment all of our mobile-responsive ePub books are available to download via the app. Most of our PDFs are also available to download and we're working on making the final remaining ones downloadable now. Learn more here.
What is the difference between the pricing plans?
Both plans give you full access to the library and all of Perlego’s features. The only differences are the price and subscription period: With the annual plan you’ll save around 30% compared to 12 months on the monthly plan.
What is Perlego?
We are an online textbook subscription service, where you can get access to an entire online library for less than the price of a single book per month. With over 1 million books across 1000+ topics, we’ve got you covered! Learn more here.
Do you support text-to-speech?
Look out for the read-aloud symbol on your next book to see if you can listen to it. The read-aloud tool reads text aloud for you, highlighting the text as it is being read. You can pause it, speed it up and slow it down. Learn more here.
Is Excel Tables an online PDF/ePUB?
Yes, you can access Excel Tables by Zack Barresse, Kevin Jones in PDF and/or ePUB format, as well as other popular books in Informatique & Applications de bureau. We have over one million books available in our catalogue for you to explore.

Information

Year
2014
ISBN
9781615473403
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