Years ago, when I was novice to excel, I filtered some data in excel and copied. But when I pasted the data into a different sheet for further analysis, I observed I pasted complete set of data instead of just the filtered data. It was some thousands of rows. I was disappointed thinking I have to copy each row of data one by one. My team manager observing me from a corner (May be he was writing some points for my appraisal looking at my behaviour on floor!!) walked up to me and understood my problem. He kept his hand on my shoulder, pressed F5 on keyboard and clicked on “Special…” button when he saw “Go To” dialog box. This is the first time when I saw “Go to Special” dialog box in Excel. Thanks to my boss for showing me this technique and Microsoft for giving this option in Excel. This option in excel we are exploring today.
On a day to day basis, we might come across various scenarios where we want to select a specific set of data in a given large set of data based on various criterions. Go to special gives us that option to select data based on various criterion.
“Go to special” gives us 15 criterion's to select data. I’ll be posting this as a series. In each series, I’ll be exploring one criterion.
Navigation/keyboard shortcut to get to “Go To Special”
Excel 2003 navigation: On toolbar, click on Edit and scroll down to click on “Go To”. See a dialog box like below. Now click on “Special” button highlighted on yellow in the below snapshot to see the “Go To Special” dialog box.
Excel 2007 navigation: On the extreme right-hand side of the ribbon you see Find and Select option and click on it to see “Go To Special” like in the below snapshot. Now click on “Go To Special” to see the dialog box.
Keyboard shortcut in Excel 2003 & 2007: Ctrl+G Alt+S or Press F5 and Alt+S. You will see the dialog box like below. This is the starting point for our exploration.
Below is a snapshot of Go to Special screen.
Select Comments:
By the way, for those who are not aware of inserting a comment in a cell... Right click on the cell you want to insert a comment and click on ‘Insert Comment’. You will get a note like below and you can start typing your comment there.
Well, now that you know how to create a comment, right click on the same cell where you inserted a comment and explore ‘Edit Comment’, ‘Delete Comment’ and “Show/hide Comment’.
Ok, back to selecting comments, one might want to select cells that have ‘Comments’ for further analysis or processing. In a large sheet of data one want to figure out if there are any comments at all or select all cells that have comments and delete all the comments or colour the cells with comments or may be delete all the rows or columns that have comments.
Go to “Go to Special” and click on the ‘comments’ radio button and click ‘Ok’. If you have comments in your worksheet, you will by now observe that all cells that have comments are already selected.
Extra Tip: While the cells with comments are selected, press ‘Tab’ key on keyboard to travel from one cell to another. This is pretty useful when you want to choose one in your selection for your next action. Do you know? This is my favourite pastime! I select a range and hold ‘tab’ key to see how it is moving among the cells. When I get bored, I hold both ‘Shift’ and ‘tab’ keys together!! This clearly means I’m exhausted and I need a good break.
Keyboard shortcut:
Press Ctrl+Shift+O to select all cells that contain comments.
Personally, I use this select comment to know if there are any comments on the worksheet. I know people insert comments to leave some important information about the data which might be useful for further processing. So, I usually do not miss doing this step.
Share your experience:
Do you use this option more often? Share your experiences, how do you deal with this type of data. Have another tip do this stuff? Also, do not forget to leave your comments on this post. I learn a lot from you all.
I’ll be posting soon, second post of this series “Go to Special – Constants”.
No comments:
Post a Comment