Go to | last cell | visible cells only

Last Cell

Last cell denotes the cell that contains data and/or formatting.
Even one deletes the data and/or formatting of the cell, Last Cell function identifies that cell. 

Last Cell

Procedure

• Click any where in the sheet.
• Press Ctrl+G and click 'Special..' or Click 'Find & Select' under Home Tab and click 'Go to Special...'.
• Tick the Radio button associated with 'Last Cell' function.
• Click Ok.

Visible Cells Only

'Visible Cells Only' function identifies those cells that are visible in a range. It helps to identify the filtered rows or columns when filters are enabled in a sheet. 

Even it helps to identify any hidden row or column in the sheet.

It is useful.

Conditional Formats

'Conditional Formats' function identifies the cells with conditional format embedded.

Data Validation

Data validation function identifies cells with data validation embedded. Further sub categorized as 'All' and 'Same'.

Go to | Precedents | Dependents

Precedents and Dependents feature sometime proves useful.

Cell Precedents

This function is applicable to cells that contain formulas only.
Precedent cells are cells that contribute 'directly' or 'indirectly/All levels' to a formula result.
Say cell G1 has a formula =(E1*F1)
Accordingly cell G1 has two precedent cells namely E1 and F1.
Indirect/All levels precedent cells don't directly contribute to formula result but used as reference. 

Precedent cells are often used to identify irregularities in formula.

How to identify precedent cells?

• Select the cell (to identify precedents)
• Press F2 - This function colorize the precedent cells but is limited to the active working sheet.
• Under Home Tab click Find & Select.
• Click Special.
• Check the radio button associated with 'Precedents'.
• Further check the option 'Direct' or 'All levels'.
• Click Ok.

Shortcut Ctrl+[
This shortcut identify precedent cells in the active sheet.

Cell Dependents

This function is applicable to cells that contain formulas only.
Dependent cells are cells that contribute 'directly' or 'indirectly/All level' to a formula result.
Say cell G1 has a formula =(E1*F1)
Accordingly cells E1 and F1are dependent on G1. There must be at least one dependent cells in case of formula. 

Hence before deleting any formula cell one should go through the precedents and dependents options.

How to identify dependent cell? 

• Select the cell that you want to identify dependency.

• Under Home Tab click Find & Select.
• Click Special.
• Check the radio button associated with 'Dependents'.
• Further check the option 'Direct' or 'All levels'.
• Click Ok.

Shortcut

Ctrl+]

So before deleting any cell one should check the dependency too.

Alternatively

Precedents and Dependents can also be found under 'Formulas' tab.

Under Formula Tab 

ILL 1

Explanations:

G2 & G3 cell contents are the multiplications of E2 and F2, E3 and F3 respectively. 
Active the cell F2 and click 'Trace Dependents'.
Active the cell G3 and click 'Trace Precedents'.
To remove the arrows click 'Remove Arrows' (further sub categorized into 'Remove Dependent Arrows' and 'Remove Precedents Arrows')
 

Go to | Row Differences | Column Differences

Row Differences

Excel has a very important function to find out differences between various rows. The function is called Row Differences.
To access the function 

• Shortcut key - Press Ctrl+G.
• Click Special

Alternatively 

• Under Home Tab click 'Find & Select'.
• Click Go to Special.

 

ILL 1 Row Differences Option

Lets illustrate with a data range.

ILL 2 Data Range

You are requested to go through the above selected data range. Where A2 cell (White cell in the range) is default active. You can observe the various differences of data from one cell to another but mostly row wise. Of course some are common.
After highlighting the data range.

Procedure

• Press Ctrl+G (Instead use Find & Select).
• Click 'Special'.
• Highlight the radio button associated with 'Row Differences'.
• Click Ok.

Cells with different contents are greyed out. For easy understanding highlight the resultant (greyed) cells with Fill color say 'Yellow'.
Result is as follows:

Result

Explanations: 
In the 2nd row content of cell C2 is different from cells A2 & B2.
One can argue that the content of cell A3 is different from cell contents of B3 & C3. So why B3 & C3 is highlighted? Why not A3? Since B3 & C3 are similar and A3 is dissimilar. 


Lets observe the data range (ILL 1) once again. 
We can see that the default active cell is A2 (white), so the basis for comparison starts from column A. 

According to the logic since the content of cells B3 & C3 are different from content's of cell A3, hence B3 & C3 has been selected.

The basis can be shifted by pressing Tab key (shifting the active white cell). Result would be different.

Again

Shifting the active cell to cell B3

Result

Explanations: 
Since the basis of comparison is shifted to cell B3, result is different from previous one.Here A3 cell's content is different from B3's content (basis) and C3's content, hence A3 has been highlighted.

Column Differences

Excel has a very important function to find out differences between various rows. The function is called Column Differences.
To access the function 

• Shortcut key - Press Ctrl+G.
• Click Special

Alternatively 

• Under Home Tab click 'Find & Select'.
• Click Go to Special.

ILL3 Column Differences

Lets illustrate with a data range.

ILL4 Data Range

You are requested to go through the above selected data range. Where B2 cell (White cell in the range) is default active. You can observe the various differences of data from one cell to another but mostly column wise. Of course some are common. After highlighting the data range. 


Procedure

• Press Ctrl+G (Instead use Find & Select).
• Click 'Special'.
• Highlight the radio button associated with 'Column Differences'.
• Click Ok.

Cells with different contents are greyed out. For easy understanding highlight the resultant (greyed) cells with Fill color say 'Yellow'.
Result is as follows:

Result

Explanations: 
In case of the column C the contents are different C2 cell's content is different from cell's content of C3 & C4. Hence C3 & C4 has been highlighted.


In case of column differences the basis of comparison is row wise.
If we observe carefully the data range (ILL4) we will see the default active cell in the selected data range is cell B2 and the cell lies in 2nd row. As a result the basis of comparison is the 2nd row.

But if we move the active cell (white cell in ILL4) to 3rd row (by pressing Tab key) and follow the procedure result will be different.

Changed Result

 

Go to | Special | Blank

Microsoft Excel provides a dedicated dialogue box from where you can access some particular group of cells.

• Shortcut Ctrl+G.
• Click 'Special'.

Alternatively

• Under Home Tab click 'Find & Select'.
• Click 'Go to Special'.

Comments

You can find radio button associated with 'Comment' is by default selected when you access the Go to special dialogue box.

Click Ok.

The cells in the range with comments will be greyed out.

Constants

This option refers to cells that contain constants.

There are other sub categories.

• Numbers.
• Texts.
• logical.
• Errors.

Formulas

Formula options refer to cells that contain formulas.

There are other sub categories

• Numbers.
• Texts.
• Logical.
• Errors.

By default all are usually checked.
Say if you want to find only formula that relates to numbers check mark only 'Numbers'.

Blanks

This function denotes those cells that are blank within the data range.

It also recognizes only space/spaces within a cell but the cell is also blank. It will not consider this cell while selection. Since it is not blank technically.

Typically what I believe this function recognizes data column wise.

If the datasheet contains any cell that is non contiguous to other cells but has data in it, it will consider entire column of that cell as blank. Observe the exhibit below.

 

Blank Cells greyed out

The cell marked with Red square has spaces, Blank function didn't consider the cell while selecting blank cells. Also the column J has been considered as it has a single cell of data (Binder). Again the column I has been considered as blank as no data in it but contiguous to main chunk of data. 
But columns K, L, M etc are not considered.

Current Region

Current Region here denotes the entire list or data.

Click any where in the data range and select Current Region, excel will grey out the entire data range. Very effective way to select a large piece of data.

Current Array

It will select entire array (we will discuss array in later posts) if active cell is contained in an array.

Objects

Go to function with 'Objects' selected denotes graphical objects including charts and graphs in the worksheet.