First, let’s define the difference between a Workbook and a Sheet (or sometimes called a Worksheet.) A workbook is the overall Excel file, whereas the Sheets are the individual spreadsheets. A Workbook can contain multiple worksheets, which are the tabs along the bottom of the Excel window.
Link Data in the Same Sheet
In any given sheet, you can use data from any other cell. This is most commonly used for formulas. You can also use a very basic formula to duplicate a cell’s content.
=E14
Entering this simple formula will display whatever’s in cell E14, whether it’s text, numbers, currency, or a date. If you change the contents of cell E14, all the other cells that reference E14 will change as well.
Link Data Between Worksheets
Where this linking ability of Excel really shines is when linking data between different worksheets. For example, if you have one spreadsheet (IT Expenses) that details expenses broken down by day or week, and another (Expenses by Dept) that compares total expenses between departments, you can pull the totals from the IT Expenses spreadsheet for use in the Expenses by Dept spreadsheet.
In the Expenses by Dept spreadsheet, you would enter this formula to pull the data from the IT Expenses sheet:
=(IT Expenses!C21)
Now, this looks a little complicated, but it uses the same basic structure as the =E14 formula we used before. We’re just adding the name of the spreadsheet from which we want to pull the data.
Link Data Between Workbooks
Similar to how we can link data from separate worksheets, we can use data from entirely separate Excel files (workbooks). Again, the structure of the formula is simple; we just need to give Excel a little more information about where it should look for the data.
=(‘[IT Expense Report.xls]IT Expenses’!C21)
In this case, we’re adding the other workbook’s file name (IT Expense Report.xls) in square brackets. Then we enclose the file name and sheet name in single quotes.
Please note that for security and convenience, try to keep the two Excel workbooks in the same folder.
These data linking techniques can also be incorporated into more complex formulas the same way you would use local cells.
I love tech, but don’t have as much time or money to spend on it as I’d like. I get what I need to sustain my Geek vicariously through other sources and pass it along to you.
{ 4 comments… read them below or add one }
Wow, sweet tip. I had no idea you could link between worksheets.
Thank you – this is what I was looking for.
It is not right way to linking the work books
Thank you very much, I was looking for such cels references between two different worksheets.