In this tutorial we’re going to go through all the various options that Excel offers with the Go To Special command. This is one of my favorite options in Excel and I use it practically every day. So take a few minutes and you too will see all the benefits of Go To Special in Excel.
You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.
Go To Special is a great tool that Excel offers that allows you to find various items on your worksheet. You can access this tool in a few ways. One is by going to the Editing group on the Home tab and selecting the down arrow on the Find & Select command:
Another option is to hit the F5 key then use the keyboard shortcut Alt + S and this dialog box will pop up:
In this write-up I’ll explain each of the items and what they can do. To see actual examples of these options in action, take a few minutes and watch the video. I think you’ll find it worthwhile.
Comments: This option will highlight all the comments you have on your worksheet.
Constants: This option will highlight all the cells that have fixed numbers or text in them. It will not highlight any formulas or blank cells.
Formulas: This option will highlight all the cells in your workbook that contain formulas. You can filter down to the type of formulas you want to highlight in the four options below the Formulas option.
Blanks: This option will highlight all the blank cells in your worksheet from cell A1 to your “last cell” (see below), or the blanks just in the range you have highlighted.
Current region: This option will highlight the current region in which your active cell is located. I think you’d be better off using Ctrl + A or Ctril + * to do this.
Current array: This option will highlight the entire range of cells that contain an array formula. This is a very rarely used option for me, if at all.
Objects: This option will highlight all the objects on your worksheet such as images, charts, checkboxes, etc. This can be useful if you want to delete all the objects on a worksheet, or maybe move all the checkboxes at one time to a better location in their cells.
Row differences: This is an excellent way to compare two columns of data, row by row, including text, to find any differences that may not be visually evident.
Column differences: Same as above but comparing two rows, column by column.
Precedents: This will do the same thing as the Trace Precedents option on the Formulas tab for the formula in the selected cell, but highlights the cells rather than drawing arrows.
Dependents: Same concept as Precedents but with Dependents.
Last cell: This option will highlight only the last cell in your worksheet that has any data or any type of formatting. This also includes any cells that had data but you may have deleted.
Visible cells only: This option will allow you to perform various actions on cells that are visible only and will skip those in hidden rows or columns.
Conditional formats: This option will highlight all the cells in your worksheet that contain any type of conditional formatting.
Data validation: This option will highlight all the cells in your worksheet that contain Data Validation drop down lists. The “All” option will highlight all of them, while the “Same” option will only highlight any Data Validation drop down lists that are exactly the same as one you have selected.
Again, all these options can be very useful when trying to locate various items in your worksheet, and can save you a lot of time. Take some time to watch the video to see how each of these actually works.
What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!