Excel Tables Page 9

  Table Element—You can choose from this list of 13 style elements (discussed later in this chapter). After you select the element to edit, you can click Format to open the Format Cells dialog box. Any style elements that have settings defined appear boldfaced in this list. Clicking Clear clears all settings from that style element and removes the boldface from the style element name.

  Preview—This area provides a small representation of what the Table style element looks like when applied.

  Element Formatting—This area shows the formatting applied to the selected element, including font color (specified in RGB format), borders, and fill color/pattern.

  Set as default table style for this document—Select this check box to set the Table style as the default Table style for all new Tables created.

  Error message when you try to save a custom Table style with a name that already exists in the workbook.

  CAUTION

  You can name a custom Table style the same as a built-in Table style name. However, it’s not a good idea to do so because it can be confusing.

  Table Style Elements

  A Table style comprises multiple style elements that format the parts of a Table, such as the header row, odd and even rows, and odd and even columns. You can enable or disable each style element. If you disable one, the Whole Table style element applies to that part of the Table.

  Each element can apply font, border, and fill formatting but not number, alignment, or protection formatting. Only style elements that are defined (enabled) in the Table style are applied when you apply that Table style to a Table. The four Table style options discussed earlier in this chapter also apply to custom Table styles and override how the Table style settings are applied to a Table on a Table-by-Table basis. If a Table style element has not been defined for the part of the Table covered by the Table style option, toggling that Table style option has no effect on how the Table style is applied to the Table.

  Each Table style includes 13 style elements. You can enable each element by defining one or more style properties (font, border, or fill). You disable an element by selecting that style element and clicking the Clear button. These are the 13 style elements:

  Whole Table—Applies to the entire Table, unless superseded in another Table element.

  First Column Stripe—Applies to alternating columns.

  Second Column Stripe—Applies to alternating columns following the column(s) affected by the First Column Stripe style element.

  First Row Stripe—Applies to alternating rows.

  Second Row Stripe—Applies to alternating rows following the row(s) affected by the First Row Stripe style element.

  Last Column—Applies to the rightmost column in the data body range unless the Table contains a single column. If this element is defined, it supersedes the settings in the First Column Stripe and Second Column Stripe style elements.

  First Column—Applies to the leftmost column. If this element is defined, it supersedes the settings in the First Column Stripe and Second Column Stripe style elements.

  Header Row—Applies to the header row if the Header Row style is enabled.

  Total Row—Applies to the total row if the Total Row style is enabled.

  First Header Cell—Applies to the leftmost header cell if the header is enabled. If this element is defined, it supersedes the settings in the Header Row style element.

  Last Header Cell—Applies to the rightmost header cell if the header is enabled. If this element is defined, it supersedes the settings in the Header Row style element.

  First Total Cell—Applies to the leftmost total row cell if the total row is enabled. If this element is defined, it supersedes the settings in the Total Row style element.

  Last Total Cell—Applies to the rightmost total row cell if the total row is enabled. If this element is defined, it supersedes the settings in the Total Row style element.

  Note that some of these style elements refer to a stripe rather than to a column or row. This is because they can apply to more than one column and more than one row. The default is a single column and a single row, but as many as nine (contiguous and visible) columns and nine (contiguous and visible) rows can be affected by changing the style element's Stripe Size setting (see the next figure).

  How the first and second stripes are applied depend on each style element's stripe size. When the default of one is used for both the first and second stripes, the first stripe is applied to all odd columns or rows, and the second stripe is applied to all even columns or rows. If either the first or second stripe's stripe size is more than a single column or row, the results are different. For example, if the first stripe size is two and the second stripe size if three, the first two columns or rows are formatted with the first stripe style, the next three columns or rows the second stripe style, the next two columns or rows the first stripe style, and so on, alternating between the first and second stripe styles.

  Stripe Size settings: 1 through 9.

  NOTE

  Stripe formatting is applied only to visible columns and rows. Any hidden columns or rows are not counted in the stripe size.

  NOTE

  Most built-in Table styles define only the First Column Stripe and First Row Stripe elements. The striping effect is generally achieved by relying on the Whole Table element to format the columns and rows that are not affected by the first stripe element (that is, any columns and rows that would be affected by the second stripe element defined). Regardless of whether one or both of the first and second stripe elements are defined, toggling the Table style options Banded Rows and Banded Columns affects both style elements at the same time.

  TIP

  When you create custom Table styles, unless you have a good reason to do otherwise, it is easiest to follow the strategy used in the built-in Table styles and define only the first stripe element and let the Whole Table element format the other columns or rows.

  Each style element can apply font, border, and fill properties:

  As shown in the next figure, the Font tab lets you set these options:Font style

  Underline

  Color

  Effects (Strikethrough only)

  The Border tab lets you set these options:Line style

  Color

  Border part

  The Fill tab lets you set these options:Color

  Effects

  Pattern

  Format Cells dialog box for customizing a Table style element.

  If you want to apply cell format attributes that are not available in a Table style, you can do so by applying a cell style or manually applying any cell format settings.

  TIP

  You can change the accent colors available to a Table style element (font color, border color, and fill color) by selecting PAGE LAYOUT | Themes | Colors and choosing the desired color set. You can define a custom theme color palette by selecting PAGE LAYOUT | Themes | Colors | Customize Colors, which opens the Create New Theme Colors dialog box, shown in the next figure. To edit an existing custom theme color palate, you right-click the theme and select Edit.

  Create New Theme Colors dialog box.

  NOTE

  You can create custom styles for regular cells, PivotTables, Slicers, and Timelines. Creating and maintaining these styles is similar to creating and maintaining Table styles.

  Setting the Default Table Style

  By default, the Table style Table Style Medium 2 is applied to all new Tables. However, you can change the default Table style for a workbook at any time; you can select any built-in or custom Table
style as the default Table style. To change the default Table style, right-click the Table style you want in the Table Styles gallery and select Set As Default.

  TIP

  When you customize a Table style by using the New Table Style or Modify Table Style dialog box, you can make that Table style the default Table style by selecting the check box Set as default table style for this document, as illustrated in the next figure.

  Check box to set a Table style as the default. All new Tables created will then use this style.

  Modifying an Existing Custom Table Style

  You modify an existing custom Table style by right-clicking that Table style in the Table Styles gallery and selecting Modify, as shown in the next figure. The Modify Table Style dialog box appears.

  Right-click menu from a style in the Table Styles gallery.

  Table Style Portability

  When you create a custom Table style, it is saved to the workbook in which you created it. Unlike with workbook colors, fonts, effects, and themes, there is no native way to easily copy a Table style to another workbook. However, you can follow these steps to copy a Table style from one workbook to another:

  1. Select the entire source Table with the desired Table style applied.

  2. Copy the range (using CTRL+C).

  3. Activate the destination workbook.

  4. Add a new worksheet (optional).

  5. Paste the source Table (using CTRL+V).

  6. Delete the newly added Table or the worksheet into which it was copied (optional).

  When the Table is copied into the other workbook, Excel brings the Table style with it; the Table Style remains after the Table in the destination workbook is deleted. This method is implemented as a VBA macro in Chapter 9.

  NOTE

  The new file formats allow you to go under the hood of an Excel file. With the file closed, change the extension from XLSX or XLSM to ZIP. (XLSB files are handled differently.) Then you can open the file and navigate to see different parts of the spreadsheet, which is stored in Extensible Markup Language (XML). As a precaution, save a backup copy of your file before you explore. The XML for custom Table styles, Slicer styles, and Timeline styles is stored in the workbook file in the same node (“//styleSheet/tableStyles/tableStyle”), inside the “/xl/styles.xml” file.

  8 Working with External Data

  External data is any data from outside Excel. You may get external data from a SQL Server database, an Oracle data set, an Access database, or a Sybase database, for example. If Excel can connect to such an external source, you can pull that data into a Table. Excel offers a number of functions to query and manage data from external sources.

  Why External Data Is Important

  Acquiring and housing massive quantities of data has become an important part of the modern business landscape, and analyzing such big data has become increasingly challenging. To address this trend, Microsoft continues to add more powerful data querying and analysis tools to Excel. The latest addition is a suite of what Microsoft calls BI (business intelligence) tools to pull, aggregate, and transform very large data sets. These tools allow Excel users to present that data with traditional tools such as Tables, PivotTables, and charts to effectively tell a story.

  The majority of this book to this point has focused on how to use Tables within the scope of a workbook. This chapter discusses how Excel extends the reach of Tables to data originating outside the workbook. It presents three scenarios covering three different kinds of sources: external databases, text files, and the Azure Marketplace. The data in these sources can be from any entity capable of producing data in a tabular form: from point-of-sale systems, accounting and finance departments, or other business sources anywhere there is Internet connectivity.

  How Excel Exposes External Data Connections

  Excel devotes an entire tab to data. The DATA tab provides functions for pulling data into Excel from external sources as well as transforming and working with local data. You can access the following external data sources via Excel's DATA ribbon tab:

  Access databases

  Web services and pages

  Text files

  SQL Server databases

  SSAS (SQL Server Analysis Services)

  Windows Azure Marketplace data sources

  OData data feeds

  XML data import files

  other OLEDB or ODBC connections, including Microsoft Query NOTE

  The Get External Data group on the DATA tab is disabled if a Table is selected or if the destination of an existing query is selected, as shown in the figure below. This is because you can’t place a new query into a destination that already has a defined structure.

  Disabled Get External Data group on the DATA tab.

  Data connections are stored with the workbook, and you can view them by selecting DATA | Get External Data | Existing Connections and navigating to the Connections tab. The Tables tab of this dialog box shows existing Data Model connections that use a Table as a source, such as when a PivotTable is created using a Table as a source. (See Chapter 5 for more on this.)

  CAUTION

  Data connections may be disabled on your computer. To enable data connections, select FILE | Options | Trust Center | Trust Center Settings | External Content and ensure that either Enable all Data Connections (not recommended) or Prompt user about Data Connections is enabled.

  Refreshing External Data Connections

  When you use Tables with data connections, you can rearrange and delete columns without affecting a query. Excel continues to map the queried data to the correct columns. Column widths, however, are typically not maintained and AutoFit on refresh.

  TIP

  To stop Excel from AutoFitting Table columns with every refresh, right-click anywhere on the Table and select Table | External Data Properties and then uncheck Adjust column width. Two other options are Preserve column sort/filter/layout and Preserve cell formatting. They are both selected by default. These options are not available to all types of external data connections.

  Working with Data from a Database

  Every desktop Excel installation provides many different drivers for connecting to external data sources. A driver is software that knows about a specific kind of data source, including how to query it and how to interpret the query result and transform the result into a form compatible with Excel. Older drivers use ODBC application API technology, and newer drivers use the Object Linking and Embedding, Database (OLEDB) application API methodology. The differences between these two methodologies are beyond the scope of this book, but basically, you should use the newer OLEDB whenever possible.

  The database example in this chapter focuses on SQL Server, but there are drivers for just about any database source, including Oracle, MySQL, Access, Hadoop, SSAS, OData data feeds, and many others.

  NOTE

  When you’re connecting to an Access database, you cannot connect to a crosstab query. Chapter 9 presents a VBA solution to this problem.

  Connecting to SQL Server

  To connect to a SQL Server database, select DATA | Get External Data | From Other Sources | From SQL Server. The Data Connection Wizard appears, offering controls for specifying the server name and the type of login to use to open the connection. Contact your SQL Server or IT administrator to determine how to enter the login credentials.

  NOTE

  The following example uses the AdventureWorks2012 sample SQL Server database, which is freely available from Microsoft.

  Creating the Query

  Making a connection is only part of
the process of pulling data out of a database. Once a connection is established, a query is defined to find and return the desired data in a meaningful form. Many databases support custom routines on the server that do a lot of the querying. A database administrator is responsible for creating tables and custom routines. A query originating from an external location such as Excel can be as simple as a query for the entire contents of a single table in the database, or it can be a query for a complex joining of many tables and filtering on specific values. How much or little support an Excel developer has from the database administrator determines how simple or complex the query on the Excel side of the connection will be.

  Fortunately, Excel provides many powerful tools for performing virtually all the data transformation tasks that the database administrator can do in a stored routine. While this book does not delve into the many nuances of relational database queries, this example does present the basic process.

  With an active connection to the source database, Excel asks for the database to which to connect. (Each SQL Server instance can house any number of databases, which may or may not be related to each other.) When a database is selected, the tables and views are displayed for selection, and you can select any number of them. When you click the button Select Related Tables, you select every table with any known relationship to any currently selected table. By clicking this button again, you add more tables to the selection if there are more relationships defined in the database.

  When you import a single table, by default the destination is a new Excel Table, and the data is not loaded into the Data Model. There are two ways to load the queried data into the Data Model:

  Select the check box Enable selection of multiple tables.