How can I expand Excel datasets that have formulas attached?
I'm trying to work with some relatively simple datasets in Excel, but I realized that I don't know how to expand them properly.
See the image below for reference:
If I wanted to add a USER E or CAT. 5 to this set, what's the easiest way to do so while keeping all the formulas in tact (and possibly adding the formula for the new row/column automatically)?
microsoft-excel
edited Jan 19 at 12:39
Toto
3,888101226
asked Jan 19 at 12:37
KonaKona
83
add a comment |
I'm trying to work with some relatively simple datasets in Excel, but I realized that I don't know how to expand them properly.
See the image below for reference:
If I wanted to add a USER E or CAT. 5 to this set, what's the easiest way to do so while keeping all the formulas in tact (and possibly adding the formula for the new row/column automatically)?
microsoft-excel
edited Jan 19 at 12:39
Toto
3,888101226
asked Jan 19 at 12:37
KonaKona
83
Hi Kona, where is the dataset coming from?
– wizlog
Jan 22 at 17:15
add a comment |
I'm trying to work with some relatively simple datasets in Excel, but I realized that I don't know how to expand them properly.
See the image below for reference:
If I wanted to add a USER E or CAT. 5 to this set, what's the easiest way to do so while keeping all the formulas in tact (and possibly adding the formula for the new row/column automatically)?
microsoft-excel
edited Jan 19 at 12:39
Toto
3,888101226
asked Jan 19 at 12:37
KonaKona
83
I'm trying to work with some relatively simple datasets in Excel, but I realized that I don't know how to expand them properly.
See the image below for reference:
If I wanted to add a USER E or CAT. 5 to this set, what's the easiest way to do so while keeping all the formulas in tact (and possibly adding the formula for the new row/column automatically)?
microsoft-excel
microsoft-excel
edited Jan 19 at 12:39
Toto
3,888101226
asked Jan 19 at 12:37
KonaKona
83
edited Jan 19 at 12:39
Toto
3,888101226
asked Jan 19 at 12:37
KonaKona
83
edited Jan 19 at 12:39
Toto
3,888101226
edited Jan 19 at 12:39
Toto
3,888101226
edited Jan 19 at 12:39
Toto
3,888101226
3,888101226
asked Jan 19 at 12:37
KonaKona
83
asked Jan 19 at 12:37
KonaKona
83
asked Jan 19 at 12:37
KonaKona
83
83
Hi Kona, where is the dataset coming from?
– wizlog
Jan 22 at 17:15
add a comment |
Hi Kona, where is the dataset coming from?
– wizlog
Jan 22 at 17:15
Hi Kona, where is the dataset coming from?
– wizlog
Jan 22 at 17:15
Hi Kona, where is the dataset coming from?
– wizlog
Jan 22 at 17:15
add a comment |
2 Answers
2
active
oldest
votes
If you insert a column between D and E, the formulas will not change. They will still reference the columns you referenced before and you will need to update them manually to include the new user E.
If you want the formulas to automatically include data in new columns, the techniques to do that will vary, depending on what sort of references you use in the formulas.
For example, if the formulas in E2 and F2 are, respectively
=sum(B2:D2)
=average(B2:D2)
Insert a new column E and the formulas will have to be manually adjusted to =sum(B2:32).
Or, you could change the formulas to
=sum(B2:index(B:B,column()-1))
=average(B2:index(B:B,column()-1))
In words: Sum everything from B2 to the cell immediately left of the current column and average everything from B2 to two cells left of the current column.
If you have trouble applying that to your scenario, pipe up.
answered Jan 19 at 20:13
teylynteylyn
17.4k22539
It took a little bit of finessing (since a row variable seems to be required for the index function) but this worked great for my data set; thanks!
– Kona
Jan 21 at 1:46
Index can be used with just two parameters. If the first parameter is a reference to a range in a single column, then the second parameter will be interpreted as the row number. If the first parameter is a reference to a range in a single row, then the second parameter will be interpreted as the column number. If the first parameter is a multi-row and multi-column range, then you need a row AND a column parameter.
– teylyn
Jan 21 at 2:58
add a comment |
To insert a column while conserving all properties of columns to the right:
- Select the column heading to the right of where you want the new column to appear. For example, if you want to insert a new column between columns D and E, select column E.
- Click the Insert command on the Home tab
- The new column will appear to the left of the selected column.
When inserting rows and columns, make sure you select the entire row or column by clicking the heading. If you select only a cell in the row or column, the Insert command will only insert a new cell.
Source:
Excel 2013 - Modifying Columns, Rows, and Cells.
answered Jan 19 at 12:59
harrymcharrymc
261k14271577
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
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%2fsuperuser.com%2fquestions%2f1396061%2fhow-can-i-expand-excel-datasets-that-have-formulas-attached%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
If you insert a column between D and E, the formulas will not change. They will still reference the columns you referenced before and you will need to update them manually to include the new user E.
If you want the formulas to automatically include data in new columns, the techniques to do that will vary, depending on what sort of references you use in the formulas.
For example, if the formulas in E2 and F2 are, respectively
=sum(B2:D2)
=average(B2:D2)
Insert a new column E and the formulas will have to be manually adjusted to =sum(B2:32).
Or, you could change the formulas to
=sum(B2:index(B:B,column()-1))
=average(B2:index(B:B,column()-1))
In words: Sum everything from B2 to the cell immediately left of the current column and average everything from B2 to two cells left of the current column.
If you have trouble applying that to your scenario, pipe up.
answered Jan 19 at 20:13
teylynteylyn
17.4k22539
It took a little bit of finessing (since a row variable seems to be required for the index function) but this worked great for my data set; thanks!
– Kona
Jan 21 at 1:46
Index can be used with just two parameters. If the first parameter is a reference to a range in a single column, then the second parameter will be interpreted as the row number. If the first parameter is a reference to a range in a single row, then the second parameter will be interpreted as the column number. If the first parameter is a multi-row and multi-column range, then you need a row AND a column parameter.
– teylyn
Jan 21 at 2:58
add a comment |
If you insert a column between D and E, the formulas will not change. They will still reference the columns you referenced before and you will need to update them manually to include the new user E.
If you want the formulas to automatically include data in new columns, the techniques to do that will vary, depending on what sort of references you use in the formulas.
For example, if the formulas in E2 and F2 are, respectively
=sum(B2:D2)
=average(B2:D2)
Insert a new column E and the formulas will have to be manually adjusted to =sum(B2:32).
Or, you could change the formulas to
=sum(B2:index(B:B,column()-1))
=average(B2:index(B:B,column()-1))
In words: Sum everything from B2 to the cell immediately left of the current column and average everything from B2 to two cells left of the current column.
If you have trouble applying that to your scenario, pipe up.
answered Jan 19 at 20:13
teylynteylyn
17.4k22539
It took a little bit of finessing (since a row variable seems to be required for the index function) but this worked great for my data set; thanks!
– Kona
Jan 21 at 1:46
Index can be used with just two parameters. If the first parameter is a reference to a range in a single column, then the second parameter will be interpreted as the row number. If the first parameter is a reference to a range in a single row, then the second parameter will be interpreted as the column number. If the first parameter is a multi-row and multi-column range, then you need a row AND a column parameter.
– teylyn
Jan 21 at 2:58
add a comment |
If you insert a column between D and E, the formulas will not change. They will still reference the columns you referenced before and you will need to update them manually to include the new user E.
If you want the formulas to automatically include data in new columns, the techniques to do that will vary, depending on what sort of references you use in the formulas.
For example, if the formulas in E2 and F2 are, respectively
=sum(B2:D2)
=average(B2:D2)
Insert a new column E and the formulas will have to be manually adjusted to =sum(B2:32).
Or, you could change the formulas to
=sum(B2:index(B:B,column()-1))
=average(B2:index(B:B,column()-1))
In words: Sum everything from B2 to the cell immediately left of the current column and average everything from B2 to two cells left of the current column.
If you have trouble applying that to your scenario, pipe up.
answered Jan 19 at 20:13
teylynteylyn
17.4k22539
If you insert a column between D and E, the formulas will not change. They will still reference the columns you referenced before and you will need to update them manually to include the new user E.
If you want the formulas to automatically include data in new columns, the techniques to do that will vary, depending on what sort of references you use in the formulas.
For example, if the formulas in E2 and F2 are, respectively
=sum(B2:D2)
=average(B2:D2)
Insert a new column E and the formulas will have to be manually adjusted to =sum(B2:32).
Or, you could change the formulas to
=sum(B2:index(B:B,column()-1))
=average(B2:index(B:B,column()-1))
In words: Sum everything from B2 to the cell immediately left of the current column and average everything from B2 to two cells left of the current column.
If you have trouble applying that to your scenario, pipe up.
answered Jan 19 at 20:13
teylynteylyn
17.4k22539
answered Jan 19 at 20:13
teylynteylyn
17.4k22539
answered Jan 19 at 20:13
teylynteylyn
17.4k22539
answered Jan 19 at 20:13
teylynteylyn
17.4k22539
17.4k22539
It took a little bit of finessing (since a row variable seems to be required for the index function) but this worked great for my data set; thanks!
– Kona
Jan 21 at 1:46
Index can be used with just two parameters. If the first parameter is a reference to a range in a single column, then the second parameter will be interpreted as the row number. If the first parameter is a reference to a range in a single row, then the second parameter will be interpreted as the column number. If the first parameter is a multi-row and multi-column range, then you need a row AND a column parameter.
– teylyn
Jan 21 at 2:58
add a comment |
It took a little bit of finessing (since a row variable seems to be required for the index function) but this worked great for my data set; thanks!
– Kona
Jan 21 at 1:46
Index can be used with just two parameters. If the first parameter is a reference to a range in a single column, then the second parameter will be interpreted as the row number. If the first parameter is a reference to a range in a single row, then the second parameter will be interpreted as the column number. If the first parameter is a multi-row and multi-column range, then you need a row AND a column parameter.
– teylyn
Jan 21 at 2:58
It took a little bit of finessing (since a row variable seems to be required for the index function) but this worked great for my data set; thanks!
– Kona
Jan 21 at 1:46
It took a little bit of finessing (since a row variable seems to be required for the index function) but this worked great for my data set; thanks!
– Kona
Jan 21 at 1:46
Index can be used with just two parameters. If the first parameter is a reference to a range in a single column, then the second parameter will be interpreted as the row number. If the first parameter is a reference to a range in a single row, then the second parameter will be interpreted as the column number. If the first parameter is a multi-row and multi-column range, then you need a row AND a column parameter.
– teylyn
Jan 21 at 2:58
Index can be used with just two parameters. If the first parameter is a reference to a range in a single column, then the second parameter will be interpreted as the row number. If the first parameter is a reference to a range in a single row, then the second parameter will be interpreted as the column number. If the first parameter is a multi-row and multi-column range, then you need a row AND a column parameter.
– teylyn
Jan 21 at 2:58
add a comment |
To insert a column while conserving all properties of columns to the right:
- Select the column heading to the right of where you want the new column to appear. For example, if you want to insert a new column between columns D and E, select column E.
- Click the Insert command on the Home tab
- The new column will appear to the left of the selected column.
When inserting rows and columns, make sure you select the entire row or column by clicking the heading. If you select only a cell in the row or column, the Insert command will only insert a new cell.
Source:
Excel 2013 - Modifying Columns, Rows, and Cells.
answered Jan 19 at 12:59
harrymcharrymc
261k14271577
add a comment |
To insert a column while conserving all properties of columns to the right:
- Select the column heading to the right of where you want the new column to appear. For example, if you want to insert a new column between columns D and E, select column E.
- Click the Insert command on the Home tab
- The new column will appear to the left of the selected column.
When inserting rows and columns, make sure you select the entire row or column by clicking the heading. If you select only a cell in the row or column, the Insert command will only insert a new cell.
Source:
Excel 2013 - Modifying Columns, Rows, and Cells.
answered Jan 19 at 12:59
harrymcharrymc
261k14271577
add a comment |
To insert a column while conserving all properties of columns to the right:
- Select the column heading to the right of where you want the new column to appear. For example, if you want to insert a new column between columns D and E, select column E.
- Click the Insert command on the Home tab
- The new column will appear to the left of the selected column.
When inserting rows and columns, make sure you select the entire row or column by clicking the heading. If you select only a cell in the row or column, the Insert command will only insert a new cell.
Source:
Excel 2013 - Modifying Columns, Rows, and Cells.
answered Jan 19 at 12:59
harrymcharrymc
261k14271577
To insert a column while conserving all properties of columns to the right:
- Select the column heading to the right of where you want the new column to appear. For example, if you want to insert a new column between columns D and E, select column E.
- Click the Insert command on the Home tab
- The new column will appear to the left of the selected column.
When inserting rows and columns, make sure you select the entire row or column by clicking the heading. If you select only a cell in the row or column, the Insert command will only insert a new cell.
Source:
Excel 2013 - Modifying Columns, Rows, and Cells.
answered Jan 19 at 12:59
harrymcharrymc
261k14271577
answered Jan 19 at 12:59
harrymcharrymc
261k14271577
answered Jan 19 at 12:59
harrymcharrymc
261k14271577
answered Jan 19 at 12:59
harrymcharrymc
261k14271577
261k14271577
add a comment |
add a comment |
Thanks for contributing an answer to Super User!
- 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%2fsuperuser.com%2fquestions%2f1396061%2fhow-can-i-expand-excel-datasets-that-have-formulas-attached%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
Hi Kona, where is the dataset coming from?
– wizlog
Jan 22 at 17:15