To copy from one worksheet to another worksheet in a same workbook we use the formula
(1 Point)
a.)Worksheetname!cell_reference
b.)=Worksheetname!cell_reference 2
c.)=Worksheetname.cell_reference
Answers
Explanation:
Now, to transfer a formula: Assume the formula is in sheet 1, cell A1. Highlight A1, Copy (Ctrl+C) it, go to the cell in sheet 2 where you want the formula to go and Paste (Ctrl+V) it. Excel will adjust the formula to =Sheet1! A1.
Answer:
To be able to reference cells and ranges is what makes any spreadsheet tool work. And Excel is the best and most powerful one out there.
In this tutorial, I will cover all that you need to know about how to reference cells and ranges in Excel. Apart from the basic referencing on the same sheet, the major part of this tutorial would be about how to reference another sheet or workbook in Excel.
While there is not much difference in how it works, when you reference another sheet in the same file or reference a completely separate Excel file, the format of that reference changes a bit.
Also, there are some important things you need to keep in mind when referencing another sheet or other external files.
But worry… nothing too crazy!
By the time you’re done with this tutorial, you will know all there is to know about referencing cells and ranges in Excel (be in the same workbook or another workbook).
Let’s get started!
This Tutorial Covers:
Referencing a Cell in the Same Sheet
This is the most basic level of referencing where you refer to a cell on the same sheet.
For example, if I am in cell B1 and I want to refer to cell A1, the format would be:
=A1
When you do this, the value in the cell where you use this reference will be the same as that in cell A1. And in case you make any changes in cell A1, these would be reflected in the cell where you have used this reference.
Referencing a Cell in the Another Sheet
If you have to reference another sheet in the same workbook, you need to use the below format:
Sheet_name!Cell_address
First, you have the sheet name followed by an exclamation sign which is followed by the cell reference.
So if you need to refer to cell A1 in Sheet 1, you need to use the following reference:
=Sheet1!A1
And if you want to refer to a range of cells in another sheet, you need to use the following format:
Sheet_name!First_cell:Last_cell
So, if you want to refer to the range A1:C10 in another sheet in the same workbook, you need to use the below reference:
=Sheet1!A1:C10
Note that I have only shown you the reference to the cell or the range. In reality, you would be using these in formulas. But the format of the references mentioned above are going to remain the same
In many cases, the worksheet you refer to would have multiple words in the name. For example, it could be Project Data or Sales Data.
In case you have spaces or non-alphabetical characters (such as @, !, #, -,etc.), you need to use the name within single quotes.
For example, if you want to refer cell A1 in the sheet named Sales Data, you will use the below reference:
='Sales Data'!A1
And in case the name of the sheet is Sales-Data, then to refer to cell A1 in this sheet, you need to use the below reference:
='Sales-Data'!A1
When you refer to a sheet in the same workbook, and then later change the name of the worksheet, you don’t need to worry about the reference breaking down. Excel will automatically update these references for you.
While it’s great to know the format of these references, in practice, it’s not such a good idea to manually type these every time. It would be time-consuming and highly error-prone.
Let me show you a better way to create cell references in Excel.
Automatically Creating Reference to Another Sheet in the Same Workbook
A much better way to create cell reference to another sheet is to simply point Excel to the cell/range to which you want to create the reference and let Excel create it itself.
This will ensure that you don’t have to worry about the exclamation point or quotes being missing or any other format issue cropping up. Excel will automatically create the correct reference for you.
Below are the steps to automatically create a reference to another sheet:
Select the cell in the current workbook where you need the reference
Type the formula till you need the reference (or an equal-to sign if you just want the reference)
Select the sheet to which you need to refer to
Select the cell/range that you want to refer to
Hit Enter to get the result of the formula (or continue working on the formula)
The above steps would automatically create a reference to the cell/range in another sheet. You will also be able to see these references in the formula bar. Once you’re done, you can simply hit the enter key and it will give you the result.
For example, if you have some data in cell A1:A10 in a sheet named Sales Data, and you want to get the sum of these values in the current sheet, following will be the steps:
Type the following formula in the current sheet (where you need the result): =Sum(
Select the ‘Sales Data’ sheet.
Select the range that you want to add (A1:A10). As soon as you do this, Excel will automatically create a reference to this range (you can see that in the formula bar)
Hit the enter key.
Explanation: