{"id":2312,"date":"2026-09-16T15:28:25","date_gmt":"2026-09-16T07:28:25","guid":{"rendered":"https:\/\/xlwings.net\/blog\/?p=2312"},"modified":"2026-03-28T12:51:33","modified_gmt":"2026-03-28T12:51:33","slug":"how-to-use-worksheetconsolidationoptions-in-the-xlwings-api-way","status":"publish","type":"post","link":"https:\/\/xlwings.net\/blog\/how-to-use-worksheetconsolidationoptions-in-the-xlwings-api-way\/","title":{"rendered":"How to use Worksheet.ConsolidationOptions in the xlwings API way"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">The <code>ConsolidationOptions<\/code> member of a Worksheet object in Excel&#8217;s object model refers to settings that control how data consolidation is performed on the worksheet. In xlwings, this is accessed through the <code>api<\/code> property, which provides direct access to the underlying Excel object model. The <code>ConsolidationOptions<\/code> property returns a <code>Consolidation<\/code> object, which itself has several properties and methods to define the source ranges, function, and other options for consolidating data from multiple ranges into a single summary range. This feature is useful for summarizing data from various sheets or workbooks, such as combining sales figures from different regions.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Syntax and Parameters:<\/strong><br>In xlwings, you access <code>ConsolidationOptions<\/code> via <code>Worksheet.api.ConsolidationOptions<\/code>. This returns a <code>Consolidation<\/code> object with key properties including:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>Sources<\/code>: A list of source ranges as strings (e.g., <code>[\"Sheet1!R1C1:R10C5\", \"Sheet2!R1C1:R10C5\"]<\/code>). Each string represents a range in R1C1-style notation.<\/li>\n\n\n\n<li><code>Function<\/code>: An Excel constant specifying the consolidation function, such as <code>xlwings.constants.xlSum<\/code> for sum or <code>xlwings.constants.xlAverage<\/code> for average. Common values include:<\/li>\n\n\n\n<li><code>xlSum<\/code> (-4157): Adds values.<\/li>\n\n\n\n<li><code>xlAverage<\/code> (-4106): Calculates the average.<\/li>\n\n\n\n<li><code>xlCount<\/code> (-4112): Counts non-empty cells.<\/li>\n\n\n\n<li><code>xlMax<\/code> (-4136): Finds the maximum value.<\/li>\n\n\n\n<li><code>xlMin<\/code> (-4139): Finds the minimum value.<\/li>\n\n\n\n<li><code>TopRow<\/code> and <code>LeftColumn<\/code>: Boolean values indicating whether to use labels from the top row or left column of the source ranges for consolidation.<\/li>\n\n\n\n<li><code>CreateLinks<\/code>: A boolean that, if <code>True<\/code>, creates links to the source data (default is <code>False<\/code>).<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">To set up consolidation, you typically assign these properties and then use the <code>Consolidate<\/code> method of the <code>Range<\/code> object where you want the consolidated data. However, note that <code>ConsolidationOptions<\/code> itself is primarily for retrieving or setting options rather than executing consolidation directly.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Code Example:<\/strong><br>Here is an example using xlwings to set consolidation options and perform consolidation on a worksheet. This script assumes you have an Excel workbook open with data in multiple sheets, and you want to sum values from specific ranges into a summary sheet.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>import xlwings as xw\n\n# Connect to the active workbook\nwb = xw.books.active\n# Assume we have a summary sheet named \"Summary\"\nsummary_sheet = wb.sheets&#91;\"Summary\"]\n\n# Access the ConsolidationOptions via the api property\nconsolidation = summary_sheet.api.ConsolidationOptions\n\n# Set the sources for consolidation (using R1C1 notation for ranges from two sheets)\nconsolidation.Sources = &#91;\"Sheet1!R1C1:R10C3\", \"Sheet2!R1C1:R10C3\"]\n\n# Set the function to sum (xlSum constant)\nconsolidation.Function = xw.constants.xlSum\n\n# Use labels from the top row and left column\nconsolidation.TopRow = True\nconsolidation.LeftColumn = True\n# Do not create links to source data\nconsolidation.CreateLinks = False\n\n# Now, consolidate the data into a starting cell on the summary sheet (e.g., A1)\n# The Consolidate method is called on the Range object where consolidation begins\nsummary_sheet.range(\"A1\").api.Consolidate(Sources=consolidation.Sources,\nFunction=consolidation.Function,\nTopRow=consolidation.TopRow,\nLeftColumn=consolidation.LeftColumn,\nCreateLinks=consolidation.CreateLinks)\n\n# Optionally, you can check the current consolidation settings\nprint(\"Current sources:\", consolidation.Sources)\nprint(\"Function used:\", consolidation.Function)<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n","protected":false},"excerpt":{"rendered":"<p>The `ConsolidationOptions` member of a Worksheet object in Excel&apos;s object model refers to settings t&#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-2312","post","type-post","status-publish","format-standard","hentry","category-xlwings-api-reference"],"_links":{"self":[{"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/posts\/2312","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=2312"}],"version-history":[{"count":1,"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/posts\/2312\/revisions"}],"predecessor-version":[{"id":3526,"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/posts\/2312\/revisions\/3526"}],"wp:attachment":[{"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/media?parent=2312"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/categories?post=2312"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/tags?post=2312"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}