How to optimise cell range selection in Excel?
I have a range of data in a column (500 data points) and I need to analyse them in a table, 250 points at a time. As such, the first time it is A1:A250, and then A2:251, etc. So I have to do this 250 times. Any tips for optimising the retrieving of the data in column A, to then placing it in a new table containing the 250 units.
If I was coding I would just say =A(N+1):A(N+250) where N is the number of times the process has been done previously, but there doesn't seem to be a good way to do it in Excel.
Any thoughts?
excel
asked Nov 14 '18 at 23:24
NerrohhNerrohh
1
add a comment |
I have a range of data in a column (500 data points) and I need to analyse them in a table, 250 points at a time. As such, the first time it is A1:A250, and then A2:251, etc. So I have to do this 250 times. Any tips for optimising the retrieving of the data in column A, to then placing it in a new table containing the 250 units.
If I was coding I would just say =A(N+1):A(N+250) where N is the number of times the process has been done previously, but there doesn't seem to be a good way to do it in Excel.
Any thoughts?
excel
asked Nov 14 '18 at 23:24
NerrohhNerrohh
1
1
Have you tried using VBA? Because this is easily doable.
– BigBen
Nov 14 '18 at 23:26
add a comment |
I have a range of data in a column (500 data points) and I need to analyse them in a table, 250 points at a time. As such, the first time it is A1:A250, and then A2:251, etc. So I have to do this 250 times. Any tips for optimising the retrieving of the data in column A, to then placing it in a new table containing the 250 units.
If I was coding I would just say =A(N+1):A(N+250) where N is the number of times the process has been done previously, but there doesn't seem to be a good way to do it in Excel.
Any thoughts?
excel
asked Nov 14 '18 at 23:24
NerrohhNerrohh
1
I have a range of data in a column (500 data points) and I need to analyse them in a table, 250 points at a time. As such, the first time it is A1:A250, and then A2:251, etc. So I have to do this 250 times. Any tips for optimising the retrieving of the data in column A, to then placing it in a new table containing the 250 units.
If I was coding I would just say =A(N+1):A(N+250) where N is the number of times the process has been done previously, but there doesn't seem to be a good way to do it in Excel.
Any thoughts?
excel
excel
asked Nov 14 '18 at 23:24
NerrohhNerrohh
1
asked Nov 14 '18 at 23:24
NerrohhNerrohh
1
asked Nov 14 '18 at 23:24
NerrohhNerrohh
1
asked Nov 14 '18 at 23:24
NerrohhNerrohh
1
asked Nov 14 '18 at 23:24
NerrohhNerrohh
1
1
1
Have you tried using VBA? Because this is easily doable.
– BigBen
Nov 14 '18 at 23:26
add a comment |
1
Have you tried using VBA? Because this is easily doable.
– BigBen
Nov 14 '18 at 23:26
1
1
Have you tried using VBA? Because this is easily doable.
– BigBen
Nov 14 '18 at 23:26
Have you tried using VBA? Because this is easily doable.
– BigBen
Nov 14 '18 at 23:26
add a comment |
2 Answers
2
active
oldest
votes
It can be done in a number of ways. The simplest one, I think, is to use formulas.
Range E2 is named offset, by changing its value you can show values offset by that number. There are other ways of doing it as well. HTH.
answered Nov 15 '18 at 3:06
Michal RosaMichal Rosa
1,3221814
add a comment |
It may be about as easy to create all 251 sets at the one time, say with (in B1 copied across to IR1 and then B1:IR1 copied down to B250):
=INDEX(OFFSET($A1,COLUMN()-2,,250,1),1,1)
That is quite a lot of calculation when involving a volatile formula so having created all the sets you might want to Copy/Paste Special/Values over the top before any further processing.
answered Nov 15 '18 at 4:47
pnutspnuts
48.7k76299
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53310271%2fhow-to-optimise-cell-range-selection-in-excel%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
It can be done in a number of ways. The simplest one, I think, is to use formulas.
Range E2 is named offset, by changing its value you can show values offset by that number. There are other ways of doing it as well. HTH.
answered Nov 15 '18 at 3:06
Michal RosaMichal Rosa
1,3221814
add a comment |
It can be done in a number of ways. The simplest one, I think, is to use formulas.
Range E2 is named offset, by changing its value you can show values offset by that number. There are other ways of doing it as well. HTH.
answered Nov 15 '18 at 3:06
Michal RosaMichal Rosa
1,3221814
add a comment |
It can be done in a number of ways. The simplest one, I think, is to use formulas.
Range E2 is named offset, by changing its value you can show values offset by that number. There are other ways of doing it as well. HTH.
answered Nov 15 '18 at 3:06
Michal RosaMichal Rosa
1,3221814
It can be done in a number of ways. The simplest one, I think, is to use formulas.
Range E2 is named offset, by changing its value you can show values offset by that number. There are other ways of doing it as well. HTH.
answered Nov 15 '18 at 3:06
Michal RosaMichal Rosa
1,3221814
answered Nov 15 '18 at 3:06
Michal RosaMichal Rosa
1,3221814
answered Nov 15 '18 at 3:06
Michal RosaMichal Rosa
1,3221814
answered Nov 15 '18 at 3:06
Michal RosaMichal Rosa
1,3221814
1,3221814
add a comment |
add a comment |
It may be about as easy to create all 251 sets at the one time, say with (in B1 copied across to IR1 and then B1:IR1 copied down to B250):
=INDEX(OFFSET($A1,COLUMN()-2,,250,1),1,1)
That is quite a lot of calculation when involving a volatile formula so having created all the sets you might want to Copy/Paste Special/Values over the top before any further processing.
answered Nov 15 '18 at 4:47
pnutspnuts
48.7k76299
add a comment |
It may be about as easy to create all 251 sets at the one time, say with (in B1 copied across to IR1 and then B1:IR1 copied down to B250):
=INDEX(OFFSET($A1,COLUMN()-2,,250,1),1,1)
That is quite a lot of calculation when involving a volatile formula so having created all the sets you might want to Copy/Paste Special/Values over the top before any further processing.
answered Nov 15 '18 at 4:47
pnutspnuts
48.7k76299
add a comment |
It may be about as easy to create all 251 sets at the one time, say with (in B1 copied across to IR1 and then B1:IR1 copied down to B250):
=INDEX(OFFSET($A1,COLUMN()-2,,250,1),1,1)
That is quite a lot of calculation when involving a volatile formula so having created all the sets you might want to Copy/Paste Special/Values over the top before any further processing.
answered Nov 15 '18 at 4:47
pnutspnuts
48.7k76299
It may be about as easy to create all 251 sets at the one time, say with (in B1 copied across to IR1 and then B1:IR1 copied down to B250):
=INDEX(OFFSET($A1,COLUMN()-2,,250,1),1,1)
That is quite a lot of calculation when involving a volatile formula so having created all the sets you might want to Copy/Paste Special/Values over the top before any further processing.
answered Nov 15 '18 at 4:47
pnutspnuts
48.7k76299
answered Nov 15 '18 at 4:47
pnutspnuts
48.7k76299
answered Nov 15 '18 at 4:47
pnutspnuts
48.7k76299
answered Nov 15 '18 at 4:47
pnutspnuts
48.7k76299
48.7k76299
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53310271%2fhow-to-optimise-cell-range-selection-in-excel%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
Have you tried using VBA? Because this is easily doable.
– BigBen
Nov 14 '18 at 23:26