4 ms·

FTFY: "Excel THINKS IT CAN DEAL with many gigs of data thanks to PowerPivot and the addition of an in-memory database." It's so cute when I hit ctrl-downarrow

FTFY: "Excel THINKS IT CAN DEAL with many gigs of data thanks to PowerPivot and the addition of an in-memory database." It's so cute when I hit ctrl-downarrow on a blank sheet and Excel sends me to row 1,048,576. Wishful thinking because if I ever filled 1M cells with functions, well... lololololol... time to use JMP...

tomnipotent 8y ago
I'm guessing you've never used these features if this is your comment.
TabTwo 8y ago
Cisco Global Pricelist, there are still not enough lines in .xlsx to cover all their products
- tomnipotent 8y ago
  If you're putting the data in Excel's new data model, this is no longer a problem. I regularly have files with tens of millions of rows of data which pivot tables can work against with sub-second aggregations across multiple columns.
  - intended 8y ago
    Is this in 2016, and do you have to do anything specific to make it load large data sets ?
    - tomnipotent 8y ago
      Actually much earlier in 2012, when Excel first shipped with xVelocity branded PowerPivot. It supports a new data model that reminds me of Microsoft Access in some ways (drag & drop relationships etc). This is a whole different beast from copy/pasting data into sheets - in fact, the data doesn't show up in sheets by default and you usually have to add other things (like pivot tables) to take advantage of it. Microsoft is a sleeping giant in BI self-service right now, and the things they've been "quietly" adding (only if you don't follow them) are actually very compelling. I actually run a Windows VM on my MBP just so I can run Power BI.
      - intended 8y ago
        Is this power bi..? Ah, I think it is. I’ve really tried to get up to speed with it, but it feels so alien to normal excel in many ways. I feel an existential dread when I drop a column in power BI. But yeah, it’s very powerful. It’s very sql like in the way you have to treat actions and data.
        tomnipotent 8y ago
        Yes and no. Power BI is a mixed cloud/desktop thing - the desktop GUI for creating reports/charts is actually pretty cool. PowerPivot and Power Query in Excel are add-ons that tack on the same analytical engine that Microsoft purchased to power new features in SQL Server. I believe MS is branding this all now as Power BI, but it's still a little confusing to be honest. In the Power BI desktop app you can connect to standard RDBMS' like MySQL, Postgres, or SQL Server and basically it works like Tableau. The really interesting part is you can export these data sources and hook them into Excel (local or via the cloud). > It’s very sql like It should be, this is essentially using Excel as an GUI on top of technology designed to run an analytics RDBMS. It actually is an entirely separate interface from Excel and feels bolted on after-the-fact. Here's an example of the PowerPivot Excel add-on screens: https://i2.wp.com/www.kasperonbi.com/wp-content/uploads/2010/06/data1.png https://i2.wp.com/www.kasperonbi.com/wp-content/uploads/2010... https://i.ytimg.com/vi/NDO6MpT70PM/maxresdefault.jpg https://i.ytimg.com/vi/NDO6MpT70PM/maxresdefault.jpg
  - pq0ak2nnd 8y ago
    You busted me: I guess that's my age showing because I didn't know Excel -had- a new data model. What's it called and how is it different than sheets, and when was it introduced? I was using Excel2013 up until I left the project in 2014.
- pq0ak2nnd 8y ago
  Oof.
zhte415 8y ago
I still deal with people that are limited to 65535 rows.
deleted 8y ago
[deleted]

tomnipotent 8y ago

I'm guessing you've never used these features if this is your comment.

TabTwo 8y ago

Cisco Global Pricelist, there are still not enough lines in .xlsx to cover all their products

tomnipotent 8y ago
If you're putting the data in Excel's new data model, this is no longer a problem. I regularly have files with tens of millions of rows of data which pivot tables can work against with sub-second aggregations across multiple columns.
- intended 8y ago
  Is this in 2016, and do you have to do anything specific to make it load large data sets ?
  - tomnipotent 8y ago
    Actually much earlier in 2012, when Excel first shipped with xVelocity branded PowerPivot. It supports a new data model that reminds me of Microsoft Access in some ways (drag & drop relationships etc). This is a whole different beast from copy/pasting data into sheets - in fact, the data doesn't show up in sheets by default and you usually have to add other things (like pivot tables) to take advantage of it. Microsoft is a sleeping giant in BI self-service right now, and the things they've been "quietly" adding (only if you don't follow them) are actually very compelling. I actually run a Windows VM on my MBP just so I can run Power BI.
    - intended 8y ago
      Is this power bi..? Ah, I think it is. I’ve really tried to get up to speed with it, but it feels so alien to normal excel in many ways. I feel an existential dread when I drop a column in power BI. But yeah, it’s very powerful. It’s very sql like in the way you have to treat actions and data.
      - tomnipotent 8y ago
        Yes and no. Power BI is a mixed cloud/desktop thing - the desktop GUI for creating reports/charts is actually pretty cool. PowerPivot and Power Query in Excel are add-ons that tack on the same analytical engine that Microsoft purchased to power new features in SQL Server. I believe MS is branding this all now as Power BI, but it's still a little confusing to be honest. In the Power BI desktop app you can connect to standard RDBMS' like MySQL, Postgres, or SQL Server and basically it works like Tableau. The really interesting part is you can export these data sources and hook them into Excel (local or via the cloud). > It’s very sql like It should be, this is essentially using Excel as an GUI on top of technology designed to run an analytics RDBMS. It actually is an entirely separate interface from Excel and feels bolted on after-the-fact. Here's an example of the PowerPivot Excel add-on screens: https://i2.wp.com/www.kasperonbi.com/wp-content/uploads/2010/06/data1.png https://i2.wp.com/www.kasperonbi.com/wp-content/uploads/2010... https://i.ytimg.com/vi/NDO6MpT70PM/maxresdefault.jpg https://i.ytimg.com/vi/NDO6MpT70PM/maxresdefault.jpg
- pq0ak2nnd 8y ago
  You busted me: I guess that's my age showing because I didn't know Excel -had- a new data model. What's it called and how is it different than sheets, and when was it introduced? I was using Excel2013 up until I left the project in 2014.
pq0ak2nnd 8y ago
Oof.

zhte415 8y ago

I still deal with people that are limited to 65535 rows.

deleted 8y ago

[deleted]