{"id":2288,"date":"2026-09-04T15:35:05","date_gmt":"2026-09-04T07:35:05","guid":{"rendered":"https:\/\/xlwings.net\/blog\/?p=2288"},"modified":"2026-03-28T12:32:03","modified_gmt":"2026-03-28T12:32:03","slug":"how-to-use-worksheetpivottablewizard-in-the-xlwings-api-way","status":"publish","type":"post","link":"https:\/\/xlwings.net\/blog\/how-to-use-worksheetpivottablewizard-in-the-xlwings-api-way\/","title":{"rendered":"How to use Worksheet.PivotTableWizard in the xlwings API way"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">The PivotTableWizard method in Excel&#8217;s object model is a legacy but powerful tool for programmatically creating pivot tables. In xlwings, this functionality is accessed through the <code>api<\/code> property, which provides direct access to the underlying Excel object model. The <code>PivotTableWizard<\/code> method belongs to the <code>Worksheet<\/code> object and allows for the dynamic generation of pivot tables based on specified source data and parameters. It is particularly useful when you need to automate the creation of pivot tables with custom configurations that might be cumbersome to set up manually through the Excel interface.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The syntax for calling <code>PivotTableWizard<\/code> via xlwings is as follows:<br><code>worksheet.api.PivotTableWizard(SourceType, SourceData, TableDestination, TableName, RowGrand, ColumnGrand, SaveData, HasAutoFormat, AutoPage, Reserved, BackgroundQuery, OptimizeCache, PageFieldOrder, PageFieldWrapCount, ReadData, Connection)<\/code><br>Here, <code>worksheet<\/code> is an xlwings <code>Sheet<\/code> object. The parameters control various aspects of the pivot table creation. Key parameters include:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>SourceType<\/code>: Specifies the source of data. It can be <code>xlDatabase<\/code> (Excel range), <code>xlExternal<\/code> (external data source), <code>xlConsolidation<\/code> (multiple ranges), or <code>xlScenario<\/code>. Commonly, <code>xlDatabase<\/code> is used.<\/li>\n\n\n\n<li><code>SourceData<\/code>: The range containing the source data. This can be a <code>Range<\/code> object or a string address.<\/li>\n\n\n\n<li><code>TableDestination<\/code>: A <code>Range<\/code> object specifying the top-left cell where the pivot table should be placed.<\/li>\n\n\n\n<li><code>TableName<\/code>: A string for the pivot table&#8217;s name.<br>Other parameters like <code>RowGrand<\/code> and <code>ColumnGrand<\/code> control the display of grand totals, while <code>SaveData<\/code> determines if data is saved with the pivot table. Many parameters are optional and can be omitted by using <code>None<\/code> in Python.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">For example, consider creating a pivot table from data in <code>Sheet1<\/code> ranging from A1 to D100, placing the pivot table in <code>Sheet2<\/code> starting at cell A3. The following xlwings code demonstrates this:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>import xlwings as xw\nfrom xlwings.constants import PivotTableSourceType\n\n# Connect to the active workbook\nwb = xw.Book.active\nsource_sheet = wb.sheets&#91;'Sheet1']\ndestination_sheet = wb.sheets&#91;'Sheet2']\n\n# Define source data range\nsource_range = source_sheet.range('A1:D100')\n\n# Create the pivot table\ndestination_sheet.api.PivotTableWizard(\nSourceType=PivotTableSourceType.xlDatabase,\nSourceData=source_range.api,\nTableDestination=destination_sheet.range('A3').api,\nTableName='SalesPivotTable',\nRowGrand=True,\nColumnGrand=True\n)<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n","protected":false},"excerpt":{"rendered":"<p>The PivotTableWizard method in Excel&apos;s object model is a legacy but powerful tool for programmatical&#8230;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[25],"tags":[],"class_list":["post-2288","post","type-post","status-publish","format-standard","hentry","category-xlwings-api-reference"],"_links":{"self":[{"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/posts\/2288","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/comments?post=2288"}],"version-history":[{"count":1,"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/posts\/2288\/revisions"}],"predecessor-version":[{"id":3491,"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/posts\/2288\/revisions\/3491"}],"wp:attachment":[{"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/media?parent=2288"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/categories?post=2288"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/tags?post=2288"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}