how to do a pivot table in excel with ease. What a difference this PivotTable? Notice how the list of Fields on the right-hand side shows not one but a collection of tables. Each table contains the fields that you can combine into one PivotTable to cut your data in various ways. Manual formatting is not required or the preparation of the data. You can directly compile the PivotTable based on the related tables immediately after you import the data.
To get some tables in the PivotTable field list:
Import of related databases, such as Microsoft SQL Server, Oracle, or Microsoft Access. You can import multiple tables at the same time.
Import multiple tables from other data sources including text files, data feeds, data, Excel worksheets, and much more. You can add this table to the Model Data in Excel, create relationships between them, and then using the Data Model to create YourPivotTable.
Here’s how to import multiple tables from SQL Server databases.
a, make sure you know the server name, database name, and the credentials of which one to use when connecting to SQL Server. Your database administrator can provide the information required.
b. Click > Data Get External Data from other sources > > From SQL Server.
c. in the Server name box, enter the name of the computer network from a computer that is running SQL Server.
e. in your sign-in credentials, click Use Windows Authentication if you are connecting as yourself. If not, enter your user name and password provided by the database administrator.
f. press Enter and select box, in Database and table, select the database you want, and then click Enable selection of multiple tables.
g. If you know exactly which tables you want to do manually, select the table that you want to do. If not, select one or two, and then click Select Related Tables to automatically select the table associated with that you selected earlier.
h. If the Import relationship between the selected table is selected, keep it to allow Excel to recreate the same table relationships in the workbook.
i. click Finish.
j. in the Import Data dialog box, select PivotTable Report.
k. click OK to start the import and the collection of List fields.
Note that the field list contains multiple tables. This is all the tables you selected when importing. You can expand or collapse each table to display the fields. As long as the tables are still related, you can create a chart of Your PivotTable by dragging the fields from the table where the value, row, or column.
Drag the field to a numeric value. For example, if you use the sample Adventure Works database, you can drag the SalesAmount from table FactInternetSales.
–Drag the date fields or to the row or column to analyze sales by date or region.
Sometimes you need to create a relationship between two tables before you can use it in a PivotTable. If you get a message indicating that the required table relationships, click Create to begin with.
–Create Button will appear when the relationship is required
If you are working with the database type to another:
-To use a relational database, such as Oracle, you may need to install additional client software. See Your database administrator to find out if the software is required.
–You can import multiple tables from Access. See Tutorial: data analysis using PivotTable Data in Excel Model 2013 for details.