Excel automatically select specific columns using Custom Views and Query

Posted on March 9, 2019 by Wyman W

This Excel tutorial explains how to select specific columns in a worksheet with many columns using Custom Views and Query. You may also want to read: Create Excel Query and update Query Excel automatically select specific columns using Custom Views and Query Many people including myself like creating a master report with many columns and then send it to users for them to manually select columns they need. The reason is that it is time consuming to customize a lot of reports and it is dif...

Excel first date of month and last date of month

Posted on March 14, 2016March 14, 2016 by Wyman W

This Excel tutorial explains how to find first date of month, last date of month using Excel worksheet Function and Excel VBA Function. You may also want to read: Excel Use Date in IF Condition Excel first date of month To find first date of month in Excel, first we have to understand DATE Function, you can use it to combine the year, month and day to a date. Syntax of Date Function DATE( year, month, day ) Example Formula Result Explanation =Date(2012,1,1) 1/1/2012 Co...

Excel Hyperlink Function

Posted on February 21, 2016February 21, 2016 by Wyman W

This Excel tutorial explains how to use Excel Hyperlink Function to add hyperlink to Cell and send email. Excel Hyperlink Function Excel Hyperlink Function is to add a hyperlink to a Cell. You can add hyperlink without using any Function. Right click on a Cell and then select Hyperlink to enter a web address or email address. With Excel Hyperlink Function, you only need to enter arguments for the Function instead of right clicking a Cell. This is useful if you are trying to create a ...

Excel dynamic data range

Posted on December 30, 2015January 3, 2016 by Wyman W

This Excel tutorial explains how to create Excel dynamic data range using Name Range. You may also want to read: Excel dynamic Data Validation list Excel Dynamic Print Area Excel graph dynamic data range Excel dynamic data range The purpose of Excel dynamic data range is to select data range based on how many data you have input instead of using a fixed range. Assume that you have the data set below. You can select A1 to B11 as the data source, but if you do that, you n...

Check Excel #N/A using ISNA Function

Posted on December 16, 2015 by Wyman W

This Excel tutorial explains how to check Excel #N/A cells using worksheet formula ISNA and VBA Function. You may also want to read: Excel IFERROR Function Cause of Excel #N/A #N/A is usually caused by lookup related functions where a value cannot be found in lookup table. Lookup Functions include Vlookup, Match, HLookup, Lookup. Another common cause is that when you use an Add-In formula, and then send the file to someone who doesn't have that Add-In. The formula was fine at the time...

Excel VLookup different format (a mix of text and number)

Posted on October 29, 2015September 17, 2017 by Wyman W

This Excel tutorial explains how to Vlookup different format (a mix of text and number). You may also want to read: Case sensitive lookup Excel verify Text format and convert number to text Excel verify number format and convert Text to Number Issue with VLookup different format (a mix of text and number) Sometimes your data source table may contain a mix of text and number, let's say Employee ID in column A. In the Lookup table, when your lookup value (column D) are all number,...

Excel VBA Copy Range and Paste Method

Posted on July 12, 2015June 2, 2017 by Wyman W

This Excel VBA tutorial explains how to copy Range and paste data using VBA Excel VBA Copy Range Method Range.Copy Method is a very convenient Method to copy and paste Range to destination in one line of code. All the formatting will be copied and pasted. You can copy a Range, a column, a row. Syntax Range.Copy(Destination) Destination is optional. If not specified, Excel copies the Range to the Clipboard. In some cases, we may just want to copy to Clipboard and then use Paste/PasteSp...

Excel verify Text format and convert number to text

Posted on July 11, 2015 by Wyman W

This Excel tutorial explains how to verify Text format in a column. If it is a Number then convert Number to Text. You may also want to read: Excel verify Number format and convert Text to Number Excel verify Text format Three kinds of Cell contents are considered as Text 1) Non-number - such as alphabet, symbol 2) A mix of number and non-number 3) Number with small triangle on the top left of the Cell Number can be in two forms in Excel - Text and Number. If a Number is a ...

Excel verify Number format and convert Text to Number

Posted on June 22, 2015July 12, 2015 by Wyman W

This Excel tutorial explains how to verify Number format in a column. If it is a Text then convert Text to Number. You may also want to read: Excel verify Text format and convert number to text Excel Cell Function to check Cell format, address, contents Excel verify Number format Number can be in two forms in Excel - Text and Number. If a Number is a Text, there is a small triangle on the top left of the Cell. By default, number in Text format aligns to the left while Number alig...

Excel data validation and combo box

Posted on June 7, 2015February 22, 2016 by Wyman W

This Excel tutorial explains how to create Data Validation (drop down box) and Combo Box (Form Control / ActiveX Control). You may also want to read: Excel Indirect Function to convert text to Reference Excel difference between Form Controls vs ActiveX Controls Excel Data Validation and Combo Box If you want to design an Excel template to restrict users to input specific values instead of free text, you need to create a drop down box for users to choose values from. There are three...