Pivot Tables are a great tool for manipulating your data but when it comes to choosing which data source you want to manipulate the list is almost endless, especially with any flat text file, and yes, WITHOUT VBA! Here is a cheat sheet to get you started!

After clicking on the PivotTable and PivotChart Report… option the first step of the wizard will ask you where is the data you want to analyze, make sure you click the External Data Source radio button.

For the second step, click on the Get Data… button.

From the Choose Data Source dialog box, double click on the <New Data Source> option.

A Create New Data Source dialog box will , type in a name for your New Data Source (in this example I have named mine, sample_data_source), the driver drop down list will now be active. The option we need is the Microsoft Text Driver (*.txt, *.csv), select this option.

For the third option we need to specify the directory where the .txt or .csv file is located, click on the Connect button, then click on the Select Directory button and from the file browser navigate and select the directory which contains your text files.

Now the directory should be visible under the Create New Data Source dialog box.

The last step is to select a default table, I know it says it is optional but I recommend it’s best to do this now so you don’t encounter problems down the line. The drop down box should have all the .txt and .csv files listed under the directory we selected previously.

Select the text file you want to base your Pivot Table on and click the OK button. Now you should be able to see under the Sample Data Source menu the data source we just created, select it and click on the OK button.

Now you will be take through all the standard steps that follow setting up a Pivot Table on an External Data Source which I have gone through in detail for these tutorials.

That’s how you create a Pivot Table on a flat text file!

Enjoy!