How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask The-PC-Guy Your Own Question

The-PC-Guy
The-PC-Guy, Computer Manager
Category: Microsoft Office
Satisfied Customers: 1844
Experience:  20 years experience providing remote computer support
62934938
Type Your Microsoft Office Question Here...
The-PC-Guy is online now
A new question is answered every 9 seconds

Delete Subtotals

This answer was rated:

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??


The-PC-Guy :

Thanks for using JustAnswer. My name isXXXXX will do whatever I can to answer your question

The-PC-Guy :

please upload the file to

The-PC-Guy :

www.wikisend.com

The-PC-Guy :

after uploading they will give you a file id#

The-PC-Guy :

paste that number here

The-PC-Guy :

and I will see if I can fix it for you

Customer:

926364

The-PC-Guy :

ok

The-PC-Guy :

i have the file open

Customer:

I'm 64 and am new to this chat format so bear with me.

The-PC-Guy :

ok

The-PC-Guy :

can you tell me which tab

The-PC-Guy :

this formula is in

The-PC-Guy :

and which cell

Customer:

Input

Customer:

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.

The-PC-Guy :

ok

The-PC-Guy :

first of all

The-PC-Guy :

what is happening is when you apply filters

The-PC-Guy :

rows will disapear

The-PC-Guy :

if you have a formula in rows

The-PC-Guy :

those will disapear

The-PC-Guy :

now are you looking to get a sum of that column

The-PC-Guy :

on all rows

The-PC-Guy :

or only the rows you filter?

Customer:

I want the sum (in several columns actually) on all rows. I don't want any formulas in any hidden rows.

The-PC-Guy :

ok

The-PC-Guy :

i can do that

The-PC-Guy :

first of all

The-PC-Guy :

i have all of your rows showing again

The-PC-Guy :

i had to reset your filters to do that

Customer:

Excellent! I realize I may have to reformat some stuff but right now I want the subtotals out of there.

Customer:

How do you reset filters?

The-PC-Guy :

ok

The-PC-Guy :

what version of excel do you have?

Customer:

2007 I think

The-PC-Guy :

oh 2007?

Customer:

yes

The-PC-Guy :

ok

The-PC-Guy :

for 2007

The-PC-Guy :

you would click on the menu on the top

The-PC-Guy :

where it says Home

The-PC-Guy :

it says home insert page layout

The-PC-Guy :

ect...

The-PC-Guy :

you want to click the one that says home

The-PC-Guy :

then from there on the ribbon

The-PC-Guy :

that is the thing underneath the menu

The-PC-Guy :

with all the fancy icons

The-PC-Guy :

all the way on the right hand of the ribbon

The-PC-Guy :

you will see sort and filter

The-PC-Guy :

if you click that

The-PC-Guy :

then a menu pops up

The-PC-Guy :

and you click clear

The-PC-Guy :

that will unhide all of your rows

The-PC-Guy :

now before I set up your sums

The-PC-Guy :

are you going to ever want to filter the data at some point

Customer:

Yes

The-PC-Guy :

in that case

The-PC-Guy :

i would put the sum at the top of the column

The-PC-Guy :

under the heading

The-PC-Guy :

there is no rule that says the sum has to be at the end

The-PC-Guy :

this way you would avoid

The-PC-Guy :

filtering the row that has the formula in it

Customer:

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?

The-PC-Guy :

well what was happening was i think you were filtering rows that had data in them

The-PC-Guy :

those same rows also had subtotal formulas in them

The-PC-Guy :

so if I am folowing you correctly

The-PC-Guy :

you do not want subtotal formulas

The-PC-Guy :

or you do?

Customer:

no - I don't want any subtotal formulas

The-PC-Guy :

ok

The-PC-Guy :

as of now

The-PC-Guy :

I cannot find any subtotal formulas in the sheet

The-PC-Guy :

I see a few sum

The-PC-Guy :

and sum if formulas

The-PC-Guy :

but I assume you want those

Customer:

yes - i want those. So why were rows hidden?

The-PC-Guy :

again

The-PC-Guy :

that was do to filtering

The-PC-Guy :

you see

The-PC-Guy :

for example

The-PC-Guy :

if you filtered one of your $value columns

The-PC-Guy :

to only show cells with a certain amount

The-PC-Guy :

or greater than a certain amount

The-PC-Guy :

all other rows would be hidden

The-PC-Guy :

until you reset the filters

Customer:

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.

The-PC-Guy :

your meathod works too

The-PC-Guy :

however you would have to do that for each column

The-PC-Guy :

you filtered

The-PC-Guy :

the commands I gave you are global

The-PC-Guy :

in other words does all of them at once

Customer:

Can I now copy columns that have my If then statements and can I also sum columns without getting the subtotal 9 format?

Customer:

Can I download the file you fixed?

The-PC-Guy :

ok

The-PC-Guy :

as far as your if statements

The-PC-Guy :

you have to be careful whether you are using relative or absolute addressing

The-PC-Guy :

you can use a strait

The-PC-Guy :

=sum(a1:a50)

The-PC-Guy :

or whatever

The-PC-Guy :

instead of =subtotal(a1:a50,9)

The-PC-Guy :

essentially those 2 formulas do exactly the same thing

Customer:

I didn't think I had intentionally used any =subtotal formulas. Do you see any?

The-PC-Guy :

none on the input sheet

The-PC-Guy :

i did not look at any of the other sheets

Customer:

They would have been on the input sheet.

The-PC-Guy :

i will teach you another trick if you like

Customer:

yes

The-PC-Guy :

if you see the find and select icon next to the filter sort icon I told you about earlier

The-PC-Guy :

if you click that

The-PC-Guy :

and then click find

The-PC-Guy :

in the find what box

The-PC-Guy :

type what you want to look for

The-PC-Guy :

in this case the word

The-PC-Guy :

subtotal

The-PC-Guy :

then click the options button

The-PC-Guy :

under look in

The-PC-Guy :

choose formulas

The-PC-Guy :

and that will tell you if you have any of that formula on the sheet

The-PC-Guy :

and also tell you which cells they are in

Customer:

can you wait while I try that quickly? It's a long string of directions so i want to try it now

The-PC-Guy :

yup

The-PC-Guy :

i have to upload the other file anyway

The-PC-Guy :

ok you can click this link to download

The-PC-Guy :

I am around if you have additional questions later

The-PC-Guy :

and you can request me specifically by name

The-PC-Guy :

put "The PC Guy"

The-PC-Guy :

in your question tital

The-PC-Guy :

or goto my profile and ask in my box

Customer:

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?

The-PC-Guy :

you wont need the file

The-PC-Guy :

because that is all i did

Customer:

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

The-PC-Guy :

anyway, PLEASE RATE MY SERVICE WHEN YOU ARE READY

The-PC-Guy and 7 other Microsoft Office Specialists are ready to help you