Software

Compute your entire Excel worksheet with a click of the mouse

You already know how to use Excel's AutoSum feature -- now use it to enter all the formulas with just one click and sum it up. Here's how.

Most users know how to take advantage of Excel's AutoSum feature to quickly total spreadsheet data across a row or a column. For example, in the spreadsheet below, most users would use AutoSum to calculate the totals for each state by clicking in B7, double-clicking the AutoSum button, and then copying the formula across the range C7:E7. Likewise, to obtain the total sales for each software category, they would click in cell F4, double-click the AutoSum button, and then copy the formula down the range F5:F7.

Even though using AutoSum this way can save users quite a bit of time over entering each formula separately, they can save even more time by following these steps:

  1. Select the range A2:F7.
  2. Click the AutoSum button.

AutoSum lets you enter all the formulas with just one click! What's more, you can do this for all the auto functions. For example, if we change the labels in F3 and A7 to Average, we can then complete the worksheet by following these steps:

  1. Select the range A2:F7.
  2. Click the AutoSum button drop-down arrow and select Average.

Miss an Excel tip?

Check out the Microsoft Excel archive, and catch up on other Excel tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

7 comments
mjbnet
mjbnet

Would like to know where this is going

phil.jones
phil.jones

A pernnial issue - how can you achieve vector averaging (sometimes called geometric averaging etc) to average Wind Direction data, where an arithmetic average for wind directions of 350 degs and 10 degs gives the totally incorrect result of 180 degs.

NickNielsen
NickNielsen

But it's not pretty and it only works properly if your prevailing winds are from northerly directions (between 270 and 90). For the line of data in cell A1, use this formula in cell B1: =IF(A1

LocoLobo
LocoLobo

Assume your values to average are in A1:A7 The formula would be =(MOD(SUM(A1:A7),360)/COUNT(A1:A7))

LocoLobo
LocoLobo

set up a formula where if the difference between the angles is greater than 180? then add 360 to the larger angle? Then use a mod function to put it back to 1-360?? I don't think this can be done in a formula in 1 cell. So have a cell that calculates the range. Then next to your raw values transform the value more than 180? lower than the max. Then average and transform back to 1-360?. Maybe someone else has an elegant solution. I would be interested too.

NickNielsen
NickNielsen

For seven values between 310 and 360, your formula yields a result of 26-something. If you go past 360 to 5 or 10, the result increases to 45. Somehow, I don't think that's correct.