Archive for June 14th, 2013

Excel Tip: Art of Summing with Excel June 14th, 2013

Vinod Kumar

Let me bring this weeks Excel Tips and Tricks series with a simple tip. The scenario I am talking is most used – You want to Build a Sum of values for a table of values.

The most common requirement for any table of data is to build a SUM or Average along its rows and Columns. There are multiple approaches and let me outline some of the most common ways it is done today and I find each of these methods interesting and one method will always be efficient than the other. I am sure I have not outlined all the possible ways of doing this task.

Initial DataSet

This is a simple dataset of names and various Sales number. Now we need to have create totals Month-wise as well as employee-wise. So our dataset looks like below:

And we want to build SUM across and along as shown below:

Using SUM Formula

The basic old school of thought will be to Type “=SUM” as shown in figure below and we can give the range of C3-C6. Similarly we will do it for each of the cells.

Copy-Paste: A more efficient way to get the values across will be copy the Cell C7 and paste it across in D7, E8 and so on. This copies the formula of sum and gets the relative cells accordingly for each cell.

Using Shortcut

When the Cursor is at C8, Press “Alt+” (Alt and + key). This generally gets the adjacent cells and does the auto select with the SUM option. This is really powerful and your screen would look like this:

Now that we have got the C8, we can do Copy-Paste. Wait, now if you move your mouse to the C8 cell corner – you will be presented with a cross-arrow. Just drag this till F Column. It will magically fill the formula and make the SUM.

Now let me move to the Columns G3 and to get the SUM, let me use “Alt+” and press Enter. Voila, you have the Sum across columns done. Now we can use the same cross-arrow and drag – hold on.

Once you see the cross-arrow in columns, just double click. You will automatically fill till G7. Awesome right?

Using Toolbar

I am sure you have used any of these previous techniques in the past. The most efficient way I have figured out for Summing is using the Toolbar –> Home –> Editing Section –> AutoSum.

For this to work, select the region and just one click from toolbar. The values are filled.

It is just not only for single click SUM, you can also get AVERAGE, COUNT, MAX, MIN and many more functions as shown in figure below.

The best part of this method is, we have not used any Keyboard keys and I call this “Magic with mouse :)”.

Finally, I complete this weeks Excel Tips series and do let me know your feedbacks, I will be more than happy to write more on these lines and your comments are most welcome if you want me to write more of these in the future. I can surely write based on your feedbacks and enthusiasm for this topic. Office is an ocean and we can never be lost for tips to share. Thanks again for reading this far.

Continue reading...