Blog Posts on MS Excel
There are at least 4 different ways to create Excel Tables and they are… Using Shortcuts CTRL + T is the most used Keyboard Shortcut for creating Excel Tables. But there is one more shortcut for the same and that is CTRL +L Select a cell in the data range > Press CTRL + T … Read more
How to import Facebook Friends List to Excel is one of my most watched YouTube videos. In this blog post I will explain this technique step by step, for those whose prefer reading over video lectures. Facebook has an information section, where we can View or Download all those data related to our Facebook Account. … Read more
In the previous post, I have explained How to Combine Data from Multiple Worksheets of an Excel Workbook. That was a crude method of static nature and won’t be the right choice, when you have plans to Add or Delete worksheets. The method explained here is recommended for those who want to Combine data from … Read more
In this blog post, I am explaining the Power Query Method to combine data from multiple worksheets. If you are a person who deals with data in Multiple Worksheets, You should definitely try this one. Following is the screenshot a workbook which contains 7 worksheets called Violet, Indigo, Green, Blue, Orange, Yellow and Red. These … Read more
When we transform data using Power Query, every Transformation Step is recorded as an M Code. And most of the time, values got hard coded in these codes. The problem with Hard coding is, when the user decides to change the input data, the Source code (Query in this case) also should be modified accordingly. … Read more
Like the UPPER, LOWER and PROPER functions we can also use Power Query to convert letters cases. Following is a list of names/words that are written with a combination of upper and lower case letters. To convert these words into UPPER, LOWER or PROPER cases using Power Query, Select a cell in the data set … Read more
In this blog post, I am explaining an easy method to add a new column to a data set which contains Automatically updating Serial Numbers. Following is the data set to which I want to add a new column of Serial Numbers. To add a column of Automatically Updating Serial Numbers to this data, Select … Read more
Like the Remove Duplicates feature in Excel, Power Query also can be used to remove duplicates from a data set. But, why should we use Power Query to get rid of the Duplicates? Here is why . . . Following is a sample sales data containing duplicate records (The records with arrows against them). Now, … Read more
In this article, I will explain an easy method to Extract or Remove Special Characters, Alphabets or Numerals from a data set using Power Query in Excel. Following is the data set from which I want to remove the special characters like ‘!’, ‘@’, ‘#’, ‘$’, ‘%’, ‘&’, ‘*’, ‘(‘ etc. To clean this data, … Read more
MODE function in Excel will return the most frequently occurring number in a data set. But for finding the most frequently occurring text string in a data set, we don’t have an Excel function for that. In this article, I will show you how to find the most repeated word in a data set using … Read more