[HN Gopher] Excel Never Dies
___________________________________________________________________
Excel Never Dies
Author : iamacyborg
Score : 232 points
Date : 2021-03-08 14:58 UTC (8 hours ago)
(HTM) web link (www.notboring.co)
(TXT) w3m dump (www.notboring.co)
| rob74 wrote:
| ...but where Excel really shines and enhances productivity is in
| combination with that other tool we all love and will never give
| up, email. Someone emails you (and 10 other persons) some data in
| an Excel sheet, you (and unbeknownst to you, 2 other persons)
| update the sheet with some new data and reply to the email, and
| after a few iterations nobody knows anymore how many versions of
| the document are in circulation and which one is the most up-to-
| date. Oh, joy!
| aksss wrote:
| Sounds like a training issue - Excel has a much better tool
| available now. Click the "Share" button in upper-right corner
| and now you're all working on same doc and have concurrent
| editing capability.
| pmarreck wrote:
| I'm wondering if spreadsheets could be used as a tool to
| introduce programming to kids, since all the intermediate values
| are visible, and most programming is just data transformation.
| The fact that spreadsheets are effectively functional languages
| might mean that looping could be implemented as recursion with
| tail-call optimization... instead of only having cell references
| you could have references to keyboard or mouse input values, or
| URLs pulled from outside sources, or files read off the local
| storage... Instead of just having charting and graphing tools,
| you could have, say, a Turtlegraphics output box that would
| follow the instructions from some designated series of cells
| (which perhaps were themselves generated by formulas)
| adamredwoods wrote:
| Excel is horrible to program in. Example: I needed to import a
| bunch of URLs, then pull the query params out and auto-create a
| SQL query based on those. I was able to do it, but when I entered
| the Visual Basic editor, it was a whole new word of UI hurt.
| iamacyborg wrote:
| That sounds like probably the sort of thing that shouldn't be
| done in Excel though.
| adamredwoods wrote:
| I like to push software around, but yes, there are better
| ways to do this.
| dragonwriter wrote:
| > Excel is horrible to program in.
|
| VBA, and the built-in DE for it, isn't great, but you can
| program Excel other ways (Office Add-In, xlwings, etc.)
| breck wrote:
| Spreadsheets and Programming Languages are about to procreate.
| The future is nigh.
|
| https://youtu.be/vn2aJA5ANUc?t=145
| asdff wrote:
| They were procreating 45 years ago with S.
| breck wrote:
| Link to a screenshot that shows relevance?
| chrisgd wrote:
| It is used as a graphics output of those financial models as
| well. Bankers, both commercial and investment, use it to copy and
| paste into PowerPoint presentations that form the basis of
| billion dollar transactions everyday.
| submeta wrote:
| Is there a way to manipulate Excel cells from within
| Python/Pandas? Last time I checked I had a hard time to write to
| an existing Excel file (= prevent overwriting it).
|
| Would love to do the complex calculations in Python instead of
| using VBA.
| hypermachine wrote:
| Not just Excel, the integrated scripting environment around it
| too. A lot of the heavy lifting in complex setups is done by
| custom VBA scripts.
| onychomys wrote:
| Someday they'll update the VBA IDE to have some super basic
| features (like autoformatting or text highlighting or
| whatever). That'll be so great.
| asdff wrote:
| Someday accountants and business people everywhere will bite
| the bullet and spend the 5 hours or so it takes to get a
| working knowledge of R and finally step into the first world
| of programming languages.
| hypermachine wrote:
| Sign up for our mailing list (link in profile), that's what
| we are building (and more including support for collab
| editing, version control, and cloud native deployments).
| Tade0 wrote:
| I've been having this nagging thought lately: what if web app UIs
| started off as in-browser spreadsheets and evolved from that?
|
| After all 90% of web apps are just forms with some validation and
| lists of things - a spreadsheet can do all that with greater
| flexibility as a bonus.
| lopatin wrote:
| Love the idea. An app that evolves from a spreadsheet is pretty
| brilliant, mate.
| jason2323 wrote:
| lol. One of my observations from my experience at startups : if
| you want to create a B2B SaaS company, convert a company's
| spreadsheets into a webapp and voila!
| nicoburns wrote:
| > just forms with some validation and lists of things - a
| spreadsheet can do all that with greater flexibility as a
| bonus.
|
| Speadsheets are great at a lot of things, but data validation
| doesn't tend to be one of them. In fact, I'd argue that's one
| of the main reasons to move off of spreadsheets: to make your
| data more structured.
|
| It may well be possible to create a spreadsheet-like UI that is
| good at these things though. And I can certainly see that being
| successful. It'd be difficult to tradeoff flexible vs
| constrained though.
| DavidPeiffer wrote:
| > Speadsheets are great at a lot of things, but data
| validation doesn't tend to be one of them.
|
| What limitations are you thinking of? I can go to Data ->
| Data Tools -> Data Validation and restrict to whole number,
| decimal, list, date, time, or string length. If that's
| insufficient, you can create a custom formula which has to
| evaluate to TRUE for the input to be considered valid. Regex
| isn't supported out of the box, but quite a number of string
| functions are, and there are readily available Regex user
| defined functions (VBA called via formala) available online.
|
| You can also customize the error alert that would be
| displayed to the user if they try to input something invalid.
| I think it'd work quite well for many MVP/single page web
| apps.
| grey-area wrote:
| The question is not what a sufficiently motivated competent
| person can do with the tools.
|
| It's what a sufficiently motivated incompetent person can
| do to your data with the tools.
|
| Infinite flexibility in the tooling means infinite ways to
| mess the database up in subtle and/or irredeemable ways,
| and a formula in a cell or even a regular expression are
| not great ways to tidy up real-world data, you often want
| autocorrect, autosuggest, defaults and friendly error
| messages for bad data rather than just ERROR IN CELL G91.
| When you reach that level of complexity it becomes much
| harder to build something useful with a spreadsheet-like
| tool alone.
|
| It is a really interesting idea though and for certain
| classes of data could really work well.
| nsm wrote:
| Airtable (airtable.com) is doing something like that. It's less
| Excel and more a souped up relational database, so a slightly
| different angle where you surface the CRUD of a CRUD app to the
| user.
| hermitcrab wrote:
| I see Airtable as an online database with the ease of use of
| Excel. I created a system for a local charity using Airtable.
| I found it worked really well and the charity are delighted.
| sidpatil wrote:
| I think Microsoft dropped the ball big-time by not making it
| easy to export an Excel workbook or Access project as a mobile-
| ready turnkey Web application. (It looks like it's _possible_
| to do, but not _easy_ to do--from what I can tell, it either
| requires Office 365 or third-party software.)
|
| If that functionality existed and was easy to use, it could
| have been a very convenient option for businesses running their
| operations on Access database and looking to take advantage of
| mobile.
|
| I know Microsoft has InfoPath and Forms, but those aren't
| Access, so they're not going to be as popular.
| breck wrote:
| I figured out how to do this years ago, even before I worked at
| Microsoft (and pitched it while there a number of times).
|
| It wasn't easy, and there was a lot to figure out, but the end
| result is starting to look pretty simple:
|
| https://youtu.be/vn2aJA5ANUc?t=145
|
| https://github.com/publicdomaincompany/copypaster
| pwdisswordfish0 wrote:
| The copypaster.publicdomaincompany.com URL in the project
| About box is broken.
| breck wrote:
| Fixed! Thank you for letting me know!
| Tade0 wrote:
| > In 2021 let's start making all web forms copy/pasteable.
|
| I love the premise - will give it a go.
| breck wrote:
| Great! If you take any notes on pain points trying to
| implement that pattern, would love to hear about them! My
| email is in profile
| tonymet wrote:
| I would love an entire OS (like ChromeOS) & protocol around
| Excel/spreadsheet. you log in, and everything on your desktop is
| a spreadsheet : contacts, calendars, email/messaging, forms. Like
| a primitive hypercard with only table view.
| hateful wrote:
| Over the years, the wife and I tried numerous shared calendars -
| but nothing has yet topped the one I made fairly manually in
| Google Sheets. I just copy a few rows down every once in a while
| and we keep it going. Color coded, Emoji for different types of
| events, each week is collapsible and shows just the emoji for
| that day. Never happier.
|
| Every other calendar's interface and customization seemed like a
| limitation rather than a feature.
| tgb wrote:
| I hadn't used Excel in years and now that I do, there's some
| things about it that drive me _nuts_ :
|
| - It forgets what you've copied to clipboard. Copy something.
| Insert another row so that there's space for it. Paste. Nothing
| happens. Huh? It lost my copy. It does this for a large number of
| operations and it drives me crazy. I've never seen any other
| program do this.
|
| - You can't open two spreadsheets of the same name. This is
| because spreadsheet formulas can refer to cells in other tables.
| But I don't use that feature - can't I just open the second
| spreadsheet with a warning that this feature won't apply to it?
|
| - (This one applies to too many pieces of Microsoft technology.)
| You can't use common keyboard shortcuts properly. Ctrl-backspace
| deletes a word in any useful text box. Not in formula editing in
| excel. And ctrl+delete deletes the rest of the line instead of
| just the next word. Why?
| bhandziuk wrote:
| The clipboard being cleared when you do some not-paste action
| is pretty annoying. I assume it is this way though because if
| you alter the sheet in some way which intersects with the cells
| being copied paste might not know what to do. Like if you
| insert rows, or change a value, the thing you copied might have
| changed. It might not have but it would be more confusing I
| think to have Excel make than analysis that it changed in some
| meaningful way then clear the clipboard sometimes than to just
| always clear the clipboard.
| setr wrote:
| Aren't things that could change always defined by a formula?
| You already have two paste operations -- by value or by
| function. It seems to me the reasonable thing is to simply
| follow through: if you paste by value, you paste whatever it
| was at the time of copy -- if you paste by formula, you paste
| the formula... and whatever computed result.
|
| Pasting the possibly modified value is, IMO, always
| undesirable; so you can ignore the possibility altogether
| bhandziuk wrote:
| I think if Paste Values pastes something that is no longer
| on the screen that is weird. How long do you wait to clear
| the clipboard if that's the case? What if I copy some
| cells, then change everything about my workbook (changed
| sheet names, changed named range names, refreshed linked
| data, inserted rows in the middle of my copied range...),
| then paste, do you still expect it to paste the original
| values? Do you expect that most users understand what's
| about to happen? I think the whole question is avoided by
| clearing the clipboard earlier than might sometimes be
| desirable.
| tgb wrote:
| Paste Values should paste the values that it had at the
| time of copying, period. Other programs behave thusly. If
| I'm making an image in Inkscape, I copy a portion, delete
| it and then paste it, I get the value I copied even
| though it had since been deleted. Same as if I edited it
| after copying, like changing a color. Excel should mimic
| that.
| bhandziuk wrote:
| So if the underlying value changes enough that pasting
| anything but the values doesn't make sense then what's
| the UI here? Ctrl+C does nothing but Ctrl+Shift+C gives
| you the option to paste values with the inappropriate
| ones greyed out? Does the dotted border on the copied
| text go away because ctrl+C isn't going to work or does
| it stay because some portions of paste special can still
| work?
| tgb wrote:
| I think the scenario for pasting formula is the same as
| for pasting values: paste what was there at the time of
| copying. Is there a situation where this doesn't make
| sense? I'd be fine with it being kinda-wrong in
| situations like the original cells referred to another
| cell but now there's been a new row inserted and those
| cells are elsewhere and the clipboard hasn't updated.
| That's what I would expect a copy to do. Excel allows
| references to cells in other spreadsheets and if you edit
| the other spreadsheet, then the references in your first
| spreadsheet don't update to reflect that, so the
| clipboard would just act like another spreadsheet.
|
| Edit: the better way to say this is that "copy (do
| something) paste" should always act the same as "copy,
| paste on a new blank spreadsheet, (do something) and then
| copy and paste that onto the original spreadsheet".
| bhandziuk wrote:
| It'd be cool if it worked like that but I'm sympathetic
| to it not
| setr wrote:
| I'm not sure I am; the current behavior is almost always
| surprising and unexpected. You effectively need to keep a
| list in your head of everything that will clear your
| clipboard, which is really a list of everything that
| could _ever_ cause problems, more often than not a flag
| that is entirely unrelated to your actual intent /action.
|
| You might as well have it clear the clipboard if your
| next action isn't immediately paste and be done with it.
| Just don't bother to set the expectation that pasting
| will work like elsewhere.
| ModernMech wrote:
| I love Excel and it's one program that's always open on my
| computer. But the thing that still kills me after all these
| years is that every open Excel process shares the same undo
| stack.
|
| Say you have have Excel Process A and Process B, and you make
| an edit in A, then an edit in B, then an edit in A again. If
| you try to undo the edit in B, it will instead undo the edit
| you did in A. Infuriating.
| jamses wrote:
| You can open separate Excel instances by holding ALT when
| starting it up (second time onwards). Not quite the same
| thing as you want, but think the undo behaviour you describe
| makes sense if you're working in two sheets that are linked
| in some way.
| smhenderson wrote:
| The thing about the clipboard has been around since the
| beginning of Excel. I thought I had read an article about it by
| Joel Spolsky years ago but all I could find now was a quote by
| Joel answering a question on a forum. A superuser comment
| quoted Joel as an answer [1]. The archive link for the original
| discussion is here [2].
|
| [1] https://superuser.com/questions/611854/prevent-excel-from-
| cl...
|
| [2]
| https://web.archive.org/web/20160725070440/http://discuss.fo...
| sokoloff wrote:
| The one that drives me insane is that while editing a cell
| formula, that using the arrow keys move cells (and update
| [read: wreck]) the formula rather than moving the cursor within
| the formula you're editing.
| airstrike wrote:
| Just hit F2 while editing a formula to toggle between Edit
| and Enter modes, one of which will behave as you expect. The
| other mode, which you hate, is very useful when you want to
| add references to other cells into your formula
| jacurtis wrote:
| Yes, the edit mode (using arrows to create formulas) is
| actually something I really find convenient.
|
| To me it is a feature, not a bug. But yeah, F2 is fairly
| easy to use once you get used to it. Its like using VIM,
| the shortcuts seem annoying to the uninitiated, but once
| you get understand how everything works, the power can
| really speed things up and you appreciate it.
| Balgair wrote:
| > - It forgets what you've copied to clipboard. Copy something.
| Insert another row so that there's space for it. Paste. Nothing
| happens. Huh? It lost my copy. It does this for a large number
| of operations and it drives me crazy. I've never seen any other
| program do this.
|
| Try 'WindowsKey + V'.
|
| It'll bring up a list of your previous copies. Not as easy as
| 'Ctrl+V', of course, but it does save a bit of time. And yes I
| agree, Excel's Alzheimer's is quite annoying.
| lifeisstillgood wrote:
| Mind ... blown ...
| myself248 wrote:
| Whoah.
| ant6n wrote:
| - Excel constantly wants to change cell contents, in the name
| of improving formatting. For example, paste a table with
| something that excel thinks are dates, it will convert it to
| dates, and show the data in some format. Formatting it back to
| text will give you the #days since epoch instead of the
| original text.
|
| - Excel hates text cells containing numbers. It whines about it
| all the time and eagerly changes the data to what it thinks it
| should be.
|
| - Excel doesnt get it if a sheet contains a data table with
| consistent formatting. Just recognize it and store it
| internally as a small Infile DB. Often, an Altertx table will
| blow up 100-fold when exported to Excel.
| linuxftw wrote:
| I haven't tried it on Excel specifically, but most programs
| support ctrl+shift+v to clear formatting.
| OldHand2018 wrote:
| > Excel constantly wants to change cell contents, in the name
| of improving formatting. For example, paste a table with
| something that excel thinks are dates, it will convert it to
| dates, and show the data in some format. Formatting it back
| to text will give you the #days since epoch instead of the
| original text.
|
| This is definitely annoying behavior, but you do know that if
| you format the cell as text prior to pasting the data in it
| will keep it as text, right?
| magicalhippo wrote:
| > This is definitely annoying behavior, but you do know
| that if you format the cell as text prior to pasting the
| data in it will keep it as text, right?
|
| This only works sometimes, and I have no idea when or why.
|
| The most reliable way I've found is to copy more than one
| column and use the text importer thing, where you can
| specifically mark columns as being text.
| marcosdumay wrote:
| Some times yes, other times Excel will replace the format
| you set with what it guesses from the data. I have no idea
| what triggers each behavior.
|
| What's more interesting is reading those dates by the COM
| interface. Depending on how the user input the data, you'll
| get formated dates as text or seconds since the epoch as
| number.
| beefield wrote:
| I'd have a couple of development proposals for excel:
|
| 1. Remove the ability to save workbooks. This would keep the
| excel in organisations where it excels (pun intended), namely
| quick & dirty sketches and visualizations. If you need something
| twice, it is likely you need that more than twice and you should
| be using something else.
|
| 2. If not that, give me a worksheet type that forces each cell in
| a column to have same formula or data type. and just a sheet that
| you can refer to as any other sheet, not a powerwhantnotthingy.
|
| 3. Version control. Seriously, Microsoft, what on earth are you
| paying your excel developers for if not this?
| layer8 wrote:
| You get close to #2 using Excel's table feature.
| aksss wrote:
| > 3. version control
|
| I think this is provided through either OneDrive/SharePoint or
| some other offboard solution. Do you really want Excel to have
| a native version control system? Seems like other solutions
| would always do this better.
| aarondia wrote:
| I totally agree that this makes sense for certain types of
| Excel work -- the data analysis, report generating workflows.
| You can see my comment above about how we're addressing some of
| these issues with Mito.
|
| However, not sure that these three points hold for the type of
| financial modelling work that is done in Excel today. There
| isn't a great unbundling of Excel for the LBO, etc world (yet),
| so the inability to save or have columns with multiple data
| types/formulas seems quite limiting for that world.
| beefield wrote:
| I was thinking that there were two types of worksheets. the
| current, freeform sheets, and in addition to that a new type
| that would enforce data integrity over columns. That would
| make it _much_ safer to have worksheets for structured data
| and freeform sheets for visualizing etc separate.
|
| (the first one was admittedly a bit tongue in cheek.)
| morningtigerx wrote:
| Another thing Excel can't do well is when you need multiple
| people to be updating a single spreadsheet shared in OneDrive /
| SharePoint. If too many people have it open, OneDrive can fail to
| merge the changes, and you might have races and end up with data
| corruption.
| runawaybottle wrote:
| Startups that began as a Craigslist category:
|
| https://cbi-blog.s3.amazonaws.com/blog/wp-content/uploads/20...
|
| Now someone make that for web apps that began as an excel
| spreadsheet.
|
| Or even better, ML projects that should have stayed a simple
| spreadsheet.
| guessbest wrote:
| If anyone wants to gather ideas on an app idea, they would be
| well served by running the following query in their favorite
| search engine : "<app idea> xlsx"
| sega_sai wrote:
| As a person who mostly does (large) data analysis and mostly uses
| python, I've recently thought of when I prefer to use
| spreadsheets vs python and my (personal) conclusion was to use
| spreadsheets, when my data can fit into one screen. In that case
| with excel you directly see all your data, you can change inputs
| and see output change in real time. But if the amount of data is
| larger than that, I am better off with dedicated calculations (in
| say python) and plots summarizing the data.
| jplr8922 wrote:
| I used to think that I hated excel, and that as a DataScientist-
| TradingDeskAnalyst, python or R would save the world. Now I
| realize that my hatred for Excel has nothing to do with the
| software itself, but the way it is used for political motives. If
| your VP or Top Trader or Boss choose to keep everying on 'his'
| excel, its not for sofwtare quality. Its for control. You can run
| away with your excel-data-model on a USB stick. Not so true for
| SaaS. You can keep it on your PC, and not share it with others.
| You can put passwords without IT knowing about it. Excel = Shadow
| IT department with hidden political goals.
| SeanLuke wrote:
| > Excel, and more importantly, the spreadsheet is the best way to
| build intuition for a dataset, hands down.
|
| Excel is a low-dimensional, untyped, flat database. I couldn't
| think of something worse. It has been successful only because its
| design mimicked traditional accounting books. But for more
| complex datasets, ugh.
|
| Back in NeXTSTEP days there was Lotus Improv (and later
| Lighthouse Design Quantrix). It permitted high dimensions, true
| names for rows, columns, hypercolumns, cells, and so on, and
| sophisticated modeling capabilities. It was, clean, required none
| of the ugly bug-filled hacks you see in Excel, and very easy to
| get your head wrapped around. Of course it's dead now.
| iamacyborg wrote:
| > It permitted high dimensions, true names for rows, columns,
| hypercolumns, cells, and so on, and sophisticated modeling
| capabilities. It was, clean, required none of the ugly bug-
| filled hacks you see in Excel, and very easy to get your head
| wrapped around. Of course it's dead now.
|
| Do named ranges in Excel not match some of what you're after
| here?
| SeanLuke wrote:
| A little bit. But it's hard to explain just how advanced
| Improv was (and still is).
| intrasight wrote:
| Me and spreadsheets go way back. My first job was hacking Lotus
| 1-2-3 at Kodak as a summer intern in 1985. I consider it my first
| programming job.
|
| A year later, I got interested in neural networks, and built
| back-propogation models in Excel on the mac. Yes very SLOW but
| still a great way to learn.
|
| As side project ten years back was to leverage Excel's native web
| query (IQY) mechanism to build a profitable SaaS company just
| based upon letting users get data into Excel from various 3rd
| party social media and analytics platforms.
|
| Now I work in big oil and our team basically turns Excel models
| that users create into scalable data warehouse apps.
|
| Even after years working with Excel, I still consider myself a
| journeyman.
| sumtechguy wrote:
| Using excel for learning NN. Multi input on a node would be a
| bit wonky (but doable) and layers could just be sheets in the
| workbook. It even already has a bunch of nice built in tools to
| import/export data. That is one of those 'why did I not think
| of that' things!
| ekianjo wrote:
| Excel Never Dies except when you feed it more than 1 million rows
| which is very common these days. Completely outdated for many
| uses.
| rustybelt wrote:
| PowerPivot is one of the best ways to start exploring an
| unfamiliar dataset of 1 to 10 million records. Built into
| Excel.
| mbreese wrote:
| But that's rare for the vast majority of users. How many people
| need to _really_ calculate with 1 million rows of data? That
| may be common in some fields, but when you hit that level of
| data, you know that you should be using other tools. Excel hits
| a solid sweet spot for a large percentage of the computing
| public.
| cyrialize wrote:
| Agreed. People who use Excel daily knows it's limitations.
|
| They know that it can't work with huge amount of data, but
| they do know they could have their internal tech team upload
| the data into their database and send them a snippet of the
| data.
| hermitcrab wrote:
| Try telling that to the UK government:
|
| https://www.bbc.co.uk/news/technology-54423988
| iamacyborg wrote:
| Excuse me, our Tory government has a long history of
| competence.
|
| /s
| hermitcrab wrote:
| I have built 3 commercial products: a seating planner, a visual
| planner and a data transformation tool. I'm confident that Excel
| is the main competitor for all 3.
| williamtwild wrote:
| >But there's one software product born in 1985
|
| Excel born in 85? Ok, but spreadsheets were around long before
| Excel
| kgwgk wrote:
| And long before computers!
| Tijdreiziger wrote:
| That's discussed in the article.
| moonbug wrote:
| not only will it never die, it's more important and has greater
| impact than all your hipster languages ever will.
| terryf wrote:
| Definitely it will never die.
|
| You know you've won when there's a special interest group (with a
| yearly conference!) focused on risks the software is creating
| http://www.eusprig.org/
| jedjdjdjaj wrote:
| Excel is shit.
| moonbug wrote:
| Excel is programming. fight me.
| fersarr wrote:
| I'm also one of the people trying to build a more convenient way
| to do these kind of things: https://hupreter.com
| GnarfGnarf wrote:
| I've seen spreadsheets with horribly complex, incomprehensible
| algorithms, that are so much easier and simpler to write in C++
| or Python or any programming language.
|
| BTW XLWare makes a great library LibXL to create genuine .XLSX
| files from a program.
| _pdp_ wrote:
| Excel is great no doubt but everyone forgets that it is solving
| only some types of problems - mostly data input, fomula-based
| calculations, etc. It is completely unfit for purpose for others.
| zwieback wrote:
| I remember playing around with Multiplan and 1-2-3 in the 80s but
| the first time I saw Excel I knew I witnessed the birth of the
| greatest SW product of all times, ever. At the time engineers
| still spent a lot of time writing crappy one-off homebrew
| scientific apps and Excel really helped as a first platform for
| calculations you didn't want to do on your calculator.
|
| It's hard to imagine now but at the time PCs were command-line
| only and the early Excel versions, at least the one I saw, booted
| up a runtime version of Windows just to run Excel. I'm not sure
| whether they ported it over from MacOS to that special version or
| what but it was shocking to see.
| selimthegrim wrote:
| Engineers can do better than Excel! See Blockpad
| (https://blockpad.net)
| zwieback wrote:
| Looks nice, I'll give it a whirl.
| pjbster wrote:
| I will never trust Excel for manipulating any real world data.
| Period.
|
| Excel only keeps the first 15 digits of any number you give it
| [0]. If you want to keep the full number, you have to store it as
| text instead. And then you can't perform calculations with it
| without converting back to a number and losing fidelity.
|
| The two most prevalent data types in business are numbers and
| dates. It's incredible that Excel is rubbish at dealing with both
| and yet the world thinks it's the gold standard for doing
| "business-y stuff" in.
|
| [0] https://docs.microsoft.com/en-
| us/office/troubleshoot/excel/l...
| hermitcrab wrote:
| If anyone is interested in reading about Excel related disasters
| then I recommend Matt Parker's intersting and entertaining
| 'Humble Pi' book.
| TheRealDunkirk wrote:
| Like a lot of Linux zealots who ran Linux everywhere for 20
| years, I hated most things associated with legacy Microsoft,
| because of EEE, and what they did through SCO. However, I had to
| relax my attitude about 10 years ago, when I realized that I owed
| my entire career to Excel.
|
| I've mostly made a living by being an engineer who creates
| proper, focused software tools for other engineers to use, and
| almost every program I've written is because there was a crappy
| Excel tool trying to cope with the problem, and falling over due
| to size and unshareability. That's when I write a website in
| Rails, or a WinForms .NET application, keep adding features until
| users stop asking for fixes, then move on to the next one.
|
| Of course, there's been ebb-and-flow in my career, but the bulk
| of it has been driven by the fact that Excel is so seductive, and
| easy to start something useful. Then, like a lot of Microsoft
| products, leaves you hanging when it's time to get serious. So,
| credit where it's due. Whatever you can say about it's
| shortcomings, they've been my bread and butter for 27 years, and
| counting.
| F_J_H wrote:
| _> > "Then, like a lot of Microsoft products, leaves you
| hanging when it's time to get serious."_
|
| This is like a moving company saying their small moving vans
| "left them hanging" once they outgrew them and started doing
| national vs. local moves. A tool that proves useful at one
| stage is not useless because it can't be as useful at ALL
| stages.
|
| The great thing about Excel as it enables you to do the one
| thing that kills most start-ups, projects, etc., which is
| simple to _start_.
| TheRealDunkirk wrote:
| I mean... that's kind of exactly what underlies my whole
| comment.
| aksss wrote:
| > the one thing that kills most start-ups
|
| I thought you were going to say taxes and bookkeeping. :D
| xupybd wrote:
| The company I work for has their CAM manufacturing calculations
| run through excel.
|
| The non technical director has calculated every possible route
| line required for our CNC process. This is something that would
| be very hard to do in a conventional programming language. He did
| it with no coding background and it's one of the most
| maintainable pieces of software in the business. It's all laid
| out in front of you. If they need a diagram to explain something,
| it's there inline. There are no unreadable long nested if
| statements. He didn't even know you could nest them. It is truly
| amazing, I've not seen anything like it. I've seen plenty of
| train wrecks where people try to run other parts of the business
| through excel.
| makach wrote:
| Except for when it plunges itselfs into the fiery pits of Mordor
| capybara_2020 wrote:
| This is the most confusing article layout I have ever seen. The
| first part is almost an article in size and is an ad and then
| what seems to be another ad about some people. I was about to
| close this article assuming it was just spam with a click baity
| title.
| mbreese wrote:
| I'm fairly certain the original format is for an email
| newsletter. Once I figured that out, it all made more sense.
| wmab wrote:
| Agreed, it's a well researched piece published without any
| editing. Some good treats in there though if you can find them!
| monroewalker wrote:
| Do others here still recommend using Excel/Google Sheets despite
| having programming knowledge, or are there more powerful tools
| available (eg. Airtable) which might be less accessible to people
| without tech backgrounds? If spreadsheets are still the way to
| go, any recommended resources on learning some best practices
| with them?
|
| I never got into spreadsheets because it seemed unnecessary after
| learning how to program, but I end up missing out on applications
| that might work in excel but which I don't care enough about to
| hand-write.
| munchbunny wrote:
| Yes, I absolutely still recommend Excel/Sheets.
|
| It highly depends on what work you need to do, but in general I
| use Excel for quick and dirty data crunching where the number
| of rows isn't that big (<100,000) and I don't expect to need to
| repeat the analysis often. For example, as a cyber security
| analyst, one-off sifting through some CSV format logs. Being
| able to do some basic transforms on the data with the benefit
| of real-time visualization is nice.
| aksss wrote:
| Absolutely. Excel is a workhorse. It would be like asking if,
| since I got my bulldozer, do I ever still use a shovel. Yes.
| All the time. It has massive casual utility on a day-to-day
| basis. Add to that a lot of your business partners communicate
| information in Excel. When I was a young programmer I
| intentionally developed good excel skills because it was the
| idiom my customers knew. I felt like they would think less of
| me if I wasn't competent on such a basic tool of business. And
| I think that instinct was correct. I look at someone in the
| corporate world who's unable to sling a good spreadsheet as
| developmentally disabled. Like if you walked in saying you
| don't know how to use a word processor. Spreadsheets are a way
| of exchanging data between humans in the same that word
| processors facilitate this, but for structured data sets. Time
| spent learning basic Excel functions (math stuff and xlookup
| for example) and how to build pivot tables will pay you
| dividends over the years.
| Arainach wrote:
| Excel is incredibly useful for all sorts of quick data
| crunching. My preferred introduction to intermediate-level
| spreadsheet usage is Joel Spoelsky's "You Suck at Excel"
|
| https://youtu.be/0nbkaYsR94c
| coliveira wrote:
| One of the main excel advantages come from the fact that the app
| presents useful features and never changed their interface.
| Unlike modern software that try to change their workflow to
| "improve" it, excel has kept the same stable interface for
| decades. This allows people to create workflows built on the
| software, not fighting it. Excel users know that the interface
| they employ will be stable for another 20 years, or even more.
| Very few applications can say this, the other ones I know of
| including Vim and Emacs.
| warmwaffles wrote:
| When they updated the interface from office 2003 to fit with
| the new hot rounded UI was a tragedy.
| asdff wrote:
| No one held a gun to your head and told you to uninstall
| perfectly good office 2003
| warmwaffles wrote:
| Except when it was forced on us by our IT department.
| charwalker wrote:
| Yeah, they tried adding a Ribbon once in 2007 and it blew up so
| much they left it in but barely changed it since.
| asdff wrote:
| R would like a word. 45 years of workflows and still going.
| pwdisswordfish0 wrote:
| "Stable"? Like the version that screws up basic window
| management features? (Simulates the Alt+Tab behavior; doesn't
| actually let you have two Excel windows side-by-side.) Thanks,
| Office team.
| fermienrico wrote:
| Native Windows XP + Excel was badass. No new features were
| needed. No one asked for anything else. It was feature complete.
|
| Have you tried running an older version of Excel (2007 era) even
| in a VM? It is lightning fast. No cell-movement animations and
| this unibar crap at the top, saving a file is 2 clicks away
| unlike the newer version going off to the cloud, making requests
| and having to back out to save locally. WTF.
|
| Excel is an amazing app. UI engineers and PMs at Microsoft are
| trying to kill it.
| aksss wrote:
| That save as... workflow is most annoying. Adobe does that too.
| In almost every scenario there's a sync tool to take care of
| cloud storage. The web is great, but the web is slow and
| brittle. The cloud save is best when it's an asynchronous
| background task.
| tgbugs wrote:
| In academic data management I have identified what I suspect is a
| common progression over the course of a career. Obliviousness to
| the problem. Rage at the inadequacy. Search for alternatives.
| Realization that you do not have the resources to build and
| maintain something else, much less train all the labs to use it.
| Further realization that it is harder to explain how to set the
| right file encoding and the fact that a csv file is not a colon
| separated file to hundreds of labs than to just accept xlsx.
| Acceptance that Excel is a more reasonable user interface for
| data deposition than anything you could come up with.
|
| It is sad, but at the end of the day, however bad Excel is for
| life sciences data (to the point where standards bodies renamed
| genes due to autoformat issues!?), it ends up being better when
| usability and bad data edge cases are considered. Defaults matter
| for non-technical users, and even asking them to change the
| format they save in to csv is likely to cause issues because it
| is one more manual step that can go wrong, or there is some
| locale nonsense that will cause something to break etc.
| aarondia wrote:
| Excel, and more importantly, the spreadsheet is the best way to
| build intuition for a dataset, hands down. The alternatives,
| especially when it comes to Python and the default pandas output
| in a Jupyter Notebook are horrendous.
|
| Where Excel falls short, is data size limitations + auditability.
| Putting more than 1M rows of data into Excel is not possible, and
| once you get into the low 100K's, it becomes almost unbearable.
| And handing off an Excel workbooks to a colleague is handing them
| hours of cell dependency tracing. On the other hand, data size +
| auditability are the super powers of Python data analysis.
|
| I've been building a Python package, Mito (https://trymito.io/),
| its an interactive spreadsheet that automatically converts your
| spreadsheet analysis to the equivalent pandas code. You can write
| spreadsheet formulas, merge datasets, create pivot tables, etc.
| And because its implemented in Python, you can manipulate
| datasets with 10M rows of data with no problem. Our goal is to
| bring the intuitiveness of Excel data manipulation to Pandas.
| screye wrote:
| > best way to build intuition for a dataset
|
| Could you elaborate on this please ? I work with a lot of
| datasets, and have found python + libraries
| (plt/pd/np/scipy/regex) to be far more useful. But, that might
| just be my inexperience with excel.
|
| Can you give a few examples of analyses that work better in
| excel than python ?
| csydas wrote:
| Not the GP, but I think they are speaking specifically to
| non-programmers with this statement.
|
| It's not about which analyses are more performant/easier in
| one object versus the other, it's how do you most easily
| introduce the general audience to big data, both reading,
| manipulating, and transforming.
|
| I actually disagree with their statement tbh, as I think that
| it's too nuanced of a situation to scope like this.
|
| I used to work in a university, and depending on the dataset
| and the intended output, I would switch between R and Excel
| for the students. Those who needed R level analysis
| eventually saw why it was more useful for them than Excel and
| got good at seeing when to use R versus when to use Excel.
|
| Those who had datasets/output goals that didn't need heavy
| lifting really just needed Excel. It's not incorrect to say
| that learning heavier tooling/languages is a benefit, there
| is also a time consideration to learn and become efficient at
| a given toolset. The heavier toolsets have their nuances and
| accomplishing the same task in less robust toolings like
| Excel is the more efficient and better approach for those who
| have extremely limited time and for those who are not likely
| to need the heavier toolset in the future.
|
| It's just a simple cost benefit analysis -- what tool is
| going to give the best return on time investment?
|
| There is a very valid and reasonable argument that investing
| into the heavier toolsets will eventually reach a point where
| even the simple tasks that Excel and other tools allows users
| to perform more easily with less knowledge is faster/better
| with the heavier language; the question is "when is it
| optimal for a given person to invest the time to get to that
| stage?", and that's a question that doesn't always have all
| available data to make an informed decision on since it's
| hard to predict the future.
| aarondia wrote:
| You're definitely correct that its a nuanced question
| whether for a given (user, analysis) pair they are better
| off in Excel or Python/R/etc. Specifically with respect to
| building intuition for a dataset, however, there is a huge
| benefit of having an interactive data representation (if
| only for the ability to scroll and see all of your data).
|
| Because you can think of Mito as a frontend interface to
| Pandas, using Mito doesn't prohibit you from building
| intuition or analyzing your data in the same way you would
| if you didn't have the spreadsheet frontend. It just helps
| you write the Python/Pandas code faster + see the most up
| to date version of your data set in live time.
|
| The typical Mito user uses Mito multiple times throughout
| an analysis. A common pattern is: start by just visualizing
| the data in Mito, create a few graphs to help understand
| the distribution using matplotlib (right now we only have a
| tiny bit of graphing support), passing the data back into
| Mito to do some filtering and cleaning, then lastly
| creating a pivot table output using Mito. Of course, it
| varies greatly from user to user, but that's a general flow
| we see often!
| iamacyborg wrote:
| I think it's more simple than that. The way the data is
| presented to you in Excel makes it incredibly easy to grok.
| F_J_H wrote:
| For me it's SQL and then simply visualizing.
| aarondia wrote:
| As a bit of background on Mito, it works by passing the
| parameters from the frontend spreadsheet to the Python
| kernel backend, which transpiles the spreadsheet formula
| into Python/Pandas code [0]. So what we're hoping to do
| down the line is let the user pick which language to
| translate to, SQL and R being the obvious next steps. But
| that's a ways away :)
|
| [0] https://trymito.io/blog/transpiler
| fifilura wrote:
| The problem with SQL is that it is not great with
| pivoting. But maybe that is not a big problem when you
| auto-generate the SQL.
|
| I agree, SQL is what I like more for mangling. Except for
| the pivot/melt part that is
| loudmax wrote:
| IMHO where Excel falls short is in interoperability. Try
| processing .xlsx files in anything _other_ than Excel and it
| can be painful. Let 's see the new more open Microsoft really
| embrace competing on an even playing field that doesn't rely on
| ossified proprietary file formats.
| aarondia wrote:
| Lack of interoperability is an interesting angle, but not one
| that I've ran into quite as much. I've usually gotten around
| that by converting to a csv file in the case of data, or
| screenshotting graphs, etc.
|
| Would love to hear a bit more about your workflows where
| you're trying to process an .xlsx file in another system. I'd
| imagine it would be a nightmare, but haven't ran into it
| myself :)
| fartcannon wrote:
| CSV files is one of the main problems with excel. Auto-
| guessing formats is the bane of many casual importers. And
| the warnings about tsv or csv when you open is tantamount
| to dark pattern. I can accept it once, but let me disable
| the pop up. Do Not auto save to xls or xlsx.
| aksss wrote:
| I don't see that as a dark pattern because it's honest -
| I will often open up a CSV and then turn on filters, sort
| the data, maybe highlight a few rows. The warning about
| losing that when trying to save as a csv is an honest
| warning. The same as opening a jpg in Photoshop or Gimp,
| adding layers and trying to save it back out - you'll
| also get a warning that you're about to lose work by
| saving to a simple file format.
| fartcannon wrote:
| Do it once, provide a "Don't remind me again" check box
| and save that for eternity.
|
| The dark pattern is in repeatedly nagging me about this
| fact.
| orhmeh09 wrote:
| How is that a "dark pattern?" There's no deception
| involved. You're calling things "dark patterns," but I
| think you think dark patterns are just UX you find
| annoying.
| fartcannon wrote:
| I call it a dark pattern because it indirectly
| discourages the use of interoperability in file formats.
|
| The suggestion is 'use xlsx or suffer this annoying pop
| up'.
|
| It's an easy fix. Have an option to disable it.
|
| The other dark pattern, since we are chatting about them,
| is that you cant disable auto formatting. Excel will try
| to guess at your data, forcing you to jump through hoops
| to prevent it. The subtle suggestion is 'just use xlsx
| and this problem goes away' when in fact, they could
| offer an option to disable autoformating.
| gsich wrote:
| CSV is not interoperable as you lose the formulas.
| rchaud wrote:
| Whatever is in your XLSX can be converted to a static CSV
| file. Charts, pivot tables and formulas won't be transferred
| over, but then again I wouldn't expect them to.
|
| The value Excel provides far outweighs the drawbacks of a
| vendor-specific solution.
| zelphirkalt wrote:
| Now we only need to get everyone to export to a
| standardized CSV, perhaps one adhering to its own name,
| comma separated values, and we are good.
| LeifCarrotson wrote:
| The data in an Excel file can be exported as CSV, but the
| value in an Excel file is the dependencies and formulas
| that have been built up into a representation of the
| business rules. The problem is that this often becomes the
| _only_ representation of the rules, and auditing it or
| puzzling the data out of that Excel sheet after the fact is
| like pulling teeth.
|
| A business might want to get to improve, say, their quoting
| accuracy. I've seen lots of places that write quotes using
| Excel. They use a complicated spreadsheet to estimate "We
| need $4500 in parts from vendor A, but in previous projects
| with components from vendor A often needed rework, so we
| multiply their quotes by 1.5 to account for the risk and
| for someone (typically Bob) to rework them; Bob's workload
| is over 90% and he's less efficient when he works overtime,
| so multiply his total hours by an additional 1.25, we also
| have to adjust his hourly rate by 1.5 to account for
| overtime..."
|
| It's a Hard Problem to convert the quoting process from one
| of a few engineers who also do quoting by copying and
| modifying the blank Excel template and years of human
| domain expertise into a process where data entry techs
| input stuff to a CRUD webapp. This is fraught with peril
| because the Javascript/SQL guy you hired to write the
| webapp (or, heaven help you, the SAP consultant) hasn't
| been reworking gear from Vendor A for 15 years and sees
| what looks like an error when the formula for actual cost
| from vendors B, C, and D takes their quote price multiplied
| by 1.1 (for shipping? margin? ) and vendor A's quoted price
| is multiplied by 1.5, and, hold on, the VBA macro
| separately takes the the estimated dollar amount purchased
| from Vendor A, divided by 2000, and adds it to the head of
| maintenance's estimated hourly total for the project?
|
| Making business decisions about logic tied up in Excel
| formulae is hard. Writing logic in something other than
| Excel where you can more easily see the business logic is
| probably harder. Convincing non-technical decision makers
| to learn VBA to evaluate their vendor selection is probably
| harder still.
| rchaud wrote:
| I wasn't really able to follow this, if I'm being honest.
| But given the number of dependencies you listed, both
| human and software-based, it does not seem like Excel is
| the problem here.
|
| It sounds like VBA has allowed that team to build an
| advanced prototype of a quote generation web app. The
| next step seems to be to convert the Excel formulas and
| scripts into JS or Python. Quality assurance may be a
| hassle, but that is to be expected with any kind of
| refactoring.
| bcoates wrote:
| Not really. Processing xlsx files in full generality is
| basically reimplementing Excel, but if all you need to do is
| extract/modify values and formulas or produce a workbook
| excel will accept there's plenty of libraries that will do
| that, and it's not that crazy to just hack something up using
| an xml parser.
|
| I had to port some stuff that was using the google sheets API
| over to manipulating xlsx files instead, and it wasn't a big
| deal.
| civilized wrote:
| I'm a data scientist who isn't an Excel wizard and I'd like to
| have the other direction, turn my dplyr code into Excel
| aarondia wrote:
| I'm less familiar with the R ecosystem, but there are tools
| in the Python world to go that direction --
| https://xlsxwriter.readthedocs.io/
| Robotbeat wrote:
| What non-spreadsheet programming languages/environments do you
| think work better than Python/Jupiter?
|
| I've been fairly happy with the default Matlab IDE personally.
| Visibility and representation of data has the
| straightforwardness of a spreadsheet. But surely there must be
| others?
| twobitshifter wrote:
| Julia in VSCode is nice. The speed gains from Julia are worth
| it alone if you are working with large datasets.
| hypermachine wrote:
| A lot of environments do not have easy to use hot reloading
| out-of-the-box (and also quick GUI creation support). This is
| the primary reason why development feels less intuitive in a
| lot of text based languages versus more visual tooling like
| Excel which can give instantaneous feedback.
|
| Hot reloading is most famous for being a staple of Lisp
| languages (but they tie it to the repl rather than as a
| standalone feature). For Microsoft languages this is provided
| by Visual Studio (commonly known as edit-and-continue, it is
| available in some form or other since the original VB days).
| You can try it out with the embedded VBA interpreter in Excel
| (under the Developer tab).
|
| For JavaScript this is a recent innovation (driven primarily
| by the React/SPA crowd). In Java, most IDEs have the feature
| but it requires a fair bit of setup and configuration (look
| up hot swap for Intellij). The closest thing Python has is
| Jupyter which admittedly is not that pleasant to use.
| lispm wrote:
| > but they tie it to the repl rather than as a standalone
| feature
|
| Lisp has a function called LOAD, which can load source
| and/or compiled code.
| hermitcrab wrote:
| There are a number of visual data transformation
| environments, including https://www.easydatatransform.com (I
| am the developer), https://www.alteryx.com and
| https://www.knime.com .
| airstrike wrote:
| > What non-spreadsheet programming languages/environments do
| you think work better than Python/Jupiter?
|
| RMarkdown + RStudio + knitr
| jdgoesmarching wrote:
| I really wish Python had the kind of support for Markdown
| that R does. Notebooks are fine, but the simplicity of
| having essentially a plain text file is just amazing.
| Something1234 wrote:
| Rmarkdown theoretically supports python. I haven't used
| it though.
| vharuck wrote:
| Knitr theoretically supports anything you can call from
| R. Chunks pass their code and options to "engine"
| functions that return the results. Knitr provides a bunch
| of engines out of the box (Python, awk, SQL). But you can
| also write your own.
|
| yihui.org/knitr/options/#language-engines
|
| I once made a SAS engine to show coworkers how to adopt
| report automation without having to rewrite all existing
| code.
| hypermachine wrote:
| Mito looks really interesting, I look forward to trying it out.
| I'd there a way to sign up for Mito without the hubspot
| meeting?
|
| For us we are going the opposite approach, we are building a VB
| interpreter to make it easier to run, build, and extend
| existing Excel programs. We allow calling libraries written in
| WebAssembly and GraalVM supported languages.
| aarondia wrote:
| Feel free to Twitter DM me @_aaronDR, would love to hear
| about what you're building + get you set up with Mito :)
| igorkraw wrote:
| How does it compare to visidata?
| aarondia wrote:
| There's a bunch of ways that Mito and Visidata are different.
| The two largest probably being:
|
| 1) Mito is an extension to JupyterLab whereas Visidata is a
| CLI tool. As a result, Mito is a react frontend that is more
| of a traditional Excel-styled spreadsheet interface. You can
| use your mouse to perform point-and-click transformations,
| like writing configuring pivot tables or writing spreadsheet
| formulas.
|
| 2) Mito generates Python/pandas code for every edit the the
| user makes. So users are generating a script to manipulate
| their dataframes, running that script, and then continuing to
| use their dataframes throughout the analysis. People use Mito
| in a Jupyter notebook the way that they use pandas code,
| multiple times throughout their analysis, interspersed with
| graphing, ML, etc.
| algorithmsRcool wrote:
| > Putting more than 1M rows of data into Excel is not possible,
| and once you get into the low 100K's, it becomes almost
| unbearable.
|
| I dispute this. Yes, the normal spreadsheet view of excel will
| buckle under 1M rows, but excel has another feature called
| "Power Pivot" that is backed by an embedded database and scales
| into the high millions at least.
|
| I've personally used excel on a dataset of 18M rows and
| PowerPivot handled it just fine.
|
| [0] https://support.office.com/client/Data-Model-
| specification-a...
|
| [1] https://support.office.com/client/power-pivot-powerful-
| data-...
| aarondia wrote:
| There's also a workaround in Google Sheets where you can
| store your data in BigQuery and use the spreadsheet to
| interact with it.
| stilisstuk wrote:
| Yes pp can handle data. But vba can not. And most
| spreadsheets contain vba for reporting and magic interfaces
| for managers. Slow and unmaintainable.
|
| Any day: rmarkdown and csv
| texasbigdata wrote:
| Big fan of this, but powerpivot sits on vertipaq (I believe)
| which is an in memory columnar DB or sorts (apologies if
| that's incorrect). So at this point you're getting awfully
| close to direct querying (another msft feature) which while
| analogous resembles more traditional db/client if you squint
| hard enough.
|
| But yes, big fan of vertipaq which I believe also powers
| PowerBI.
| craig_asp wrote:
| Just to clarify.. Yes, vertipaq is the tech behind power
| pivot, power bi and sql server analysis services (in
| tabular mode) and the same column-oriented storage is also
| used in sql server. Excel generates queries against the
| data stored in a vertipaq model. You cannot write normal
| excel formulas on top of it and you have to use DAX (a
| unique to msft language, which is the replacement for MDX)
| instead, which is pretty much a no-go for anyone but well-
| trained power users.
| agumonkey wrote:
| Excel, depending on who organizes the data, can lead to god-
| table with 123 columns.
|
| Now someone with a bit of balance, can go quite far with it.
| 1980phipsi wrote:
| New sheets are your friend.
| sonthonax wrote:
| Nice product, I noticed that you are updating the next Jupyter
| cell; what was your solution to doing that reliably since
| `set_next_input` is so damn flakey?
|
| I personally grew so frustrated with the state of GUI
| development in Jupyter that I tried to fix it in such a way
| that would allow proper message passing between cells and
| python code (because you can't wait on Comm events).
|
| > https://github.com/ipython/ipykernel/pull/589
|
| But sadly the priorities of big open source projects don't
| always match your own. So I had to extract that logic into my
| own kernel.
| tryitnow wrote:
| What's the pricing model? There's a lot of potential here.
| aarondia wrote:
| We're still exploring the business model to support the
| project. Right now, Mito is free to trial, and we're
| experimenting with a subscription plan somewhere in the range
| of $10 per month. I'd say about half of our users are using
| it for free and half of them are on some sort of
| subscription.
|
| We're also considering open sourcing the tool, and doing the
| classic Enterprise Sales / consulting / other value add
| services on top.
|
| If you have ideas about which direction to take it, would
| love to hear!
| serjester wrote:
| Horrendous? Strongly disagree if you're doing anything non-
| trivial. As soon as you start mixing the two, both become
| worse. You're basically losing the UX benefits of native Excel
| and adding confusion to Pandas.
|
| Yes, Pandas has a learning curve but so does Excel once you get
| into advanced functionality. It's inevitable. Once you get
| through this it's a fairly intuitive powerhouse.
| aarondia wrote:
| That's a good point -- and maybe horrendous was too strong of
| a word. It's actually been really interesting talking to
| people who come from the Excel world vs the Python world
| first. There's a group of people we've ran into who have
| never used Excel before, and for them, you're right, giving
| them a spreadsheet interface to write formulas and manipulate
| their data is actually a huge disadvantage.
|
| I think the way that Mito tries to walk the line is by making
| the Python code visible for the user to see what the
| equivalent Python looks like + easily usable in your
| analysis, but also completely generated for you. So
| hopefully, we're not introducing the confusion of pandas into
| your workflow.
| hacker20210308 wrote:
| I have a soft spot for Excel.
|
| It inspired me to do this 4 (almost 5 now) years ago in
| JavaScript. The prototype was written to prove a DOM renderer
| could handle trillion cell datasets having a high on screen cell
| density running at 60fps.
|
| Bonus: Watch to the end of the video to see video running in a
| cell.
|
| https://m.youtube.com/watch?v=ff-eDS4OZpk
| syntaxing wrote:
| Excel just works so well, almost too well. Wanna share some
| formulas? Excel. Finance calculator? Excel. A small (<2000 lines)
| database? Excel. Even with Python for Google Sheets, I sometimes
| want to rip my hair out when I use Google Sheets compared to
| Excel (though the Google Python is pretty useful for
| autogenerated sheets).
| guessbest wrote:
| Excel is basically a 4GL at this point replacing Foxpro and
| other. The only thing I've seen Access is used for is ODBC
| connections to databases.
| aksss wrote:
| Access is an interesting beast. Much maligned by IT shops
| everywhere. But it's pretty powerful when you think about it
| as excel with a relational table model and a built-in
| reporting interface. I mean it makes all the sense in the
| world why it exists as a business tool, but it's a cul-de-
| sac, usually tipping you off that it's time for a "real"
| database and/or some COTS apps. Been decades since I
| seriously used Access, was before I ever got really exposed
| to true RDBMS's and writing against them. But it's probably
| where I got introduced to SQL, something a surprising number
| of developers don't know these days.
| not_knuth wrote:
| Tell me more about these developers that don't know SQL.
| How is that possible? What line of work is this in?
| dgdosen wrote:
| I think MS is making a postitive step in allowing loading js/ts
| libraries via node as part of it's programming model compared
| to just using VBA.
|
| This could be very powerful. It would help Excel to be
| repurposed to potentially something greater....
|
| Of course, Google Sheets and Apple Numbers should tap into that
| same functionality...
| scubbo wrote:
| > I sometimes want to rip my hair out when I use Google Sheets
| compared to Excel
|
| Earnest, non-"gotcha" question - what is it about Google Sheets
| that you dislike or find irritating? I'm only an entry-level
| user for both, but I've found them of similar quality and
| functionality.
| kthejoker2 wrote:
| As someone who pretty much only uses them for quick and dirty
| data shaping, at least for me Google Sheet's filter
| experience is garbage compared to Excel. Takes 5 clicks to do
| anything, can't right click to add/remove filters, the
| filtering process itself is janky, doesn't recognize data
| types ...
| iamacyborg wrote:
| > Earnest, non-"gotcha" question - what is it about Google
| Sheets that you dislike or find irritating?
|
| Using Tables in Excel is a gamechanger. Not having support
| for them is a huge point of frustration for me whenever I
| have to use GSheets. 95% of that is the fact that I can refer
| to the Table and columns by a given, logical name rather than
| having to use arbitrary cell identifiers.
| quacked wrote:
| Excel Tables combined with Excel Power Query will turn "hey
| boss, I figured out a way to save a few hours a week" into
| "hey boss, I just eliminated several people's jobs".
| aaisola wrote:
| Google sheets is useful for basic tasks. But as a power tool
| for complex models etc. it pales in comparison to excel. Not
| to mention that keyboard shortcuts are not the same which
| makes everything take significantly longer.
| p00dles wrote:
| ^1 for Excel keyboard shortcuts.
|
| One can customize the ribbon at the top for must used
| functions, which can make Excel such a fast tool to use
| compared to Google Sheets or even Excel for Mac (speaking
| as a Windows user).
|
| If I had a big Excel project to do, and I had the choice of
| 1/2 day on Excel (Windows) vs. a full day with Google
| Sheets or Excel (Mac), I would pick the 1/2 day with Excel
| (Windows).
| aksss wrote:
| The keyboard shortcuts improve productivity so much.
| Yeah, as another said it's amazing when I try to use
| Excel on a Mac how much I evidently depend upon the
| shortcuts in normal use. They're all different on the Mac
| version, and I can only take so much of it before I just
| email myself what I was working on and pick it back up on
| the PC. Seems like an easy thing for MS to reconcile but
| I don't want to give up my keyboard mapping, and I'm sure
| the Mac Excel guy doesn't either. Nice feature would be
| to choose what shortcut layout you wanted despite
| platform.
| aaisola wrote:
| Agreed..I actually have a separate laptop (Windows) that
| I use for nothing but excel. Even Excel on Mac isn't the
| same as on Windows
| p00dles wrote:
| I removed the F1 from a keyboard so that I don't misfire
| when going for F2, resulting in the dreaded 'Help' window
| that you have to use a mouse to click out of.
| aksss wrote:
| Microsoft PowerToys on Win10 will let you remap keys. If
| nothing else you could make F1 do what F2 does.
|
| https://docs.microsoft.com/en-us/windows/powertoys/
| layer8 wrote:
| Ctrl+Space, C used to work for closing the Help window
| until recent versions. Unfortunately that doesn't work
| anymore, the only solution is to use a VBA macro bound to
| a keyboard shortcut. At least in return the keyboard
| focus now remains in Excel instead of switching to the
| Help dialog.
| ghaff wrote:
| This is true of GSuite generally. So long as you need
| fairly basic functionality (which is all a lot of people
| need), its simplicity is a virtue and it works well. I
| prefer it to Microsoft Office 99% of the time. (Though I
| sometimes need Office for interoperability as well.)
|
| I used to sometimes have to run massive spreadsheets. But
| these days, I mostly use it for things like personal
| activity tracking.
| mediaman wrote:
| Agreed. Google sheets feels great initially. Then you go to
| do that thing that you do in Excel, and...you look for it,
| and it seems like a basic omission, so you figure you just
| missed it, and Google around for it, and no, it's just not
| there.
|
| Even silly little things, like the fact that Sheets doesn't
| have an indent function, which makes it harder to neatly
| format financial data. I think the accepted workaround is
| to manually put spaces in front of every single row you
| need indented.
| fifilura wrote:
| What we'd do - as the layer between engineering and upper
| management - was to do as much aggregation as necessary
| using notebooks and at the end run the "export to google
| sheets" call (thin layer on top of google apis). That would
| give the recipient some kind of control and allow them to
| feel the data and twist and turn it their own way, while
| not having to do the "big data" python/SQL stuff
| themselves.
|
| I am at a new company now and I have yet to figure out how
| to create the "export to onedrive/excel" command. Google
| libraries to google sheets seemed so much more competent
| and well built. (But maybe i am biased...)
| jeanloolz wrote:
| I have extensive experience with both and the main benefit
| Excel has over Google Sheets (in my opinion) is the amount of
| rows you can handle all at once. With Excel you can
| manipulate 200k rows easily. The same can not be said with
| Google sheets due to the fact that it remains a cloud based
| tool. I found Google sheet to be enough though in 90% of my
| cases (may change depending on what you usually work on). The
| scripting ecosystem google sheets has is amazing (apps
| scripts and various python libraries) and is much stronger
| compared to Excel.
| neolog wrote:
| > With Excel you can manipulate 200k rows easily. The same
| can not be said with Google sheets due to the fact that it
| remains a cloud based tool.
|
| How is the number of rows related to cloud-based?
| scubbo wrote:
| That's helpful to know, thanks!
| [deleted]
| ulucs wrote:
| My personal gripes are the lack of tables, RC notation and
| iterative calculation
| bcoates wrote:
| Google sheets has a 5 million cell limit, and can't usefully
| do things cross-workbook. In practice complex spreadsheets
| substantially smaller than that are slow and unstable.
|
| That said, the multi-user editing is much smoother than excel
| and the remote API is better.
| yread wrote:
| Except that it keeps changing. It's already on version 4
| and at least one forced a complete rewrite of accessing
| cell values (and of course the old version was
| discontinued). Compared to Excel files from the 90s that
| keep running
| Spooky23 wrote:
| Sheets is awesome and has a lot of power, but it's
| constrained in the browser and is defined by its competitor.
|
| The other issue is that you don't see as many power users of
| Google Docs and Google doesn't have a clear strategy. For
| example, they could easily make a power bi type tool on top
| of Sheets and Slides.
| tln wrote:
| Like Data Studio?
| iamacyborg wrote:
| Right, but why invest more in that when they also offer
| Looker?
| tln wrote:
| I didn't know about Looker, but my BI needs are modest
| and Data Studio is free and simple enough.
|
| Looker is a third party solution right? Or does Google
| offer Looker directly in some way? If you're up for
| sharing the pricing for looker, I'd be curious (the
| looker website has a request quote button, so I'm
| guessing it's not cheap)
| Spooky23 wrote:
| Went through a product evaluation and that product was
| never mentioned!
| Mengkudulangsat wrote:
| If Excel can handle >1m rows, all these "Big Data" analysis will
| just mean a bigger spreadsheet.
| apples_oranges wrote:
| I've just read the chapters on VisiCalc and Lotus 1-2-3 in
| "Founders at Work". It looks like VisiCalc made the Apple II take
| off back in the early days. And Lotus 1-2-3 addressed all the
| pain points people had with it a few years later on a more
| powerful platform (PC). Interesting stuff for sure. Why do I
| discover this book 12 years after it was published?
| wmab wrote:
| This is a well researched article touching on some key points as
| to why Excel lives on. Interesting for me is the unbundling of
| Excel, and the birth of many B2B SaaS products. I'm not sure
| Excel was ever the right program to generate many of these
| products, and so it makes sense someone built a specific program
| to house it (product boards, CRMs, calendars all fall in this
| imo). Others however, absolutely, I'm not sure the long term
| value in some products that can simply be done in Excel - often
| when a startup goes after one of these verticals it's hard to
| create value for the user and the business, because by building a
| product you are by definition limiting the potential/power of the
| program by limiting what it can do. So you're asking someone to
| pay for something that is more limited than it's Excel-cousin,
| but might be simpler to use and look prettier. An example to me
| of this is financial modeling / flightpath type apps for
| businesses. Instead of paying for a bunch of saas, maybe a
| company should go back to hiring more Excel ninjas, ergo Excel
| Never Dies.
| Balgair wrote:
| Aside: Joel Spolsky's now famous talk about Excel is a _must_
| watch for anyone that uses Excel but hasn 't been arsed to
| actually take a course in it. Even for Excel haters out there,
| the talk is _very much_ worth your time today.
|
| https://www.youtube.com/watch?v=0nbkaYsR94c
| omega3 wrote:
| I don't see any software produced today approaching the level of
| user-centric productivity and speed one can get out of (keyboard
| only) Excel. Is software like this just not being produced now?
| jerjerjer wrote:
| Hilariously, Access is another Microsoft product which also
| helps with user-centric productivity.
|
| Many hate it but it sure is an easy way to build an entire data
| entry/CRUD app without any programming knowledge.
| asdff wrote:
| It totally is. R is even faster and more productive than Excel.
| Buttons840 wrote:
| I've been thinking about trying to create my own open source
| spreadsheet. I can't decide if they're simple or complicated.
|
| The simple view is that all you need is: (1) One main UI
| component, "the cell" (2) a domain specific formula / programming
| language (3) the underlying reactive system that tracks cell
| dependencies and updates them, etc.
| munchbunny wrote:
| I think it depends on what you intend for it to be used for.
|
| The reductionist view of it is that it's a workspace for
| crunching numbers. But in practice, Excel is sometimes used
| like a frontend for a database engine, with sometimes heavy
| scripting to integrate into software processes and workflows. I
| think Excel's ability to stretch beyond what anybody would
| still reasonably consider the scope of "spreadsheet software"
| is why Excel is as entrenched as it is.
|
| I don't think many programmers see it as a safe or ideal way of
| handling the kinds of workloads that people use it for, but I
| think we all acknowledge its unmatched ability to let non-
| programmers automate data crunching.
| airstrike wrote:
| Well, you can """Write your own Excel in 100 lines of F#""":
| http://tomasp.net/blog/2018/write-your-own-excel/#
|
| Posted here but didn't get traction:
| https://news.ycombinator.com/item?id=24980325
| haolez wrote:
| Excel is like Emacs for non-programmers. It's a sandboxing tool
| for your imagination to go wild.
| ianhorn wrote:
| People are all talking about how it's useful for quick stuff with
| datasets, but it goes far beyond that. It's useful for quick
| _anything_ you might want a small database for. Imagination is
| the limit.
|
| For example, if any of you play D&D online, you might be familiar
| with dndbeyond's character sheets. They're a fantastic way to
| onboard new players who might not have the inclination to spend
| hours with the rule books before they even start playing. It does
| all the calculations for you and gives you some buttons like
| "roll athletics" and doesn't let you add more spells than your
| character can have with their stats.
|
| I recently persuaded some friends to give FATE a try and built
| analogous push-button character sheets with google sheets [0]. It
| was quick and simple. With conditional formatting, you highlight
| bad states (rules say you can't have more of X than Y!). With the
| script editor, you can add full on buttons for dice rolls and
| other state changes with whatever logic you want (anything you
| can code up!). Checkboxes are obvious but super useful. And the
| transparency of the calculations is helpful for teaching people
| the system (this stat is "min(A4, B1+C5)").
|
| Without google sheets, it would be a serious endeavor to build a
| stateful, database backed, live collaborative GUI that can be
| added to and customized on the fly by my users. With google
| sheets, it was a quick fun afternoon hack. Excel/google sheets is
| an amazing piece of technology.
|
| [0] Screenshot of the "app":
| https://raw.githubusercontent.com/imh/public_images/main/Scr...
| asdff wrote:
| I find with excel it takes just as much time to set up a
| spreadsheet to do what I want as it takes for me to do the same
| in R, usually a lot more time with Excel. Both pieces of
| software have learning curves, just to me R is the better tool
| for the job for working with tabular data. Excel forces you to
| hardcode your fomulas and ultimately adds a lot of cruft and
| time wasted, compared to R which is much more modular. That
| "min(A4, B1+C5)" is liable to break if your spreadsheet
| changes. R functions on the other hand are pretty well
| documented, and you could do anything you want in R after
| following a tutorial for a couple hours. Instead of having to
| hard code a position, you can refer to it relatively or by some
| unique identifier, so your calculations still work no matter
| how your underlying spreadsheet changes or is shuffled around
| (and familiar formulae like sum and min and max are there by
| the same names). It's way easier to do statistical tests and
| plot data consistently in R as well. Oh, and you can export to
| .csv or .xlsx from R if you'd like of course.
| CJefferson wrote:
| At no point do you talk about UI here -- for a character
| sheet (or most simple things), I want to control layout to
| some degree, let users edit some numbers, and see others
| automatically update. Obviously I could have a file full of
| constants and a bunch of print statements at the end, but is
| there anything nicer / more dynamic?
| jimbokun wrote:
| 1. You know how to program.
|
| 2. How does the UX for your R solution to the "DND Character
| Generation" problem compare to the screenshot from
| grandparent comment, for users not familiar with either R or
| Google Sheets?
| jjnoakes wrote:
| > That "min(A4, B1+C5)" is liable to break if your
| spreadsheet changes.
|
| I don't usually have that problem. Inserting or deleting rows
| or columns around the cells doesn't break these formulas.
| Only changing what type of information a cell contains would.
| Does this happen often for you?
| stjohnswarts wrote:
| Thanks, I'm definitely going to check out those sheets. I
| haven't d&d'd in a while and was thinking about joing up to a
| group at my local comics shop.
| navneetloiwal wrote:
| We started a company with the core premise that spreadsheets
| will never die [0]. Spreadsheets are so good at the most casual
| data viewing and exploring tasks to creating complex financial
| models. They are also the de-facto choice when you have data
| (not big data) from multiple sources that you need to "join".
| We tend to underestimate the beauty of this tool which can be
| used productively at all points of the skill spectrum. Everyone
| feels at ease in the familiar territory of a spreadsheet, which
| is what makes it ubiquitous and basically impossible to kill.
|
| If spreadsheets were two-way connected with your core systems
| like SaaS tools, DBs, Slack, etc then you could represent
| serious business logic and actions without being a programmer.
| It is the best platform to build a "no code" tool for non-
| programmers.
|
| [0] http://coefficient.io
| Waterluvian wrote:
| Absolutely.
|
| My last five uses of excel are widely variant in theme:
|
| - validate my taxes make sense
|
| - track Bloodborne platinum trophy progress
|
| - collab with wife on Christmas gift planning
|
| - estimate lumber purchase for project
|
| - collab with coworkers to explore ota data culling options.
| wlesieutre wrote:
| I'm running a game of D&D and needed an accessible character
| sheet that works from a phone with good support for pinch-to-
| zoom.
|
| Tried fillable PDFs and a bunch of online stuff. None of it
| worked well. The spreadsheet fields' font sizes were all weird,
| and even if you manually correct them it would reset on every
| edit. There were some promising web-based options described as
| "responsive character sheet", but they tended to fall apart at
| large text sizes.
|
| Best option? A spreadsheet from Knights of The Braille:
| https://knightsofthebraille.com/59-2/
|
| Instead of trying to shove an 8.5x11 paper layout into a phone,
| it just groups stuff into tabs that make more sense anyway. And
| if you were completely blind I bet it's still easy to navigate
| with VoiceOver.
|
| We're using Numbers because it's what we both have, but I think
| Excel should work similarly.
|
| If anyone's reading this from the Google Docs team, please take
| another look at Sheets' pinch-to-zoom behavior. That was the
| first place I ended up when I went looking for character sheet
| spreadsheets online, and it was the first one I ruled out
| because of how shitty the experience was on mobile.
| fredfoobar wrote:
| OT, but does anyone remember the easter egg in excel that
| literally opened up a flight sim?
___________________________________________________________________
(page generated 2021-03-08 23:00 UTC)