Excel Search Function

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

This Excel tutorial explains the Excel Search Function for Excel worksheet, and also explains the differences between INSTR and FIND function. Excel Search Function Excel Search function allows Wildcard to search a substring (part of the string) within a string and return the position of the first occurrence. Excel Search Function is not case sensitive, you should consider using Excel FIND Function for case sensitive comparison, but FIND Function cannot use Wildcard. For example, ...

Import Chinese CSV to Excel

Posted on January 21, 2015September 16, 2017 by Wyman W

This Excel tutorial explains how to import Chinese CSV to Excel with correct encoding. (解決CSV轉EXCEL亂碼) You may also want to read: Export Chinese Excel to CSV Import Chinese CSV to Excel When you import Chinese CSV to Excel, some Chinese characters will become a question mark "?" or convert to unexpected Chinese characters, same will happen for symbols and other languages such as Japanese, why is that? To understand what is happening, we need to know what is encoding. Computer stores a...

Export Chinese Excel to CSV

Posted on January 21, 2015September 16, 2017 by Wyman W

This Excel tutorial explains how to export Chinese Excel to CSV, the method is also applicable to other languages. (解決Excel轉CSV亂碼). You may also want to read: Import Chinese CSV to Excel Export Chinese Excel to CSV When you export Chinese Excel to CSV (Comma-separated values), some Chinese characters will become a question mark "?", same will happen for symbols and other languages such as Japanese, why is that? To understand what is happening, we need to know what is encoding. Compute...

Excel Shortcut key and function

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

This Excel tutorial summarizes a list of Excel shortcut key as well as Windows short cut key which are applicable to Excel. Excel Shortcut key Below is a table that summarizes most Excel shortcut key. I referred to the Microsoft website for the Excel shortcut key, removed some unimportant Excel shortcut key, and then combined with some Windows short cut key that are also applicable in Excel and finally reordered and changed description of each shortcut key. Some keys do not work in older ve...

Tips on Microsoft Office Specialist for Excel exam MOS

Posted on January 16, 2015March 31, 2015 by Wyman W

What is Microsoft Office Specialist for Excel exam? Microsoft Office Specialist (also known as MOS) is a certificate that can be earned if you pass an exam. Microsoft Office Specialist has exams for different Office products, which include Microsoft Word, Powerpoint, Excel, Access, Outlook, SharePoint, OneNote. Microsoft Word and Excel are slightly different, each of them have two levels, one is Core level and the other is Expert level. If you take a specific combination of exams, you can...

Excel AutoCorrect Option

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

Excel AutoCorrect Excel AutoCorrect literally means to correct a misspell text to a correct text automatically, but you can also make use of this function to make a mapping list, to convert a text to another text automatically, so Excel AutoCorrect is more than just "Correction". Some people use it to convert abbreviation, convert language. For example, if your mapping list maps M as Mother, as you type "M" in a Cell, it will convert to "Mother". Other Office products also have the exact ...

Excel iterative calculation and circular reference

Posted on January 15, 2015January 15, 2015 by Wyman W

Excel iterative calculation and circular reference To understand what is Excel iterative calculation, you should first understand what is circular reference. Circular reference happens when you type a formula in one Cell which refers to another Cell, but that Cell also refers back to the Cell. For example, Cell A1: = 1+A2 Cell A2: = 1+A1 The above formula refer to each other, this is called circular reference (refer to each other forming a circle). The formula will fail and the be...

Excel lookup partial text using Vlookup with Wildcard

Posted on January 14, 2015February 18, 2016 by Wyman W

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 Vlookup allows Wildcard in the lookup value. Lets recap the syntax of Vlookup first. Syntax of Vlookup...

Change Pivot Table Layout using VBA

Posted on January 14, 2015February 13, 2015 by Wyman W

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 each group. Excel 2007 and onwards - Compact layout By default, Pivot Table layout is called ...

Create Pivot Table using Excel VBA

Posted on January 12, 2015July 2, 2015 by Wyman W

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 create Pivot Table, change Pivot Table Fields, select Critera, delete Pivot Table, etc. In this ...