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.
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 competitor
crystal report competitor.
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
Subtotal is a formula as well as function in excel. As a function, it is able
to insert subtotal into different groups of data. For example,
subtotal function can
calculate the total sales turnover for each industry group.
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.
|
