• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Getting the right computer and system to run excell

Ross Fontana

New Member
Hi,
I am using excel to conduct analysis for a project I am working on. My computer is and I7 6700HQ with 16GB ram. I have a hard drive and an SSD drive to save and work files from.

I have windows 10 and MS Office 2016 Professional

I currently have a file that has 230,000 rows and 105 columns of data. A lot of data.

I am doing vlookups and producing pivot tables to produce some out puts that I use to analyze the data sets.

The processor takes about 3-5 minutes to run a vlookup and will do so each time I update a record. this is time consuming and extremely inefficient.

1. What computer set up is best to handle such large volumes of data and calculation? I would figure that mine was sufficient.

2. Is there something I am doing wrong in the set up that prevents Excel form working efficiently?
 
1. Yes, your machine is even a bit of overkill.
2. Excel isn't really designed by itself to handle very large data set. But there are tools available.

You may want to consider upgrading your Office to Pro-Plus or buying standalone Excel (if running Office 365, 2016 or 2013) Which comes with PowerPivot. For 2010, PowerPivot is free add-on.

There are other ways to work with large data set. But I find that PowerPivot makes data analysis work much more efficient. See one of the article here.

http://chandoo.org/wp/2014/01/29/5-reasons-why-you-should-learn-power-pivot/

Few more articles.
https://blogs.office.com/2010/10/01/top-5-ways-powerpivot-helps-excel-pros/
http://www.powerpivotpro.com/what-is-power-pivot/
http://newtech.about.com/od/microsoft/tp/5-Cool-Things-You-Can-Do-With-PowerPivot-For-Excel.htm

How to get PowerPivot (or does your Excel version support it?)
http://www.powerpivotpro.com/get-the-software/
 
IMHO - 200,000 + rows of data screams SQL database. The benefits of a database is the efficiency in which it handles data. If you look at your excel table(s) and can segment data easily, that's another sign for a database. I am relatively new to the power of EXCEL and I am a huge fan (growing bigger as I learn more)...

That said, I personally would never consider having excel do a job that large. In fact if I knew the job would be over 100,000 rows AND I was doing complex queries (pivot tables, etc...) I would definitely convert it to a sql database.

If you choose microsoft sql server, they make it very easy to convert your excel workbook into a sql database. Front end design (if needed) is the only difficult part, unless your talking about multiple workbooks with linked datasets etc...

Anyhow, the specs on your system as stated by Chihiro are more than adequate.

Cheers!
 
Hi ,

There may or may not be something wrong in your worksheet structure / layout , and the way you are processing the data.

Unfortunately , without taking a look at your workbook , there is nothing more specific that can be said.

I do not know the connection between the VLOOKUPs and the pivot tables ; probably if you explained what your data looks like , and what you want to do with it , alternatives may be possible.

You may want to go through at least one , if not all , of the following :

https://vlookupweek.wordpress.com/2...ams-why-2-vlookups-are-better-than-1-vlookup/

https://fastexcel.wordpress.com/2012/03/29/vlookup-tricks-why-2-vlookups-are-better-than-1-vlookup/

http://trumpexcel.com/2014/11/vlookup-vs-index-match-debate-ends/

http://www.excelguru.ca/forums/showthread.php?132-INDEX-MATCH-versus-VLOOKUP

Narayan
 
Back
Top