Large data set - 3 million rows - how to use excel?

[Myura]

Hi!
Please help me with my thesis research. I have downloaded huge data set to analyse.
I heard it can be managed via Power pivot tool. But I do not have intensive knowledge on this.
For the first step, I want to transform the data. I want dates to be in the rows, and company names to be in the columns.
Currently it is like this;

Name Date Price
Company A 15/05/2015 3.5
Company A 16/05/2015 4.5
Company B 15/05/2015 3.5
Company B 16/05/2015 4.5


Mush appreciated for your help

 

[SteveO59L]

Insert, PivotTable & follow the wizard

 

[Myura]

Thank you very much for your reply.
Since I'm dealing with very large data set, I think I cannot use normal excel sheet or Pivot table. I tried upload CSV file to the data manage to power pivot tool. But I'm not sure how to do the transformation in there.

Thanks again

 

[Fishboy]

Doesn't Excel stop at 1048576 rows? That seems 1951424 rows less than you need

 

[Myura]

Yes, That's the problem.But I read in this link( https://social.technet.microsoft.co...-1048576-rows-to-a-worksheet?forum=powerquery) that data model can support unlimited size. But I do not know how to work in the data model tool.

 

[Peter_SSs]

Welcome to the MrExcel board!

Are you aware that there is a specific forum here for Power BI Tools (including Power Pivot). You might be best to ask your question there. Or post back here & I could move the whole thread to that forum for you.

 

[Myura]

I'm a noob here. It would be much appreciated if you could move the whole thread to that forum for me. Thank you so much.

 

[Peter_SSs]

I'm a noob here. It would be much appreciated if you could move the whole thread to that forum for me. Thank you so much.

Done

 

[one3nine0]

The easiest way to do this, would be to use MS Access if you have it.

First upload the CSV file to MS Access via the External Data tab in the ribbon. As it is a CSV, select "Text File" and follow the wizard.
Once you have saved the Access file, you can access it in PowerPivot.

In Excel, you can go to the PowerPivot Tab in the ribbon, and select "Manage"
From there, select "From Database" and select the Access file and table with the data.

Once the table has been inserted into PowerPivot, select Pivot table and you can table out the results as you need.