How to resize an existing named range in Excel?
In daily Excel tasks, named ranges are commonly used to simplify formulas, data analysis, and data validation. However, as your data grows or changes, you may find that an existing named range no longer covers all the cells you need. In such cases, you must resize or expand the named range to include more rows or columns without the need to create a new name from scratch. Understanding how to adjust named ranges efficiently helps maintain the accuracy of calculations and ensures that formulas referencing the named range continue to work as expected. This article discusses three practical methods to resize an existing named range in Excel, each suitable for different usage scenarios and user preferences.
Resize an existing named range with the Name Manager function
Resize an existing named range with VBA code
Resize an existing named range with Kutools for Excel
Resize an existing named range with the Name Manager function
When you want to manually adjust the size of a named range, Excel’s built-in Name Manager provides a clear and straightforward option. This method is most effective for single-use or occasional adjustments, where you need to visually select the new area for your named range. It is particularly helpful if you are managing a small number of named ranges or want to confirm the new range selection directly on your worksheet.
Use the steps below for resizing a named range with Name Manager:
1. Click the Formulas tab in the Excel ribbon, then select Name Manager. This opens a list of all named ranges in your workbook for easy management. See screenshot:
2. In the Name Manager dialog box, you’ll see all the defined names in the workbook. Select the range name you want to change, and then click the 
button found at the right side of the Refers to box.
3. Now, the Name Manager – Refers to box is activated. At this point, you can select a new range directly on your worksheet, or you can manually enter the new range address (for example, =Sheet6!$A$1:$E$6) in the box. After selecting or entering the new range, close this pop-up box to apply your changes.
4. You will return to the Name Manager dialog box. Click the 
button to confirm your changes, and finally click the Close button to exit the dialog box. Your selected named range will now refer to the new area you specified. See screenshot:
After completing these steps, the specified named range will be instantly updated. All formulas or tools referencing this named range will now automatically reflect the data in the resized range.
Tips: When resizing a named range using Name Manager, be sure that you do not overlap with other critical data or delete any required cells from the new range, as this can affect formulas or data validation rules that depend on that range.
Common issues to watch for: If you encounter an error when saving the new range, double-check that the range reference is valid (no missing sheet names or misspelled references), and ensure that the workbook is not set to read-only.
Resize an existing named range with VBA code
If you need to resize named ranges frequently, or if you have multiple ranges to update at once, using VBA code can save significant time by automating the process. This method is especially recommended for advanced users or those comfortable with working in the Visual Basic for Applications environment. VBA also allows more control, such as resizing multiple named ranges in a single operation.
Follow these steps to resize a named range via VBA:
1. Press Alt + F11 together to open the Microsoft Visual Basic for Applications editor.
2. In the VBA editor, click Insert > Module from the menu. This will open a new module window where you can enter your VBA code.
3. Paste the following VBA code into the module window:
VBA: Resize named range
Sub ResizeNamedRange()
Dim xWb As Workbook
Dim xNameString As String
Dim xName As Name
Set xWb = Application.ActiveWorkbook
xNameString = Application.InputBox("Name:", xTitleId, "", Type: = 2)
Set xName = xWb.Names.Item(xNameString)
With xName
.RefersTo = .RefersToRange.Resize(10, 6)
End With
End SubNotes:
4. After modifying the variables as needed, press the F5 key to run the code. In the prompt that appears, enter the name of the range you want to resize in the Name box, then click OK to proceed.
The named range will be resized immediately according to your specifications. All references to this named range throughout your workbook will be updated to reflect the new area.
Advantages: VBA is suitable for batch processing, handles repetitive resizing efficiently, and can be incorporated into larger macros for complex workflows.
Caveats: Macros must be enabled for VBA code to run; macro-enabled workbooks are required for saving changes. Use care when running VBA code, and consider saving a backup of your file in advance just in case unintended changes occur.
Troubleshooting: If you receive run-time errors, check that the named range exists, that the new size does not exceed worksheet limits, and that you have permission to modify the file.
Resize an existing named range with Kutools for Excel
For users who frequently manage multiple named ranges or need a more visual and efficient approach, Kutools for Excel provides a handy solution. All range names are prominently displayed in its Navigation Pane, making it convenient to find, resize, and manage named ranges without navigating multiple dialog boxes. This approach is especially helpful if you work with complex workbooks or need to make quick adjustments as your data updates.
Here’s how you can resize a named range using Kutools for Excel:
1. After you have installed Kutools for Excel, the Navigation Pane will automatically appear on the left side of the Excel workspace. Click the Name manager
button to display all named ranges. Select the named range you want to resize, then click the 
button to initiate the resizing process.
2. In the pop-up Kutools for Excel dialog, you will see options to specify a new range. Either use your mouse to select the desired new cells on your worksheet or manually type the new range address in the field. Afterwards, click the OK button to confirm.
3. Finally, click the 
button in the Navigation Pane to apply your changes. The named range will now point to your newly selected area, and all formulas referencing this name will be updated instantly.
Note: If you do not see the Navigation Pane after installing Kutools, you can enable it by clicking Kutools > Navigation from the menu bar.
With the Navigation Pane in Kutools for Excel, you can manage named ranges more efficiently. Besides resizing named ranges, you can create new names, manage auto texts for frequently used content, and quickly switch between workbooks and worksheets. This provides a more centralized and user-friendly way to keep track of data names and perform adjustments as your data changes.
Benefits: This method is ideal for users who prefer a graphical interface and need to manage a large collection of named ranges regularly. Kutools can greatly simplify workbook management, especially in complex projects.
Considerations: Kutools for Excel is a third-party add-in and needs to be installed separately. Make sure you are using a compatible version of Excel and that Kutools is properly installed and enabled to access these functions.
Kutools for Excel - Supercharge Excel with over 300 essential tools, making your work faster and easier, and take advantage of AI features for smarter data processing and productivity. Get It Now
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in