This Excel tutorial explains how to lookup partial text using Vlookup Function with Wildcard. Excel Vlookup second matched value or specific occurence Excel Lookup Function lookup multiple criteria not in the first column Excel lookup Function lookup multiple criteria Excel lookup partial text using Vlookup with Wildcard Many people know how to use Vlookup but not many people know Vlo...

Change Pivot Table Layout, why? To illustrate why we want to change Pivot Table Layout, lets see the below example. Assume that we use the following data to create a Pivot Table. We want to show the total summary, group by Department, Empl ID. Excel 2003 - Tabular layout Pivot Table layout is called "Tabular", where each grouping is in one column, and the subtotal is at the bottom of e...

This Excel tutorial explains how to create Pivot Table using Excel VBA, set Print Area for Pivot Table Create Pivot Table using Excel VBA Create Pivot Table using Excel VBA is very straight forward and easy to use. If you don't understand any Object or Property that VBA Pivot Table uses, simply use "Record Macro" function to do recording to see the underlying VBA code behind each action, such as...

This Excel tutorial explains how to use Excel RANDBETWEEN Function and Excel RAND Function to generate random number and explain the difference. Excel RAND RANDBETWEEN Function - generate random number Before Excel 2007, Excel uses RAND Function to generate a random number that is greater than zero and smaller than 1. Since Excel 2007, a new Function RANDBETWEEN was added, it is used to gen...

This Excel tutorial explains the use of Excel Developer Tab and how to add Excel Developer Tab for Excel 2007, 2010 and 2013. What is Excel Developer Tab? Excel Developer tab is a category in menu bar which contains everything you need regarding Macro, such as recording Macro, inserting Controls (button,  text box, drop down list, etc), view the Property of Control. Below is a screenshot of Exce...

This Excel tutorial explains how to use Excel INSTR Function for VBA, and explain difference among vbUseCompareOption, vbBinaryCompare and vbTextCompare. Excel VBA INSTR Function Excel INSTR function is used to search a substring (part of the string) within a string and return the position of the first occurrence. For example, in the string "FinanceDepartment", the substring "Department" ca...

This Excel VBA tutorial explains how to apply multiple criteria in Label Filter of Pivot Table. Problem with multiple criteria in Label Filter of Pivot Table There are three kinds of Filters in Pivot Table for each Pivot Filters - Label Filter, Value Filter, Manual Filter. In this tutorial, I will focus on Label Filter, because I use "Department" as an example, which is non-numerical. Suppo...

This Excel tutorial explains how to set dynamic print area. You may also want to read: Excel dynamic Data Validation list Excel dynamic data range Excel graph dynamic data range Excel Print Area Print Area is a function to define the worksheet Range you want to print. To set a Print Area, select a Range, say A1:B6, then click on Set Print Area   Click on FORMULA tab...

This Excel VBA tutorial explains how to use Formula Property in Excel VBA. Formula Property in Excel VBA There are two functions for Formula Property: 1. To set formula in a Range, like the way you type a formula in worksheet 2. To retrieve formula from a Range Syntax of  Formula Property To set a formula: Range.Formula = "=your formula" To retrieve a formula: variableName = Range.F...

This Excel tutorial explains how to use Excel IFERROR function for worksheet. IFERROR can be used with vlookup to capture ISNA error. You may also want to read: Check Excel #N/A using ISNA Function What is Excel IFERROR Function? Excel IFERROR Function is an error handling function, if an "Error" is found, then a desired value is returned.  This is a new function introduced in Excel 2007, ...