How to Change Default Excel Date Format (such as MMDD to DDMM)

Posted on April 2, 2019 by Wyman W

This Excel tutorial explains how to change default Excel Date Format from Change Date and Time Format in Control Panel. How to Change Default Excel Date Format In the country I live, we normally use date format dd/mm/yyyy. However whenever I work for the US company, the system defaults to mm/dd/yyyy. This is annoying and it causes Excel error. For example, if I mistakenly type UK format 31/12/2019, the date will be recognized as a text because there is no such month as 31. It is even risky if ...

How to convert Excel to PDF

Posted on March 31, 2019April 1, 2019 by Wyman W

This Excel tutorial explains how to export Excel to PDF in Microsoft Excel and convert Excel to PDF using different online tools. How to convert Excel to PDF Excel spreadsheets are widely being used in today’s world in order to manage data. The ability to organize data effectively has contributed a lot towards the popularity of the Excel files. However, the Excel files are not portable and it is not the best method available for you to send out a document. That’s because there is a possibility...

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 check duplicate values using Conditional Formatting

Posted on May 5, 2018 by Wyman W

This Excel tutorial explains how to check duplicate values in Excel spreadsheet by highlighting duplicate values using Conditional Formatting. You may also want to read: Excel assign sequence number to duplicate records Excel delete duplicated data in consecutive rows Excel check duplicate values Back in Excel 2003, it was a pain to identify duplicate values. What I used to do was to sort the values in ascending order and then added an assist column to check if the value on the left e...

Excel delete all pictures or delete all pictures in specific Cells

Posted on August 23, 2017 by Wyman W

This Excel tutorial explains how to delete all pictures in a worksheet or delete all pictures in specific Cells. Excel delete all pictures When you copy contents from website to Excel, it is unavoidable to also copy unwanted pictures. As some websites contains dozens of pictures, it is a waste of time to delete pictures one by one. In this tutorial, I will demonstrate how to delete all pictures at once. Let's say I want to copy the below table from a website. In the Country of Origin field...

Excel VBA freeze panes using FreezePanes property

Posted on March 12, 2017 by Wyman W

This Excel tutorial explains how to freeze panes in Excel spreadsheet and freeze panes in Excel VBA using FreezePanes property. Freeze Panes in Excel spreadsheet To explain how to freeze panes in Excel VBA, first I have to explain how to freeze panes in Excel spreadsheet. Freeze Row The most common kind of freeze panes is to freeze the first row as it contains the header of the column. So that when you scroll down the spreadsheet, you can still see the header in row 1. Highlight row 2, na...

Excel Descriptive Statistics

Posted on February 28, 2016 by Wyman W

This Excel tutorial explains how to interpret the summary table generated from Excel Descriptive Statistics. Excel Descriptive Statistics One of the options under DATA > Data Analysis is Descriptive Statistics, which generates a statistics summary of a variable. It is very useful because it saves you a lot of time from entering a lot of formulas in order to get some basic analysis. Before using Excel Descriptive Statistics feature, you should first install Analysis Toolpak Add-Ins. As...

Excel find the last row using Worksheet formula

Posted on January 4, 2016 by Wyman W

This Excel tutorial explains how to find last row using Excel worksheet formula. Excel find the last row Finding the last row in Excel is very important especially for creating dynamic data Range, which can be used for setting dynamic Print Area, dynamic Data Validation list, dynamic data source for Pivot Table, etc. When you google this topic, most article talks about how to find the last row using VBA but not the non-VBA solution. In my previous post I have also detailed different way...

Excel dynamic Data Validation list

Posted on January 3, 2016 by Wyman W

This Excel tutorial explains how to create dynamic Data Validation list that automatically adjusts the data Range. You may also want to read: Excel Dynamic Print Area Excel dynamic data range Excel graph dynamic data range Create Excel Data Validation list Excel Data Validation list restricts users to select values from a drop down box. When the input is different from the predefined list, an error message will pop up. Before we create a dynamic Data Validation list, let's brief...

Excel Share Workbook

Posted on January 2, 2016January 9, 2016 by Wyman W

This Excel tutorial explains how to use Share Workbook function to allow multiple users to open the workbook at the same time. Excel Share Workbook Share Workbook function allows multiple users to open the workbook at the same time. Assume that we have a staff list workbook that is placed in the network share drive. Click on REVIEW > Share Workbook Check the box "Allow changes by more than one user at the same time. This also allows workbook merging" &gt...