Excel AutoSum Revisited

You just can't get away from AutoSum. If I had a pound for every time I've clicked AutoSum to add up a column of numbers I would probably be sitting on a tropical island somewhere still clicking it to add up all my vast wealth. It's one of the first things you learn about in Excel and, of course, everybody knows it inside out.

Select the cells

Here's a really easy way of calculating column and row totals without having to do all that infernal copying.

First job is to select all the cells containing your numbers and extend the selection to the empty cells where you want the totals.

Click AutoSum

Then you click AutoSum and you're done! It really is that easy. If you only want the column totals then just select the cells with the numbers and the totals are returned into the row below.

Column and Row totals

Here's all the column and row totals done in two simple steps. And they are done perfectly as the range references in the SUMs are implied by the selection. They hardly need to be checked.

AutoSum shortcut

If you prefer to do the whole thing with your keyboard then hold down the SHIFT key and extend the cell selection by pressing the arrow keys. Then use the shortcut key combination ALT= to generate the SUM formulas.

Grand Totals

But it's not just one table is it, you've got loads of them and some serious number crunching to do. You can extend the select-and-click-AutoSum method to do grand totals as well. But, be careful, this method only really works if every line item in your worksheet is drawn into a subtotal.

The Selection for AutoSum Grand Totals

Firstly, calculate all of your individual subtotals and then make a selection of all of the cells containing your numbers and the cells containing your subtotals.

Now, click AutoSum and all the cells containing your subtotal SUMs are detected and the Grand Total is calculated correctly without any risk of double-counting.

Ranges and Unions

Here's the resulting formulas displayed in the cells and you clearly see the formulas containing the original range references for the subtotal SUMs.

The grand total SUMs are unions, showing each subtotal reference separated by commas.

Checking your Formulas

Show the Formulas

As some of us know from bitter experience it is very embarrassing when a simple mistake in your formulas messes up all your careful calculations. It's always a good policy to check that you've got your formulas right but it's a bit painful to view them in the formula bar. 

Formulas displayed in the cells

Display the formulas directly in the cells by clicking the Show Formulas control. It's in the Formula Auditing group of the Formulas tab. Click once to show the formulas and click again to show the results.

Show Formulas shortcut

If you have an older version of Excel then you have to search in the Tools, Options dialog for this option which is not much fun and I recommend that you use the shortcut key combination CTRL ` (that's usually the key under the ESC key on the top right of your keyboard}

Happy number crunching.

Related Posts

Excel-Calculations without formulas
Excel-Sorting by last name
Excel-Switching columns to rows
Excel-Calculating age from date of birth

Training Courses

If you've still got that "I just don't know what I'm doing" feeling then you might like to arrange an Excel training course for yourself or with some of your colleagues. It's really easy to book one of our courses and they're great value for money. See our website for full details.

Word Typing-in Accented Characters

Accented Characters

Hold down the ALT key on the left hand side of the keyboard while you type in the four digit code using the numeric key pad on the right hand side of the keyboard. The character is displayed when you release the ALT key.

Activate the numeric key pad by pressing the NUM LOCK key before typing the code. Do not use the number keys at the top of the keyboard as they do not generate the correct codes. Always remember to enter the full four digit code including the leading zero.

Cedilla and Ligatures

Download Accented Characters as pdf

PowerPoint Removing Backgrounds from Pictures

Removing Backgrounds from Pictures

Extracting the main subject from the background of a picture or photograph used to be a marathon task (for me!) involving Photoshop masks and paths or background erasers but with Microsoft PowerPoint 2010 and later you can do it directly in process using the Remove Background control.

Elvis on car

You won't get quite the sophistication or fine control of the Photoshop process but for simple images the edge detection is really good and it only takes a few minutes. 

This is perfect for simple image manipulation, like removing the white background from a company logo.

Here's a photograph of our mascot, Elvis, sitting on the bonnet of a car. He loves doing this while the engine's still warm. But I didn't want him on the car, I wanted him on a slide. PowerPoint will do this in 5 minutes, in real life there's no way that cat's moving. The first job is to insert the photograph onto your slide as you usually do and keep it selected.

Next, find the Remove Background control, it's on the extreme left-hand side of the Picture Tools tab. Give it a click and see what it comes up with.

You usually find that the initial results are quite impressive and after a wee bit of tweaking you've got an excellent extraction.

First go. Not bad!

As you can see with this one most of it's good but part of the car has been retained and Elvis has lost his stripey tail.

There's a bounding box around the captured subject image and all the areas of the photograph that are to be removed are coloured mauve.

Now for the tweaking. We're going to adjust the bounding box to include the tail and then zoom in on the other areas and use the controls on the ribbon to specify which parts we need to keep or remove.

Adjusting the bounding box

I've dragged the left edge of the box to the left and Elvis gets his tail back. Great. I've dragged the bottom edge of the box down and Elvis gets his front paw back but I've gone too far down and included his reflection on the car.

Keep going with the box until you get as close as you can to your desired image. Now it's time to zoom in and deal with all the fine work by using the keep and remove controls on the ribbon.

Zoom in or out of your image either by using the Zoom control at the lower right-hand corner of the PowerPoint window or by holding down the CTRL key as you spin your mouse wheel forward or back.

Controls on the Ribbon

When you use the Mark Areas to Keep and Mark Areas to Remove controls don't bother trying to draw a line around the areas to keep or remove, instead draw a line straight across them and let the edge detection do the work for you.

Using the Remove control

Draw a line with the pencil pointer straight across the offending areas.

Zoomed in for fine work

Here we can see the results, after a few swift strokes most of the car has been removed and if I had more patience then I would have continued going around Elvis and tidying up his fur but I wasn't bothered as it's a fairly low-res photograph and the intended slide image is quite small.

It's time to click the Keep Changes control and let the cat out of the bag.

I've often wondered why that cat is called Elvis.

The Thoughts of Chairman Elvis

And finally, here's the old fella on his slide ready to wow his audience by telling them all about the really important things in any cat's life.

I've changed the background image and I've flipped and rotated Elvis. He won't like that at all.

"Where's my dinner?" "Miaow!!!" I think it's time I left the building. Best of luck with your slides.

Related Posts

Excel-Calculations without formulas
Excel-Sorting by last name
Excel-Switching columns to rows
Excel-Calculating age from date of birth

Training Courses

If you've still got that "I just don't know what I'm doing" feeling then you might like to arrange a PowerPoint training course for yourself or with some of your colleagues. It's really easy to book one of our courses and they're great value for money. See our website for full details.

PowerPoint Text Boxes

Formatting Text in Text Boxes

When you format the text inside a text box your formatting is only applied to the word with the focus and all too often you have to drag across your text to select it and, if you're a bit clumsy, you end up moving the text box.

No need. Just click the outside edge of the text box or, even easier, press the ESC key. Now the entire text box is your current selection and your formatting is applied to all of the text in the text box.

Only click on the text in the text box when you want to format a single word or a particular phrase.

White Space in Text Boxes

You don't want every element of text in your presentation to be a bullet point, sometimes it is more natural to use a series of short paragraphs. But when you press ENTER to start a new paragraph the space between the paragraphs is full height because it's based on the font size you are using. 

Paragraphs with full height spacing

Text usually looks better if you reduce the amount of white space between your paragraphs to around half or two-thirds of the height of the font. Say you're using an 32pt font, then you should consider reducing the space to around 18pt or so.

This is a good one to know for Word as well, instead of banging away with ENTER, ENTER after each paragraph; adjust your paragraph's Space After setting to a suitable amount and then press ENTER once to start a new paragraph. ENTER, ENTER is typewriter formatting.

Anyway, let's stay with PowerPoint. To make the setting you either click the Line Spacing control in the Paragraph group on the Home tab and then look for Line Spacing Options or (far easier) click the launcher at the lower right corner of the Paragraph group.

Dialog launcher on the Paragraph group

Take a quick look at the font size you are using and then enter a suitable value into the After box of the Spacing section. This needs a quick bit of mental arithmetic but it's only working out halves or two-thirds and there's no need to be precise.

The space between your paragraphs is now huge because you have your new space after plus the original full height paragraph. Just click after the first paragraph and press DELETE to remove the empty paragraph and leave you with your beautiful, balanced paragraph spacing. 

Paragraphs with balanced spacing

Space After

Space After values can be either typed in or you can use the spinner control.

The effect of the reduced spacing is subtle but it looks so much better than before and that's the type of thing that makes a big difference when your slide is projected on the big screen.

White Space around Bullets

You can also use paragraph formatting to adjust the amount of white space around any bullet points on your slide. This is done using the Indentation settings available when you click the Dialog box launcher at the lower right corner of the Paragraph group.

Paragraph Indentation for Bullet Points

The Before text value sets the amount of white space between the left edge of the text box and the bullet. The Hanging value is the space between the bullet and the text.

Standard bullets with tight spacing

Bullets with looser spacing

Related Posts

PowerPoint-Removing Backgrounds from Pictures

Training Courses

If you've still got that "I just don't know what I'm doing" feeling then you might like to arrange a PowerPoint training course for yourself or with some of your colleagues. It's really easy to book one of our courses and they're great value for money. See our website for full details.