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: 1209
Experience:  20 years experience providing remote computer support
Type Your Microsoft Office Question Here...
The-PC-Guy is online now
A new question is answered every 9 seconds

Delete Subtotals

Resolved Question:

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

Submitted: 7 months ago.
Category: Microsoft Office
Expert:  The-PC-Guy replied 7 months ago.

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, Computer Manager
Category: Microsoft Office
Satisfied Customers: 1209
Experience: 20 years experience providing remote computer support
The-PC-Guy and 8 other Microsoft Office Specialists are ready to help you

JustAnswer in the News:

 
 
 
Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.
 
 
 

What Customers are Saying:

 
 
 
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
< Last | Next >
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
  • This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex Los Angeles, CA
  • Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP Hesperia, CA
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin Kernersville, NC
  • Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther Woodstock, NY
  • Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know. Robin Elkton, Maryland
 
 
 

Meet The Experts:

 
 
 
  • jstinehelfer's Avatar

    jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
< Last | Next >
  • http://ww2.justanswer.com/uploads/JS/jstinehelfer/2012-6-6_215146_Pictureofme1.64x64.png jstinehelfer's Avatar

    jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
  • http://ww2.justanswer.com/uploads/JA/JasonJames122/2011-12-16_135647_jasonjamesheadshotweb.64x64.jpg JasonJames122's Avatar

    JasonJames122

    Computer Enthusiast

    Satisfied Customers:

    0
    I manage all information systems (network of Windows PC's and servers with Microsoft Office products) for a small business
  • http://ww2.justanswer.com/uploads/JE/jessmagz/2012-6-6_18129_jm.64x64.jpg Jess M.'s Avatar

    Jess M.

    Computer Support Specialist

    Satisfied Customers:

    270
    Computer Software Specialist for more than 10 years
  • http://ww2.justanswer.com/uploads/CH/chutz747/2011-11-6_14231_me.64x64.jpg The-PC-Guy's Avatar

    The-PC-Guy

    Computer Manager

    Satisfied Customers:

    260
    20 years experience providing remote computer support
  • http://ww2.justanswer.com/uploads/SE/Seashore2011/2011-12-6_33418_111205223015.64x64.jpg Chris L.'s Avatar

    Chris L.

    Support Specialist

    Satisfied Customers:

    120
    Certified Expert with over 10 years experience.
  • http://ww2.justanswer.com/uploads/SP/spherrod/2012-6-6_174244_1000852.64x64.JPG Steve Herrod's Avatar

    Steve Herrod

    Computer Support Specialist

    Satisfied Customers:

    116
    Trained in Microsoft Office from 2000 through to latest version 2010.
  • http://ww2.justanswer.com/uploads/KR/krkljatorm/2012-6-24_153417_ITMiro.64x64.jpg IT Miro's Avatar

    IT Miro

    Computer Scientist

    Satisfied Customers:

    94
    Bachelor's Degree in Information Technology, Microsoft Certified Professional