If I set up a column of items I want to sum up (say items in Column A, Rows 3-10) I’d like to avoid the problem that adding a row at the top defaults to that row not being automatically included in the sum. Is this possible, or do I have to always set my sum function up so that it adds at least one row above the top row?
This isn’t a problem when adding a row in the middle. The sum function automatically sums up to one more row at the bottom-- that would be Row 11 in my example. But if I select Row 3 in my example and add a row, then the new Row 3 is not automatically included in the sum. This seems like a not so good a feature, and I’m wondering if there is a setting I can change to fix this.
AFAIK, Excel ALWAYS copies row attributes from the immediately prior (above) row. I can’t think of a way around this except a macro that would insert the row just after your first SUMmed row, then move the first row contents to the new second row. Easy with VBA. But an inherent function or setting? Don’t think it can be done.
Good question. No easy answer that I know, only workarounds, but perhaps one will be new and helpful for you:
Option 1: Set the sum formula in your example to sum up to row 2. Not so good if you have years in that row like “2012”, but ok if it’s just titles, like “revenue”
Option 2: Insert the new rows in the middle of the table instead of the top, which I know is non-intuitive and not also ideal
Option 3: Have the data rows on a separate sheet or standalone column and the sum formula on another and sum the entire column =sum(data!C:C). Works well if you have lots of records and will be adding a lot, not so good if you want a simple table for a presentation
Best of luck, will be interested to see what experience other folks can share.
Try using “sum(A$3:A10)” to pin the top at the first row. I am not absolutely sure it will not modify the row number when you add one, but it might work. I think there is a setting somewhere (explore right-clicking on the sum cell) to control this behavior, but it might be sheet, file or even application global.
Got it. Maybe I wasn’t clear in the OP, but I use Option 1, generally. It’s just that once in awhile I forget to do so. Nothing I do prevents me from including one more row in the sum, although I did get burned one time because that row was formatted as text.
For You: Ineresting idea. I hadn’t thought of that. May try it next time, but it’s an extra step and probably easier to just include Row 2. I was looking for some fool-proof way by changing a default setting.
Slightly off topic… does anyone know WHY Excel was set up that way? Is there any reason why it’s better to have the inserted row default to not being part of the sum?
Just tried this, and, to my astonishment, when you insert a row, it changes the formula to A$4:A11. It keeps the dollar sign…but corrects the relative address anyway!
Because it is the default in most MS Office apps, Word and Excel especially, and most modern office apps, to use the immediately preceding element (paragraph, row, whatever) as the model for an inserted element.
In Word and most other word-manglers, hitting Return at the end of a paragraph starts a new paragraph with the same style or characteristics. If you move the cursor to the start of the next para with a different style applied, hitting return will create a preceding paragraph with the same characteristics.
I don’t think it’s anything specific about SUM or any other group function. I think it’s just how the program handles a newly inserted object.
To answer your question another way, suppose the default were to include the added row in the sum. Pretty soon someone would pop in here and ask how to make sure Excel didn’t do that. Every time they added a a row of labels and any of them were numerical, their sums got all messed up.
OldGuy’s got it. It’s reliable, sensible, consistent behavior… it’s just an irritation for you the way you are working with the sheet.
If you do this a lot, write a short VBA macro that does the insert-copy move I described above. You could even have it automatically search for the start of the SUM range and work from there.
Another thing: you could create a “ghost” row – hide it or reduce it to zero height – so that it lurks above the highest row in the data-portion of your chart. When you insert a new row at the “top” of the data area, it is actually just below this ghost row.
(I use “ghost columns” like this a lot to contain intermediate calculations. Instead of one big hairy gnarly formula, I break things up into sub-calculations in their own columns, and then hide all but the final result. This makes it slightly easier to edit a calculation chain. Emphasis on “slightly.”)
Not to get into GD territory here, but I don’t think the two scenarios are equivalent. I think it is MUCH more common to be adding a row that needs to be part of the sum than adding a label. The labels are usually already there. But often you have the line items ordered in a specific way, and you need to add a new 1st item, not just shove it anywhere in the column.
I often work with such a column of numbers. They are arranged in a specific order, and the bottom 4 rows are all sums, with different multipliers, of the entire row. If I need to add a new 1st item, then I have to change 4 formulas at the bottom. As I said, I usually make the sum go above the first row specifically to correct this problem. But it seems like an odd way for the default “insert row” to work.
Definitely not to GD this, but you’re missing the reasoning: by inserting a row at the top of your array, you’re copying the row ABOVE the array. Excel is assuming, as it always does under these conditions, that you want to duplicate the row above your insertion point. Having it duplicate the row below would be completely anomalous behavior.
You might try selecting the top row and using the specific “insert row above” command to see it that makes it duplicate the selected row instead.
Yes I know that’s what they do. My question is why.
Here’s my logic: In order to insert that row, I have to select a row with data in it. Since that row was selected, and I’m asking to insert a row, I’m wondering why Excel would assume that I want it to be like the row above it instead of the row I actually selected. If I wanted it to be like the row above, I would have selected the row above. But that doesn’t work, since that will result in an empty row and even that row does not keep the format of the row below.
I am not Microsoft, nor Bill Gates, nor have I ever written a line of code for Big Mick. But I have done enough app and UI design to understand that everything has to work some way, and a consistent result across functions and features is best. When you go to insert a row (or paragraph, in Word), you have to model the new element after something. Microsoft, and AFAIK most other app makers, have chosen to use the immediately preceding element as the model, and maintain this function consistently across their slate of apps.
Sometimes, in Word or most publication programs for example, you can define what follows a paragraph style: that is, Heading 1 will product a Normal paragraph when you hit return; you can modify that to do anything from “default” (which will produce an identical style) to any other defined style. Very useful when writing flowing documents without wasting time styling every paragraph.
It’s similar in Excel: you have to model the new line after something, and it’s been judged more useful and consistent to model it after the immediately preceding element rather than a default generic one, or - what you want - the following line, which would be contrary to MS and conventional program design. There is no real “why” to it except a UI choice made long ago and now nearly universal. To have Excel “be smart” and guess that you want a top-inserted row to join lower functions would be much more confusing than otherwise. IMVHO.
My bad. I was thinking of the insert function, from right-click in a cell, that allows you to insert a whole line or just cells. I confabulated that with the Word command that allows you insert cells in a table above or below, or left or right.
Well at least the sum function ignores text. As I said before, you can include the header if it’s a number by forcing it into text. Now you can insert all you want and it will stay within the range of the sum function and add correctly.
now something you can do if you absolutely need a date or other math type number in the header is to hide the row below it after you include it in the sum function. You’re now inserting within the function. Keep the hidden cell empty and locked out so nothing weird can be entered into it.
yet another trick is to copy the header and paste it into a text box as an image. You then hide the header and allow the floating text box to take up the 1st cell of the sum formula. I’ve used the floating text image for data that is referenced in another sheet that I don’t want tampered with but want the info to display. It’s a great way to add extra data to something like a chart that will update as it is changed but isn’t a function of the chart itself.