The easiest way to create a PivotTable in Microsoft Excel is to create a cross-tab report and output the report to an Excel PivotTable. However, you may also wish to create a PivotTable from a quick report or other type of report. Here's a step-by-step guide to doing that.

  1. Create a report containing the fields you'd like to create a PivotTable on. For example, say you want to analyze sales by country and product. Create a new report and select Country from the Customers table, Product Name from the Products table, and Total Price from the Order Details table.

  2. Set any desired filter for the report.

  3. Output the report to a Microsoft Excel file by going to Output page in the Reports Explorer, choosing File for the Output to option, clicking the File button, choosing Microsoft Excel - Data Format (*.xlsx) in the Save as type option, entering the name of the file to create, and clicking Save. Turn on the Display file after creation setting and click the Process button to create the file and open it in Excel.

  4. Click the "+" button in the tabs area at the bottoms of the Excel window to create a new worksheet. From the Insert tab of the ribbon, choose PivotTable. In the Create PivotTable dialog, choose Select a table or range.

  5. To select the data from the report, click the first tab at the bottom of the Excel window, click in the A1 cell, scroll to the bottom of the data, and, while holding down the Shift key, click in the last cell containing data to select all of the cells. The range is automatically filled in for the Table/Range setting.

  6. Click OK in the Create PivotTable dialog.

  7. In the PivotTable Fields panel, turn on the fields you want included in the PivotTable.

  8. Excel immediately creates a PivotTable for you. Format the PivotTable, filter certain values out of the rows or columns, print the table, save it, etc.

© Stonefield Software Inc., 2023 • Updated: 04/16/21
Comment or report problem with topic