How can I get autosum to automatically highlight totals as well as numbers?

Posted on 2016-09-01
Last Modified: 2016-09-02
I'm attaching a file with a total of numbers (F7)  and then another number. When I insert autosum underneath them to add them up, Autosum just picks up F8 instead of autoselecting F7 and F8. How can I get it to do its usual thing of automatically putting its "marching ants" around the F7:F8 range. Thanks
Question by:agwalsh
  • 3
  • 2
  • 2
LVL 31

Expert Comment

by:Rob Henson
ID: 41780062
The AutoSum is clever enough to recognise the contents of a cell already being a SUM.

With your sample, use the AutoSum in F9 and accept just the one cell. Then go to F10 and try another AutoSum. This time it recognises the two sums above and suggests adding those.

That is the way AutoSum is supposed to work. Sometimes Excel can be too clever for its own good and doesn't always do what we expect or want.
LVL 31

Accepted Solution

Rob Henson earned 350 total points
ID: 41780066
If you highlight the cells to be summed first ie F7 and F8 and then click the AutoSum it will do what you are expecting.
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41780090
Autosum will not include any cell with the SUM formula in the range to be summed up.
Try Rob's suggestion, that would do the trick.
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

LVL 31

Expert Comment

by:Rob Henson
ID: 41780098
@Neeraj - not strictly true, the AutoSum will choose cells containing SUM if they "appear" to be the only cells valid to SUM; as suggested in my first comment when doing the AutoSum on the next cell again, it SUMs the previous SUMs.
LVL 28

Assisted Solution

by:Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj) earned 150 total points
ID: 41780113
Yeah that's correct Rob!
I meant that if one cell has a SUM formula in itself and other cells contains only numbers, the autosum will behave incorrectly. i.e. the cells content must be homogeneous.

Author Comment

ID: 41780125
Yeah, I get I can just select the other cell with the summed numbers but I just like the elegance of Excel automatically anticipating what I want (yes, I know this is a 1st World problem LOL). Will check out the highlight thing...

Author Closing Comment

ID: 41781140
Got the answer I wanted - a workaround :-)

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Excel file merge 2 cells with line break 2 23
Excel 6 19
Populate data based on a  criteria 2 17
Quickbooks - Merging data 3 8
Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now