# Microsoft Excel Formulas (Formulae) & Tips/Tricks

## Convert Excel Spreadsheets Into Live Calculating Web Pages

• ### Summing Formulas

Excel SUM Formula Probably, the most widely used Excel formula, the SUM function in Excel is specifically designed to add values from different ranges, or one range. The SUM formula can be typed into a cell in Excel, or inserted via the Insert Function tool to the left of your Formula bar.

Excel Autosum Function Because adding numbers is probably the most common function that Excel is used for, Excel has a built-in Feature called AutoSum located on the Standard toolbar.

Array Formulas in Excel I strongly suggest you read this very important information on using array formulas in your spreadsheets. Array formulas can let you specify more then one criteria to Sum, Average, Count etc by. Many examples of how to use them.

Excel Conditional Sum Wizard The Conditional Sum Wizard is an Add-In to Excel that is used to summarize values in a list based on set criteria.

Sum With Multiple Criteria Examples of Excel formulas to sum a range of cells that meet multiple criteria. ,DSUM, SUMPRODUCT and SUM with an IF function/formula.

Sum Every nth Cell

Sum nth Largest/Smallest

Sum Cells by Color

Increase/Decrease Values If you have values on an Excel Worksheet that you need to permanently increase, or decrease you can use Paste Special. No Excel formulas needed!

Excel Subtotals In Excel we can use the Subtotals feature found under Data on the Worksheet Menu Bar to Subtotal a table of data.

Bold Excel Subtotals Here is how we can use Conditional Formatting in Excel to automatically bold the results of Subtotals.

Making the SUBTOTAL Function Dynamic How to use the one function (not feature as above) to perform a chosen operation on only visible cells after using Auto Filter.

Excel Data Tables Data Tables are a range of cells that are used for testing and analyzing outcomes on a large scale.

Sum Between Date Ranges

• ### counting Formulas. Do this often? Free Custom Excel Functions Add-in

Count With Multiple Criteria Examples of Excel formulas to count a range of cells that meet multiple criteria. ,DCOUNT, DCOUNTA, and SUM with an IF function/formula.

Count Each Item Only Once Count repeats in range one-time only.

Count Words in a Cell Unlike Microsoft Word, Excel does not give us a ready made way to find out the number of words in a cell, or a range of cells containing text, or words. These Excel formulas show you how.

Count of Each Item in a List With the aid of Advanced Filter and the COUNTIF we can get a count of each item that appears in a list.

Count Between Date Ranges

• ### LOOKUP & REFERENCE Formulas. Do this often? Free Custom Excel Functions Add-in

VLOOKUP Perhaps one of Excels most commonly used Excel Formulas is the VLOOKUP. It is also possibly the Excel formula that most people have problems understanding.

Stop #N/A! In Vlookup

Excel HLOOKUP Another one of Excels most commonly needed Excel Formulas is the HLOOKUP.

Left Lookup in Excel Excel is very rich in Lookup formulas, with perhaps the VLOOKUP being the most popular. However, the draw-back with all Excel's Lookup formulas is that they will only look in the left most column and return the result from the corresponding cell to the right. There are times when users need to lookup data in any column of table and return the corresponding cell to the left.

Excel Lookups With Array Constants Would like to show you what I call: In-Cell-Lookups. These are the perfect replacement for multiple nested IF functions.

Dynamic Formulas Rather than bog you Spreadsheet down with hundreds, if not thousands of formulas, use a single formula with flexible and changeable Arguments. In this example I will use the INDEX/MATCH functions nested together. You can also instruct the end formula to return the corresponding cell, to the match, on the left or right.  However, the the same principles can apply to most Excel formulas.

INDEX/MATCH Functions While the Vlookup Function is very useful, it cannot look in any Column, only the 1st. Also, it cannot offset x columns to the left or return the value x rows before or after the found value. An INDEX & MATCH combo will allow for all of this flexibility.

Find Nth Occurrence in Excel As you may already know, we can use VLOOKUP, or INDEX/MATCH to locate the first occurrence of a specified value in a list, or table of data. However, Excel has no ready made formula that allows us to locate say the second, or third occurrence etc of a specified value.

Excel Lookup Table VLookup is the perfect Excel formula for numerical values contained in a range. However if you tried to use VLookup with text in a table, it's use would be limited, For example surnames such as Smith, Smithson, Smithy, Smithson-Jacobs would create problems.

Dynamic Excel Lookups These are very handy for when you lookup data but cannot be sure which column your returned data should come from. In other words, users may have inserted a column within the table.

Lookup & Return Corresponding Result While we can use any of the links above for lookup formulas, all require a table of cells in a Worksheet. If you only have small number of items to return based on the value of another cell, we can do the lookup without leaving the cell!

Excel Hyperlinks How to get the most from hyperlinks. Hyperlinks that still work when a Worksheet name changes. Create a hyperlink to a Chart Sheet

Hyperlink to Lookup Result Excel is quite rich in Lookup type formulas, some of the more popular ones are VLOOKUP , INDEX/MATCH and HLOOKUP . These all do a great job in looking up a value we specify and then return a corresponding result. However, it's often the case that we need to go to the row containing the found value, or its offset return.

Dynamic Reports Here you can see how we can the Database functions to produce dynamic reporting from a table of data.

Multi-Table Lookup Use the Dependent Validation Lists method to tell Excel to lookup any chosen item in any table you tell it.

• ### IF Formula. Do this often? Free Custom Excel Functions Add-in

Nested IF Formula Limitation One limitation of Excel is that we can only nest Excel formulas up to 7 levels. This is particularly limiting when trying to add nested IF Functions/Formulas that require greater than 7 conditions.

7 Nested IF Formula Limitation Workaround 2

• ### ROUNDING & PERCENTAGES. Do this often? Free Custom Excel Functions Add-in

Round Excel Numbers Methods and Excel Formulas for rounding numbers in Excel

Percentages in Excel A percent is a ratio of a number to 100 and is usually expressed using the percent (%) symbol. In Excel, a number can be expressed in a few different ways and used to calculate a percentage in Excel.

• ### Excluding ZEROS. Do this often? Free Custom Excel Functions Add-in

Average Excluding Zeros Excel has a built in formula/function that makes averaging a range of cells easy. However, the Excel formula AVERAGE does not exclude zeros.

Minimum Excluding Zeros Excel has a built in formula/function that makes finding the minimum value in a range of cells easy. However, the Excel formula MIN does not exclude zeros.

• ### tips and tricks

Quick & Dirty Tips For Microsoft Excel

Excel Tips and Tricks This page consists of many tips and tricks you can use in Excel, from shortcut keys to working with formulas and functions. If you know of a good one, please let us know!

Handy Hints For Spreadsheets Here you will find out how to use Excels Conditional Formatting and Data Validation. It explains in detail how you can use these great Excel features to work with duplications in your spreadsheets. Best of all it shows how they can be used to prevent them!

Excel Formulas This page will take you to lots of examples of Excels Formulas. It includes formulas for dates, times, text and numbers. Each page also includes some formula tips and tricks.

Nifty Excel Formulas Download a Workbook full of nifty Excel formulas: For Last Value, Close Match, AutoFilter Lookup & Much More

Excel resources

• ### SELF HELP

Excel Formula Errors As soon as you have discovered how to use formulas in Excel, you will likely need to lean how to correct or hide formula errors.

Excel Newsletter Archive Have a browse through our free Excel newsletter archives. Here you will find a bit of everything from basic shortcuts to VBA for Excel. If you like what you see then sign up, it's completely free!

DEBUG / EVALUATE FORMULA ERRORS #NULL!, #DIV/0!, #VALUE!,#REF!,#NAME?, #NUM! and #N/A

FREE Excel Training Free Level 1 (Basic) Excel Training Compliments of Ozgrid!

Excel Forum FAQ List of the most popular asked questions in the Excel help forum . Excel Formulas through to Excel VBA macro code.

• ### Charts

Excel Charts Here are many examples that can be used to create Excel charts with a difference. The Excel charting pages listed here show many different charting techniques, examples and solutions.

Excel Chart Tips and Tricks There are many little known tricks that can be used with Excel charts and these are just some of them. If you want your chart to update automatically, not plot zeros, or have scrolling charts etc, then you will find it here!

• ### commission/tax formulas. Do this often? Free Custom Excel Functions Add-in

Calculate Sliding Scale Tax Calculating tax, or commission that is based on a sliding scale, or by bracket, can be complicated. The Excel formula here is one that can be used using Excel's built in function/formulas. That is, the IF function/formula and the SUM function/formula.

Calculate Fixed Rate Tax The Excel formula VLookup can be used to look for numeric values in lists and tables and to use those values in a calculation. A perfect example of how VLookup can be used to glean information is to work out how much tax you are required to pay.

• ### PIVOT TABLES

Excel Pivot Tables Take the FREE online tutorial on Excel Pivot Tables. Download the Excel Workbook examples are more.

Pivot Table Tutorial

Pivot Table Grouping Problems

Calculated Fields in Pivot Tables  Add your own custom calculations in Pivot Tables.

Grouping Pivot Table Data Grouping Pivot Table Data. How to Group so you don't get: Cannot Group that Selection

>> Onto Excel Formulas/Tips Page 2

 Accessibility Activating Excel Add-ins Automation and programmability Charts Collaboration Customizing Excel and the Web Excel Demos Exchanging data with other programs File conversion and compatibility Filtering, sorting, and conditionally formatting data Forms Formula and name basics Function reference Getting help Glossary Importing data
 Installing Macros PivotTable reports and PivotChart reports Publishing to SharePoint Server Excel Services Quizzes Saving and printing Security and privacy Summarizing, consolidating, and outlining data Training Validating data What-if analysis What's new Workbook management Working in a different language Working with graphics Working with other programs Working with XML Worksheet and Excel table basics
FROM MICROSOFT - Excel 2003
 Creating Formulas Using References | Array Formulas | Names and Labels

 Common Formulas Conditional Formulas | Date and Time Formulas | Math Formulas | More...
 Controlling Calculations
 Correcting Formulas
 Working with Euro Currency

### ALL EXCEL FORMULAS BY CATEGORY Or Subscribe to Excel Formula Feeds

Live Excel Formula Feeds You Can Subscribe to

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over \$70. 00. ALL purchases totaling over \$150. 00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] com 31 days after purchase date.

Looking for Custom Excel Formulas & Functions Or Excel VBA Macros?