Link data and spreadsheets – Creating references CBSE Class 10 Information Technology (IT)
For linking data between spreadsheets you need to understand the concept of references.
What is a cell reference ?
A cell reference refers to the particular address of a cell of a worksheet in spreadsheet software. This cell contains data or value in the worksheet. Sometimes it can be a range of cells.
For example, A1, B2, C3 or A1:C5 etc.
This cell reference can be used for the following:
- In different formulas or function to take the values for computation or calculation
- To display the value of particular cells
- To carry forward data from one worksheet to another
Link data and spreadsheets – Creating references to other sheets
Sometimes when you are working with spreadsheet software you need to repeat or use same data values frequently in worksheet. So you can insert value in worksheet and then use that value by a reference in other worksheets.
There are two ways to creating references:
- Using keyboard
- Using mouse
Using keyboard
You can do this by following these simple steps:
1 Enter the data into the worksheet for the master sheet.
2 Insert new worksheet by pressing sheet + F11. Move your worksheet to next to the mastersheet rename the worksheet as Data Entry. In this worksheet we will carry forward Roll No and Names from the mastersheet.
3 Now point the cursor in the cell where you need the cell value. Type = in the cell.
4 Now switch to the master sheet to take the reference. (Press Ctrl + Page Up key) and select the cell for the Roll No column. Observe the following screenshot.
5 Now switch to the data entry worksheet by pressing the Ctrl + Page Down Key. Place the cursor where you want to display the referenced value. You will get something like this:
6 Press enter when you finished. Repeat the similar steps for the names. Observe the following screen shot.
That’s it!
Using mouse
When you want to create reference using mouse follow these steps:
1 Prepare your mastersheet by entering the data.
2 Click on + symbol in the sheet tab to insert new worksheet.
3 Place the cursor where you want to create a reference.
4 Type = and click on mastersheet to switch the worksheet.
5 Place the cursor which reference you want to take.
6 Switch back to data entry worksheet.
7 Click and drag the data as per the requirement.
Hyperlinks in Spreadsheet
As you are aware with links generated from google meet or other web applications to connect different online text based web pages.
Hyperlinks are text based links that allows to redirect or open another file or website in MS Excel.
Create Hyperlink in Spreadsheet
To create a hyperlink follow the given steps:
1 Place the cursor where you wan to display the link text.
2 Click on Insert –> hyperlink option from Links group. Look at this screenshot.
3 Insert hyperlink dialog box will appear. Select appropriate option from dialog box and click on OK button.
Options
Existing File or web page: You can select a file from your computer or web page from internet.
Place in this document: This option allows to select a place like cell references or defined names or ranges.
Create New Document: It allows to create a new document which can be edited now or later.
Edit Hyperlink in Spreadsheet
To edit hyperlink follow the similar steps as insert hyperlink and do the changes.
Remove the hyperlink from Spreadsheet
Follow these steps to remove link from the excel worksheet.
1 Click on the cell where the link is created.
2 Click on Insert –> Hyperlink option from Links group.
3 The Edit Hyperlink dialog box will appear.
4 Find the Remove Link button in the dialog box.
5 Click on Remove Link button
