Search Contextures Sites
Excel Function Tutorials and Samples
Excel Function Tutorials
Follow the links below to step-by-step Excel worksheet function tutorials.
30 Excel Functions in 30 Days This series on the Contextures Blog covered 30 Excel functions from the Text, Information, and Lookup categories, based on a reader vote. Each article covers the function basics, and has examples and a video tutorial.
Excel Function -- Count cells Includes examples for the COUNT, COUNTA, COUNTBLANK, COUNTIF, SUMPRODUCT and SUBTOTAL functions. Count cells with numbers, data, blank cells, or cells that match one criterion or multiple criteria. Count numbers in a filtered list, specific items in a filtered list, or visible items in a filtered list.
Excel Function -- IFERROR Use the IF function and IFERROR function with VLOOKUP to prevent error results.
Excel Function -- INDEX The Excel INDEX function can return an item from a specific position in a list. The INDEX and MATCH functions can be used together, as a flexible and powerful tool for extracting data from a table.
Excel Function -- INDIRECT The INDIRECT function returns a reference to a range. Create a reference that won't change if row or columns are inserted in the worksheet. Or, use it to create a reference from letters and numbers in other cells.
Excel Function -- MATCH The Excel MATCH function can return the position of a value in a list. The INDEX and MATCH functions can be used together, as a flexible and powerful tool for extracting data from a table.
Excel Function -- SUBTOTAL Use the SUBTOTAL function to sum numbers in a filtered list.
Excel Function -- Sum cells Includes examples for the SUM, OFFSET, SUMIF, SUMPRODUCT and SUBTOTAL functions. Sum a specific range of cells, or cells that match one criterion or multiple criteria. Sum numbers in a filtered list, or the largest numbers in a list.
Excel Function -- TEXT Use the TEXT function to format numbers and dates when combined with other data.
Excel Function -- VLOOKUP Create a lookup table, and a VLOOKUP formula to extract specific data from the lookup table. Use the IF function and IFERROR function with VLOOKUP to prevent error results. Troubleshoot the VLOOKUP formula.
Excel Function Video Tutorials
Follow the links below to short video tutorials for Excel functions.
- Find Product Price in Excel with the VLOOKUP Function
- Use MATCH in a VLOOKUP formula
- Change Percentages to Letter Grades in a VLOOKUP formula
- Hide Errors in a VLOOKUP Formula With IF and ISNA
- VLOOKUP From Another Workbook
- Count functions for Numbers, Data and Blank cells
Example Files and Add-ins
The Excel function tutorials listed above have sample files that you can download, and use to work through the tutorials.
Also, a set of Excel function sample files (links below) were created by Microsoft Excel MVP Norman Harker, an Australian academic specialising in real estate appraisal, investment analysis and real estate financial feasibility studies.
The files are provided "AS IS" with no warranties. They can be freely distributed and used without a licence, but may not be sold, or included as part of any other product without the written permission of Norman Harker & Associates.
Excel Function Samples -- a zipped Excel workbook that contains:
-- All Functions Sheet with Name, Source, Description, Syntax
-- Classified functions Sheets with Name, Source, Description, Syntax and separate listing
of all arguments with descriptions for that classification.
-- All Arguments Sheet -- All Excel Arguments and Descriptions Apx01FuncList.zip 80 kb
-- Version with buttons that open applicable Excel Help page, for Excel 2000 or higher
available by email request to Norman Harker & Associates
or click here to download FuncRefXLv4-0.zip 301 kb
Excel Function Examples -- a zipped Word document that contains a few notes on special items such as Rounding and Counting functions. Hard Copy of Excel File in Table form (Except for All Arguments sheet). FuncRefWdV2-1.zip 156 kb
ISO Week Number -- Excel add-in which contains a User-Defined function to calculate the ISO-compliant week number. ISOWEEKNUMNH.zip 9 kb
Contextures Inc., Copyright ©2011
All rights reserved.