I have Excel 2007. I seem to have accidentally inserted Subtotals (subtotal, 9) formulas into my spreadsheet. I need to know how to delete them. I can't copy formulas from one column to the next. It looks like these subtotals are in hidden rows??? I'm a novice.
Should I upload the file??
Thanks for using JustAnswer. My name isXXXXX will do whatever I can to answer your question
please upload the file to
after uploading they will give you a file id#
paste that number here
and I will see if I can fix it for you
i have the file open
I'm 64 and am new to this chat format so bear with me.
can you tell me which tab
this formula is in
and which cell
Well, when I try to look at some totals, like in GD258. It shows an error. When I try to just get a sum there, it automatically gives me the subtotal 9 format. Also - there are some hidden rows in this spreadsheet and I can't unhide them. I think that's part of the problem.
first of all
what is happening is when you apply filters
rows will disapear
if you have a formula in rows
those will disapear
now are you looking to get a sum of that column
on all rows
or only the rows you filter?
I want the sum (in several columns actually) on all rows. I don't want any formulas in any hidden rows.
i can do that
i have all of your rows showing again
i had to reset your filters to do that
Excellent! I realize I may have to reformat some stuff but right now I want the subtotals out of there.
How do you reset filters?
what version of excel do you have?
2007 I think
you would click on the menu on the top
where it says Home
it says home insert page layout
you want to click the one that says home
then from there on the ribbon
that is the thing underneath the menu
with all the fancy icons
all the way on the right hand of the ribbon
you will see sort and filter
if you click that
then a menu pops up
and you click clear
that will unhide all of your rows
now before I set up your sums
are you going to ever want to filter the data at some point
in that case
i would put the sum at the top of the column
under the heading
there is no rule that says the sum has to be at the end
this way you would avoid
filtering the row that has the formula in it
But I should be able to use the filters just for sorting and not for summing anything. Then the subtotal formulas won't appear, right?
well what was happening was i think you were filtering rows that had data in them
those same rows also had subtotal formulas in them
so if I am folowing you correctly
you do not want subtotal formulas
or you do?
no - I don't want any subtotal formulas
as of now
I cannot find any subtotal formulas in the sheet
I see a few sum
and sum if formulas
but I assume you want those
yes - i want those. So why were rows hidden?
that was do to filtering
if you filtered one of your $value columns
to only show cells with a certain amount
or greater than a certain amount
all other rows would be hidden
until you reset the filters
Hmm. I always just reset the filters by checking the "select all" and then all my rows appeared. i never used the commands you mentioned above.
your meathod works too
however you would have to do that for each column
the commands I gave you are global
in other words does all of them at once
Can I now copy columns that have my If then statements and can I also sum columns without getting the subtotal 9 format?
Can I download the file you fixed?
as far as your if statements
you have to be careful whether you are using relative or absolute addressing
you can use a strait
instead of =subtotal(a1:a50,9)
essentially those 2 formulas do exactly the same thing
I didn't think I had intentionally used any =subtotal formulas. Do you see any?
none on the input sheet
i did not look at any of the other sheets
They would have been on the input sheet.
i will teach you another trick if you like
if you see the find and select icon next to the filter sort icon I told you about earlier
and then click find
in the find what box
type what you want to look for
in this case the word
then click the options button
under look in
and that will tell you if you have any of that formula on the sheet
and also tell you which cells they are in
can you wait while I try that quickly? It's a long string of directions so i want to try it now
i have to upload the other file anyway
ok you can click this link to download
http://wikisend.com/download/889892/Copy of Electric Only C&I Batch Tool - 2014-2017 Planv1 - Ja - fix.xlsx
I am around if you have additional questions later
and you can request me specifically by name
put "The PC Guy"
in your question tital
or goto my profile and ask in my box
I went to Home and then "data" and that's where the filter icons were. I just hit clear and all the rows appeared. So maybe I don't need the file you fixed. But how do I get it downloaded?
you wont need the file
because that is all i did
Aha. Well - thanks a bunch and thanks for offering to answer any follow-up questions I might have on this issue as I try to work with the file. Thanks again
anyway, PLEASE RATE MY SERVICE WHEN YOU ARE READY