How to insert or display named range on another sheet?
In some cases, you may want to insert or display a named range data in another sheet, but by default, when you type the formula =name(the range name), and press Enter key, it only inserts the first data of the named range as below screenshot shown. In this article, I introduce the way to quickly insert the whole data of the named range into another sheet.
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
To display a named range on another sheet, you just need to use the formula =name (the range name) and press Shift + Ctrl + Enter keys together.
Select a range which you will place the named range, and type the formula =name, the name is the named range you want to display, press Shift + Ctrl + Enter keys. See screenshot:
Navigation--AutoText (add usually used range cells to AutoText pane, one click to insert it to any sheets if you need.)
You are guest
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 19 days agoYou need to select a larger area before you enter the formula and hit the key combination. If you only select one cell, it will only show one cell.
- To post as a guest, your comment is unpublished.· 2 months agohi, when i click, shift+cntrl + enter i still only get the 1st row any ideas? note i have UK settings (semi colon delimiter, etc) thanks a million, this one is killin me
- To post as a guest, your comment is unpublished.· 18 days agoSelect a large range which can place all the values of the name, if you juse select single cell to place, only the first one shown.
- To post as a guest, your comment is unpublished.· 2 months agohello, when i try to display the range with cntrl shift enter i only get the 1st row, any ideas? THANKS