Excel Tip of the Week: Go To Special

Today’s tip is using Go To Special.

You might remember how I referenced the Go To function last week. I didn’t go into an explanation of the function because the function is a bit self explanatory. But like a lot of things in Excel, there is a deeper level to this function.

Deeper and deeper…way down

When you call up the Go To function (With Ctrl+G or F5) you will see a dialog box that looks like this:

Notice the “Special…” button in the bottom left. If you click on this button (or use the keyboard shortcut Alt + S) a host of options will be opened. Observe:

Here is the set up for an example: Pretend you’ve created a spreadsheet with a mix of constants and formulas. You got a little too excited in making such a cool spreadsheet (who doesn’t though?) and you forgot to change all the formulas to blue (a really good practice to use, especially if you’re sending your spreadsheets to other people) Now you have to go back to each cell, check if it is a constant or formula and then change the text color.

NOT! (I’m going with 90’s comedies this week)

Here is how to accomplish the task with the Go To function:

1)      Download the example spreadsheet here and then open it
2)      Ignore the numbers, they are unimportant. Just know we have a mix of formulas and constants in this spreadsheet.
3)      Ctrl+G or F5 to open the Go To function
4)      Click the “Special” button or hit Alt+S
5)      Select Formulas or just type the letter “f”
6)      Click “OK” or Hit “Enter”
7)      All the formulas should be highlighted now
8)      Change the text color to blue

With just a few keystrokes you’ve selected and changed the text color of 24 different cells. Great success.

Keep in mind this is just one example of how to use this feature. I’ve also used it to select a ton of objects at once and also fill in blank cells quickly.