Nov 20 2022 05:53 AM
I have created a sample table for maintaining a students records. Table name is "First_Semister_Result". Table has 3 headings "Students", "Subjects" and "Analysis". Under "Subjects" heading, exam marks are entered for 5 subjects and total marks of those 5 subjects are calculated under "Total" column using SUM function.
Now I have inserted new column After "Maths" subject. I have 6 subjects as below.
SUM function is doing the correct calculations for all 6 subjects but in the formula bar, it still shows range as =SUM(First_Semister_Result[@[Column3]:[Column7]])
As I have inserted a new column, SUM function should ideally show the range as
=SUM(First_Semister_Result[@[Column3]:[Column8]])
Please advise me on the above issue.
Nov 20 2022 06:35 AM - edited Nov 20 2022 06:42 AM
Solution@Vinit0412 You are abusing structured tables a bit here. :):)
The yellow header row is definitely not part of the table, and you chose to not display the real column names of the table. Your table probably looks something like this when you show the header row.
In this example I started with columns 1 to 8 and then inserted a column between "Column4" and "Column5" and Excel automatically called it "Column42". The formula in the Total column (i.e. "Column8") is still summing from the first column in the range "Column3" to "Column7" and it included "Column42". Exactly as it should.
In other words, inserting a column in a structured column does not increment the column numbers towards the right of the inserted column as they merely are texts ending with a numerical character. If you want to show the formula as the sum of Column3 to Column8 You need to rename the columns.
Nov 21 2022 01:16 AM
Nov 20 2022 06:35 AM - edited Nov 20 2022 06:42 AM
Solution@Vinit0412 You are abusing structured tables a bit here. :):)
The yellow header row is definitely not part of the table, and you chose to not display the real column names of the table. Your table probably looks something like this when you show the header row.
In this example I started with columns 1 to 8 and then inserted a column between "Column4" and "Column5" and Excel automatically called it "Column42". The formula in the Total column (i.e. "Column8") is still summing from the first column in the range "Column3" to "Column7" and it included "Column42". Exactly as it should.
In other words, inserting a column in a structured column does not increment the column numbers towards the right of the inserted column as they merely are texts ending with a numerical character. If you want to show the formula as the sum of Column3 to Column8 You need to rename the columns.