Remove Columns Using Power Query or Get & Transform
Excel in 2016 has a new set of features known as Get & Transform which was known as Power Query in Excel version 2010. It provides you with a platform to perform a series of steps to help you to transform your Excel data. There is one step which will help you to remove columns easily. Sometimes you must want to remove some existing columns which you don’t want to see in your final report but also want to keep it in your source data, but you don’t know how to do it so, this tutorial will be helpful for same.
For this tutorial, I have the following source data given below. Now you will be seeing that the marked column is the one I need to remove to show you as an example.
STEP1: Insert TABLE
First you need to turn you selected data into an Excel Table by pressing this shortcut Ctrl + T or by going to Insert > Table.
STEP 2: Open Power Query
If you are using Excel 2016:
Go to Data > From Table (Under Get & Transform)
Or if you are using Excel 2013 & 2010:
Power Query > Excel Data > From Table
STEP 3: Removing Column Using Power Query
After following them you will see an open Power Query Editor on your screen.
Now hold the CTRL key to select the multiple columns if you need to remove multiple columns or if you need to remove only one column then select that column. After selecting the column(s) follow this procedure:
Go to Home > Manage Columns > Remove Columns > Remove Columns
Now to open a new worksheet in your Excel workbook with the cleaned table, Click Close & Load from the Home tab.