Need spreadsheet with pivot tables, but can't really use Excel or Sheets

December 6, 2016 6:46 PM

We have a very large Excel spreadsheet with a lot of raw sales data. I'm then creating pivot tables to look at the info in various ways to help our sales team. However, we're bumping into some severe limitations with regards security, sharing, and user-friendliness of the UI and trying to figure out if there are alternatives.

Why we want to move away from Excel:
1. The spreadsheet is HUGE (over 100mb) and playing around with pivot tables and/or adding info takes a long time.

2. We want our sales managers access to the data to create/filter/tweak reports themselves, but we really don't want to send people all the raw data.

Ideally, I would put this on a Google Sheet, and it'd kind of solve problem #2, because I could, in theory, put the raw data on an unshared Sheet, then do an Importrange to a protected, shared sheet that people could pivot from. That way, they don't have *direct* access to the raw data, but could construct useful pivot tables to do sales analyses.

Also, this would make it a lot easier for some of our more tech-literate people to help the managers construct pivot tables and then just send them a link and give them basic instructions (if you want to see only these customers, filter the pivot table this way...)

However, the spreadsheet is way too big for Google Sheets to handle.

I think we're probably in the realm of putting the data in an actual database and then using something like Jasper Reports or Crystal Reports (too expensive), but that's got a *steep* learning curve for our sales managers, who can barely figure out pivot tables.

We've also looked at relatively inexpensive tools like Amazon Quicksight, but that seems more geared towards graphs and charts than pivot-table-like reporting/analysis.

Are there any viable, and relatively inexpensive alternatives that give us the flexibility of having a large amount of data in the backend, and a fairly simple-to-use front end that's similar to pivot tables?

posted by edjusted to Computers & Internet (15 answers total)

Not sure if this would solve your problem, but it might be worth checking out Airtable.
posted by thirdletter at 6:54 PM on December 6, 2016

Depends on who would be creating the reports. If you create a database with an interface to enter data and you create the reports then there really isn't much of a learning curve. I think spending any more time on trying to make a spreadsheet do so much more than it can handle is just going to be frustrating and time lost. That time could be spent porting stuff over to a database and then you can slice and dice everything in a million different ways.
posted by dawkins_7 at 7:00 PM on December 6, 2016 [1 favorite]

Maybe PowerBI? You'd have to put the data in a database somewhere, but then PowerBI could act as a frontend for doing pivot table-like queries on the data. It can do natural language queries on data (using very specific language, so not exactly natural language, but that's how they advertise it) and generate tables and/or graphs.
posted by retypepassword at 8:27 PM on December 6, 2016 [1 favorite]

Check out smartsheets.com.
posted by misanthropicsarah at 8:35 PM on December 6, 2016

You are correct - the end game here is to not use a spreadsheet as your ultimate datastore.

That being said, given the current limitations and what not.... I have run into a similar situation and had reasonable success with the following:

i) Sticking with the spreadsheet for storage, very tightly controlled write access
ii) Using pandas to read said spreadsheet into a DataFrame
iii) Writing small functions to query the DataFrame to yield smaller reports
iv) Passing said processed reporting DataFrames to PivotTable.js for client said reporting. Some other report (even dumping back to excel) might be more appropriate for your use-case
v) Periodically re-parsing the backing spreadsheet (as in ii )

This way the whole thing could sit behind an authenticating web server somewhere, could be reasonably performant an access logs and the like are actually useful.
posted by mce at 8:35 PM on December 6, 2016 [1 favorite]

You could try something like R and shiny - if you are good with shiny, you could make it all click and drop down boxes for display reporting/pivoting/graphing.

See here or the overall gallery here.
posted by scodger at 8:57 PM on December 6, 2016 [1 favorite]

Not sure about parameters of how many sales people you have (you have a lot of data), or what 'too expensive' means in your context, but as others have said this is a classic use case for a BI platform. I have personal experience with Tableau, which costs around $2k for a lifetime single user license, with an optional annual maintenance of $400 per license. Another option would be to use its hosted cloud service at a cost of $500 per user per year. Probably an enterprise pricing discount if you were setting up many licenses. It'd do a good job with your data I think, given what you have written.

You've quite possibly already explored this - but just to be sure: do you have a CRM, and have you checked whether any of the in-built reporting functionality meets your needs? Reporting in Salesforce for example is generally pretty good.
posted by bifter at 12:11 AM on December 7, 2016 [1 favorite]

You could dump the entire dataset into Amazon RDS & run SQL queries against that to generate data to stick into a dashboard of some kind.

Whatever you do is going to take developer time & effort though: That costs money. If you’re balking at the cost of a copy of Crystal Reports for each Sales Manager then I’m not sure you’re actually willing to spend the money required to solve this problem. It’s what, $400 / seat or something? I find it hard to believe that faffing about with pivot tables in Excel isn’t wasting $400 of your Sales Manager’s time already...
posted by pharm at 2:21 AM on December 7, 2016 [1 favorite]

Tableau public could do this for you, but it depends on how private your front-end data is. Tableau will give you a beautiful, easy to use dashboard as long as someone learns how to build it.
posted by nakedmolerats at 5:48 AM on December 7, 2016

A pivot table in Excel is just a summary query in Microsoft Access. Access is part of the Office suite but it may not be installed on everyone's machines by default. It has advantages like quick imports and exports with Excel and built in web databases. In Access, you can write Reports to display data the way they need it and those can include filters. You can also create Forms to facilitate data entry for new records. If some of your end users are around or below the pivot table level of ability, ease of use for them and ease of customization for your administrators are both important.
posted by soelo at 7:14 AM on December 7, 2016

Response by poster: Thanks all. I'll check out the suggestions.

FWIW, some clarification:

The spreadsheet has details of every single invoice and every single item our customers have purchased from us for the past few years. It currently has around 900,000 rows and has 17 columns (so about 15M cells).

So we use the pivot tables to see, for example, what product categories did xyz customers order in 2015 vs 2016, which product categories sold the past 6 months regardless of customer, etc. Lots of different ways to slice and dice this.

We add on to the spreadsheet once a month by doing an export of an invoice details report out of our ERP and copying-pasting it into the spreadsheet. The reason we don't do the reporting slicing/dicing in the ERP is because its reporting functions stink.

By "fairly inexpensive"...just looking at the answers, I think $2k for a lifetime single user license and $400/year/license seems reasonable.

It's really kind of a shame that Excel chokes on the data, because otherwise the pivot tables plus slicers work beautifully and are very easy to use for our needs. I kind of feel like I'm trying to reinvent the wheel.

Thanks for the suggestions. Time to do some more research.
posted by edjusted at 9:46 AM on December 7, 2016

USE A DATABASE. Holy cow, please.
posted by uberchet at 10:03 AM on December 7, 2016 [5 favorites]

You might have all of the tools that you need.

Download the powerpivot add-in for Excel. Work with your IT dept or DBA to create a connection to your ERP database where the invoices are stored.

Then use powerpivot to pull the data from your ERP as needed. You then create pivot tables based on your powerpivot data.

I regularly work with reports of pretty much the same data you are describing that are over 2 million rows. Feel free to PM me if you need more details or report suggestions.
posted by TomFoolery at 11:25 AM on December 7, 2016

Response by poster: @TomFoolery: I think that just might be the ticket. Thanks, I'll add that to my research list.
posted by edjusted at 12:36 PM on December 7, 2016

Echoing the need to have a database to store all this information.

I have to create a weekly report at work showing how many mailing new mailing projects arose this week and the status of existing projects.

I store the data in an Access database and then use a data connection with Excel to update the tables in my spreadsheet. I can then use pivot tables and slicers to my heart's content.

The solutions listed above are ideal if you can get set up to directly tap into your company's data warehouse. If not, then the Access to Excel solution may be the ticket.
posted by reenum at 4:05 AM on December 8, 2016