Select Page

There is no defined way for keeping data in excel. Excel users maintain data as desired or as they feel easier to handle. Some users keep data in one worksheet for a week while others for a month. Generally all these weekly or monthly data sheets have common formats and named as WK1, WK2 or Jan, Feb, Mar etc. Irrespective of how they maintain their data one thing is common, they maintain a summary sheet to generate the report by adding data of these sheets.

Selection of range is very basic in excel, even a beginner know how to select a defined number of cells, a complete row or a column. It is easier to apply a formula in a contiguous range then to a non-contiguous.

Suppose, we have to add a non-contiguous range in worksheet, for this we may use the formula =E3+E6+E12+E15, but what if we have to add a cell across the number of sheets, may be sheets for each week e.g. WK1, WK2, WK3 etc or for each month like Jan, Feb, Mar etc.

I have seen a number of excel users to use below formula to add cell E4 from sheet1 to sheet4.

=Sheet1!E4+Sheet2!E4+Sheet3!E4+Sheet4!E4

In fact they go each sheet and click the cell, it means if they have to add data for 12 months i.e. from Jan to Dec they open all twelve sheets one after another and click the same addressed cell twelve times. To me it is time taking and error prone.

The smart way of adding in such cases is use name of first and last sheet and common cell address, suppose you have to add cell E5 of all 12 sheets named from Jan to Dec, use the following formula

=SUM(Jan:Dec!E5)

In case you have any query regarding above, you may send the same as comment to this blog.

The best preparation for tomorrow is doing your best today.

H. Jackson Brown, Jr.

By Rajesh K Saraogi

Rajesh is a hardcore finance person with a penchant for latest technology. His passions is to improve the productivity with simple, yet powerful software – mainly on Microsoft Excel platform. He is a qualified MCA. Whenever he gets time he loves to blog, to share the knowledge, which is the only thing which increases after sharing!

Pin It on Pinterest

Share This