18 Jun 2018

Do you ever hold down the Ctrl key to select multiple non adjacent cells or ranges? I’ve found a way to select multiple non adjacent ranges (any sheet) by pasting a formula into Excel’s Name Box!

 

Exploring Excel

There are other possible solutions (i.e. vba, hyperlinks?) but I enjoyed exploring this idea in Excel.

 

How It’s Useful

Holding down the Ctrl key while selecting cells or ranges is tedious. My solution allows you to select multiple non adjacent ranges (any sheet) based on your criteria. 

 

How It Works

Any valid cell or range reference can be pasted into the Name Box. Examples:

  • ‘StateData(4)’!D8
  • ‘StateData(1)’!F15:M13
  • ‘StateData(8)’!D16:J12, ‘StateData(8)’!F6:I2, ‘StateData(8)’!H21:K19

It can be text like above or formulas that dynamically create these references! The formula below creates a reference to this:  ‘StateData(1)’!F15:M13

 

 

 

 

Download My Excel File

To really understand how it works download my Excel file and audit the formulas!

 

Homage To The Square

In March I visited Guggenheim museum in Manhattan. Some of the art was beautiful. Then there was Homage To The Square exhibit by Josef Albers. Paintings of squares!?

Maybe I should’ve called this post “Homage To Multiple Non Adjacent Cells And Ranges!“.

More about Homage To The Square: Guggenheim, KhanAcademy, Albersfoundation.

About me? I’ve been a Data Analyst since 2001.

I love Microsoft Excel 🙂

I live in Markham Ontario and work in Toronto.

Visit my blogmy recommended Excel training.

Post a comment