In the earlier article, we explored a new method of learning.

In this article, we will find practical scenarios where the learning about Go To Special can be applied.

Estimated reading time 12 min

Recap

In the previous two articles, we saw how to understand the items in dialogs and menus and apply them to our needs.

In this article we will perform the same proce3ss with Go To Special dialog in Excel.

The need for Go To

Excel is a very large grid. Each sheet contains one million+ rows and 16000+ columns. Although we don’t fill all that space in every workbook, data can be quite large.

Therefore, navigation becomes a problem. Scrolling endlessly is not the solution. Therefore, Excel provides a GOTO feature. CTRL G or F5 are the shortcuts. Here it asks you the cell or range. Type the cell address and press ENTET to go there. If you type a range like B4:Z10, the entire range is selected.

Although technically correct, not very useful – because we never remember exact location of cells in all the files we handle every day.

Of course, if you have defined named ranges, we can type the names here. But there is a simpler method to go to named ranges and tables – open the drop down on the left side of formula bar.

In short, GOTO itself is of limited practical use. (Actually not. But that requires another article to explain it).

But there are special cases where you may want to select specific cells – WITHOUT knowing their exact addresses. Those options are shown in the Special… dialog.

Go To Special = Select some cells with specific properties

Select cells with COMMENTS

This one is easy. But WHY would you like to do so? The only practical reason I can think of is to DELETE all comments in one single operation.

Why do we need it ? Because DELETE ALL COMMENTS option is not there in the default ribbon!

Constants and Formulas

These options give you a very quick way to select all cells containing values (also called Constants) or formulas. Why would you like to do that?

Ideally all cells where data can be entered should have a standardized formatting. By selecting all cells containing values – consistent formatting can be applied in just ONE action. Ideally you should use the ready-made style called Input for all data entry cells.

Similarly, the style called Calculation should be applied to all formulas. This way, everyone in your team / organizations knows where formulas are. It also makes the workbooks look more professional and easier to troubleshoot.

Blanks

Selecting all blanks may sound like a futile activity. But it is not.

Selecting blanks can be used to clean up unwanted rows in reports.
Read the article – Data Clean-up: System Reports – for details.

It can also be used for filling in gaps in data. Read this article for details:
Bad Data to Good Data: Pivot Copy Paste

Current Region

This is equivalent to CTRL A – select all command in Excel. It usually selects all surrounding cells. Remember that CTRL A is a multi-level command. First time it selects the local block of data – Current Region. Pressing it again selects the entire sheet – without disturbing the active cell. This is very useful in correcting global errors like Number stored as text. Read this article for details: Green Marks Part 3: More error handling

Row and Column Differences

For example, there is an option called SELECT Row Differences. Even if you understand the meaning, it is still not making sense in the context of Excel. Now you will have to do some experimentation to understand this option. Here is data in 5 rows and 2 columns. All the cells are already selected.

Now choose GOTO (CTRL G) – Special – Row Differences and see what happens.

Excel highlighted the rows where the values were DIFFERENT.
ROW DIFFERENCE now makes sense! Column Difference is similar but in other direction.

Precedents and Dependents

These are same as the Trace Precedents and Trace Dependents options. The difference is that the cells are actually SELECTED. Direct means only one level. All Levels means all the related cells on the same sheet (selection cannot be done across sheets).

We will cover this in another article soon.

Last Cell

This is the last used cell. Excel stores data from First Cell (which is always A1) to the Last Cell. CTRL END is the shortcut to go to the last cell.

In some sheets, the last cell is at a very far away position – which increases the file size unnecessarily. When you encounter files with small amount of data but very large file sizes, check the last cell in each sheet and remove unwanted rows and columns. Save and close the file and reopen it. This should reduce the file size dramatically.

Visible Cells Only

If you copy data which contains hidden rows or columns, pasting it pastes all cells – even the hidden ones. This is because the COPY operation does not bother about hidden-ness.

If you only want to copy the visible cells – first select as usual – then Go To Special – Visible Cells only and then choose COPY. Now only visible cells are pasted. Very useful.

Conditional Formats and Data Validation

These options select cells containing similar conditional formatting or data validation if you choose SAME option. If you choose ALL option, every cell containing Conditional Formatting or Data Validation is selected. Very good for clearing all. Or if you use SAME option, also useful for modifying the formats or validation settings quickly.

What Next?

We learnt how to understand the often cryptic or confusing options in menus and dialogs. Use this knowledge and explore a new menu or option everyday. Trust me you will thank me for this suggestion. Don’t forget to share your new learning with your colleagues and friends.

Enjoy!

***