{"id":2355,"date":"2026-10-08T07:27:46","date_gmt":"2026-10-07T23:27:46","guid":{"rendered":"https:\/\/xlwings.net\/blog\/?p=2355"},"modified":"2026-03-29T09:49:59","modified_gmt":"2026-03-29T09:49:59","slug":"how-to-use-worksheetusedrange-in-the-xlwings-api-way","status":"publish","type":"post","link":"https:\/\/xlwings.net\/blog\/how-to-use-worksheetusedrange-in-the-xlwings-api-way\/","title":{"rendered":"How to use Worksheet.UsedRange in the xlwings API way"},"content":{"rendered":"\n

The UsedRange<\/strong> property of a Worksheet<\/strong> object in Excel is a powerful feature that returns a Range<\/strong> object representing the area of a worksheet that is currently in use. This includes all cells that have ever contained data, formatting, or formulas, even if some of those cells are now empty. It is particularly useful for dynamically determining the extent of your data without needing to know the exact row and column boundaries beforehand. In xlwings, accessing this property allows you to programmatically interact with the actual used portion of a sheet, making your code more robust and adaptable to changing data sizes.<\/p>\n\n\n\n

Syntax in xlwings:<\/strong>
The UsedRange property is accessed as an attribute of a Worksheet object. There are no parameters to specify.<\/p>\n\n\n\n

used_range = ws.used_range<\/code><\/pre>\n\n\n\n
Here, ws<\/code> is an xlwings Worksheet object (e.g., obtained via wb.sheets['Sheet1']<\/code>). The returned used_range<\/code> is an xlwings Range object. This Range object has properties like .address<\/code>, .row<\/code>, .column<\/code>, .rows.count<\/code>, and .columns.count<\/code> which you can use to get the location and dimensions of the used area.<\/p>\n\n\n\n
Key Functionality and Parameters:<\/strong>
Since UsedRange is a property, not a method, it does not take direct parameters. Its behavior is controlled by the state of the Excel worksheet. However, understanding what constitutes the “used range” is crucial:<\/p>\n\n\n\n
    \n
  • Inclusion Criteria:<\/strong> Any cell that has ever been edited, formatted, or contained a formula is considered part of the used range. This means clearing cell contents might not remove it from the used range if formatting remains.<\/li>\n\n\n\n
  • Dynamic Nature:<\/strong> The used range can expand when new data is added at its edges and can sometimes be reset by saving the workbook after deleting all content and formatting from unused cells.<\/li>\n<\/ul>\n\n\n\n
    Code Examples:<\/strong>
    Here are practical examples of using ws.used_range<\/code> in xlwings:<\/p>\n\n\n\n
      \n
    1. Getting the Address and Dimensions:<\/strong><\/li>\n<\/ol>\n\n\n\n
      import xlwings as xw\napp = xw.App(visible=False)\nwb = xw.Book('data.xlsx')\nws = wb.sheets[0]\n\nused_rng = ws.used_range\nprint(f\"Used Range Address: {used_rng.address}\")\nprint(f\"Number of used rows: {used_rng.rows.count}\")\nprint(f\"Number of used columns: {used_rng.columns.count}\")\nprint(f\"Top-left cell: ({used_rng.row}, {used_rng.column})\")\n\nwb.close()\napp.quit()<\/code><\/pre>\n\n\n\n
        \n
      1. Clearing All Content and Formatting in the Used Range:<\/strong><\/li>\n<\/ol>\n\n\n\n
        import xlwings as xw\nwb = xw.Book('report.xlsx')\nws = wb.sheets['Summary']\nws.used_range.clear() # Clears both content and formatting\nwb.save()<\/code><\/pre>\n\n\n\n
          \n
        1. Dynamically Reading All Data into a Python List:<\/strong><\/li>\n<\/ol>\n\n\n\n
          import xlwings as xw\nwb = xw.Book('inventory.xlsx')\nws = wb.sheets['Stock']\n\n# Read all values from the used range into a 2D list\nall_data = ws.used_range.value\nfor row in all_data:\n    print(row)\n\n# If you need the data as a pandas DataFrame\nimport pandas as pd\ndf = pd.DataFrame(all_data)\nprint(df.head())<\/code><\/pre>\n\n\n\n
            \n
          1. Applying Formatting to the Entire Used Area:<\/strong><\/li>\n<\/ol>\n\n\n\n
            import xlwings as xw\nfrom xlwings.constants import LineStyle\nwb = xw.Book('financials.xlsx')\nws = wb.sheets['Q1']\n\nused_rng = ws.used_range\n# Add borders around all used cells\nused_rng.api.Borders.LineStyle = LineStyle.xlContinuous\nused_rng.api.Borders.Weight = 2\nwb.save()<\/code><\/pre>\n\n\n\n
            <\/p>\n","protected":false},"excerpt":{"rendered":"
            The **UsedRange** property of a **Worksheet** object in Excel is a powerful feature that returns a *…<\/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-2355","post","type-post","status-publish","format-standard","hentry","category-xlwings-api-reference"],"_links":{"self":[{"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/posts\/2355","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=2355"}],"version-history":[{"count":2,"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/posts\/2355\/revisions"}],"predecessor-version":[{"id":3590,"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/posts\/2355\/revisions\/3590"}],"wp:attachment":[{"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/media?parent=2355"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/categories?post=2355"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/tags?post=2355"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}