Quick Calculation of highlighted range

Enter the following values into a new worksheet

A B

01 20 10
02 5 15

The first row is the column label and the first column shown is the row label. Highlight Cell A1:B2. Have you ever notice that the total sum of 50 is shown near to the botton right hand corner of the spreadsheet. You don't need to enter a sum formula to find the total.

And if you have known that, then do you know that you could let the results shows the average of the 4 numbers, i.e. 12.5. How do I do that? Point your mouse at the status bar. (If you don't see the status bar, you could go to menu, view and activate the status bar option). Click on the right mouse button and you could see the list which allows you to select whether you want to see the sum (as shown above), average, etc. Select the one that you uses most often and start calculating how much time you have saved with this new discovery.

Removing Hyperlinks (All at one go)

If you want to remove a hyperlink from one of the cells, what you need to do is to point at the cell using the mouse and click the right mouse button. In the floating menu, select "remove hyperlink". Done.

The option above will not work for multiple hyperlinks. If you have ten links, you have to do the action ten times.

Now, here is the solution to solve the above multiple links problem.

1. Enter the number 1 in a blank cell
2. Copy the number 1.
3. Highlight the cells that contain the hyperlinks.
4. From the menu, select edit, pastespecial.
5. In the Paste Special Dialog box, select the mulitply radio button. Click OK.
6. All the hyperlinks are removed. Delete the cell with the number 1 created in step 1.

Group columns for instant hiding and unhiding

There are times when you need to hide a group of columns. At other times, these columns needs to be unhidden and another set of columns needs to be hidden. If you are in such a situation, this is something you should look at.

 

Assuming below is the layout for a worksheet.

 

                A        B        C        D        E        F        G        H

1

2

3

4

5

 

To hide column A, E and F at one go, this is what you should do.

 

1. Go to Cell A1. From the menu bar, select data, group and outline, group. In the popup box named group, select columns.

2. Highlight the cells E1 and F1. From the menu bar, select data, group and outline, group. In the popup box named group, select columns.

3. You will notice that there are 2 buttons called 1 and 2 on the top left hand corner above the column labels. See diagram.

When you click on 1, the column A, E and F will be hidden. When you click on 2, the columns will appear again. You can try grouping more columns and see how the presentation changes.

 

Multi-Dimensional Sum

Assuming you have a set of data as shown:

A B C
01 ABC XYZ 10
02 ABC XYZ 20
03 DEF XYZ 30
04 PQR ABC 40
05 UVW XYZ 50

You want to add up those numbers that satisfy the conditions Column A contains "ABC" and Column B contains "XYZ". So what are some of the solutions?

Solution 1
1. Enter a subtotal function in cell C7. The formula should be "=subtotal(9,C1:C5)". The cell C7 is chosen instead of cell C6 so that when you do the autofilter later on, it will not be included in the autofilter.
2. Do a autofilter and set the criteria for column A as "ABC" and the criteria for column B as "XYZ".
3 Cell C7 should return the result 30 (10+20)

Solution 2
Enter in cell C6 (or anywhere you prefer) the formula "=sum(if(A1:A5="ABC", if(B1:B5="XYZ",C1:C5)))". Instead of the normal enter key, you need to use shift + ctrl + enter. This is because it is an array formula. And you should get the same results as solution 1 (30).

Comparing two list with similar list of items

Now that you have learnt the required functions for comparing the two lists, we can now show you how to do the comparison.

 

Assuming you have 2 lists as shown, each residing in one worksheet.

 

List 1

Excel	good
Excel VBA	good
SynergyWorks	good
Internet Marketing	good

 

List 2

Excel VBA	good
Synergyworks	good
Adsense	good
Adwords	good

   

Both lists have items of their own (Excel, Internet Marketing in list 1 and Adsense, Adwords in list 2) and items that are available to both of them (Excel VBA). You need to compare the 2 lists and find out which item is present in either list or both.

 

For this to happen, first combine the 2 lists and put them into fresh new worksheet as shown.

 

Excel

Excel VBA

SynergyWorks

Internet Marketing

Excel VBA

SynergyWorks

Adsense

Adwords

 

Do a sort so that similar items are listed together. To remove the duplicate items, you must use the If function in the next column. The following table starts from A2 and A1 is a blank cell. Enter the formula "=IF(A2=A1,"Duplicate","")" as show in the digram below. Copy the formula down the list.

 

Adsense	=IF(A2=A1,"Duplicate","")
Adwords
Excel
Excel VBA
Excel VBA	Duplicate
Internet Marketing
SynergyWorks
SynergyWorks	Duplicate

 

The formula will flag out the duplicates for Excel VBA and SynergyWorks. Do a autofilter using the criteria "blanks" and the duplicates are sieved out.

 

Highlight the entire list and press Function key F5, go to special cells and select the option visible cells only and click "OK".

 

Click on the copy icon or go to the menu bar and click on edit, copy.

 

Paste the results in a brand new worksheet. You have a list of unique items.

 

Use vlookup and lookup the value in the repective cells as shown. replace the word "list1" and "list2" with the relevant ranges. "=VLOOKUP(lookupcell,list1,2,FALSE)". Assuming that adsense is in cell A2, then lookup cell is A2. 

 

	List 1	List 2
Adsense	#N/A	good
Adwords	#N/A	good
Excel	good	#N/A
Excel VBA	good	good
Internet Marketing	good	#N/A
SynergyWorks	good	good

 

To beautify the results, you could extend the formula using if function ---> "=IF(ISNA(VLOOKUP(B2,Sheet1!$B$2:$C$5,2,FALSE))=TRUE,"",1)" It means that if the result is "#NA", show a blank cell "", else show the number 1. ANd you have got a table which show which word/phrase exist in which table as show below.

 

	List 1	List 2
Adsense		1
Adwords		1
Excel	1
Excel VBA	1	1
Internet Marketing	1
SynergyWorks	1	1

 

That's the end of this topic. Hope you enjoy this series.