3

I'd like to know if there's a way to make excel show me where are the boundaries of an array, like the highlight I made in the picture below.

boundaries highlight

Some explanation: I have a worksheet that is full of arrays everywhere, and sometimes I have to insert a line (or column) in the original table, that should be reflected in the array and all formulas that depend on it. After some experimenting, I figured out the best way to keep consistency is to use the insert line/column, because it automatically shifts all formulas. But to insert a line in the middle of the array, I first have to "destroy" it, then insert the line, and then rebuild it. Having excel show me the highlights would really make this job easier.

PS: TRANSPOR = TRANSPOSE

GabrielF
  • 143
  • 5

2 Answers2

2

Also, you can choose all the array pressing Ctrl+/ (Ctrl-slash) when selecting just any cell of the array.

John_West
  • 330
  • 3
  • 12
  • there! Much better solution. Only drawback for me is that it only works with the numpad slash. Still better than [F2]+CSE. – GabrielF Feb 22 '16 at 17:08
  • I've found it 2 minutes before posting it :-[ And I like it :) It also works with slash from `/ ?` key on my PC with Excel 2010. Did you try this key? (Convince you use English layout - if you're not from English-speaking countries) – John_West Feb 22 '16 at 19:03
1

Ok, I figured it out. If I edit any cell in the array ([F2]) and then I confirm it with CSE (Ctrl+Shift+Enter), Excel automatically selects the entire array.

GabrielF
  • 143
  • 5
  • I verified it on a column in Excel 2010, it seems not not work as expected. If you select any cell in the column array, `F2`, `Ctrl-Shift-Enter`, only the part from the top of array to that cell is selected. – John_West Feb 22 '16 at 19:01