Excel RAND RANDBETWEEN Function to generate random number

Posted on January 12, 2015March 27, 2015 by Wyman W

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 generate a random integer between two desired integers. Note that RANDBETWEEN and RAND Functions are...

Add Excel Developer Tab

Posted on January 11, 2015January 30, 2017 by Wyman W

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 Excel 2013 Developer Tab. In Excel 2003, you can find everything under "Tools" > "Customize", ...

Excel VBA INSTR Function

Posted on January 11, 2015September 1, 2016 by Wyman W

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" can be found at 8th position within "FinanceDepartment", the function will return number "8". If we...

Excel VBA multiple criteria in Label Filter of Pivot Table

Posted on January 9, 2015September 26, 2016 by Wyman W

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. Suppose you have the below Pivot Table and you want to show Department Name contains "Fin" and "Aud". ...

Excel Dynamic Print Area

Posted on January 9, 2015January 3, 2016 by Wyman W

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 > Name Manager You will find Range A1:B6 is named as Print_Area, which is the reserved name f...

Excel VBA Formula Property of Range

Posted on January 8, 2015May 19, 2016 by Wyman W

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.Formula Remarks You can also set a formula using Value Property Range.Value = "=your formula" ...

Excel IFERROR Function

Posted on January 7, 2015December 16, 2015 by Wyman W

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, which means Excel 2003 and prior versions cannot be used. Syntax of IFERROR IFERROR(value,value_if...

Excel SUMIFS Function

Posted on January 7, 2015 by Wyman W

What is Excel SUMIFS Function? Excel SUMIF Function sum up an array (a range of values) if condition is met. Excel SUMIFS function is an advanced version of SUMIF, allow you to set multiple conditions. If you only need to apply one criteria, please read the use of SUMIF Excel SUMIF Function Syntax of Excel SUMIFS Function SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2, ... criteria_range_n, criteria_n] ) sum_range The actual cells to add, if you wa...

Excel SUMIF Function

Posted on January 7, 2015February 28, 2015 by Wyman W

What is Excel SUMIF Function? Excel SUMIF Function sum up an array (a range of values) if condition is met. Note that you can set only one condition, for multiple conditions you need the SUMIFS Function (with "s" in suffix) Read the below article about SUMIFS Function Excel SUMIFS Function Syntax of Excel SUMIF Function SUMIF( range, criteria, [sum_range] ) range The range of cells that you want evaluated by criteria. Cells in each range must be numbers or names, arrays, or refe...

Writing your first Excel Macro by recording Macro

Posted on January 5, 2015June 22, 2015 by Wyman W

This Excel tutorial guides you to create your first Excel Macro by Record Macro function, and explain how to modify Macro in Visual Basic Editor You may also want to read: Create Excel Add-in and add in Ribbon Excel Record Macro Writing your first Excel Macro by recording Macro, why? First of all, you should face the fact that you cannot know everything. You can use Microsoft Windows does not mean you can write your own Windows. Even if you are a Windows Administrator, it only means...