Contextures

30 Excel Functions in 30 Days 30XL30D

How to use Excel's Text, Information and Lookup functions, such as VLOOKUP, INDEX, INDIRECT and CELL

Introduction to the 30XL30D Challenge

Do you want to learn more about Excel functions? Even the functions that you use every day can have hidden talents, and pitfalls that you aren't aware of. And there are so many functions, that you probably only use a fraction of them.

In January 2011, for the 30XL30D challenge, we focused on 30 Excel functions in the following categories:

  • Text
  • Information
  • Lookup and Reference

Follow the Contextures Blog

Every day, from January 2, 2011 to January 31, 2011, there was an article on the Contextures Blog, featuring one of the Excel functions. You can read the articles there, and watch the daily Excel Function video.

The Contextures Blog readers voted for the functions they wanted to learn about, and final list of functions isn't decided yet, but should include a few favourites, like:

To see how the Excel functions work, you can download the 30XL30D sample files, from the daily Contextures Blog articles. The files will be in Excel 2010/2007 format, and zipped.

Excel Functions Challenge Daily Blog Posts

Day 01 - EXACT -- check for an exact match between text strings, including upper and lower case.

Day 02 - AREAS -- returns the number of areas in a reference

Day 03 - TRIM -- removes extra spaces from text string

Day 04 - INFO -- get information about the operating environment

Day 05 - CHOOSE -- get a value from a list, based on an index number

Day 06 - FIXED -- round a number and return the result as text.

Day 07 - CODE -- returns a numeric code for the first character in a text string

Day 08 - CHAR -- returns a specified character, for the number entered

Day 09 - VLOOKUP -- looks for a value in first column of table, returns another value from same row

Day 10 - HLOOKUP -- looks for a value in first row of table, returns another value from same column

Day 11 - CELL -- gives info about cell formatting, contents and location

Day 12 - COLUMNS -- returns the number of columns in an array or reference

Day 13 - TRANSPOSE -- changing vertical ranges to horizontal ones, or vice versa

Day 14 - T -- returns the text from a value, or an empty string for no text

Day 15 - REPT -- repeats a text string a specified number of times

Day 16 - LOOKUP -- returns a value from a one-row or one-column range or from an array

Day 17 - ERROR.TYPE -- returns a number based on error type

Day 18 - SEARCH -- looks for a text string, within another text string, and returns its position

Day 19 - MATCH -- searches for value in array, and returns its position, if found

Day 20 - ADDRESS -- creates address as text, from row and column numbers

Day 21 - TYPE -- identifies the type of value in a cell, by returning a number

Day 22 - N -- Returns number based on a cell's value

Day 23 - FIND -- finds text within another string, and is case sensitive

Day 24 - INDEX -- returns value or reference, based on row and column number

Day 25 - REPLACE -- replaces a specified number of characters in a text string, with new text.

Day 26 - OFFSET -- returns reference, of specified size, offset from starting reference

Day 27 - SUBSTITUTE -- replaces old text with new text, in a text string, and is case sensitive

Day 28 - HYPERLINK -- creates hyperlink in a cell, based on link location and friendly name

Day 29 - CLEAN -- removes non-printing characters from text

Day 30 - INDIRECT -- returns the reference specified by a text string

Buy the 30 Excel Functions in 30 Days eBook Kit

If you don't have time to dig through all the old blog posts, you can have all of the 30XL30D content in an easy-to-use single reference file -- the 30 Excel Functions in 30 Days eBook Kit.

Learn more about the 30 Excel functions with

  • a 150 page written lesson guide (PDF format)
  • links to the Excel video tutorials
  • an Excel file with 100 function examples -- choose xlsx format (for Excel 2007 or later), or xls format (for Excel 2003)
  • a handy cheat sheet summarizes the 30 functions with the syntax and description

Buy the kit now -- Only $10

Add to Cartxlsx format -- Excel 2106/2013/2010/2007
Add to Cart
xls format -- Excel 2003

View Cart

30 Excel Functions in 30 Days

More details on the 30 Excel Functions in 30 Days eBook Kit

Watch the Function Videos

To see the functions in action, you can watch the short video tutorial that is posted in each blog article.

There are other videos on the Contextures website too. For example, you can watch one of these videos on MATCH or VLOOKUP:

Excel MATCH Function Video Tutorial  

Excel VLOOKUP Function Video Tutorial 

Hide Excel VLOOKUP Function Errors With IF and ISNA 

Or watch this HYPERLINK video.

More Function Tutorials

30 Excel Functions in 30 Days Kit

SUM Function

VLOOKUP Function

INDEX and MATCH

Count Functions

INDIRECT Function

Search Contextures Sites

Search Contextures Sites

 

Excel Tools Add-in

Free Pivot Table Tools

 

Pivot Power Premium

 

Excel Data Entry Popup List

 

 

 

Excel UserForms for Data Entry

 

Last updated: October 22, 2016 4:02 PM
Contextures RSS Feed