This Excel tutorial explains how to automatically refresh pivot table using Macro / VBA and Pivot Table Options. Excel automatically refresh pivot table You may have a pivot table that links to data in the same workbook or external data, and you may want to refresh pivot table automatically in order to see the most updated data. In this tutorial I will show several ways to automatically refresh ...

This VBA tutorial explains how to delete all workbooks in folder using Access VBA and Excel VBA. Access Excel VBA delete files in folder Suppose you want to write a Macro to export files to a folder. There could be problem if the folder already contains the files you want to export. To avoid this, you probably want to delete the folder and create a new folder before exporting the files. Alternat...

This tutorial explains how to use Access / Excel VBA to delete folder if folder exists already. Access Excel VBA delete folder if folder exists Suppose you want to write a Macro to export files to a folder. There could be problem if the folder already contains the files you want to export. To avoid this, you probably want to delete the folder and create a new folder before exporting the files. A...

This Excel tutorial explains how to delimit cell value into rows using VBA and non VBA. You may also want to read: Convert cell contents into table Access Excel custom split Function to delimit text Excel VBA delimit Cell value into rows To demonstrate how to delimit Cell value into rows, I use email address as an example. One day I tried to copy a list of email addresses from Outloo...

This Excel tutorial explains how to show / get formula of a cell using FormulaText Function and VBA. Search formula of a Cell Assume that you have the below spreadsheet and you don't know whether any cells contain a formula.   Press CTRL + F to open Find and Replace dialog In the Find what text box, enter equal sign = , then press Find Next button Note that if a cell con...

This Excel tutorial explains how to use T.TEST Function to perform Independent Sample T Test and Paired-samples T Test. What is Independent T Test In statistical inference, we are interested to know whether a small sample comes from a population. To inference using sample mean, when the population standard deviation and population mean are known, we can use Z test to interference the population ...

This Excel VBA tutorial explains how to use Worksheet.Select Method to select a single worksheet or multiple worksheets. Select worksheets in Excel When you click on a worksheet tab, the worksheet is highlighted. To select multiple worksheets, you can hold down Ctrl and then left click the mouse on each worksheet tab. To select all worksheets at once, right click on one of the sheet, and t...

This Excel VBA tutorial explains how to sort worksheet tab in alphabetic order (ascending order or descending order). You may also want to read: Excel Range Sort Method to sort data Use VBA Excel Function to sort data in ascending order Access Excel assign number for custom sorting sequence Sort worksheet tab by name When you have a lot of worksheets in a workbook, you may want...

This Excel VBA tutorial explains how to consolidate multiple workbooks into one workbook. You may also want to read: Excel VBA Consolidate worksheets into one worksheet Excel VBA combine worksheets columns into one worksheet Consolidate multiple workbooks into one workbook Assume that you have a lot of workbooks, each workbook contains multiple worksheets. You may want to copy all works...

This Excel tutorial explains how to use Excel IRR Function to calculate Internal Rate of Return. You may also want to read Excel NPV Function to calculate Net Present Value Internal Rate of Return Internal Rate of Return (IRR) is the discount rate of cash flow at which an investment breaks even. Similar to Net Present Value (NPV), which discounts a series of cash flow back to present value...