The Top Ten secrets of Excel

Hi all,

My apologies for not updating the blog for such a long time. I have been overwhelmed with work that I could not find the time to provide new information to you. It is quite coming up with topics out of the blue. As such, I appeal to you to send me some of your Exel-related problems and I will solve it FOC. The condition is that I could share it with others on the blog.

Last week, I have attend a live telecast called the "Learn 10 Secrets About Excel" You can view it online @ https://www118.livemeeting.com/cc/mseventsbmo/viewReg or download the whole e-book at a cheaper.

A quick way to copy formula down hundreds of rows.

If you have 2 columns with 1000 rows each (say column A and B) and you created a formula in the first row (e.g. in column C). In most cases, you would have copied the formula throughout the 1000 rows by dragging the formula down. And one problem you faced is that you would take quite a while to drag to the 1000th row. In addition, you are likely to drag beyond the 1000 rows and have to back track.

Now, there is a simpler way. It just take 2 seconds to copy the formula down 1000 rows, or even 10000 rows. What you have to do is to move your mouse cursor to the bottom right corner of the formula cell in column C until a cross appears. Then double click on your left mouse button. Excel will make reference to the cells to the left of the formula cell (Column B in this case) and copy the formula all the way down to the last row (reference to column B). And you are done.

Enjoy your weekend.

The power of Pivot Table (Part VIII)

My apologies for not blogging for the past 2 days. I have to attend a conference on coporate planning and was busy preparing for my presentation for the conference. I was also trying to meet some project schedules.

I guess this should be the last posting on Pivot Table. There are too many things to cover on Pivot Table that I can spend 2 full days sharing with you all that I know. Having said that, I didn't want to bore you with more details until you have played around with those that I have posted in the last 2 weeks.

Do you know that Excel Pivot Table can give you the raw data that make up a particular result in one single step?
For example, if you are to double click on any one of the Sum of Amount cells, Excel will present the string of records that make up that number in another new worksheet. It will give you all the other details including fields that are not even presented in the pivot table. This is a wonderful function because it allows you to drill down to the most basic details. I don't think crystal report or Business Objects provide that. I may be wrong though.

The power of Pivot Table (Part VII)

Let's have something simple today. Now if you have a pivot table that shows two attributes on the left column of the pivot table, i.e. Customer Name followed by Promotion as per our pivot table we created in PART V. Do you know that you could hide the details such as those under promotion?

1. What you need to do is to double click on a particular customer name and the promotion details are hidden.
2. To unhide the details, double click on customer name again and the details under promotion appear.

The power of Pivot Table (Part VI)

First, let us recap what we have covered so far.

1) We have learnt how to create a pivot table.
2) Format the results
3) Present the company sales results by customers, by promotion and by salesman.

I mentioned in point 3 that the sales results were company's results. What if you want to find out who are the key customers in a particular category or division? Here is how:

a) Move your mouse over the pivot table.
b) Click on the right mouse button and select wizard.
c) When you are brought back to Step 3 of 3 of the wizard, click on the layout button.
d) Notice on the top left hand corner that say page? Drag and drop the field call "division" into the page area.
e) Click Ok to get out of the layout dialog box and click finish to complete the wizard.
f) On the top left hand corner of the pivot table, you will see the field "division". With this, you can select the desired division and the data are filtered to show only the top selling customers by promotion by salesmen for the that division.

I hope I have amazed you with what Excel can do. Please come back if you want to discover more. Do come back if you are still not convinced about the power of Excel.

Have a blessed weekend.

The power of pivot table (Part V)

If you have followed through my recent blogs on The power of Pivot Table Part I to Part VI, you would have formatted the pivot table in a presentable format with the customers ranked by the total amount they have purchased from the company in descending order. The pivot table would have displayed which salesmen have sold products to the customers.

You may find that the above format is not sufficient for you to make decision on how you should classify the customers. You wonder whether the top customers are purchasing more of promotion items or normal items from you. Is pivot table able to do that? The answer is yes and here is how:

1) Move the mouse pointer over the pivot table.
2) Right click the mouse button.
3) A pop up menu will appear. Look for wizard and select it.
4) You are brought back to the step 3 of the pivot table wizard. Click on the layout button.
5) Now drag the field "promotion" from the list to the column area right below the customer name.
6) Click Ok and finished.
7) You should see the field "promotion" appearing on the left side of the "customer name"
8) Move the mouse button over the the right border of the row label as shown in this diagram.
9) Left click on the mouse button.
10) All the sub-total would be selected.
11) Highlight the sub-total with a color. For me, I would choose yellow.

Now you are able to make more informed business decisions with the additional dimension added.

Note: You can add as many dimensions to the column or row area. But remember that having more may not be better. Try not to micro analyse.

Take care and God Bless.

Back to The power of Pivot Table (Part IV)

Now that you have formated the results (Sum of Amount) for the pivot table, you might want to know that you could also rearrange the customers within the pivot table by ascending or decending alphabetical orders. That is not all. You can also arrange the customers list based on their purchases (ascending or decending order). What's more, you could even do a filter such that only the top x or bottom x number of customers are displayed in the pivot table.

Interested to find out how? Read on.

1) Double click on the grey box/button on the top left corner of the pivot table with the label "Customer Name"
2) A dialog box called Pivot Table Field wil appear. Click on the Advanced button.
3) Another Dialog box called PivotTable Field Advanced Options will appear.
4) Under the AutoSort Option, you can select Manual (Arranging the customer based on your own preference), ascending or descending order.
5) If you click on ascending or descending order, you would be able to choose the sorting preference, By customer name or by Sum of Amount (Purchases)
6) You can activate the Top 10 Auto Show by selecting the "on" option. Click on the drop down box and choose bottom if you decided to show the bottom x results in the pivot table. X is determined by the box next to the top/bottom selection.
7) If you have more than one result in the pivot table, you could select the field which the top or bottom x results should be based on.
8) Once you have made the relevant selection for the pivot table, click "ok" twice to return to the pivot table.

Task Completed.

God bless you with a wonderful day ahead.

If you want to learn about pivot table hands-on and are in Singapore on 12 and 19 Dec, you may want to consider signing up for my course. It will benefit those who are interested in business intelligence. Click here for more details.

Take a break from our Pivot Table Tutorial

I was reading an article in a Sinapore newspaper which talks about using business intelligence and using the right tool to yield predictive data. It was a worldwide bank which has successfully cull their databases for information as they assess the likely response for their new credit card.

As I read it, I was thinking that small and medium business owners must be envious of the cash that big corporations have to purchase tools to extract this data (customers' profile) out of their systems. What these small and medium business owners don't know (and I hope that they are reading this), is that their have the tool right in their desktops. Which tool is more suitable and at NO incremental cost to them than using Excel. I expect some of them to be skeptical about what I said but I would challenge them to find out more. One such evidence I have provided is the current subject which I have been sharing with you on this blog, that is, the Excel Pivot table.

It is not only capable of pulling out data from an Excel Spreadsheet. In fact, I have used Excel to pull out data from huge databases and present reports without the need to learn another software tool. Excel is all I need. If you are a small and medium business owner, come back often to this blog and you will know that I am not lying.

It was mentioned that business intelligence requires technologist and business analyst. True and not true. As I was working for some of the mobile companies and even a Fortune 500 company, I was the one (a business analyst) that are pulling out the data from databases and systems. And throughout my job, the IT department did not really intervene. If you are a business analyst, you must also be equipped with the skill of to cull databases or the concept of doing it. This is because data quality is important and half of these technologists do not know which set of data to extract. You have to because it affects your analysis. As the saying goes, garbage in garbage out.

There is so much to share and I can write and write but I wouldn't want to overload you with too much. I am planning to commit some resources to start a page on this. Look out for it. I am still planning the site.

If you want to learn about pivot table hands-on and are in Singapore on 12 and 19 Dec, you may want to consider signing up for my course. It will benefit those who are interested in business intelligence. Click here for more details.

The power of Pivot Table (Part III) - Formating the pivot table

After creating the pivot table, it is important to format the results from the pivot into an easily viewable format. This is important for your readers as well as for your own data analysis. By having a well formatted pivot table allows you to see the data patterns more clearly.

For example, the "Amount" field can be formatted the same way as a cell. The more important consideration is to ensure that the format remains even if you re-arrange the rows(in this case "customer names") and columns(Slsman No). This is how it should be done. Double click on the label called (Sum of Amount - from last week example). A dialog box appears. Click on the number button and the familiar dialog box for cell formating appears. Select the desired format and click Ok twice to complete the formating. The formating will be preserved even if the pivot table fields are re-arranged.

If you want to learn about pivot table hands-on and are in Singapore on 12 and 19 Dec, you may want to consider signing up for my course. Click here for more details.

The power of Pivot Table (Part II)

Not convinced by what I have shared yesterday? Read on. I will prove to you with examples. The only way you can be convinced is to let you experience the power of Excel Pivot Table. Here's what I will do. Over the next few sessions, I will share with you what you could do with Excel Pivot Table and you judge for yourself whether what I said is true.

1) Download the data file I have created for you.
2) Open up the file.
3) Go to menu, data and select "pivottable and pivotChart report"
4) In step 1 of 3 of the pivot table wizard, select the option "Microsoft Excel List or database" and for the kind of report you want to create, select "pivot table"
5) Click Next
6) In Step 2, highlight the entire data table
7) Click Next,
8) Click on the Layout button when you are at step 3 of the pivot table wizard.
9) Drag the label called "customer" on to the section of the table called row.
10) Do the same for "Slsman No" and "Amount". The layout should look like this.
11) Click ok to go back to step 3 of the wizard.
12) Click Finish. Remove all the pop up boxes.
13) You should see the layout as shown in this diagram.

The power of Pivot Table (Part I)

For the benefit of those who don't know what is pivot table and how it works, I will start from basic and progress gradually from simple to difficult.

What is pivot table and what it can do?
Pivot table is a tool that is provided in Microsoft Excel Application. It's purpose is to help you analyze the data you have. Pivot table in Microsoft Excel seems like a cheapo tool to many because it is just one of the features in Excel. What most people don't know is that it would have serve the needs of most data analysers. In fact, with the right experience, Microsoft Excel pivot table would have diminished the value of the crystal reports based on the price paid. Not only crystal report, but it can be a strong competitor for business objects, inpromptu and other reporting tools. I have used these other tools listed and I still fall in love with Microsoft Excel Pivot Table. It is easy to use and the amount time you need to learn how to use pivot table is much less than those reporting tools listed. What more, Microsoft Excel is a tool that is familiar to you and most probably, you have it on your computer.

PS: I am conducting an Excel Workshop on the 12 and 19 Dec. If you are in Singapore during this 2 dates, yoiu might want to consider attending the course. Click here for more details

The power of Pivot Table

I have spoken to many and am amazed that many Excel users are not aware of the existence of pivot table. For some, they have heard of it but have not used it before. To these 2 groups of people, I must say that they have not truly experience the power of Excel. In fact, my performance in data analysis improved by leaps and bounds after I learnt about Excel's pivot table.

For those who have used Excel Worksheet as a data source, you are one step ahead of these two groups. But you are only using 30% of Excel's pivot table capability. Those who are already using MS Query would have harness the true power of Excel pivot table. In fact, it would take about a day for me to cover every aspects of Excel's pivot table. In the next few days, I would share with you some of these functions so that you could appreciate what Excel pivot tables can do for you and how it could improve your data analysis capability.

Getting the list of unique records (Method 2)

I have recently discover another method to retrieve the unqiue list of items. And here it is:

Assuming that you have the same list as yesterday.

What is a blog

Internet Marketing

Excel Formula

Adword Equaliser

Internet Marketing

Adword Equaliser

Excel Formula

Excel Formula

1) Sort the list so that all the same items are group together.
2) Input a header on top of the list. This is required for the sub-total function we are going to use in the next step.
3) Activate the subtotal by going to menu, data, sub-total
4) When the dialog box appears, set the following options
a) At each change in: list (Header)
b) Use function: Sum
c) Add Subtotal to: list
d) Select the option: Replce current total
e) Select the option: Summary below data (see diagram for details)
5) Click OK.
6) Click on the button 2 located at the top left hand corner. The list would be compressed as shown in this diagram.
7) use the Goto Special function to extract the unique list to another excel worksheet.
8) Use the Replace function to remove the " total"
9) The list is extracted.

I might not be able to share more excel application with you on a daily basis from tomorrow till 12 Dec. This is because I might be tied up with the preparation of my Excel course which I am conducting on 12 Dec and 19 Dec (1.5 days in total). This is an inhouse course I am conducting for my client. As there are still some places left, my client has agreed to allow me to offer this course to you if you are interested. Of course, you must located in Singapore during this time. Drop me a comment if you are interested to find out more and I will furnish you with more details when it becomes available.

Getting the list of unique records (Method 1)

Previously, we touched on the various methods to count the number of unique records listed in an excel worksheet. However, nothing was mentioned about producing the list of unique records using Excel. And I thought you might be interested to find out how. And here is one method you could use:

Assuming that the following product list is in column A starting in row 2.

What is a blog
Internet Marketing
Excel Formula
Adword Equaliser
Internet Marketing
Adword Equaliser
Excel Formula
Excel Formula

1) Sort the list so that all the same items are group together.
2) In cell A2, input the formula "=if (A2=A1,1,0)".
3) This formula will check to see if the cell A2 is the same as A1. If they are the same, the number 1 will appear. If they are different, the number 0 will appear. Since we have grouped the same items together, the 1 will appear if item appears the second or third time in the list. For example:

Adword Equaliser 0

Adword Equaliser 1

Excel Formula 0

Excel Formula 1

Excel Formula 1

Internet Marketing 0

Internet Marketing 1

What is a blog 0

4) Activate the autofilter function and filter using the criteria "0"

5) Once the duplicates are hidden, use the Goto Special function to extract the unique list to another excel worksheet.

Mission completed.

About Goto Special function

Press the F5 function key and then click on the Special button. Select the option "Visible Cells only" (see diagram). Click "OK". Only the visible cells are selected for now. Activate the copy function, go to another Excel worksheet, select a cell say A1 and activate the paste function.

Separate a string of values into different columns (Part III)

Spliting strings of values which are separated by a symbol is a breeze using the text to column function. What happens when you have a string without any separator, like those given below?

tops278484092828

name732180244063

baby276540921867

You can use the fixed width option instead of the delimited option in "Text to Column" function. For those who are using Excel 95 or 97 or evenExcel 2000, you might need to convert the font to one that is fixed width. This means that a "M" will occupy the same width as an "I". The font type I recommend is "Courier" or "Courier New".

Here are the steps:

1) Highlight the rows you want to parse.

2) Change the font to Courier.

3) Goto menu, Data, Text to Column

4) In step 1 of the wizard, select the option "fixed width". Click Next

5) Point the mouse pointer at the appropriate width and click the left mouse button once.

6) If you want to remove any of the preset separator, click the left mouse button at the appropriate black line or separator twice.

7) Once you have set the relevant width, click finish and the strings of value are separated into the respective columns.

Separate a string of values into different columns (Part II)

We found a way to split the values into the respective columns if they are separated by commas. What happens if they are separated not by commas but by the sign \ or ! or ~ ....... Is it possible then to separate them? The answer is yes. If we have a set of values which are separated by "" like those below , we can also split them up into the respective columns.

top 100 baby names\2784840\92828

baby names\732180\24406
baby\27654\09218

This is how it should be done.

1) Highlight the rows you want to break up.

2) On the menu, select data, text to column.

3) In Step 1 of the wizard, make sure the option "delimited" is selected.

4) Instead of checking on the comma delimiters box as demonstrated yesterday, check on the box labelled others and input the sign "\" into the empty box that follows. Once you do that, the values are separated by black lines.

5) Click on finish and you are done.

Separate a string of values into different columns

Imagine you have a Comma Separated Values file (CSV in short). When you open the file, Excel fails to separate the values for you. No matter how many times you re-open the file, it remains as follows:

top 100 baby names,2784840,92828
baby names,732180,24406
baby,276540,9218

Are you going to separate them manually? On the above example, there are only 3 sets of values. What if there are 100 rows, 200 rows or more.

In Lotus 1-2-3, the function to separate the values is called parsing. In Excel, you can do the same through the function called "Text to Column".

1) Highlight the rows you want to break up.
2) On the menu, select data, text to column.
3) In Step 1 of the wizard, make sure the option "delimited" is selected. Click next.
4) Check on the comma delimiters box. Once you do that, the values are separated by black lines.
5) Click on finish and you are done.

The values are now separated into the respective columns.

Calculating Working Days

Excel contains a function that helps you calculate the number of working days between 2 dates. It is called Networkdays. The function assumes a 5 days work week. To use the function, you need to activate an add-in called analysis toolpak. You can do this by going to the menu, tools, add-in and check on the box labelled "Analysis Toolpak". You may require Microsoft Office installation disk to activate the add-in.

The function also allows you to add in holidays such that they are excluded if these holidays fall on weekday and are within the period for the calculation.

Find out the number of unique products in the list (Second formula Explained)

We have covered the first formula last week, what about this second formula
=SUM(IF(FREQUENCY(IF(LEN(A1:A100)>0,MATCH(A1:A100,A1:A100,0),""),IF(LEN(A1:A100)>0,MATCH(A1:A100,A1:A100,0),""))>0,1))?

Before we share with you how the different functions are applied, do take note that the function starts with Sum(if and not sumif. There is a bracket in between the sum and the if. This denotes that it is a multi-conditional sum. To activate the formula, you need to use Ctrl + Shift + Enter together.

What are the conditions in the multi-conditions sum? Let's break them up and see what they meant

Click here for diagram


The formula Len(B1:B8)>0 is used to determine that the cell in the range is not a blank. If there is a character in the cell, then the length is at least 1 and it will return true as shown.

The formula shown in column D is developed to indicate the first row that the content shows up. For example, Internet marketing in row 6 returns a value of 3 because the phrase "internet marketing" shows up the very first time in Row 3.

In Column E, the formula frequency will calculate the number of times the number 1 shows up in the list in column D, followed by 2, 3, ..etc. Notice the simlarity in this formula with the complete formula =SUM(IF(FREQUENCY(IF(LEN(A1:A100)>0,MATCH(A1:A100,A1:A100,0),""),IF(LEN(A1:A100)>0,MATCH(A1:A100,A1:A100,0),""))>0,1)) In fact, the range D1:D8 as shown in the simplified formula is replaced by the formula show in prior column D. The replacement moves us closer to the complete formula. For clarity, the simlarities are highlighted in blue and green colour. Different colours are used to show that the range D1:D8 are replaced twice.

=SUM(IF(FREQUENCY(IF(LEN(A1:A100)>0,MATCH(A1:A100,A1:A100,0),""),IF(LEN(A1:A100)>0,MATCH(A1:A100,A1:A100,0),""))>0,1))

In the final part shown in column F, the formula =if(E5>0,1,0) is used to pass instruction to Excel to put in the number 1 in those rows in column F where the numbers showed up in column E is more than 0. By putting the conditional sum in the formula, we have completed the entire formula where the end result shown is the number of unique records in the list. Do remember to use Ctrl + Shift + Enter together to effect the formula.

Find out the number of unique products in the list (First formula Explained)

For those who are wondering what all those excel formula meant, here is the explanation:
Before I start explaining the complex format, here is how sumproduct was meant to do

Assuming there are 2 sets of numbers as follows:

    A     B
1   6   10
2   3   5

When you entered the Excel Function =SUMPRODUCT(A1:A2,B1:B2) into one of the cells, say D1, you get a result of 75. What the excel function does is that it will multiply A1 with B1 (6 x 10) and A2 with B2 (3 x 5). After which, it will add up the 2 results and give you 75 (60 + 15).

    A
1. Excel
2. VBA
3. Internet Marketing
4. What is a Blog
5. What is a Blog
6. Internet Marketing
7. What is a Blog
8. VBA

In our unique list, the formula initially started as simple as this =sumproduct(1/countif(A1:A8,A1:A8)).
What the countif [countif(A1:A8,A1:A8)]does is actually a shortcut way of populating the formula [=countif(A1:A8,A1)] in B1 and copy the formula all the way down to B8. The results would have been: 1 count of the word Excel (which appears in A1) is found in the list, 2 counts of VBA is found in the list, etc. As the function is inversed, the result turns out to be like this:

   A              B

1. Excel           1/1

2. VBA           1/2

3. Internet Marketing           1/2

4. What is a Blog           1/3 (1/3 because there is 3 counts of the phrase "What is a blog")

5. What is a Blog           1/3

6. Internet Marketing           1/2

7. What is a Blog        1/3

8. VBA           1/2

As a result, the number of unique records are returned,

Excel = 1/1 = 1
VBA = 1/2 + 1/2 = 1
Internet marketing = 1/2 + 1/2 = 1
What is a Blog = 1/3 + 1/3 + 1/3 =1

The total is 4 unique records.

This works perfect provided that the list do not contains blank records. If not, the result 1 divided by 0 would have returned a #div/0! error To circumvent that, the following are added. 1 is changed to [A1:A8<>""] to tell the formula to ignore blank records.

Likewise, the following [(A1:A100="")] is added to make sure that denominator will return a value greater than 0. In this case, when the list contains more than a blank record, the denominator is more than 0 because of this segment of the formula + (A1:A100=""). If the list do not contain any 0, it will still return a number based on the formula COUNTIF(A1:A100,A1:A100). Either way, the denominator will return a value of more than 0.

Find out the number of unique products in the list

Assuming you have a list of product names in a column, say one hundred rows. You want to find out how many unique items are there in the list. To do that you can use the following formula:

=SUMPRODUCT((A1:A100<>"")/(COUNTIF(A1:A100,A1:A100)+(A1:A100="")))

This is the simplest formula to use. Another you could use is as follows:

=SUM(IF(FREQUENCY(IF(LEN(A1:A100)>0,MATCH(A1:A100,A1:A100,0),""),IF(LEN(A1:A100)>0,MATCH(A1:A100,A1:A100,0),""))>0,1))

The second formula is longer but will return the results faster. According to one web site, it is 4-5 times faster. If this is a simple worksheet, you may not feel the difference. However, if yoiu have a complicated worksheet with multiple formulas, the second one may prove to be a time saver if it really calculates 4-5 times faster.

Converting a number with decimals to fraction

I was have joined this yahoo group which talks about Excel topic. Somebody asked if he is able to convert a number with decimals to a fraction. As I explore the format cell, I noted that there is this category that could convert the number to fraction. Not only that, it also gave options on whether to convert the number to fraction based on on 1 to 3 decimal places. There are also other options. See this diagram for more details.

Hiding rows of expenses with zero value

My client has a list of monthly expenses that needs to be presented. To shorten the list for the presentation, those rows of expense items which have zero value need to be hidden. Imagine if the list is 100 rows and out of which, 20 rows do not have any value and are scattered among the 100 rows of expenses. What my client did was to go thru the expenses row by row and hide the expenses with $0 value one by one. She has to repeat the action 20 times if there are 20 rows of expenses with zero value. After I showed her find all, she just need to do it once. That is a saving of 95% of her time. Here's how if you have not figured out from previous post.

Highlight the column with the expense values. Activate the find function from the menu by selecting edit, find. Type in the number zero and click on the option and check the option box (Match Entire Cell Content) and click find all. Highlight the results and then close the find dialog box. Go to the menu and select format, rows, hide. And all the expenses with zero value are hidden.

Hiding column between merge cell

I was in my client's office the other day and she told me that she cannot hide the column when there is a merge cell in one of the rows. For example, there is a merge cell D1:F1. She wants to hide the column E. What she did was to click on the column header which highlight the entire column. As D1:F1 is a merged cell, the entire merged cell is highlighted. When she clicks on the right mouse button and chooses hide, a error message pops up. The error is due to the merge cell. As such, she has to resort to unmerge the cells D1:F1, hide the column E and remerge the 3 cells.

There is an easier way and here it is.

Select any cell in column E other than that in row 1 which contains the merge cells. For example, E2. Then go to the menu bar and select format, column, hide. The column E is hidden. Don't need to unmerge the cell at all.

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.