Excel Challenge

Here is an Excel Challenge I have created. Let me know how fast can you complete the task.

How to use data validation

Data validation in Excel is used to restrict user from entering unwanted value or text into the cell.

It can also be used to create a simple dropdown box so that user can select the desired value from a list.

To know how to set up a cell with data validation, click here.

Excel for teachers

How you can use Excel is really up to your imagination. In one of my training course, somebody showed me a template which is used by their GM to monitor how well the team know about the product(s) and other key salient points necessary to carry out their responsibility. The template was very useful and I have since modified it so that it can be used to profile the class performance and make comparison between a student against the class (in the case of a school teacher) This will help to identify which topic is the class is in and also the student(s) that requires special attention. Would you like to share how you are using Excel in your profession?

Uses of Excel

Excel contains a lot of formulas that will help to simplify complicated calculations. By knowing these formulas, you can actually calculate depreciation in less than 5 rows. That's right less than 5 rows. I know some of you might be shock to hear that. I have spoken to many people and also know that they have been using Excel in an inefficient way. Why do I say that? Because they have been using an entire worksheet just to calculate depreciation. Isn't that a waste? Want to learn those formulas? Take a look at my website and see if you can figure out.

Uses of Excel

What can Excel use for? Is it only capable of storing name list? Or to perform summation or tedious calculations? That is what a lot of people thought. The truth is, Excel can do more powerful things than that. One of the interesting things that it can do very well is forms, e.g. survey forms, feedback forms, etc. We are not talking about forms with boxes but form with dropdown list, option buttons and/or with check boxes, something like the form given below:

It is useful to create forms in Excel because by putting the answers in one row through links, users just need to copy and paste the answers into a consolidated worksheet. Thus saving time on consolidation.

Crystal Report

The first time I encountered crystal report was in the late 90s. It was very powerful in pulling out data from database servers. The activity on pulling out data from databses used to be the job of the IT department. Now, users can do it themselves. It was great because I can pull out the data as and when I needed it without the need to fill up IT request form. And I can get the data I want instantly. But after I learnt about pivot table and MS Query, I never looked back....more

Internal rate of return

Internal rate of return is a term used in the finance industry. The rate is derived based on the assumption that the net present value of a project is zero. At this rate (IRR), the decision to go ahead with the project is neutral. This means that the project will not add value to the portfolio, neither will it deduct any value from your existing investment. To calculate the IRR requires a financial calculator as it involves a lot of iterations to arrive at zero NPV. Alternatively, you can also use Excel to perform the calculation as a predefined worksheet formula has been developed for this purpose. Read more about how to calculate IRR using Excel here.

Net Present Value

One categories of formulas that is available in Excel is for calculating financial information. It includes Present Value, Future Value and most important of all, Net Present Value. Net Present Value is used to evaluate the viability of an investment. If the Net Present Value (NPV) is positive, it means that it is worthwhile to take up that investments. It also takes into account the risk appetite of the investor. This is achieved through the use of the discount rate. An investment can be pretty complicated to evaluate as it is likey to involve cash flows for a number of years. Also, this may include cash out flow during the terms of the investment other than that in the initial stage. To calculate NPV, we usually have to draw a time line to get a clearer picture. And Excel is one good tool that could help you draw this time line. What's more, there are formulas that are provided By Excel that will help you calculate the net present value of this investment. To find out how, click here to find out more.

What is present value and how to calculate PV in Excel

Present value is a financial term used to define the value of a certain amt of money today. The present value of $1 today is $1. It you put $100 in the bank, that $100 will become $105 in one year time at an interest rate of 5%. $105 is the FV of the $100 in the first year, i.e. Year 1. If you continue to put the money ($105) in the bank, it will earn another 5% interest. Your bank account will have $110.25. That is the future value of your $100 today in year 2. If you notice, the future value is dependent on the interest rate offered by the bank. If the interest rate is 10%, the FV of your $100 in year 2 is higher. The amount is $121($100*1.1*1.1). It is equal to your original sum of $100 plus the interest for 2 years. Don't forget that the interest you earn in the first year will also earn you interest in the second year too.

Assuming that you need to save $121 for some expenses two years from now, and you are interested to find out how much you would need to put into the bank today so that you will have $121 in the bank. As the bank is paying an interest rate of 10%, you know that you need to put in less today to obtain $121 in two years as a result of the interest your bank is paying you. That amount you are going to put in today is known as the present value and Excel is able to help you find out what is that amount with its present value formula.

Find out how with this link.

What is present value and how to calculate PV in Excel

Excel provides a comprehensive set of formulas to perform financial calculations such as the present value (PV) of an amount obtained in the future...... more

How to join texts together and place them in a cell

This page will show you 2 methods that can join texts together. The result is the same but the way the formulas are presented are different .... more

Excel course

It is true that you can solve your Excel problem(s) by searching the internet for the functions or formulas and find out how it works. I do that all the time. There are very credible websites on the net where you can find everything you want to know about Excel. The question is, do you know what to find to get to that solution. There are times when I encounter difficulty in solving my Excel problem because I do not know how to start searching. And the cause of it is that I do not know that such as solution exist or certain issues can be resolved using a particular approach. As such, it is important that you build a strong foundation in Excel. With a strong foundation, you can then make Excel work for you and not the other way round. I have come up with a course containing business case studies and how to solve these business case studies using various combination of tools and techniques. This course was orginally a classroom course. To my surprise, it was very well received and my participants are referring their friends and colleagues to attend my course. As such, I decided to create this online course so that I can share my expereince with you. My Excel test is actually the first case in my course. Take the test and you will know what you are missing.

Here is the link which will share with you my story on using Excel to improve my quality of life. If that is what you are looking for, click on the link http://business-excel-training-course.com/online_course.php now!

How to calculate the number of working days between 2 periods

Excel has a worksheet formula called Networkdays. It's purpose is to calculate the number of days between 2 periods minus Sat and Sun in that period. What's more, you could even exclude holidays in your computation. Assuming that you have a holiday in that week (between Mon to Fri), the formula will return only 4 working days for that week. To find out more about this function, visit http://www.advanced-excel.com/date_functions.html#networkdays

Pivot Table features

Pivot tables was one of the great functions ever put into Excel. Its drag and drop function make it so easy to use that minimum or no training is required. A demo is what it takes to use the pivot table. That is for the user. Training is however required to set up the pivot table and to learn about the options to make it easier for the user to manipulate the pivot table.
Another key feature about the pivot table is the ability to filter the records conveniently. For example, users can filter the sales data to look at the records for a particular month at the click of a button.

The info can be presented in multiple levels. For eg., U can present the details in product group, followed by product name, then product dimension.

Like Crystal reports, Pivot table can also retrieve data from desktop databases (e.g. Access, DBase IV) and database servers (sql servers, mysql servers). While pivot tables’ capability is not as powerful as reporting tools such as Crystal reports, impromptu, business objects, it is able to meet the needs of most users at no cost to the user since Excel is a basic office productivity tool ...... more details

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.