Excel delete blank rows and blank cells before import into Access

Posted on December 15, 2015 by Wyman W

This Access tutorial explains how to delete blank rows and blank cells in Excel before import into Access. You may also want to read: Excel Delete Blank Rows Import Chinese CSV to Excel Import Excel File into Access When you import an Excel file into Access, you may come up with several issues regarding Excel blank cells. Excel blank cells are not considered as Null value in Access Excel blank rows after the last record are also imported Excel blank columns after the last column a...

Excel VBA select multiple worksheets

Posted on December 14, 2015October 24, 2016 by Wyman W

This Excel tutorial explains how to select multiple worksheets using Excel VBA Worksheet.Select Method and using Array. You may also want to read: Excel export Excel to PDF with and without VBA Select multiple worksheets in Excel In Excel worksheet, there are two ways to select multiple worksheets Method 1. Hold down Ctrl and then left click the mouse on each worksheet tab Method 2. Right click on one of the sheet, and then click Select All Sheets One practical use...

Excel add page number in footer

Posted on December 13, 2015 by Wyman W

This Excel tutorial explains how to add page number in footer. E.g. add page number to all worksheets, add page numbers to specific worksheets. Excel add page number in footer to one worksheet Select the worksheet you want to add page number in footer Navigate to PAGE LAYOUT tab > Print Titles Click on Header/Footer tab > select the desired Footer format > OK To check the page number in print preview, Go to FILE > Print Make sure the Settings o...

Excel create organization chart

Posted on December 12, 2015 by Wyman W

This Excel tutorial explains how to create organization chart in Excel. Different ways to create organization chart Some companies use HR systems that have function to auto generate real time organization chart, some develop in house system to create organization chart automatically by pulling the HR database, some companies draw the data from HR system and do it half manually using software (such as Visio), but most companies just do it all manually in Excel / Powerpoint. In Microsoft Vis...

Access Excel extract file name from file path

Posted on December 7, 2015December 26, 2016 by Wyman W

This Excel tutorial explains how to extract file name from file path using VBA and without VBA. Excel Access VBA extract percentage from text Access Excel VBA extract number from text or extract alphabet Excel Extract Time from Date Time or Extract Date Excel extract file name from file path Assume that you have a file path such as C:\Program Files\Google\GoogleToolbarNotifier\a.jpg If you want to extract file name a.jpg from the path in Excel, first we need to think about how to de...

Excel insert file with and without VBA

Posted on December 6, 2015December 21, 2016 by Wyman W

This Excel tutorial explains how to insert file (insert object) in Excel using VBA and without using VBA. Excel insert file object (non-VBA) In Excel, insert file is known as insert object, there are two kinds of insert: Embedded Object - insert the source file into Excel Linked Object - insert the link of file into Excel, not the actual source file In terms of display of the inserted file, there are also two kinds: Display file with an icon, double click on the icon to open th...

Excel install Analysis Toolpak Add-Ins

Posted on November 15, 2015January 30, 2017 by Wyman W

This Excel tutorial demonstrates how to install Analysis Toolpak. What is Analysis Toolpak Add-Ins Excel Analysis Tookpak is an Add-Ins that adds additional analysis functions (such as statistics and engineering functions) to Excel. You have to install the Analysis Toolpak in order to use the functions. Note that if you use the Add-Ins function but the receiver of the file has not installed the Toolpak, the receiver will still be able to see the generated result but the function will fail w...

Create Excel Histogram

Posted on November 14, 2015November 27, 2015 by Wyman W

This Excel tutorial explains how to create Excel Histogram. You may also want to read: Excel clustered column chart Excel scatter chart using text name Definition of Histogram Before reading this post, you are recommended to read my below post. Types of data in statistics (Interval, Nominal, Ordinal). Histogram is a column chart that counts frequency of numbers and categorize the numbers into numerical groups (the groups are called bins). Usually people don't leave space between t...

Excel GetPivotData Function

Posted on November 2, 2015November 2, 2015 by Wyman W

This Excel tutorial explains how to use Excel GetPivotData Function to get Pivot Table Data. You may also want to read: Create Pivot Table using Excel VBA Excel VBA refresh all Pivot Table or Pivot Cache Excel GetPivotData Function Excel GetPivotData Function is used to get data in Pivot Table. For example, in a Pivot Table above, if you type =G3 in a Cell, a formula will generate automatically. =GETPIVOTDATA("Salary",$E$1,"Department","IT","Name","John") If you drag the form...

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,...