Excel Tables by Zack Barresse

Author:Zack Barresse
Language: eng
Format: epub
Tags: Excel;microsoft excel;tables;database;formatting;reporting;table syntax;creating tables;slicers;filtering;VBA macros;Excel web app
Publisher: Holy Macro! Books
Published: 2015-10-30T04:00:00+00:00

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.

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.