Excel Go to Special command has a special focus in the industry, do you know why..?
As the name suggests, the user can quickly select a cell or a group of cells based on a preferred condition.
For example, if you want to quickly see all formula cells in your workbook then your ideal option would be “Go to Special”.
It’s not just limited to formulas, you can also use it for finding blanks, Comments, Errors, Conditional formatting, Data validation etc.
By the end of this tutorial, you should be able to understand how to use Excel Go to Special command, and you should be comfortable using various options too.
So let’s dive in.
Basics: Excel Go to Special Command
To launch Go to special in excel 2010 → Click on Home tab → Find & Select → From a drop down select Go to special.
You can also use excel keyboard shortcut: Ctrl + G combination → click on Special.
1) Comments
This option is highly useful to select and highlight all the comments in a worksheet. Often times you can see comment cells are marked with a red tip, but it’s really hard to select each and every cell in case if you want to delete them from a worksheet.
To launch: Goto Special → select comments → click on Ok
2) Formulas
In general excel workbook is a combination of Text, Numbers, Formulas, Graphs & logics. But when it comes to analysis sometimes you will have to identify only number cells or only formula cells.
i) To select only numbers you can use Goto Special → Formulas → Select only numbers
ii) To select only Errors you can use Goto Special → Formulas → Errors
Similar to the above you can also use “Text” & “Logicals” options
3) Conditional Formats
Conditional formatting is a great way of presenting data. However, sometimes you need to identify those cells for editing/rebuilding logics.
To select cells with conditional formatting: Goto Special → Select Conditional formats
4) Data validation
Data validation is a great option for building excel dashboards. This option is highly useful to restrict users from entering unwanted data.
For example, if your column header says telephone number, then you can restrict users to enter only numbers. We can also use it for creating dropdowns as well.
Although we have many advantages, there a big concern always troubles excel users. The issue is when you apply data validation in your workbook, it’s hard to differentiate validation cells & normal cells.
Often it’s very frustrating to see validation errors, so by using goto special feature you can easily identify validation cells then you can take necessary action.
To identify data validation cells: Goto Special → Select Data validation → Click ok
5) Blanks
If you regularly use Pivot table, then you might have used “Blanks” option at least once in a while. If you haven’t, then no issues, I’ll show you how to use.
For example, we have a pivot table like below with sales data by Customer Segment & then by Product Category. If you want to convert below pivot table into to a normal table you can do that by using paste special option.
However, the condition is you don’t want to have any blanks in Customer segment column.
To do so, you can copy entire pivot table and do paste special values.
Select entire table → Goto Special → Select “blanks” → Click ok → then click on Equals (=) button → click on Up Arrow → now Ctl+Enter.
Tip: Make sure to select entire table again and do paste special to remove all the formulas.
So, your table now should look like below.
Conclusion:
Whether you want to find formulas in your workbook, or you want to correct errors or you may even try to fill blanks, in any given case Goto special option will make your life easier.
Go to special option in excel is a great feature, yet many of us don’t use it regularly. You may have many reasons, but after reading this tutorial you should be able to use it flawlessly.
Last but not least, if you have already used Excel Go to special command then which option you’d regularly use..? Please comment below.