Consolidating Lists While Adding Links to the Source Data in Microsoft Excel

If you have data in the same cells in multiple sheets that need to be summed up together, you can do this using a very simple technique.  And if you have a range of cells that you need to sum, you can sum one cell and then drag the formula down or right or copy it to those ranges, which need to be summed.

Let’s see how this technique works

Considering that we have a workbook that has 3 sheets – January, February, and March.

There are values in cells A2: A4 in each of these sheets.
 
img1
 
img2
 
img3
 
Now in another sheet, we need to sum up the values in each of the cells. So we need to sum A2 in January, February, and March.  Respectively for A3 and A4 in this example.

So in a blank sheet, type =SUM and then click on the January tab.  Hold the shift key on the keyboard and select the 3rd sheet – March.  Select the cell A2 and press Enter on your keyboard.

The formula will now show =SUM(January: March!A2)

You will get the sum of the cell A2 from January to March. Now drag down this formula to A4 and the values will be populated.

img4
 
In this example, we had values only in A2: A4.  However, if you have values horizontally, in columns B onwards, you can drag the formula right. Or copy the formula to other cells where you need to sum up the values from the corresponding sheets.
 
There is another method to sum the values from cells in different sheets. We can use the 3D reference option.

3D Reference:  This option is used to combine the data from the different sheets in a single sheet. 3D reference contains three dimensions – rows, columns, and worksheets.

Let’s take an example, we have sales data for 3 months in 3 different sheets in an Excel workbook, and in one sheet, we will return the total quantity of all months agent wise.

img5
 
Follow the below given steps to sum the values from the cells in different sheets -

  • Select the cell C2 in the “consolidated” sheet
  • Go to the Data tab, and select Consolidate from the Data tools group

img6
 

  • The Consolidate dialog box will appear

img7
 

  • Select Sum from the function drop down list
  • Click on Reference, select the January sheet and then select the range C2:C11

img8
 

  • Click on add button and then select the range C2:C11 in the February sheet
  • Click on add button and then select the range C2:C11 in the March sheet

img9
 

  • Click on OK

img10
 

  • The total sales quantity will appear in the range C2:C11 for all the agents

image 48

 

If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com

 
 

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube