Excel Functions

In our website, we do not refer to Excel worksheet functions as excel functions. They are called excel formulas. This is because excel worksheet functions are used within a cell together with the basic mathematical operations such as addition, subtraction, multiplication and division.

We refer Excel functions to another group of tools in Excel. This group of tools is very powerful in managing data in the worksheet. Tools that fall into this category include pivot table, scenario analysis, solver, subtotal, etc.

Find All
Don't look down on this Find ALL function which is available from Excel 2002 onwards. It is one of those functions that will help you clean up your data and help you work exceptionally fast with Excel. Read this find all wrtie-up and I am sure you will agree with me totally.

Pivot Table
Pivot table really deserve the first place in this section. It is one of the best functions ever created in Excel. It should be a common function used by many departments in a company from finance to Human Resource (HR). Of course, the user must know how beneficial is the function before they can put it to good use. We hope our write-up would arouse enough of your curosity to read our Pivot Table page.

MS Query
Pivot table combined with MS Query is a strong competitor of crystal report. The combined excel functions can pull data from databases and database servers. They can present reports that are flexible and can change according to the needs of the reader by using the drag and drop function. Drill down the details is as easy as a few clicks of the mouse. Read about this crystal report competitor.

Circular Reference
Circular reference occurs in Excel when you set up a formula that includes itself in the formula. A simple case of circular reference occurs when you create a SUM formula in D10 trying to add up D5 to D10. In this case, D10 is trying to add to itself. If you are experiencing this error, find out how to remove them in this circular reference page.

Goal Seek function
Goal Seek is an Excel function used to derive the input / variable that will help you to achieve the goal you have set. One of the common applications of goal seek is to find the breakeven point for a project or business such that it makes no profit or loss. See how easy it is to find the breakeven of a project using the goal seek function.

Subtotal function
Do not confuse subtotal function with subtotal formula. They are different. Subtotal function can be used to quickly add rows in between data and compute the total, average, count, max, min and 6 other computations. When pivot table cannot be used, you can consider subtotal function as an ALTernative. This SUBTOTAL function page will show you what it can do.

Autofilter function
The autofilter function allows you to quickly filter a set of records in the excel worksheets based on certain criteria. For example, you can select to see only the companies that belong to a particular industry class by apply the that particular industry class as the autofilter criteria. A sample example on auto-filter is given at the end of this write-up

Conditional formatting
Conditional formating as the name implies formats a cell when the condition(s) provided is(are) satisfied. Find out how conditional formatting works here.

Data Validation
Data Validation is a function provided by Excel to limit users from entering value other than those pre-defined by you. For business users, it is commonly used to create a dropdown list so that users can select the items from the list instead of typing the value or text in. It is also a great way to maintain consistency in your data. Ready to create your dropdown list?

Import Data
Excel offers you many methods to import data into an Excel worksheet. One of them is to use the Excel function "Import Data". Others include MS Query, ADO Connection (VBA required). For more details, click on this import data link.

Remove Duplicates from Data Lists
There are 2 methods to remove duplicates from a list. One is through the Advanced Filter. The other is through the use of IF formula and Auto Filter. Find out under which circumstances should you use one or the other to remove duplicates.

Protect and Unprotect worksheet
You can protect a worksheet or part of a worksheet so that your users are not able to change the content of the cells. ALThough this is a useful function, do not use it to manage confidential information because the protection can be easily broken. Find out what I mean in this protect worksheet page.