Excel Functions
In our website, we do not refer to Excel worksheet functions as 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.
In our website, functions refers to another group of tools that is very powerful in managing data in worksheets. They 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 that is ever created in Excel. It is one of the essential functions for business users, from Finance to Human Resource (HR). Of course, users must first know the uses of this function before they can apply it. Read our Pivot Table write-up if you would like to find out more.
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 page to exploit its power.
MS Query
Pivot table combined with MS Query is a strong competitor of crystal report. The combined excel functions can pull data from excel files, databases and database servers. They can present reports that are flexible and can change according to the needs of the users simply by doing some drag and drop within Pivot Table. Drill down the details is as easy as a few clicks of the mouse. Read more about this crystal report competitor.
Circular Reference
Circular reference occurs in Excel when you set up a formula that refers to the cell itself. For example, a 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 achieve the goal you have set. It is working backward to find the value that will achieve the target number 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 insert rows in between groups of data and compute the total, average, count, max, min and 6 other calculations. When pivot table cannot be used, you can consider using the subtotal function. This SUBTOTAL function page will show you what it can do.
Autofilter function
The autofilter function allows you to quickly select a set of records in your Excel worksheets based on certain criterias. For example, you can select to see only the companies that belong to a particular industry class by apply the particular industry class description as the autofilter criteria. An screenshot on Autofilter can be at the end of this remove duplicates page.
Conditional formatting
Conditional formating as the name implies, formats a cell when the condition(s) provided is(are) satisfied. Find out more about conditional formatting.
Data Validation
Data Validation is a function provided by Excel to limit users from entering value other than those pre-defined. 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 into the cell. This help 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 the circumstances for using one or the other in this remove duplicates page.
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.
Hyperlink function
Other than making the hyperlink jump to a web page, the hyperlink in Excel can also be used for navigation to different worksheets. This write-up demonstrate how hyperlink is used to create a table of content page.
PowerPivot
PowerPivot is a new function in Excel, added from Excel 2010 version onwards. It has the capability of Pivot Table plus the power to manage databases and multiple sources of data. If you have a huge database consisting of millions of records and do not want to buy an expensive report writing tool, PowerPivot is your answer. Check out this PowerPivot writeup to get a quick understanding of its capability.
