Should I use Access

**purpleozzie** · 10-31-2018, 06:10 AM

Hi guys,

I've developed a spreadsheet for my sales team to use where they can look at yearly sales, sales by account etc.. I export all the data from our sales inputting program, it exports everything into a spreadsheet one line per every order we've ever taken... The problem is it's running slow, the reason being it uses sumifs to go through about 20,000 lines of data.. Would it be better to export all this data into an access database and use this, would this make it run faster..

I have no experience of using Access but willing to learn if it makes the spreadsheet run faster.. If so, could you point me in the direction of instructions on how to do this, a website that will run me through the process of using Access..

Cheers,

**Olly** · 10-31-2018, 06:21 AM

20,000 rows is not a large dataset. The question is why are you using SUMIFS?

It sounds like you could significantly improve performance by using pivot tables / powerpivot, instead of SUMIFS formulas...

**purpleozzie** · 10-31-2018, 07:08 AM

OK if I use powerpivots should I still use an Access database to hold the data.. Would that be an easier way of doing things..

**AliGW** · 10-31-2018, 07:10 AM

I think you need to answer the question asked of you.

**purpleozzie** · 10-31-2018, 07:12 AM

Well because I'm not that clued up on Powerpivots or pivot tables in general so thought the best method would be sumifs... So know I'll go away and learn about Powerpivots but in the original spreadsheet all the data was held on a sheet and another sheet held all the formulas for working out sales between certain ranges and for certain sales channels etc..

**AliGW** · 10-31-2018, 07:14 AM

Do your SUMIFS formulae include whole ranges such as $A:$A?

**alansidman** · 10-31-2018, 07:27 AM

As to your original question, have a look at this white paper.

https://forums.aspfree.com/microsoft...es-208217.html

**purpleozzie** · 10-31-2018, 07:32 AM

Hi Ali,

Yes they do...

**AliGW** · 10-31-2018, 07:35 AM

OK - so change them all to limited arrays - e.g. $A$1:$A$20000. This should speed things up.

**Olly** · 10-31-2018, 07:38 AM

I certainly wouldn't be recommending an external database of any sort, for just 20k rows. And I would never ever recommend Access.

It may be easier to diagnose and solve your performance issues, if you provided a sample workbook - remember to anonymise your data.

**ironfelix717** · 11-24-2018, 02:39 AM

Hello,

Disagreed.

If you're using SUMIFs, the case can be made that you're using them to "query" your data. If you are querying even 20,000 rows of data, I would recommend switching to an Access platform, as the JET Engine will query data magnitudes more efficiently than Excel, not to mention, incredibly easier than writing massive SUMIF or Index formulas.

My .02.

Good luck.

**torachan** · 11-24-2018, 04:46 AM

The old 'hot potato' Access v Excel, AliGW has given you the path to follow.
Unless of course you want to extend your levels of frustration learning all the pitfalls of Access.
Recently faced similar problem proving to 'expert' that Excel is capable of acceptable speeds if code is reasonable.
Attached the speed sheet used to illustrate, it is roughly coded and no refinement, with no error checking code.
It sorts a period between two dates for a given member (column C), generates a file to import into Sage and also data to produce invoice.
The first 20 rows are used to seed the Transaction History, set the array rows cell to zero, press 'Fill Array', this also clears sheets SAGE & INVOICE
Then in N4 put a figure according to number of rows you want to sort, press 'Fill Array', (rows below row 20 will fill), scroll down for curiosity.
Press 'Start Sort', this will start the timer and give result when complete, do not blink

On my steam driven computer running Vista & Excel 2010, sort speed 20,000 rows less than )0.5secs, 200,000 rows under 4secs, 1M rows under 20secs.
regards
Torachan