Request a callback

We'll call during UK business hours

Name:
Number:
0207 987 3777

previousPrevious article   Next articlenext back to categoryExcel articles

article How To Create A Formula In Excel To Link Data In Different Sheets

Boost your Excel skills by learning how to create Excel formulas to link cells between different worksheets. This technique can save you valuable time by delegating the work to Excel to complete it for you!
Have you ever wanted to create a formula in Excel which links data in different worksheets? This article describes how to do this in easy to follow steps. You can also link data in separate Excel files in similar ways. By linking data over a number of worksheets, you can save time by letting Excel compile the information for you.

Let's look first at how to link data in different sheets in the same workbook.

Suppose you have data in three separate sheets within the same workbook. For example, sheet 1 might contain data for January, sheet 2 has data for February and sheet 3 has the data for March.

You create a fourth sheet where you want to add the data from the other sheets. First, you rename each sheet to a more meaningful name. This is not absolutely necessary but helps in identifying data. So you rename sheet 1 as Jan, sheet 2 as Feb, sheet 3 as Mar and sheet 4 as Total. In this example we'll create the same table layout in each of the sheets, but each with different data.

So we create a two column table with the titles Product and Sales in cells D3 and E3 in the Jan sheet. Then you enter these three product names in successive cells under the Product title, Gold, Silver and Bronze. In the cell under Bronze you type in the word Total. Then even though the Sales cells are empty, in cell E7 you type in the formula =SUM(E4:E6) and press the Enter key.

Then you copy this table from the Jan sheet to each of the other sheets, so you end up with four tables, one in each sheet. Now you can type in the sales data in each of the Jan, Feb and March sheets. Then you select the Total sheet. Here we want to create formula to add the data in each of the other sheets and put the totals here.

To begin, in the Total sheet select the cell for the gold sales - this should be cell E4. We will now type in the formula to add the data from the corresponding gold sales cell in each of the other sheets. So type and equals symbol, and then carefully change the selected sheet to the Jan sheet. Now select the Jan gold sales cell E4 with a single left click. Look in the formula bar and you'll see the developing formula. Then carefully change the selected sheet to the Feb sheet.

Select the Feb gold sales cell E4 with a single left click. Look in the formula bar to see how the formula has changed further. Then one more time, carefully change the selected sheet to the Mar sheet. Then select the Mar gold sales cell E4 with a single left click. You are almost there. Don't make any more switches. Just stay in the Mar sheet. Now press the Enter key, and you're taken back to the Total sheet, where you started. You should now see the total gold sales figure in cell E4. And that's the job done for the gold cells.

Still in the Total sheet, re select cell E4 and look at the formula in the formula bar. It should look like =Jan!E4+Feb!E4+Mar!E4 and you can see how Excel adds the sheet name followed by an exclamation mark, then the cell reference to show a cells in different sheets. Then you can use the Fill handle in the Totals sheet to fill down the other sales figures.

In a similar way you can link cells in separate Excel files as well as in separate worksheets. You need to ensure that the separate file is open. Then build the formula in a similar way, but you can switch files as well as sheets to select target cells. Just take note that if you do link cells from different files, then Excel adds the external cell references as absolute and adds the dollar symbols in the cells references in the formula. If you intend to then use the fill handle, you should manually remove the dollar references as appropriate.

Interested in learning more about Excel's many useful formula features? Why not consider attending one of the many courses available. That way you can learn a great deal in a short time.


Original article appears here:
http://www.microsofttraining.net/article-2062-how-create-formula-in-excel-link-data-in-different-sheets.html


Back to article list

Distribution notes

PUBLICATION GUIDELINES

  • You have permission to publish this article for free providing the "About the Author" box is included in its entirety.
  • Do not post/reprint this article in any site or publication that contains hate, violence, porn, warez, or supports illegal activity.
  • Do not use this article in violation of the US CAN-SPAM Act. If sent by email, this article must be delivered to opt-in subscribers only.
  • If you publish this article in a format that supports linking, please ensure that all URLs and email addresses are active links, without the rel='nofollow' tag.
  • Software Training London Ltd. owns this article. Please respect the author's copyright and above publication guidelines.
  • If you do not agree to these terms, please do not use this article.


Microsoft Office courses London
Course rating:
4.7 stars - based on 829 reviews
Microsoft Certified Partner Institute of Leadership and Management - Certified Courses Learning and Performance Institute - Accredited Training Provider Security Seal verified by visa, mastercard securecard

Connect with us: Google+ · Facebook · Twitter · LinkedIn · Pinterest

2nd Floor, CA House, 1 Northey Street
Limehouse Basin, London, E14 8BT
United Kingdom