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.