Find where the link in a cell comes from

In Excel data, there are times when we might pull out data from a different Excel file (link).

If that is only for only 1 or 2 chunks of data that might not be a problem, but if there are many links, then we might question ‘where does this number come from’?

In this case, we can check the link settings to know what piece of data is linked with what.

 

Example of use) Find where the link in a cell comes from

Below is a table that shows sales records of a company. Information such as planned visit dates and sales results are summarized here.

 

 

We will find out where the data in this table is linked with.

(1) Find out which foreign file this table is linked with.

① Click on the [Data] tab.
② Click ‘Edit Links’ on the left hand side of the ribbon.

 

(2) When the dialog box appears, the file name that this sheet is linked with will be displayed.

Remember or write down the file name under ‘Source’ before closing the dialog box.
(※If there are multiple file names, remember all)

 

(3) Find out which cell is linked with the file in (2).

① Click on the [Home] tab.
② Click ‘Find & Select’ from the right hand side of the ribbon and then ‘Find’.

 

(4) When the dialog box appears, click ‘Options’.

 

(5) Find which cell is linked with the file:

① Input the file name from (2) in ‘Find what:’.
② Click [Find All].

 

(6) The cell number that is linked to the foreign file is displayed at the bottom of the dialog box.

The first row in the above means that the value in cell D4 is linked with “cell D3 in Sheet 1 of the ‘Customer Sales Report’ file”.

※If multiple file names appear in (2), do the same for each of the file links.

 

Find out the data type of a cell

Every cell has a data type (a display format).
If, for example, the cell is set up in a [date] form, the display will automatically change to something like ’11/3′, and if in [currency] form, something like ‘$1,000’.

It is useful in most times, but sometimes it is difficult to judge what data type the cells are set up to.

Let us check the data type of cells in the table we used above.

 

Example of use) Check the data type of a cell

(1) Right click on cell to look for data type, and select ‘Format Cells’ from the menu.

 

(2) The dialog box that appears will show the current data type of the selected cell.

In this cell (cell ‘F4’), the data type is in ‘date’ and the display form in ‘3/14’.

 

Identify the color of a cell

Changing the cell color is a useful tool that makes the data more easier to see.

However, there are often times when we want to use the same cell color that we used in another table but cannot figure out what color it is. Excel has many variations of a color, so it might be difficult to identify.

Below, let us explain the steps in identifying the color of a cell.

 

Example of use 1) Identify the current cell color

(1) Identify the current cell color with the steps below:

① Select a colored cell to search for current cell color.
② Click on the [Home] tab.
③ Click on the opposite triangle▼ next to the [Fill color] (Bucket).

The current color is surrounded with a red border.
In Excel, there are many more colors other than the ‘Theme Colors’ displayed here, so if no color is surrounded with a red border, go on to ‘Example of use 2) Other colors’.

 

Example of use 2) Other colors

(1) Click ‘More colors’.

 

(2) The dialog box will identify the current cell color.

※When the cell color cannot be identified in the [Standard] tab, check the [Custom] tab.

 

Identify cell row/column number

To identify cell row/column number, follow the steps below.

 

Example of use 1) The usual way

Excel has something called a ‘Name box’, and the cell number of the currently selected cell is displayed.

→ In the diagram below, cell A1 is selected.

 

Example of use 2) Using a formula

=ROW(reference)

[reference] = Select cell or cell range to identify the row number for.

Definition of function A function to identify the row number of a selected cell.
EXCEL version Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

=COLUMN(reference)

[reference] = Select cell or cell range to identify the column number for.

Definition of function A function to identify the column number of a selected cell.
EXCEL version Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

① Identify the row number of cell with the Sales name ‘Stone’.

(1) [reference] : Select cell with the name ‘Stone’, cell E6.
=ROW(E6)

② Identify the column number of cell with the Sales name ‘Stone’.

(1) [reference] : Select cell with the name ‘Stone’, cell E6.
=COLUMN(E6)

 

POINT!

The COLUMN function displays the column number as ‘1, 2, 3…’ (not as ‘A, B, C…’).

We can also select ‘a range of cells‘ as a [reference].
The row and column number displayed as a result will refer to the cell in the very top left within the selected cell range.