Clever Clogs tip # 713
31 August 2022


Insert a list of named ranges (Microsoft Excel) *

I know, I know ... no tip last week, thanks for noticing; I thought I'd give it a miss because of the Bank Holiday Weekend. So a week late (or a day early, as I typically send the tips on a Thursday) ... tip 713. Named ranges in Excel.

Do any of you define names in Excel? If you don't know what I'm talking about, you probably don't ;)

I personally use them for four reasons:

  1. Save time jumping to a cell or range of cells in a large workbook
  2. Avoid the pitfalls of relative cell references (named ranges are absolute)
  3. Make formulas easier to understand
  4. Make a sheet containing useful links

But thanks to Meni Porat, one of the people I follow on LinkedIn, I now have a fifth reason ... to quickly paste a list of all named ranges in a separate sheet.

Here's how:

  1. Select the cell where you want to paste the list.
  2. Press F3. (Or use Formulas > Use in Formula > Paste Names if you don't like keyboard shortcuts.)
  3. Click Paste List.

You will now have two columns, one with the range name and one showing the relevant sheet name and cell references.

They're not linked so you cannot use the list to jump to the named = range. If that's what you after you have to work with hyperlinks. (CTRL + K, anyone?)


Related tips

  1. Speed up navigation in a large workbook using named ranges: tip_595.php
  2. Enable the F1 - F12 keys on a laptop: tip_710.php

Last and this week's CleverClogsTipTime on LinkedIn

  1. Do you use any of these select tricks in MS Word?
  2. Set hyperlink colours to match your branding

K.

PS Thank you for your continued support and enthusiasm, which motivates me to keep sharing - and to keep learning. Every day is a school day!


* Unless stated otherwise this tip is written for Microsoft 365 desktop apps and Windows 10 users, but might also be useful in Office 2010, 2013 and 2016.