In the Select Table dialog box, select the table you want and then click OK. In the Select Data Source dialog box, locate the database you want to connect to, and click Open. To create a new connection to an Access database and import data into Excel as a table or PivotTable, do the following: On page 3, enter the connection file you want to create. On page 2, enter the database, table, or query that contains the data you want. On page 1, enter the database server and specify how you want to log on to the server. In the Data Connection Wizard, complete the steps to establish the connection. To create a new external data connection to SQL Server and import data into Excel as a table or PivotTable, do the following:Ĭlick From SQL Server to create a connection to a SQL Server table.Ĭlick From Analysis Services to create a connection to a SQL Server Analysis cube. Use the Field List to further design the layout and format of a PivotTable by right-clicking the fields in the areas section, and then selecting the area you want, or by dragging the fields between the areas in the areas section. Tip: You can also right-click a field name, and then select Add to Report Filter, Add to Column Labels, Add to Row Labels, or Add to Values to place the field in that area of the areas section, or drag a field from the field section to an area in the areas section. You can move fields to a different area as needed. Typically, nonnumeric fields are added to the Rows area, numeric fields are added to the Values area, and date and time fields are added to the Columns area. In the field list section, check the box next to a field name to place the field in a default area of the areas section of the Field List. To place the PivotTable in the active worksheet, choose Existing Worksheet, and then in the Location box, enter the cell where you want the PivotTable to start.Įxcel adds an empty PivotTable and shows the Field List so that you can show the fields you want and rearrange them to create your own layout. To place the PivotTable in a new worksheet starting at cell A1, choose New Worksheet. Under Choose where you want the PivotTable report to be placed, pick a location. In the list of connections, select the connection you want, and then click Open. To reuse or share an existing connection, use a connection from Connections in this Workbook. On the Connections tab, in the Show box, keep All Connections selected, or pick the connection category that has the data source you want to connect to. In the Create PivotTable dialog box, click From External Data Source.
#Excel 2013 create pivot table how to
Here’s how to create a PivotTable by using an existing external data connection:
In that case, you’ll connect to the external data source, and then create a PivotTable to summarize, analyze, explore, and present that data. But sometimes it’s hard to know where to start, especially when you have a lot of data that is stored outside of Excel, like in a Microsoft Access or Microsoft SQL Server database, or in an Online Analytical Processing (OLAP) cube file. Lessīeing able to analyze all the data can help you make better business decisions.
Excel for Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007 More.
0 kommentar(er)
