Explore Excel

Quick Wins: Excel Keyboard Shortcuts

noreply@blogger.com (VaraK) — Wed, 05 Jan 2011 17:10:00 +0000

I have finally compiled a list of excel shortcuts few I knew and few collected from various Excel websites.

To download the file Click here

In total, I was able to collect keyboard shortcuts for 98 excel activities. I'm sure these save quite a lot of time and effort. But, I'm not happy.
I'm sure there would be some shortcuts am not able to collect. If you know any excel shortcuts that I have not captured, please feel free to put that shortcut in the "Comments" section below. I promise I'll keep this as a live document. Everytime I find a new excel keyboard shortcut, I'll update.

Keep coming back to this post to check new excel shortcuts. Go, take a shortcut now!! Happy learning.

As usual, share your comments and opinions about this post.

Quick Wins: Shortcut to type multiples of 10 in Excel

noreply@blogger.com (VaraK) — Mon, 03 Jan 2011 18:27:00 +0000

Wishing you all a very happy and prosperous new year 2011.

Starting this year, I’ll start sharing quick tips under the head “Quick Wins”. Quick wins that save time and effort and importantly easy to learn and execute.

How often do you need to type multiples of 10 in excel (10 0r 100 or 1000 or 1000000 etc)? If that is multiple times in a day then this tip is for you. I’m sure this will drool you (Unless, you are already aware of it!!!).

Typing just 10 or 100 is easy in excel. But what if you have to type 1,000,000? (A million, a billion or greater than that).

How easy is it to type 1E6 instead of 1,000,000? 3 letters instead of 7! Explanation below

1E6 simply says 1 followed by six zeroes (which is a million!).This is the scientific format of numbers in excel. Just some examples below.

To Type	Quick Way
10	1E1
100	1E2
1,000	1E3
10,00,000	1E6
1,00,00,00,000	1E9
50,000	5E4

What are you waiting for? Champion this tip and save some effort and time J

Share your experience:

Do you use this option more often? 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.

Awaiting New Year - 2011

noreply@blogger.com (VaraK) — Thu, 16 Dec 2010 17:33:00 +0000

I'm already stressed out this year. Already awaiting new year and started to count down. For all who are already exhausted like me, I created a file that counts down days, hours minutes and seconds to step into new year.

Just a preview of the file I created.

What are you waiting for? Go ahead click here and download the New Year Count Down file. I created this in Excel 2007. So, you can open thisin Excel 2007 and above. But if you are in Excel 2003 and you have a translator you can still use this.

Wishing you a very happy and prosperous new year in well advance.

Few techniques I used to create new year count down file are:

1. Downloaded a greeting card from the web and crop it to my requirements and made a Jpeg file.

2. I then went to use that Jpeg as background of my excel file. For those who doesn't know how to get colorful touch to your personal excel files here is the tip.
In Excel 2007, Click Page Layout on the toolbar. Click on Background. Browse and select the photo or wallpaper and insert to your file as background. One limitation to this is we will not be able to print the background.

3. I then sat to write little VBA code to calculate time from now to Start of new year. I did not locked the code or worksheet.So, feel free to check out the code I wrote.

By the way, do not forget to press Ctrl+F4 to close the workbook.

Hope you like it and most importantly await new year. Have another tip do this stuff? Also, do not forget to leave your comments on this post. I learn a lot from you all.

Go To Special series. Select visible cells only

noreply@blogger.com (VaraK) — Wed, 15 Dec 2010 17:08:00 +0000

Welcome to the nineth post of this series. Hope you are enjoying this series. Click here to read to navigate to go to special.

What are visible cells?

How often you filter the data in your spreadsheet? How often you hide some rows or columns from your data? I know your answer. Quite a number of times...Correct? If your answer is yes then read on and answer some more questions patiently.

How often you want to select and copy only the data you filtered and how often you want to select and copy only the rows / columns that are visible (excluding the hidden rows and columns)? This option is for you if you have failed copying this data and ended up pasting the whole set of data instead of only the filtered / visible data.

When you filtered data it means you have hidden the data that is not required to you. When you are selecting data when filter is on, then it means you are also selecting the data that is hidden in the range you are selecting. So, first we need to select the visible cells only.

Select the cells that you want to copy. Go to special. Select visible cells only. You are now only selected the visible cells. Simply copy the data and copy to the location you like and perform further analysis.

Keyboard shortcut

Ctrl+g, Alt+s+y or

Alt+;

Share your experience:

Hope you are enjoying this series.

Do you use this option more often? Share your experiences. 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, tenth post of this series “Go to Special – Select Data validation and Conditional formats”.

Links related to this series:

Go To Special Series. Select Comments

Go To Special Series. Select Constants and Formulas

Go To Special Series. Select Blanks

Go To Special Series. Select Current Region and Current Array

Go To Special Series. Select Objects

Go To Special Series. Select Row and Column Differences

Go To Special Series. Select Precedents and Dependents

Go To Special Series. Select Last Cell

Go To Special series. Select Last Cell

noreply@blogger.com (VaraK) — Wed, 15 Dec 2010 17:03:00 +0000

Welcome to the eighth post of this series. Hope you are enjoying this series. Click here to read to navigate to go to special.

Select Last cell. Cell that has data and / or formatting. This is one of those most used excel option people often use.

Most often we need to go to the last cell in the worksheet we are working.

If the data is within the visible range we can simply select the cell using a mouse. This is not the case most of the times as we would be working on worksheets with more than 10,000 rows? 25,000 rows? Or more? And you want to move to the last cell of the worksheet for any reason! Here is when this option comes handy.

Traditional way of this is go to Special, Click on Last cell. You will automatically be placed in the last cell of the worksheet that has data or formatting. But, who has the time to go to special and do all the selection. Hence keyboard shortcut below.

Keyboard Shortcut:

Ctrl+End.

Just an additional keyboard shortcut I thought would go well with the above one. To select the range from the active cell to the last cell. Ctrl+Shift+End

Share your experience:

Hope you are enjoying this series.

Do you use these options more often? Share your experiences. 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, nineth post of this series “Go to Special – Select Visible cells only”.

Links related to this series:

Go To Special Series. Select Comments

Go To Special Series. Select Constants and Formulas

Go To Special Series. Select Blanks

Go To Special Series. Select Current Region and Current Array

Go To Special Series. Select Objects

Go To Special Series. Select Row and Column Differences

Go To Special Series. Select Precedents and Dependents

noreply@blogger.com (VaraK) — Tue, 02 Nov 2010 17:57:00 +0000

Welcome to the seventh post of this series – Select Precedents and Dependents. Hope you are enjoying this series. Click here to read to navigate to go to special.

What are Precedents and Dependents?

As we all know that a result of a formula or a function is derived using a single or a set of variables and / or constants. In short, result of a formula is an outcome of one or more constants or variables. Precedents are the inputs used to get the result. Dependent is the result itself. What does it mean? The active cell must be a formula. Yeah, I know, that’s obvious. But I wanted to mention..Just in case…

Say for example, A = 1, B = 2 and C = A+B = 1+2 = 3.

In this example, A and B are the Precedents. The value of C is dependent on A and B. Hence C is dependent.

Isn’t it quite possible that A and B are also dependent on some other variables and or constants? Yes, you got that right! It is quite possible. That is why Excel gave us an option of selecting Precedents and Dependents that are Direct only and All Levels.

First things first

When you have to find the precedents / dependents you need to first select the cell to which you need to select precedents or dependents.

Example

Below is the data you can download to excel and play around with this options. I have also shown the formulae used in Table 3 and Table 4.

Prec-Depen

	A	B	D	E	G
1	Table 1
2	Sales	Amount
3	Product 1	10000
4	Product 2	15000	Table 3		Table 4
5			Profit	Amount	Total Profit
6			Product 1	2000	10000
7	Table 2		Product 2	8000
8	Cost of Goods Sold	Amount
9	Product 1	8000
10	Product 2	7000

Excel 2007

Worksheet Formulas

Cell	Formula
G6	=E7+E6
E6	=B3-B9
E7	=B4-B10

I used the above example for easiness. Total profit is calculated for a company that sells two products (Product 1 and Product 2). We have sales for both the products in Table 1 and cost of goods sold in Table 2.

Most of us know that profit is Sales minus cost of goods sold. Using this formula I calculated profit for both the products in Table 3. Direct only Precedents for Table 3 are Table 1 and Table 2 as we are using Sales from one table and cost of goods sold from another table to arrive at the profit for each product. There are no precedents to Table 1 and 2. Hence, all levels precedents and direct only precedents are same for Table 3.

Last step, Table 4 is total of profit calculated for each product i.e. total profit of the company. Direct only precedent for table 4 is table 3 as total profit is the sum of profit of the two products. All levels precedents are Table 1, 2 & 3 like you see in the above visual.

For Product 1 sales (In Table 1) and cost of goods sold (In table 2) direct only precedent is Product 1 profit (In Table 3). All levels dependents are Product 1 profit (In Table 3) and total profit (In Table 4). Same is the case with Product 2.

Note: Selecting Dependents and Precedents via Go to Special will not enable you to select the referenced cells from different worksheets. This will only select the cells referenced from active worksheet. Only exception is read the Keyboard shortcut for direct only select precedents.

Keyboard Shortcuts:

1. Select Precedents:

a. Direct Only: Ctrl + [ or Press F5, Alt + S + P (Direct Only is by default selected). This shortcut will take to a cell referenced from a different worksheet. But the limitation is, if we have multiple cells, it only selects the first referenced cell. Say for example, Sheet1! A1 = Sheet2! A1 + Sheet2! B1 then this will only select cells Sheet2! A1. Instead, if Sheet1! A1 = SUM(Sheet2!A1:B1) then it selects both the cells.

b. All Levels: Ctrl + Shift + { or Press F5, Alt + S + P + L

2. Select Dependents:

a. Direct Only: Ctrl + ] or Press F5, Alt + S + D (Direct Only is by default selected).

b. All Levels: Ctrl + Shift + } or or Press F5, Alt + S + D + L

One limitation is we will not be able to find the dependents from a different worksheet. When you try to select dependents from a different sheet, annoyingly, it says “No cells were found”.

Alternative to Selecting Precedents and Dependents:

Yes, there is an alternative to selecting precedents and dependents. It is through formula auditing. We have trace dependents and precedents. Honestly, I prefer formula auditing method to selecting precedents and dependents. The reason is that we talked about the limitation of select precedents and dependents above. This can be easily performed using trace precedents and dependents.

Navigation

Excel 2003: Toolsà Formula Auditing. Then select on trace dependents or precedents as required.

Excel 2007: Select Formulas on the ribbon. Under the formula auditing toolbar click on trace dependents or precedents as required.

Keyboard shortcut:

Trace Precedents: Alt + T + U + T

Trace Dependents: Alt + T +U + D

Note: When you click once on Trace Precedents or Trace Dependents you will see Direct Only precedents / dependents. Click twice on trace precedents or dependents to see All Levels of precedents / dependents.

When you trace precedents or dependents you will see arrows on the worksheet directed towards the dependents or precedents. When there is a dependent or precedent in a different worksheet all you need to do is trace precedent or dependent as required. An arrow is directed towards an object that looks like a spreadsheet. Double click on the arrow and you will see Go To screen with referenced cells from different worksheets. Select the reference that you want to select and double click or just click on OK button below.

A screenshot below portrays the above explanation. I used the same example to select precedents and dependents (with 4 tables). The only change I made is I kept Table 1 and Table 2 in a different sheet.

Keyboard shortcut to remove arrows: Alt + T +U + A

Both the options are useful and handy in excel. It is just that user needs to use the options to suit their requirements.

Share your experience:

Hope you are enjoying this series.

Do you use these options 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, eighth post of this series “Go to Special – Select Last cell”.

Links related to this series:

Go To Special Series. Select Comments

Go To Special Series. Select Constants and Formulas

Go To Special Series. Select Blanks

Go To Special Series. Select Current Region and Current Array

Go To Special Series. Select Objects

Go To Special Series. Select Row and Column Differences

Go To Special series. Select Row and Column Differences

noreply@blogger.com (VaraK) — Thu, 28 Oct 2010 19:30:00 +0000

Welcome to the sixth post of this series – Select row and column differences. Hope you are enjoying this series. Click here to read to navigate to go to special.

I’m sure this is one technique that will be useful for auditors, reviewers, quality checkers (all these are one job but different tags called in different companies. There might be some more which I’m not aware!). In the companies, I worked before there used to be one preparer and one reviewer (for a four-eye check or a quick sense check before the report is sent to the stakeholders. Reviewers job is to make sure the values are correct as per the inputs received and all formulas are correct and end result makes sense. This is one technique I adopted when I used to review the data.

Row differences:

Row differences select cells that are different to active cell from all the cells you selected in a row. By different, I mean cell values if they are constants or the formula differences if the cells contain formulae. When one selects a range of cells from a row one need to be careful in selecting the active cell. By default, excel selects the first cell as active cell. To change the active cell in the selection press Enter or Tab. Ensure you have the correct cell activated after which you wanted to find the differences in a row.

If more than one row is selected, the comparison is done for each individual row of that selection, and the cell that is used in the comparison for each additional row is located in the same column as the active cell.

Keyboard Shortcut: Press F5, then Alt+S+W

Below is a random data I picked to illustrate the same.

This is a gross profit calculation for four fruits sold in a market per week (I’ve shown data for two weeks). Gross profit is Sales minus COGS (Cost of goods sold). In this example, for week 1, gross profit for Apple should be C25-C26 (=50). Think as a reviewer! Check the formula in cell C27. It should be C25-C26 (Sales – COGS). If it is fine, select row 27 in this example from columns C: F. Press F5, then Alt+S+W. You will select the differences in row due to formula. The selected cells are wrongly calculated cells. (Now it’s your duty to mark your preparer an error! Well, if you do not want to screw him just let him know about the error. Am just kidding!!)

Check below the table and formulas for gross profit in table. If you rightly audit the formulae, you will observe gross profit for Grapes and Orange is not calculated using sales and COGS of Banana and Orange respectively (E27 shows D25 – D26 where it has to be E25 – E26, and F27 shows E25-E26 where it has to be F25 – F26). Same is the case with gross profit for week 2 for Banana (Cell D30). Formula was carried forwarded from apple gross profit calculation.

R-C diffs

	A	B	C	D	E	F
23
24			Apple	Banana	Grapes	Orange
25	Week 1	Sales	100	200	400	200
26		COGS	50	100	200	100
27		Gross Profit	50	100	100	200
28	Week 2	Sales	80	120	1000	120
29		COGS	40	70	600	70
30		Gross Profit	40	40	400	50
31

Excel 2007

Worksheet Formulas

Cell	Formula
C27	=C25-C26
D27	=D25-D26
E27	=D25-D26
F27	=E25-E26
C30	=C28-C29
D30	=C28-C29
E30	=E28-E29
F30	=F28-F29

Copy the above data and formulas to your excel and play around.

Select range C27:F27 (Active cell is C27), Go to special and select row differences. You will see E27 and F27 are selected as they are different from the row (Formulas pointing to different column unlike the active cell in your selection). Below is a visual selecting the multiple rows and finding the differences.

Apologies: Above visual has crossed the blog area for posting. But I wanted to show this somehow and hence this visual.

This type of data (Very small, isn’t it?) is unlikely in our day-to-day activities. We work on data spreading to large number of rows and columns. This is when select row differences option comes handy. Simply, select the differences in rows colour them, review and correct.

Column differences:

Column differences select cells that are different to active cell from all the cells you selected in a column. In row differences, your selection was row and here your selection is column. Nothing else differs. But, I just want to reiterate on selecting. Select a column. Activate the cell to which you want to find the differences in that column. To change the active cell press Enter or Tab. We can select differences in constants as well as formulae.

Below is an example for Column differences. I have just little tweaked the above example to fit the column differences example!!

R-C diffs

	C	D	E	F	G	H	I
1		Week1			Week2
2	Product	Sales	COGS	Gross Profit	Sales	COGS	Gross Profit
3	Apple	100	50	50	80	40	40
4	Banana	200	100	100	120	70	40
5	Grapes	400	200	200	1000	600	400
6	Lemon	200	100	100	120	70	50
7	Orange	300	150	100	250	150	100
8	Pine Apple	300	150	150	250	150	100
9	Sapota	100	50	150	80	40	40
10	Strawberry	200	100	50	120	70	50

Excel 2007

Worksheet Formulas

Cell	Formula
F3	=D3-E3
F4	=D4-E4
F5	=D5-E5
F6	=D6-E6
F7	=D6-E6
F8	=D7-E7
F9	=D8-E8
F10	=D9-E9
I3	=G3-H3
I4	=G3-H3
I5	=G5-H5
I6	=G6-H6
I7	=G7-H7
I8	=G8-H8
I9	=G9-H9
I10	=G10-H10

Copy the above data and formulas to your excel and play around. It will be interesting to see your comments.

I’ve hardly seen any colleagues using this technique. I found this handy and thought you would find this handy too.

Share your experience:

Links related to this series:

Go To Special Series. Select Comments
Go To Special Series. Select Constants and Formulas
Go To Special Series. Select Blanks
Go To Special Series. Select Current Region and Current Array
Go To Special Series. Select Objects

Go To Special series. Select Objects

noreply@blogger.com (VaraK) — Mon, 25 Oct 2010 14:56:00 +0000

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.

Keyboard Shortcut: Press F5, then Alt+S+B

Usefulness: I select objects for two reasons.

1. 1. Group objects.

2. 2. Quite often (Almost 10 times a day!! May be more than that!!!) I delete objects from my file.

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, sixth post of this series “Go to Special – Row and Column Differences”.

Related Links to this Series:

Go To Special Series. Select Comments
Go To Special Series. Select Constants and Formulas
Go To Special Series. Select Blanks
Go To Special Series. Select Current Region and Current Array

Hope you are enjoying this series.

Go To Special Series. Select Current Region and Current Array

noreply@blogger.com (VaraK) — Sat, 16 Oct 2010 17:49:00 +0000

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

I use this option quite often. The kind of data I see in a day o day basis contains various tables in a single worksheet. So, I need to select the current region rather than complete worksheet. Specially, I use this to either to filter the data or sort the data.

It will be interesting to see your comments and experiences on using this option.

Define Current Array

As per Microsoft an array is used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.

If your active cell is in an array and you want to select whole array then it is easy to select whole of the array.

Keyboard Shortcut: Press F5, then Alt+S+A

To be honest, I have known this option in Excel but was never able to use this. That is may be because I do not operate in area of arrays. Never mind. It’s the time for you guys to tell how often you use this option and in what scenarios.

Share your experience:

I’ll be posting soon, fifth post of this series “Go to Special – Select Objects”. Past few months, I have been using this day in and day out. Thanks to Excel (Microsoft).

Links to other posts of this series:

Go To Special Series. Select Comments Go To Special Series. Select Constants and Formulas Go To Special Series. Select Blanks

Go To Special Series. Select Blanks

noreply@blogger.com (VaraK) — Sat, 09 Oct 2010 09:19:00 +0000

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.

Tricks using select blank cells

1) Delete rows/columns or shift cells left/up: After selecting blanks, press CTRL + -. You can see the dialog box like below.

Now, from the above options choose to shift cells or delete entire row or column. I don’t know how others use it but on an average I work on it on a daily basis. Imagine the type of data I’m working on!!

2) Fill a formula or a constant in all blank cells: I loved it, when I first learn it. I’m sure you would love it too. Assuming, you have data similar to above example. Now you want to fill in the blank cells with a value from the immediate above cell. How do you do that? Here we go, select a range and select blank cells (You know how to select blank cells now!). Now, type = and select the above cell (Shown below in the example) and press CTRL+Enter. It’s magic. You are done and you have successfully filled all the blank cells with the value of cell from it’s immediate above cell.

Now, instead of formula you want to put a constant. Say for example, I want all the blank cells in my range to be “VaraK”. Easy, select blank cells. Type “VaraK” and press CTRL+Enter.

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? I’Also, do not forget to leave your comments on this post. I learn a lot from you all.

I’ll be posting soon, fourth post of this series “Go to Special – Current Region and Current Array”.

Links Related to this series:

Go To Special Series. Select Comments
Go To Special Series. Select Constants and Formulas

Go To Special series. Select Constants and Formulas

noreply@blogger.com (VaraK) — Mon, 04 Oct 2010 03:00:00 +0000

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.

Select Constants and formulas.

In the below example, I have four employees and their details of salary, bonus and their contract type (Boolean, Permanent or not). Our company policy, at the year end permanent employees get 15% on annual salary as bonus and contract employees get a fixed amount of 100,000.

In this example, Emp Id’s are constants. Emp Name is formula based (Vlookup driven from the mapping table). Salary is constant and bonus is 15% on salary for permanent employees and a fixed amount of 100000 for contract employees. Below screenshot to show what are formulas and constants.

Extra Tip: Press Ctrl+` to get to this view (to see all formulas).

Excel Shortcut: Press F5, Alt+S+O to select constants. Press F5, Alt+S+F to select formulas.

When you click on constants or formulas on the go to special dialog box, you will see four check boxes (Numbers, text, logicals and error) are activated. Now select the check box for the data type you want to select. If not, by default, constants of all data types are selected.

Share your experience:

I’ll be posting soon, second post of this series “Go to Special – Blanks”.

Go To Special Series. Select Comments.

noreply@blogger.com (VaraK) — Thu, 30 Sep 2010 17:56:00 +0000

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.

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:

I’ll be posting soon, second post of this series “Go to Special – Constants”.

Duplicates in data? Excel is a better option!!

noreply@blogger.com (VaraK) — Wed, 22 Sep 2010 20:34:00 +0000

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

1. Identifying duplicates in data

1.1. Using Countif function

Just as an example, in a list of names I want to know how many names are repeated. Here is how I use countif.

Below table starting from cell A1.

Names

Praveen

Raju

Amar

Murali

Narendar

Saravana

Praveen

Here is the countif formula in column B to know the duplicates. In cell B2 type formula =COUNTIF ($A$2:$A$8, A2) and copy it down till you have your data.

Names	Duplicates
Praveen	=COUNTIF($A$2:$A$8,A2)
Raju	=COUNTIF($A$2:$A$8,A3)
Amar	=COUNTIF($A$2:$A$8,A4)
Murali	=COUNTIF($A$2:$A$8,A5)
Narendar	=COUNTIF($A$2:$A$8,A6)
Saravana	=COUNTIF($A$2:$A$8,A7)
Praveen	=COUNTIF($A$2:$A$8,A8)

Now the result looks like:

Names	Duplicates
Praveen	2
Raju	1
Amar	1
Murali	1
Narendar	1
Saravana	1
Praveen	2

Now you know where the duplicates are. In addition, one can also do a conditional formatting to colour the duplicate names. We will discuss conditional formatting in next section.

1.2. Using a pivot table

Using the example from above.

Names

Praveen

Raju

Amar

Murali

Narendar

Saravana

Praveen

Short cut in excel ALT+D+P takes you to Pivot table wizard.

Navigation in excel 2007: Select your data in the worksheet, click Insert on the toolbar, PivotTable.

Navigation in Excel 2003: Click Data on the toolbar, PivotTable and PivotChartReport.

Choose where you want to put the Pivot table report and click finish. Add “Names” field to row labels and also to data area / labels to show the count.

Row Labels	Count of Names
Amar	1
Murali	1
Narendar	1
Praveen	2
Raju	1
Saravana	1
Grand Total	7

Now you know where the duplicates are. In addition, one can also do a conditional formatting to colour the duplicate names. We will discuss conditional formatting in next post.

1.3. Conditional formatting

In Excel 2003, select all your data. In our example, it is A1:A8.

Click Format on toolbar and Conditional Formatting. Set the dropdown to Formula is and copy/type this formula next =COUNTIF ($A$1:$A$8, $A1)>1. Now choose the format you like and click Ok. You will see the duplicates are formatted.

In Excel 2007, There is a simple option instead of typing a formula (You can still use the formula but there is an easy option) to figure out duplicate values in the range. Here we go, select the range, Click Home on the toolbar. Under Styles, click on Conditional Formatting, Highlight Cells Rules, Duplicate Values.

Select Duplicate in the duplicate/unique dropdown and choose the format you like in

the Values with dropdown and click Ok.

I’m sure subtotals are also a way but that is no easy option compared to any of the

above. We need to sort the data first and do the subtotals to know the unique values

and count of duplicates next to it. Let me know if any of our readers use sub-totals for

this purpose. If yes, then why?

2. Unique data

From the above techniques, Pivot table is one way that gives unique values. So, am not including that technique again.

2.1. Advanced Filter

Shortcut in Excel ALT+D+F+A

In Excel 2003, select Data, click on Advanced Filter and choose List range (Your

data range), click

on radio button Copy to another location , check the Unique

records only check box and choose

where you want to copy unique values to.

P.S: This option assumes you have headers. Hence first row is not taken as data in the list you choose.

P.P.S: If you do not choose Copy to another location radio button, then it filters and shows only unique values.

In Excel 2007, select Data on toolbar, click on Advanced and choose List range

(Your data range), click on radio button Copy to another location , check the

Unique records only check box and choose where you want to copy unique values

to.

P.S: This option assumes you have headers. Hence first row is not taken as data in the list you choose.

P.P.S: If you do not choose Copy to another location radio button, then it filters and shows only unique values.

2.2. Remove Duplicates

This option is in Excel 2007. Not sure with Excel 2010 (Have not used this yet).

In Excel 2007, Select your range and select Data on toolbar. Under Data Tools

click on Remove Duplicates

Select columns you need to remove duplicates and if applicable check on My Data has headers check box and say Ok.

You will be shown a message box like below with duplicates found and removed.

P.S. Duplicates are deleted from the list you choose.
Note: For simplicity sake I just took names (One column) of my friends as data. It could be more complicated data (with multiple columns). All the techniques are valid for such data. Write to me if you face any issues. I’ll be glad to help you.

Hope this is all useful. These are the techniques I use (I have not shared my VBA script yet but will share that soon) to remove duplicates/keep unique values. All said, I invite all your opinions and suggestions on this write-up. Also, please share if you use any other techniques or I skipped/missed anything.

Explore Excel

noreply@blogger.com (VaraK) — Mon, 20 Sep 2010 15:30:00 +0000

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"