HOW TO ANALYZING EXTERNAL DATA IN EXCEL
ANALYZING DATA IN EXCEL
Your users might not know that they can use Excel to analyze data from the various databases in your organization. They can use the powerful analytical tools of Excel on data they previously only viewed or edited within the database user interface. Using these features, they can pull data from your customer database, sales tracking database, product catalog, and HR systems into various sheets in a workbook and perform complex PivotTable analysis not possible using the separate applications.
This ability is provided by the use of Microsoft Query, an Office application that lives "behind" Excel. You can access Microsoft Query by clicking Data | Get External Data. You'll find three options: Run Web Query, Run Database Query, or Create New Query. The Query interface is very similar to the Design view of a query in Access. Point Query at a database, and it will allow you to build complex relational queries to get the data you need. Running the query will pull the data returned into your worksheet. Now you can build your pivot tables and perform your analysis.
Your users might not know that they can use Excel to analyze data from the various databases in your organization. They can use the powerful analytical tools of Excel on data they previously only viewed or edited within the database user interface. Using these features, they can pull data from your customer database, sales tracking database, product catalog, and HR systems into various sheets in a workbook and perform complex PivotTable analysis not possible using the separate applications.
This ability is provided by the use of Microsoft Query, an Office application that lives "behind" Excel. You can access Microsoft Query by clicking Data | Get External Data. You'll find three options: Run Web Query, Run Database Query, or Create New Query. The Query interface is very similar to the Design view of a query in Access. Point Query at a database, and it will allow you to build complex relational queries to get the data you need. Running the query will pull the data returned into your worksheet. Now you can build your pivot tables and perform your analysis.
