Go To Special series. Select Objects

Go To Special series. Select Objects

Click here to read to navigate to go to special.

In my current job, I work a lot on SAP BW. When I export a BW report to Excel, I find quite a lot of objects. Annoyingly enough, Excel processing slows down (Almost dead!) because of these numerous objects in the file. So, first thing I do after I export a report to excel from BW is select all the objects in the file and delete.

What are Objects in Excel?

Below picture I made to define objects in Excel.

Go To Special Series. Select Current Region and Current Array

Go To Special series. Select Current Region and Current Array

Happy "Dusserah" to all the readers. This Dusserah, may you be blessed with good fortune, Wealth and prosperity, Happiness. Wish you and your family a fun filled Dusserah!

Welcome to the fourth post of this series – Select current region and current array. Click here to read to navigate to go to special.

Define Current Region

The range of cells filled in with data and including the current selected cell or cells. The region extends in all directions to the first empty row or column. Selecting a current region is selecting an entire list of data.

Keyboard Shortcut: Press F5, then Alt+S+R or simply Ctrl+A or SHIFT + CTRL + SPACE bar

Go To Special Series. Select Blanks

Select Blanks:

Welcome to the third post of this series – Select blanks. Click here to read to navigate to go to special and to select comments or Click here to read to select constants and formulas.

Why Select Blanks?

I often use this option in Excel and I sincerely thank Microsoft for having this option in Excel. I must admit I hate to see blank cells in my data. It is quite common that we see blank cells in a large set of data, especially for people who download data from web and people who get a pivot table paste as values as his input for further processing. One might want to get rid of blank cells or put a constant (Sometimes, I make blank cells as zero) or fill the blank cells with a common formula etc.

Bear in mind to select a range of cells while selecting blanks or else by default excel selects all blanks in the used region. I mean by used region, from A1 to the last used cell.

Navigate to Go To Special - Blanks

Click here to learn navigation.

Keyboard Shortcut: Press F5, then Alt+S+K.

Go To Special series. Select Constants and Formulas

Select Constants and formulas:

Welcome to the second post of this series – Select constants and formulas. Click here to read to navigate to go to special and also to select comments.

What are constants and formulas?

Well, constants are values that are unchanging in nature. They are not dependent on any other constant or variable.

Formulas on the other hand are derived based on the value of referred cells. Formulae copied and pasted as values are constants as well for selectionJ. Having said that, let’s explore the options to select constants and formulas by using “Go to special”.

Data types of constants and formulas can be number, text, logical and error.

Number can be any numeric value

Text can be any text (string) value.

Logicals are TRUE and FALSE.

Errors are all the error types in excel. #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, and #VALUE!.

Tip: If you select a range and then go to special and select your criteria, it selects the criteria only from your selected range.

Go To Special Series. Select Comments.

Welcome to "Go to special" series of posts. Let’s explore “Go To special” and select Comments option in excel today.

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.

Duplicates in data? Excel is a better option!!

Here’s my first post for my blog. I’m so excited.

I’ve chosen this topic “Duplicates” as my first topic. It is quite common that we work with data that has duplicates. It is not always common how we treat duplicates each time we come across. Thankfully Excel has inbuilt tools and functions to figure out duplicates. I want to share the ways I use to deal with them.

There are times when you want to:

            1.  Identify duplicates in data

            2.     Keep only unique data

Explore Excel

Hi All,

Welcome to the board!

"Explore Excel" is what I've decide my blog name is. This is the place where I want to share some of the excel tricks and techniques I use. This is the place where I want to learn tricks and techniques from you all power excel users.

This is the place where we together "explore excel".

Cheers,
VaraK
"Explore to Excel, Excel to Explore"