How to Insert or Display Named Range on Another Sheet in Excel

Displaying a named range from one sheet onto another can streamline your workflow and make your spreadsheets more efficient. Here's how to do it:

Step 1: Define Your Named Range

First, select the range of cells you want to name. Then go to the Formulas tab and select Define Name in the Defined Names group. Enter a name for your range and click OK.

Step 2: Switch to the Target Sheet

Next, navigate to the sheet where you want to display the named range.

Step 3: Use the INDIRECT Function

In the target cell, use the INDIRECT function to reference the named range from the other sheet. The formula will look like this: =INDIRECT("Sheet1!NamedRange"). Replace "Sheet1" with the name of the sheet where your named range is located, and replace "NamedRange" with the name you defined for your range.

Step 4: Drag the Fill Handle

If your named range consists of multiple cells, click on the cell where you just entered the formula and drag the fill handle to copy the formula to adjacent cells. The fill handle is the small square at the bottom-right corner of the cell.

Step 5: Verify the Results

Make sure that your named range is correctly displayed in the target sheet.