Payback Period

I was conducting Excel training a few weeks ago when one of the participants was asking me how to use Excel to calculate pay back period. He asked specifically for that because Excel has provided a complete set of formulas for financial calculations. This includes formulas to calculate Net Present Value (commonly known as NPV), Internal Rate of Returns (IRR), Present Value (PV), Future Value (FV), etc.

Financial formulas are commonly used to evaluate business investments, as to whether it is viable to invest in a particular project. What is unknown to most people is that these financial formulas could be used by individuals as well. One example is in evaluating insurance policies. I thought of using insurance policy because my participants are mostly working in other functional areas and would be less interested in the solution if I were to use a business scenario to present the solution on the payback period. Using an insurance policy is helpful in arousing the interest of the participants because they are likely to purchase or have purchased some insurance policies.

Click here to see the solution

Remove Duplicates using Excel advanced filter

Using advanced filter, we can get the list of unique records from the data list ...
click her for more details

Using the Subtotal Function in Excel

A step by step guide in using the subtotal function in Excel. Explanation are also given in the guide. http://www.advanced-excel.com/subtotal_function.html

What is excel used for in business

The following link talks about how microsoft excel is used in businesses. http://www.advanced-excel.com/what_is_excel.html

All about Vlookup

Vlookup function is one of the most useful functions in Ms Excel. Click here to find out why

Separate the text into two lines in a cell in Excel

Here is the solution to separate the text into two lines in a cell in Excel

Permalink -- click for full blog

Excel Video

When I write for this blog and other excel webpages, I always wonder if you understand what I wrote. In the first place, you have to understand what I write and then, you would have to translate those words into images so that you could appreciate what I am trying to tell you. It must have been pretty painful for you.

But now, I have the solution. That is to produce video. In this way, I could explain what I am doing and you could watch the effects in the video. I am sure you would apprecate the tips and tricks better than what you are doing now. It is like you are in a training course where the training explains the steps and you watch it come true. This means that you can remember the steps by listening and seeing. Here is one of my recent works http://www.advanced-excel.com/find_blank_cells.html. Enjoy.

About Vlookup

Assuming that I have 2 tables. One table contains a list of invoice numbers. Another contains another list of invoice numbers with the second column containing the value of the invoice found in the 1st column.

I try to set up a vlookup formula in the cell next to the invoice number in tbale 1, to look up the invoice number in table 2 based on invoice numerb given in table 1. If the invoice number is found, I want the invoice value (residing in column 2 table 2) to be returned in the cell. In some isntances, the invoice values were returned the right results but on other times, it gives me an error syntax "#N/A". When I check table 2, the invoice number exist but the invoice value was not returned. What could have gone wrong?

To use vlookup to lookup for a particular value/text in another table (Table 2 in this case), you must make sure that the format is the same as that given in Table 1. If the invoice number in Table 1 is a number, while the invoice number in table 2 is a text, vlookup will return a "N/A" results indicating that the invoice number cannot be found. As such, before you use vlookup formula between 2 tables, you must make sure that the format for (the invoice numbers) in table 1 and 2 are the same. Only then can Excel find the value and return the results you are look for.

Converting Numbers to Text

You have an Excel worksheet that contains the phone numbers of suppliers and customers. You would like to convert them to text so that you could export these numbers into another IT system. What should you do?

Most users would simply highlight the range of numbers and then format these cells as text. They thought that by doing so, the numbers are all converted to text. THis is not so. When the cells are formated to text, the numbers in these cells remain as numbers. To convert them to text, you need to use the Excel worksheet function called "Text". Assuming that you have a cell in A1 that contains the number 5. To convert it to text, you need to enter the formula =text(A1,"@") , including the "=" into the cell say B1. The number 5 will become a text and when you try to sum up this value with others, it will not included.

Convert text to number

There are times when you receive a set of records in a worksheet and they are presented in text format. As such you are unable to use these text formatted numbers to perform calculations. In this case, you would need to convert them into numbers.

How then can we convert the text formatted numbers into real numbers that can be used for calculations?

Here is a function that will achieve what you are looking for. It is called the value function. Assuming that you have a text formatted number in A1. To convert it to a number, you just have to type in the formula say in A2 "=value(A1)". The text formatted number would be converted to value and ready for you to perform calculations.

Pulling out text from a cell

There are 3 functions that will allow you to pull out text within a cell. These 3 functions will help to pick up certain details that are necessary foranalysis or presentation.

For example, you have a column of text which shows the currency denomination and the amount :

SGD 1000
USD 3203
GBP 342
Yen 543334

In our case, we want to know the denomication of the currency used. To do that, we could use the left function to pull out the first 3 characters in the cell. Assuming that the first cell is in A1, the formula would be written as follows "=left(A1,3)" (exclude the "). By doing so, Excel will pull out the first 3 characters of text from the cell. The results would show SGD, USD, GBP, etc..... RIGHT Function works the same way as LEFT function except that the counting starts from the right.

Mid works in a similar way except that it is capable of pulling out text in middle of a phrase that is in the cell. For example, Cell A1 contains the phrase "Excel contains a lot of advanced function". To pull out the string of text say "contains", you can use the following formula "=mid(A1,7,8)" The formula will pull out the string of text starting from the 7th position and 8 characters from that position.

Have a blessed week ahead.

Making full use of the toolbar

When you install Excel, 2 toolbars (standard and formating) will be presented in the application. You should see something like this.


Tool_Bar_Original

The problem with this presentation is that the icons in both toolbars are not fully displayed. In this case, it would have been difficult for you to know all the shortcuts you could use (with the icons) since you can't see them. To make full use of the icons given in these 2 toolbars, you need to separate them. Here is how:

1) Move your mouse cursor along the icons in the toolbars from the left to the right. As you move to the right, the cursor will become a shape with 4 arrows pointing in 4 different directions.



Tool_Bar_Move

2) When the shape appears, click and hold onto the left mouse button and move the tool bar slight downward. The 2 toolbars will be presented in 2 rows and you would be able to see the full range of icons for the 2 toolbars.
3) Let go of the mouse button and you are done. You should be able to see your 2 toolbars presented as follows:


Tool_Bar_Final

Have a blessed weekend.

What is the fastest way to do this?

Happy New Year to you. I wish you and your family a blessed 2006.

My apologies for not updating the blog for such a long time. The last 2 weeks was extremely busy for me as I rush for a number of my projects. I received a query and thought it would be good to share the answer with all of you. The question is as follows:

What is the fastest way to do this: check the cells for zero and delete the row if it is zero.

Solution
1) Highlight the list in the Excel Worksheet.
2) Go to Menu and select Edit, Find or use "Ctrl F" (shortcut key)
3) Type in the number "zero"
4) Click on the "Find ALL" button. Make sure that the option exact match is selected. This is to make sure that MS Excel does not return those cells with zero such as 10, 20, 30 , etc.
5.)Select the list by
5a) Go to the first item in the list.
5b) Hold on the shift key and click on the last item in the list.
5c) You have highlighted the entire list (See diagram below)


6) Close the find and replace dialog box.
7) Delete the rows using the following command. Edit >Delete and select entire row and click Ok.

The rows with zeros are deleted.

That's all for today. Drop me an email if this has been useful to you.

Cheers.

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.