5 ms·

I always wanted Clippy to pop-up when excessive use of VLOOKUP is detected with a message: "It looks like you're trying to implement a relational database in a

I always wanted Clippy to pop-up when excessive use of VLOOKUP is detected with a message: "It looks like you're trying to implement a relational database in a spreadsheet. May I suggest you get professional help?"

bombcar 4y ago
People complain a lot about MS Access, but a easy-to-use relational database is actually a very necessary tool. It's too bad that Microsoft hasn't figured out how to make it easy to make an access-like system in Excel itself.
- EvanAnderson 4y ago
  For sure, re: Access and a relational database tool being important. If somebody ever manages to make a web-based Microsoft Access clone (that actually works) they'll make billions. It's fun help a "savvy" Excel user understand how a relational database can reduce their byzantine VLOOKUP-based fever dreams into simple joins. Typically if somebody can grok VLOOKUP-fueled nightmares they can grok SQL pretty quickly and will actually appreciate what it can do for them.
  - geoduck14 4y ago
    Snowflake
  - bombcar 4y ago
    >If somebody ever manages to make a web-based Microsoft Access clone (that actually works) they'll make billions. They did, they called it Salesforce.com
    - FastMonkey 4y ago
      I think Salesforce is fine for large companies with teams of sales reps and a well staffed IT department, but it's overkill for most small to medium sized companies with just a few sales reps. If you can run on a creaky set of excel sheets, access would have been a far better and much more flexible solution. With minimal training it could be set up and run by a competent excel user. Salesforce admin is a pain in the ass. There's mandatory updates 3 times a year that require low level software development expertise in order for the admin to understand what will break, building new functionality essentially requires tens of thousands of dollars of consultant development, and god help you if you want to run a report that involves objects that don't have a direct parent-child relationship. I'm an analyst, and because I know how a database works, Salesforce stuff gets dumped on my desk all the time. I actively seek out alternative systems for everything it does, and would dearly love to see us drop this eye-watering expense.
      - Mandatum 4y ago
        > There's mandatory updates 3 times a year that require low level software development expertise in order for the admin to understand what will break If you're veering from defaults, you're doing Salesforce wrong. Even Salesforce's own Services team these days refuse to write custom code that'll break beyond default features.
  - ArnoVW 4y ago
    Airtable?
  - DigiDigiorno 4y ago
    There was Access Services in SharePoint but it was depreciated a few years ago. Unfortunately there are some very large companies that use Access in a shared drive instead. They make daily backups just in case there is data corruption.
    - stormdennis 4y ago
      'just in case there is data corruption' In my experience that was guaranteed if any of the clients connected other than by ethernet. I've not done it but I understand that it's not difficult to have an Access front end for a SQL Server backend rather than the default mdb or accdb backend. All being Microsoft products the migration to SQL server is easy as I understand it. I find it hard to understand why large companies would not adopt this approach.
      - EvanAnderson 4y ago
        Access files are definitely super-fragile. That's not isolated to Access, though. Any time a file sharing protocol is used to host a database-- particularly with multi-user locking semantics-- I get the willies. I've done more "development" of Access-based applications over the years than I'd care to admit and, as a menu-driven front end for interaction with a client/server RDBMS (SQL Server, Postgres, etc) I think it's good cheap solution. Visual BASIC for Applications (VBA) can even do some powerful (and arguably ill-advised) stuff like directly calling Win32 APIs. It's an awesome prototyping tool in my experience. The typical Access solution I see in large corporate environments is something knocked-together by non-IT people. Once it gets to the level of needing to be upsized to a client/server RDBMS back-end the "real" IT department comes in and demands a purpose-built solution. Usually bureaucracy, disdain for Access, licensing costs for the client/server RDMS, taking away file servers, etc, end up killing it.
        _carbyau_ 4y ago
        Application Packaging was in my past; can confirm the disdain for Access. There was a definite gap in IT Support though. Upon upgrading from Windows XP, Access was removed from the environment AND existing access databases were shoved into a database farm. But any new business needs for something similar IE "Hey IT, we want a little app just to store XYZ business info" fell on deaf ears - the response was always "Hire IT Company to build it and hand it over." which of course would cost tens of thousands rather than a handful and thus priced it out of existence. Which meant of course the business people started finding solutions OUTSIDE of IT Support purview....
  - ianceicys 4y ago
    >If somebody ever manages to make a web-based Microsoft Access clone (that actually works) they'll make billions. Microsoft already has built that web-based access data-layer, it's called Dataverse - http://aka.ms/dataverse http://aka.ms/dataverse https://www.youtube.com/watch?v=iSKVxx_ghbw https://www.youtube.com/watch?v=iSKVxx_ghbw
- DigiDigiorno 4y ago
  "Easy" is tough when it comes to relational databases. Even Access requires a decent amount of technical know-how when compared to the people who use Excel heavily. In Excel Power Query and the M language are quite powerful. You can combine tables from both your sheets and/or a variety of external sources and create relational views into them. You can then display these views on a sheet. When I worked for a company where corporate used it, the problem was that people would come to me for help. M is pretty easy to learn so it wasn't a big deal for me. But I am a software dev with database experience, so I'm not sure who the target market is here--it's not the pure Excel users. Like, they wanted tables from a shared folder appended on each other and joined to another table. In pure Power Query you can do this without too much trouble, but you need to know what you are doing. These people have trouble with VLOOKUP already (I mean the string/int problem is unintuitive, so I'm not talking about that), so this is an impossible task for them.
  - unyttigfjelltol 4y ago
    It's confusing that Excel supports relational database operations at all. If you want to query the data in Excel it should seamlessly push you into Access rather than partially recreating the same functions.
    - csdvrx 4y ago
      > It's confusing that Excel supports relational database operations at all I'd say it's for the better: sometimes you want to stay in the same tool, and it's the tool's job to interpret your intent. Imagine you are editing a markdown file and have a picture in your Windows clipboard. IMHO, the proper action would be to output base64 encoded jpeg data, even if it's "partially recreating the same functions", as it makes a copy-paste of screenshots from say the snipping tool (Win Shift S) seamless. BTW, github doesn't support base64 encoded jpeg or png data, under the excuse that SVG could have security implications (which is true but not applicable for png/jpg) so don't bother trying it over there...
- solardev 4y ago
  Airtable is so much better at this than anything Microsoft has tried to make.
Arrath 4y ago
Oh you must have seen my accounting department's 'transaction register' which is a... checks latest 64mb spreadsheet that amounts to one gigantic, ever slower pivot table.
- Optimal_Persona 4y ago
  One department at my agency shrunk an ~80MB Excel sheet down to ~1-2MB by using the .xlsb (Excel Binary) file format, and it sped up quite a bit. But point taken, once this is the single source of truth for biz-critical data, Excel is not the right storage format.
jackvalentine 4y ago
“Yeah Frank I got rid of that annoying pop up telling me to get professional help by mixing vlookup and xlookup in my spreadsheets”
davchana 4y ago
> excessive use of VLOOKUP I feel called out. Maximum number of rows in a VLOOKUP in personal spreadsheet is like an array of 2x1000. In an earlier job, it was 13x39000. Excel automatically increases the range when you add rows in the middle. Then I split my data per year, & it was like 13x5000.
Spooky23 4y ago
I have a colleague who had a dedicated PC that had to be on at all times. The company bought a UPS for it, and it was on patch exception lists to prevent automatic reboot. One day, I asked him what it was and he showed me. He was running a series of excel macros that took over 96 hours to run, and fed a critical business process that was worth a few million dollars. I sat down and took a look at it, and in about 3 days implemented a prototype in SQLite and a small Perl script that ran in about 8 minutes. Basically, it was a series of sql joins and some regex. My colleague, a project manager, literally hugged me and was near tears. Some devs later took that prototype and refactored it to run on a normal production platform.
- smugma 4y ago
  My team often does the reverse of this. We need something built and it goes into project planning for the next year (we plan out bigger features a year out, with flexibility to squeeze in smaller features every month). Sometimes those features get punted out from the next year. So we end up building the feature in Excel. With large (for Excel, say 100K-500K rows) data sets, it gets pretty slow. Or it'll get too complex, so we'll put it into our own Postgres DB. Eventually, it'll get big/complex enough that we prioritize using development resources to automate the feature. To an extent, this has become a standard part of our rigor for deciding whether or not to build features. You say you need X, but you aren't doing X today. Can't do you do that with an SQL pull and then doing all the business logic in Excel, and then creating the entries by hand? In some cases, the answer is no, we need it to be part of a larger pipeline, etc., but for a lot of things we do, building it by hand proves that the feature is needed, and also helps us work through the edge cases and makes writing the stories/BRD much easier. It also takes pressure off the dev team to try and intuit the true requirements.
- ant6n 4y ago
  The problem with turning an excel solution that is maintained by a subject matter expert into a properly programmed one (even just a python script) is that it’s not clear who will maintain the solution once u leave. This can be especially difficult for consultants - yes I can “fix” the hack now to and reduce costs, but if it means hiring some dev/sysadmin etc. to maintain the solution, it may not end up being cheaper.
josephcsible 4y ago
Hold my beer. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range) Dim intRetval As Integer ' TODO maybe respect some of the properties on Assistant to let the user disable this If InStr(Target.Formula, "VLOOKUP") = 0 Then Exit Sub End If ' TODO is one use really "excessive"? Assistant.Visible = True With Assistant.NewBalloon ' TODO I should probably do ".Mode = msoModeModeless" but I don't feel like writing a callback .Text = "It looks like you're trying to implement a relational database in a spreadsheet." .Labels(1).Text = "Get professional help" .Labels(2).Text = "Just let me generate excessive technical debt without help" .CheckBoxes(1).Text = "Don't show me this tip again" .Button = msoButtonSetNone .Animation = msoAnimationGetAttentionMinor intRetval = .Show ' TODO make the user's choices actually do something End With End Sub You'll need to load Office 97 or some other ancient version in a VM if you want to actually try this, since Microsoft finally killed off Clippit completely in Office 2007.

bombcar 4y ago

People complain a lot about MS Access, but a easy-to-use relational database is actually a very necessary tool. It's too bad that Microsoft hasn't figured out how to make it easy to make an access-like system in Excel itself.

EvanAnderson 4y ago
For sure, re: Access and a relational database tool being important. If somebody ever manages to make a web-based Microsoft Access clone (that actually works) they'll make billions. It's fun help a "savvy" Excel user understand how a relational database can reduce their byzantine VLOOKUP-based fever dreams into simple joins. Typically if somebody can grok VLOOKUP-fueled nightmares they can grok SQL pretty quickly and will actually appreciate what it can do for them.
- geoduck14 4y ago
  Snowflake
- bombcar 4y ago
  >If somebody ever manages to make a web-based Microsoft Access clone (that actually works) they'll make billions. They did, they called it Salesforce.com
  - FastMonkey 4y ago
    I think Salesforce is fine for large companies with teams of sales reps and a well staffed IT department, but it's overkill for most small to medium sized companies with just a few sales reps. If you can run on a creaky set of excel sheets, access would have been a far better and much more flexible solution. With minimal training it could be set up and run by a competent excel user. Salesforce admin is a pain in the ass. There's mandatory updates 3 times a year that require low level software development expertise in order for the admin to understand what will break, building new functionality essentially requires tens of thousands of dollars of consultant development, and god help you if you want to run a report that involves objects that don't have a direct parent-child relationship. I'm an analyst, and because I know how a database works, Salesforce stuff gets dumped on my desk all the time. I actively seek out alternative systems for everything it does, and would dearly love to see us drop this eye-watering expense.
    - Mandatum 4y ago
      > There's mandatory updates 3 times a year that require low level software development expertise in order for the admin to understand what will break If you're veering from defaults, you're doing Salesforce wrong. Even Salesforce's own Services team these days refuse to write custom code that'll break beyond default features.
- ArnoVW 4y ago
  Airtable?
- DigiDigiorno 4y ago
  There was Access Services in SharePoint but it was depreciated a few years ago. Unfortunately there are some very large companies that use Access in a shared drive instead. They make daily backups just in case there is data corruption.
  - stormdennis 4y ago
    'just in case there is data corruption' In my experience that was guaranteed if any of the clients connected other than by ethernet. I've not done it but I understand that it's not difficult to have an Access front end for a SQL Server backend rather than the default mdb or accdb backend. All being Microsoft products the migration to SQL server is easy as I understand it. I find it hard to understand why large companies would not adopt this approach.
    - EvanAnderson 4y ago
      Access files are definitely super-fragile. That's not isolated to Access, though. Any time a file sharing protocol is used to host a database-- particularly with multi-user locking semantics-- I get the willies. I've done more "development" of Access-based applications over the years than I'd care to admit and, as a menu-driven front end for interaction with a client/server RDBMS (SQL Server, Postgres, etc) I think it's good cheap solution. Visual BASIC for Applications (VBA) can even do some powerful (and arguably ill-advised) stuff like directly calling Win32 APIs. It's an awesome prototyping tool in my experience. The typical Access solution I see in large corporate environments is something knocked-together by non-IT people. Once it gets to the level of needing to be upsized to a client/server RDBMS back-end the "real" IT department comes in and demands a purpose-built solution. Usually bureaucracy, disdain for Access, licensing costs for the client/server RDMS, taking away file servers, etc, end up killing it.
      - _carbyau_ 4y ago
        Application Packaging was in my past; can confirm the disdain for Access. There was a definite gap in IT Support though. Upon upgrading from Windows XP, Access was removed from the environment AND existing access databases were shoved into a database farm. But any new business needs for something similar IE "Hey IT, we want a little app just to store XYZ business info" fell on deaf ears - the response was always "Hire IT Company to build it and hand it over." which of course would cost tens of thousands rather than a handful and thus priced it out of existence. Which meant of course the business people started finding solutions OUTSIDE of IT Support purview....
- ianceicys 4y ago
  >If somebody ever manages to make a web-based Microsoft Access clone (that actually works) they'll make billions. Microsoft already has built that web-based access data-layer, it's called Dataverse - http://aka.ms/dataverse http://aka.ms/dataverse https://www.youtube.com/watch?v=iSKVxx_ghbw https://www.youtube.com/watch?v=iSKVxx_ghbw
DigiDigiorno 4y ago
"Easy" is tough when it comes to relational databases. Even Access requires a decent amount of technical know-how when compared to the people who use Excel heavily. In Excel Power Query and the M language are quite powerful. You can combine tables from both your sheets and/or a variety of external sources and create relational views into them. You can then display these views on a sheet. When I worked for a company where corporate used it, the problem was that people would come to me for help. M is pretty easy to learn so it wasn't a big deal for me. But I am a software dev with database experience, so I'm not sure who the target market is here--it's not the pure Excel users. Like, they wanted tables from a shared folder appended on each other and joined to another table. In pure Power Query you can do this without too much trouble, but you need to know what you are doing. These people have trouble with VLOOKUP already (I mean the string/int problem is unintuitive, so I'm not talking about that), so this is an impossible task for them.
- unyttigfjelltol 4y ago
  It's confusing that Excel supports relational database operations at all. If you want to query the data in Excel it should seamlessly push you into Access rather than partially recreating the same functions.
  - csdvrx 4y ago
    > It's confusing that Excel supports relational database operations at all I'd say it's for the better: sometimes you want to stay in the same tool, and it's the tool's job to interpret your intent. Imagine you are editing a markdown file and have a picture in your Windows clipboard. IMHO, the proper action would be to output base64 encoded jpeg data, even if it's "partially recreating the same functions", as it makes a copy-paste of screenshots from say the snipping tool (Win Shift S) seamless. BTW, github doesn't support base64 encoded jpeg or png data, under the excuse that SVG could have security implications (which is true but not applicable for png/jpg) so don't bother trying it over there...
solardev 4y ago
Airtable is so much better at this than anything Microsoft has tried to make.

Arrath 4y ago

Oh you must have seen my accounting department's 'transaction register' which is a... checks latest 64mb spreadsheet that amounts to one gigantic, ever slower pivot table.

Optimal_Persona 4y ago
One department at my agency shrunk an ~80MB Excel sheet down to ~1-2MB by using the .xlsb (Excel Binary) file format, and it sped up quite a bit. But point taken, once this is the single source of truth for biz-critical data, Excel is not the right storage format.

jackvalentine 4y ago

“Yeah Frank I got rid of that annoying pop up telling me to get professional help by mixing vlookup and xlookup in my spreadsheets”

davchana 4y ago

> excessive use of VLOOKUP I feel called out. Maximum number of rows in a VLOOKUP in personal spreadsheet is like an array of 2x1000. In an earlier job, it was 13x39000. Excel automatically increases the range when you add rows in the middle. Then I split my data per year, & it was like 13x5000.

Spooky23 4y ago

I have a colleague who had a dedicated PC that had to be on at all times. The company bought a UPS for it, and it was on patch exception lists to prevent automatic reboot. One day, I asked him what it was and he showed me. He was running a series of excel macros that took over 96 hours to run, and fed a critical business process that was worth a few million dollars. I sat down and took a look at it, and in about 3 days implemented a prototype in SQLite and a small Perl script that ran in about 8 minutes. Basically, it was a series of sql joins and some regex. My colleague, a project manager, literally hugged me and was near tears. Some devs later took that prototype and refactored it to run on a normal production platform.

smugma 4y ago
My team often does the reverse of this. We need something built and it goes into project planning for the next year (we plan out bigger features a year out, with flexibility to squeeze in smaller features every month). Sometimes those features get punted out from the next year. So we end up building the feature in Excel. With large (for Excel, say 100K-500K rows) data sets, it gets pretty slow. Or it'll get too complex, so we'll put it into our own Postgres DB. Eventually, it'll get big/complex enough that we prioritize using development resources to automate the feature. To an extent, this has become a standard part of our rigor for deciding whether or not to build features. You say you need X, but you aren't doing X today. Can't do you do that with an SQL pull and then doing all the business logic in Excel, and then creating the entries by hand? In some cases, the answer is no, we need it to be part of a larger pipeline, etc., but for a lot of things we do, building it by hand proves that the feature is needed, and also helps us work through the edge cases and makes writing the stories/BRD much easier. It also takes pressure off the dev team to try and intuit the true requirements.
ant6n 4y ago
The problem with turning an excel solution that is maintained by a subject matter expert into a properly programmed one (even just a python script) is that it’s not clear who will maintain the solution once u leave. This can be especially difficult for consultants - yes I can “fix” the hack now to and reduce costs, but if it means hiring some dev/sysadmin etc. to maintain the solution, it may not end up being cheaper.

josephcsible 4y ago

Hold my beer. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range) Dim intRetval As Integer ' TODO maybe respect some of the properties on Assistant to let the user disable this If InStr(Target.Formula, "VLOOKUP") = 0 Then Exit Sub End If ' TODO is one use really "excessive"? Assistant.Visible = True With Assistant.NewBalloon ' TODO I should probably do ".Mode = msoModeModeless" but I don't feel like writing a callback .Text = "It looks like you're trying to implement a relational database in a spreadsheet." .Labels(1).Text = "Get professional help" .Labels(2).Text = "Just let me generate excessive technical debt without help" .CheckBoxes(1).Text = "Don't show me this tip again" .Button = msoButtonSetNone .Animation = msoAnimationGetAttentionMinor intRetval = .Show ' TODO make the user's choices actually do something End With End Sub You'll need to load Office 97 or some other ancient version in a VM if you want to actually try this, since Microsoft finally killed off Clippit completely in Office 2007.