Excel has a new tool and it is one of the best tools available. It is called PowerQuery in versions prior to Excel 2016 and Get & Transform in Excel 2016. From Excel 2016 it is standard so you don’t need to do anything, but for the previous versions you may need to switch on/ download an add in.
Excel 2016 Get & Transform
If you have Excel 2016 then you have it already. Go to your Data Ribbon and you will see a subsection called Get & Transform.

Excel 2013 and prior- PowerQuery
For prior versions, depending on which version, you may already have it but it is not switched on, or you need to first download and install and add in.
First step would be to check if you already have it on and you didn’t notice (it happens :))
If you have a ribbon called PowerQuery as shown below, then you have it.

If you don’t have this ribbon, then perhaps it’s not switched on. Go to the File option and click on Options as shown below

Choose the Add-Ins option, change the Manage dropdown to COM Add Ins and click Go

If you see the Microsoft PowerQuery for Excel option, switch it on and click OK

If you don’t see it, then you will need to load it. There are versions available for Excel 2010 and Excel 2013 so most people should be able to get it.
To download and install the files go to the Microsoft site. Then you can follow the steps above.
To learn more about PowerQuery look at our training program.
Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.
Related
Begins with, Ends With, Contains missing in PowerQuery Filter