{"id":2311,"date":"2026-09-16T07:51:07","date_gmt":"2026-09-15T23:51:07","guid":{"rendered":"https:\/\/xlwings.net\/blog\/?p=2311"},"modified":"2026-03-28T12:51:04","modified_gmt":"2026-03-28T12:51:04","slug":"how-to-use-worksheetconsolidationfunction-in-the-xlwings-api-way","status":"publish","type":"post","link":"https:\/\/xlwings.net\/blog\/how-to-use-worksheetconsolidationfunction-in-the-xlwings-api-way\/","title":{"rendered":"How to use Worksheet.ConsolidationFunction in the xlwings API way"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">The <code>ConsolidationFunction<\/code> property of the <code>Worksheet<\/code> object in Excel VBA refers to the function used when consolidating ranges (e.g., Sum, Count, Average). However, in the context of xlwings, a Python library for automating Excel, direct access to this specific VBA property is not typically exposed as a first-class API feature because xlwings focuses more on data manipulation, calculation, and automation rather than replicating the entire UI-driven consolidation feature set. The consolidation functionality in Excel is often accessed via the UI (Data &gt; Consolidate) or VBA, and xlwings can automate these actions through the <code>.api<\/code> property to access the underlying Excel object model.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">In xlwings, to utilize the <code>ConsolidationFunction<\/code>, you would work through the Excel Object Model via the <code>api<\/code> property. The <code>ConsolidationFunction<\/code> is a property of a <code>Worksheet<\/code> object in Excel&#8217;s VBA, which returns or sets the function used for consolidation (an <code>XlConsolidationFunction<\/code> constant). It is primarily used when a worksheet has a consolidation set up. The syntax in VBA is <code>Worksheet.ConsolidationFunction<\/code>, and in xlwings, you access it similarly through the worksheet&#8217;s API object.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Functionality:<\/strong><br>This property indicates the consolidation function (e.g., sum, average, count) applied to data ranges that have been consolidated on the worksheet. It is read-only and returns an integer corresponding to an <code>XlConsolidationFunction<\/code> enumeration. Common values include:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>-4106<\/code> or <code>xlSum<\/code> for summation<\/li>\n\n\n\n<li><code>-4109<\/code> or <code>xlCount<\/code> for counting numbers<\/li>\n\n\n\n<li><code>-4116<\/code> or <code>xlAverage<\/code> for averaging<\/li>\n\n\n\n<li><code>-4135<\/code> or <code>xlMax<\/code> for maximum value<\/li>\n\n\n\n<li><code>-4136<\/code> or <code>xlMin<\/code> for minimum value<br>It is useful for programmatically checking the type of consolidation applied, especially in automated reports or when auditing workbook structures.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Syntax in xlwings:<\/strong><br>To access this property in xlwings, use:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>worksheet.api.ConsolidationFunction<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">This returns an integer representing the consolidation function constant. Note that this property is only meaningful if the worksheet contains a consolidation range; otherwise, it may return <code>xlNone<\/code> or another default.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Example Usage:<\/strong><br>Suppose you have an Excel workbook with a worksheet that has a consolidation set up to sum data from multiple ranges. You can use xlwings to inspect the consolidation function:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>import xlwings as xw\n\n# Connect to the active workbook or open a specific one\nwb = xw.Book('consolidation_example.xlsx')\nws = wb.sheets&#91;'ConsolidatedSheet']\n\n# Access the ConsolidationFunction property via .api\nconsolidation_func = ws.api.ConsolidationFunction\n\n# Map the integer to a readable function name\nfunc_map = {\n-4106: 'Sum',\n-4109: 'Count',\n-4116: 'Average',\n-4135: 'Max',\n-4136: 'Min',\n-4142: 'Unknown' # xlNone or other\n}\nfunc_name = func_map.get(consolidation_func, 'Not Consolidated')\n\nprint(f\"The consolidation function on '{ws.name}' is: {func_name} (Code: {consolidation_func})\")\n\n# Optionally, you can set up a new consolidation using VBA methods via .api\n# This requires using the Range.Consolidate method, which is more complex\n# Example: Consolidate data from multiple ranges with a sum function\nif func_name == 'Not Consolidated':\n    # Define source ranges (example: ranges from other sheets)\n    sources = &#91;\"Sheet1!R1C1:R10C5\", \"Sheet2!R1C1:R10C5\"]\n    ws.api.Range(\"A1\").Consolidate(Sources=sources, Function=-4106) # -4106 for xlSum\n    print(\"Consolidation set up with Sum function.\")\nelse:\n    print(\"Consolidation already exists.\")\n\nwb.save()\nwb.close()<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>The `ConsolidationFunction` property of the `Worksheet` object in Excel VBA refers to the function u&#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-2311","post","type-post","status-publish","format-standard","hentry","category-xlwings-api-reference"],"_links":{"self":[{"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/posts\/2311","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=2311"}],"version-history":[{"count":2,"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/posts\/2311\/revisions"}],"predecessor-version":[{"id":3525,"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/posts\/2311\/revisions\/3525"}],"wp:attachment":[{"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/media?parent=2311"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/categories?post=2311"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/tags?post=2311"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}