Hacker News Comments on
You Suck at Excel with Joel Spolsky
Joel Spolsky
·
Youtube
·
1276
HN points
·
71
HN comments
- Ranked #9 all time · view
Hacker News Stories and Comments
All the comments and stories posted to Hacker News that reference this video.You can use the Solver GUI to do it in one column maybe:
Every new hire gets to watch this video (https://www.youtube.com/watch?v=0nbkaYsR94c) then we go through daily Excel tips and tricks until they're comfortable with it. I know Excel is not the right tool for everyone or everything, but the amount of useful work you can get done quickly IF you know a few basic Excel text manipulation functions is crazy.I even have an Excel spreadsheet that helps me solve Wordle.
⬐ rubslopesI once read somewhere, maybe here: "If I were to choose a single software to use for the rest of my life, it'd be Excel". I agree with that.⬐ diego_sandoval⬐ cartoonfoxesI would choose a web browser.⬐ MisterSandmanWouldn't python be a better answer, or is that cheating?⬐ ryzvonusef⬐ hulituIs python really considered "software" in common parlance?I think the comparable item would be something like VSCode.
⬐ arminiusreturnsEmacs would be the real cheating answer.⬐ tmtvlCreate a table in org mode, pass it to Common Lisp (or Perl or Scheme or R or whatever else floats your boat), and finally hand the thing over to graphviz for an in-line view of the results... and you can write up the relevant formulas with LaTeX.⬐ joeman1000Everything in Julia src-blocks please. Super comfy.... until all your numbers become dates.⬐ aloisdg... or until all your molecules become dates.⬐ 2Gkashmiri⬐ OGWhalesWhy do I remember awk with that story?Or your numbers are wrong because you didn't wrap in parentheses.Example, put these two formulas in excel:
=(4/3 - 1)*3 - 1
=((4/3 - 1)*3 - 1)
⬐ dns_snek⬐ SantalBlushThe online version, at least, seems to be wrong when you do wrap it in parentheses:=(4/3 - 1)*3 - 1 = 0
=((4/3 - 1)*3 - 1) = -2.22045E-16
⬐ OGWhalesCheck out page 3-5 of this paper called How Futile are Mindless Assessments of Roundoff in Floating-Point Computation? [0]. It explains what is happening here better than I can and is where I got this example from.Or until your empty cells aren't truly empty, so you can't count the nonempty cells.⬐ hef19898⬐ dugmartinThat reminds of a jokester who replaced all empty cells in small spread sheet with a space. Good lick figuring out why your 5 row table all of a sudden was in the giga bytes of size (obvious exageration for dramatic reasons). Was a good practical joke so!Or all your zip codes with leading zeros get silently truncated.⬐ disgruntledphd2Or until all your 16 digit IDs get truncated...>Every new hireIn what industry?
⬐ cgranier⬐ somatAt the time we were doing online video distribution, which went from ingesting content from traditional sources (betacam, film, etc), editing, preparing distribution-ready videos, create metadata, upload to various systems... all the way to social media campaigns, etc.Where/how does Excel knowledge factor into this?
Well, we managed a lot of data, mostly (but not only) CSV files. It's very useful to learn a few text manipulation functions in Excel when creating CSV files with sequential and or repetitive content (think TV episodes, etc). Or creating several flavors of CSV for each platform. Yes, a database backend with smart exporting functions might work well, but sometimes fast beats perfect, especially for one-off jobs.
Uploading thousands of videos into YouTube was much quicker with one or two CSV files. By learning some basic Excel, everyone was able to minimize errors and maximize output.
What else could we do with Excel? We could export XML files of our video edits from Premiere/FinalCut Pro, run them through a script into Excel and immediately get a report showing all the editing errors that still needed fixing (we had to edit the videos in a very particular way). This alone saved sooooo much time. Interestingly enough, we were also able to identify individual editors by the mistakes they made (it seems each one had a particular quirk).
I also ran the entire digitizing project in an Excel file, complete with burn charts and velocity calculations.
Over the years, I've received calls from every one of my employees, now on with their lives in other jobs, and one thing they're always grateful for are the Excel lessons.
And once you learn the logic behind building Excel functions and spreadsheets it opens your mind to other uses or more programming skills.
It's much easier to teach someone the power of a few choice Excel functions than to teach them Python from scratch. Plus you can see their eyes light up immediately. Fun times.
I think of Excel as great at prototyping stuff. At some point it is better to move your project to a more formal system and leave the warts and limitations of Excel behind. if your project is data heavy this will be a relational database. if your project is logic heavy this will be a programing language.⬐ cgranier⬐ bachmeierExactly. But it is crazy how far you can take Excel (and even Google Sheets) with the proper organization.Excel is great until it isn't. As one example, I've had grad students tell me about a surprising result they found when they did some simple descriptive statistics in Excel. When I ask them to show me, or to check something else, they can't replicate their finding. Then the inevitable "Oh, I grabbed the wrong column" or some such after ten minutes of playing around with it. The thing that makes Excel convenient but dangerous is the lack of guardrails.⬐ tradertefhttps://www.cassotis.com/insights/88-of-the-excel-spreadshee..."Considering the complexity of Excel spreadsheets and the lack of mastery by many users, we have come to the title of this post: more than 80% of the Excel spreadsheets have errors. Ray Panko, a University of Hawaii professor, discovered that, on average, 88% of the Excel spreadsheets have 1% or more errors in their formulas."
There was some research on Excel's impact on finance with so many errors, but I couldn't find the citation.
⬐ kbelder⬐ chasilWhat percentage of in-house crud apps have errors?⬐ tradertefNo idea, but probably much less than 88%.SQL constraints can prevent erroneous data. Excel does not guide a user to constraining their data to improve its accuracy.https://www.forbes.com/sites/salesforce/2014/09/13/sorry-spr...
⬐ datenhorstOne of the most infamous excel errors even helped shaping global economic policy: https://www.uschamberfoundation.org/blog/post/did-excel-erro...⬐ conductrI know there are better ways but this can/should be seen as part of the learning curve with Excel. The guardrail was seeing "surprising results" they should have known to double check their steps, see if they could recreate it, etc. before showing it to you. Not sure how you handle this but some times it's a hard lesson to learn. Eg. I've seen people convince a public company executives to do strategy X based on analysis Y. Then when things aren't going as promised I get asked to review the strategy, I might find some elaborate model where somewhere deep in the layers of math/logic/lookups, someone divided instead of multiplied or something like that. They then have egg on their face, the strategy may have been implemented already, and I usually have the uncomfortable job of explaining to management why the projections were off and the strategy is probably doomed in terms of reaching the expected outcomes (basically throwing somebody else under the bus due to an innocent math error). I see this so much in my job, it's actually routine for me or my team to review any numbers the company management will see before they see it because of things like this.⬐ bachmeierWhen I was in grad school, I was doing some legal consulting on the side. A big part of one of the projects (a high nine-figure case) involved a bunch of Excel work. My boss wanted me at the trial with a laptop, Excel, and all the data just in case the other side revealed any errors in my results. He told me about other cases that were lost due to minor errors that, upon further review, had no meaningful impact on the analysis.I did not believe Excel was the right tool for the job. As you might expect, I had high levels of stress until they announced a settlement right before the trial was set to begin. I later realized that I should have done everything elsewhere to confirm that my Excel results were correct.
⬐ conductr⬐ goosedragonsYou should have also hired an audit/accounting firm to vouch for the math. In some situations you can actually get them to be liable for the results being accurate.It's a lot harder to check your work in a spreadsheet vs. some sort of scripting language like R/Python etc. By default what you did is hidden, and it's easy to grab the wrong row/column and not notice.For a while Excel was messing up basic statistics functions too. Wouldn't be surprised if there was something else not quite right in there.
⬐ WastingMyTime89⬐ LawTalkingGuyExcel forces you to break down and structure your tables in a meaningful way. I find it much easier to spot errors in than a script personally.I think how confident you feel with excel will depend of what you do with it and how you were taught to use it. Years of consulting basically drilled into me that all my tables should be built as if they were going to be delivered to a client who will need to understand them at some point. If you lack structure however, it quickly devolves into chaos.
⬐ conductrI’d say having a test suite available is the bigger advantage of something like python. Most of the big mistakes people make in Excel are logic mistakes and they could happen anywhere. It’s just plain old human error. But if you can run a test and it fails, then at least you know to go looking for the bug.Either way you’re likely trusting that the underlying code is error free. But most people aren’t checking their python imports. They may or may not be aware of the problems floats can introduce. So on.
How do you use a spreadsheet in both an intuitive and a powerful manner at once?For instance, you intuitively start with just a column of numbers, and then when you forget what they are you move the column down one and put a title above it, etc. Working this way I often end up with formulas that are off by one or two cells even though things should be adjusted, and it's hard to audit without mousing over the formulas and just looking.
If I'm trying to write a tool for others to use I'll do something more formal like putting the data on its own sheets and naming them. That helps a bit, but means that all my data is hidden on different sheets and I can't get a holistic view of the problem. And to be consistent in my formulas I'm tempted to use this model even for data that doesn't need it (not an array, for instance). And it still doesn't help for array-bounds type problems.
⬐ conductr⬐ TeMPOraLMy short answer is to build up your intuition. This is not a static value. It can be improved.I think everyone has slightly different workflows and this has no real solid answer. There’s always some trade offs. Just working in excel a lot helps, if you’re working on varying levels of complexity. I do it as a job and often helping others so I have a lot of exposure to different problems and input data types and even the desired outputs.
The intuitive example you mentioned never happens to me for example because of 2 things; 1) I intuitively leave space to add a header, it’s such a common thing to do, my intuition knows to account for it up front 2) if for some reason I ignored #1, I know how to move things without breaking the formula references and when to employ different approaches to that problem. Things like how copy and cut differ, Inserting a row above row 1, etc. One of my little hacks is avoiding referencing a range like A1:C5 and instead will make it A:C if it’s just a basic table of data. Your file may have a ton of references to this table of data once it’s built out and adding a row of data then requires some manual housekeeping which introduces an opportunity for a bug to occur. With my approach, I can add or subtract data or rows and non of the range references need to change. (Someone May point out that you could expand the table range for 6 rows in a way that the other references would expand, it’s true but I don’t design my files for that, because adding a row may have break something else to the right of column C).
A lot of the way i use and setup the data within a spreadsheet is in anticipation or avoidance of future issues. This only comes with experience. Which is kind of my answer to your more complex example. When developing for others, you’re usually trying to hide the data and complexity of things and expose only the useful bits for that end user. It’s not perfect and is very annoying that I have to hide and unhide things constantly to inspect the functionality. But that’s just the way it is. I’ve done some things like written little macros that hide all of the background stuff if a keyword is in the file name (imagine having a dev and prod version of the file, if prod is in the file name when saving all the cleanup and hiding of things happen. That’s what I distribute. The dev version is the same and where I work. When I save that file prod is not in the file name so everything in that macro doesn’t execute.)
> Eg. I've seen people convince a public company executives to do strategy X based on analysis Y. Then when things aren't going as promised I get asked to review the strategy, I might find some elaborate model where somewhere deep in the layers of math/logic/lookups, someone divided instead of multiplied or something like that.Is it Excel-specific though? I've made these kinds of errors with Excel (though not with stakes this high). I've also made them a lot when doing math on paper. And I've also made them in C++, Common Lisp, Matlab, Python, R and JavaScript. Now, with those other tools, it's easier to spot an error in a formula on review - but in Excel, it's easier to spot the intermediary results being off, so it's a wash.
I think the thing to learn is to be more careful, to sanity-check intermediary calculations; these kinds of errors are about being momentarily confused, and will happen regardless of the tool you use.
⬐ greyhair> sanity-check intermediary calculationsI am very far from being an Excel guru, very far, but one thing I have found useful is having everything come out to an intermediate result, and calculate against those intermediate results. If you see weird numbers in the middle, then end result is likely wrong, figure out why your intermediate results are messed up. If you want to be fancy, you can even error bound your intermediates if you know your valid ranges.
⬐ conductrYeah I agree with your conclusion and excel just happens to be the tool a majority of people are using so it’s a poster child of sorts. It also gives people “false” confidence in their ability at times when really they should probably ask for help. Using the reality of my team as an example again, a lot of people will just stay out of excel and ask us to build a model up front for a new strategy. My team isn’t flawless but much more experienced in the quirks and mental model of how to setup data for formulas, scenarios, etc. We also check each other’s work before something high stakes goes out of the team. Since we’re the experts it hurts us that much more when some miscalculation gets out and causes disruption.
Fantastic 54 minutes video by a fantastic guy to get started with Excel: https://www.youtube.com/watch?v=0nbkaYsR94c
Sheets has had almost no updates for the last 10 years after getting to its current level of viability. I'm reminded of this 7 year old talk (You Suck at Excel with Joel Spolsky) where he goes over a bunch of great Excel features that still aren't in Google Sheets today.
⬐ thehappypmIt’s hard to add features to something like Sheets or Excel. Large and finicky users are hard to please⬐ mcbishop⬐ londons_exploreAnd yet, somehow, some amazing features have been added to Excel recently. Dynamic array formulas and lambda functions have taken my Excel use to the next level. Let alone new standard functions (e.g. sort, filter, misc. text-manipulation functions).⬐ thehappypmDo they have QUERY like Sheets yet?It's the typical way with Google products...When the original dev team moves on to other projects, the new guys just make minor tweaks and no major product changes happen. Big new feature releases do things like "we moved this tool from the edit menu to the file menu".
Speaking of excel, for casual Excel users, the video "You Suck at Excel with Joel Spolsky" [1] is worth a watch. My casual Excel use probably got an order of magnitude easier from it.
⬐ nerdponxI love this. Is there something like this for Word? And/or for the LibreOffice equivalents, which in many cases are a better user experience than the MS originals.⬐ duncansProbably Scott Hanselman’s ‘How to REALLY use Microsoft Word’⬐ dan-robertsonExcel is, in some sense, a programming language for (mostly) purely functional incremental computations with built in debugger that shows you most intermediate values. Word is not very much like that so the contents of such a thing would surely be different. I think the first tip is probably something like ‘create named styles instead of mutating the formatting of regions ad-hoc’.⬐ bombcarAnd the “copy formatting” tool and you’re basically done.
If you're really a novice, watch Joel Spolsky's talk. I go back to it whenever I end up in Excel for something.
This was inspired by a hn comment that linked to "you suck at excel" [0].First, I realized how much I suck at excel despite being in love with it. Excel just has so many features and the basics are good enough to solve most excel problems. This means I never search for a better method and only discover features through word of mouth.
Then, I realized many of the suggestions could be automatically applied (named cells, standardized color formatting, defined tables). Other suggestions could just be recommended (you seem to be using formulas to build a pivot table, click here to learn about pivot tables).
This seems to obvious so it probably exists. But I only found Microsoft's excellent excel static analysis addin ExcelLint.
Always reminds me of this video.. https://www.youtube.com/watch?v=0nbkaYsR94c Excel has proper tables, named columns, better structured data etc.
Obligatory Joel Spolsky Excel video: https://www.youtube.com/watch?v=0nbkaYsR94c
Relevant: https://www.youtube.com/watch?v=0nbkaYsR94c
>It seems like your argument is that alternatives don't have PowerQuery. That might be true (I don't even know what it is), but isn't that like saying Linux can't compete with Windows, because it doesn't have Internet Explorer? I mean, it doesn't, but there are excellent alternatives that can accomplish exactly the same task.It's true that Google sheets and LibreOffice don't have Powerquery, and that's a big pain. But the worse thing is that they don't have tables. As in, the "format as table" button in Excel. As in, the bread and butter of anyone who gets serious work done in Excel.
Maybe it's a problem of naming -- "format" makes people think it's just about aesthetics, but actually it imparts real semantic structure onto a rectangular grid of data. It also isn't the same thing as pivot tables, with which they are often confused. It gives the grid a name that you can refer to in formulae, and the columns are named too, with their names living inside the table namespace ("structured references" is what Microsoft calls it). The table automatically expands its boundaries when you start typing a column header to the right of the current columns, and likewise it expands to comprise the row beneath it if you type values into that row. And it has smart indexing: there's special syntax to refer to "this table" and "this row" in formulae.
So you can have say, a table named "ExpensesTable", labelled "Date", "Type of Expense" and "Amount" in columns A:C. Then you can type "Tax" at the top of column D, it will expand the table to include a new blank column for Tax. Then in D2, type
and it will automatically fill down the Tax column with 20% of the value of the Amount columns. Then in a cell outside the table, do=[@[Amount]] * 0.2to get the total amount of expenses. These are both simple examples; you can do more complex and interesting things involving multiple columns, ranges of columns, joins, etc. The point is the semantic structure that makes your spreadsheet more than just a rectangular soup of cells, so you don't have to claw through endless cryptic "G70:$K100" cell references. If we add a new row or column, we don't have to alter any formulae at all; the bounds are automatically resized on the cell arrays that the column names refer to. Think of it like a mutable resizable dataframe. It's the core data structure of an efficient, scalable, maintainable Excel document.=sum(ExpensesTable[Amount])More about structured references: https://support.microsoft.com/en-us/office/using-structured-...
Also the "You Suck At Excel" talk by Joel Spolsky: https://www.youtube.com/watch?v=0nbkaYsR94c
And no, I have no idea why the eggheads at Google don't implement this for Sheets. Maybe Microsoft has a patent on it? Wouldn't surprise me. But this is why you'll have to pry Excel out of spreadsheet jockeys' cold dead hands -- the alternatives don't have this basic thing.
⬐ tavisoYou need to come up with a task that cannot be accomplished, remember you said "no you fucking well cannot [do everything]", but so far I've seen no examples.I mean, isn't this just a button that adds some named ranges for you?
You can replicate the exact example you gave with named ranges. If there is something it can do that named ranges can't, then please use that example instead. Similarly, if you think there is something that "Power Query" can do that SQL cannot, then please show that.
I literally use Lotus 1-2-3 for UNIX (I'm not kidding! http://123r3.net).
So far, all of the examples I've seen you give could have been done in 1989 on a VT100 terminal connected to SystemV. You could even write a quick macro in that generates the named ranges from column headers with one keystroke, it would be really trivial.
⬐ tavisoHuh, I looked into it - Lotus 1-2-3 does have this exact feature, it's called /Range Name Labels.Isn't this the exact feature you're describing? ...in 1989..? ...before excel even existed? :)Use /Range Name Labels to assign an existing label as the range name for the cell immediately above, below, to the right, or to the left of the label. See /Range Name Create for rules on characters in range names.⬐ _dain_>I mean, isn't this just a button that adds some named ranges for you?No, named ranges don't automatically expand when you add new rows, and they aren't automatically created when you add new columns. And they don't remain in groups, e.g. you can't make a reference like Namedrange1:Namedrange3, but in a table you can do Column1:Column3. Named ranges exist in a global namespace; column references exist in a per-table namespace. The table syntax makes columnwise operations clearer to express in formulae. Let's say you want to refer to the cell in the same row as the current cell, but in a different column: how do you do that if everything is just a named range? You need to do some kind of juggling with indexing and lookups, or else fall back to alphabet soup A1/R1C1 style referencing, because a named range is only good if you want to do an operation on every cell in the range. But that's often not what you want! In tables it's as simple as [@[other column]].
You would know this if you actually read the documentation or watched the video I posted. Or I could just repeat myself again (maybe I will write a macro to automate such tedium).
>Similarly, if you think there is something that "Power Query" can do that SQL cannot, then please show that.
Grab data from a csv file, a JSON file, a SQL database, and an Excel sheet, and combine them all together using a normie-friendly GUI.
Your question doesn't even make sense, it's like a type error. SQL and PowerQuery are not competing technologies, they're complementary.
>You could even write a quick macro in that generates the named ranges from column headers with one keystroke, it would be really trivial.
Yeah and you can also make Dropbox by getting an FTP account, mounting it locally with curlftpfs, and then using SVN or CVS on the mounted filesystem.
Spreadsheets are the only remaining programming system that people not inducted into the Programming Cult use.
⬐ tavisoI'm happy that you like this syntax, but the claim you made was "no you fucking well cannot [do everything]", not "excel syntax is more fucking beautiful".I appreciate your advice, but I don't need to watch a video on R1C1 syntax, I literally maintain a spreadsheet :)
It seems like your real claim is that you really like the way Excel does it, nobody can argue with that.
⬐ snthpyI have to agree with the OP (_dain_) here. Excel has evolved a lot in the last few years, first the whole Power Query and Power Pivot revolution and now all the functional stuff brought on by Simon Peyton-Jones and his crew like LET expressions and the functional constructs like LAMBDA, MAP, FILTER, ...There's very little you can't do neatly and efficiently in Excel anymore. Yes you can in principle do those same things in Google Shets, but at what cost of readability?
I don't think it's worth spending much time getting into these arguments because the people arguing against Excel clearly don't know modern Excel very well.
⬐ taviso> I don't think it's worth spending much time getting into these arguments because the people arguing against Excel clearly don't know modern Excel very well.That's not it at all. Excel has been in active development for over 30 years by a multi-trillion dollar development powerhouse with billions of sales, everybody is aware it's a perfectly competent product.
The dispute is the objective claim that it can do something that alternatives cannot, not the subjective claim that Excel is "neater", or more beautiful, or more user friendly. After 30 years of active development I would hope that Excel has some shortcuts, polish and syntax improvements to streamline common operations. That is not the same as not being able to do something.
I question the claim that it can do something unique, and want to hear an example. When pushed for an example I'm told that only Excel has a Solver, or only Excel has Pivot Tables. That is objectively false.
I don't want to hear about "Power Query" unless it's an example query that cannot be done in SQL. It's a proprietary query language, of course alternatives don't have it. I'm glad you're happy about it, but others might call that "Vendor Lock-in".
I was going to reply with this too. Joel Spolsky explains it well https://youtu.be/0nbkaYsR94c?t=1824
Semi-related, but if you haven't seen Joel Spolsky's Excel crash course, it's the best Excel video a I've ever seen: https://youtu.be/0nbkaYsR94c
⬐ cstratI am pretty sure I started watching this a while ago but never watched it through.His index/match formula usage is great, I always hated how vlookup is so fragile and confusing... learning something new from something so old.
Thanks for reposting!
⬐ eisa01⬐ quartzAnd now we have XLOOKUP to replace index match :)https://support.microsoft.com/en-us/office/xlookup-function-...
His dock in this video is a trip down memory lane: Sublime Text, Textwrangler, Skype, Pixelmator... takes me back.Also I didn't watch the whole video but if he made it through 54 minutes of excel for mac without it randomly crashing, that alone would be an impressive feat.
⬐ recursiveIs Sublime old now? Oh well, I still like it.⬐ cstratIt does almost crash at one point and he says he might have to restart LOL
"You Suck at Excel with Joel Spolsky": https://www.youtube.com/watch?v=0nbkaYsR94c
Not quite what you want but try this anyway. "You suck at Excel" by Joel Spolsky
Data tables:https://youtu.be/0nbkaYsR94c?t=2268
Although no validation/types
⬐ dragonwriterExcel has, IIRC, had data validation longer than it has had tables (even if you count the time before Excel 2007 when tables were called lists),and its had both (with the same inclusion) for >20 years.
⬐ 4rtthis must be about the 5th time i've watched this over the years. i still keep forgetting what's possible with it.⬐ Number157You suck at titles.⬐ egbertsIsn’t the title better reworded as “You suck at Excel, with Joel ...”
Maybe you saw it in his video called "You suck at Excel" [1]? It is in any case worth the watch.
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.
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”
This Twitter thread is a good start: https://mobile.twitter.com/keith_ng/status/13079610874515251...There also Joel Spolsky video I linked in another comment: https://youtu.be/0nbkaYsR94c
I don't actually use Excel much so others might have better resources.
⬐ mehrdadnOh I've already seen the Spolsky video, but I seem to recall for the most part (except for using tables, which is great) it's about how to use Excel effectively, not about how to produce maintainable sheets. The Twitter thread looks good though. Thanks!
I remember hearing about a mythical spreadsheet floating around for modelling something to do with our national grid a few years back.It would take about 12 hours to calculate, and would error out before finishing about 30% of the time. It needed to be run once a day for something reasonably important.
I don't use Excel much these days, but I do point people to a video if they do plan on doing anything:
* [You suck at excel - Joel Spolsky](https://m.youtube.com/watch?v=0nbkaYsR94c)
And IFERROR() wasn't added until Excel 2007. The other way to make that operation less unwieldy if the expression was complicated, to avoid repeating it I'd put that inside another cell, and that way it would be =IF(ISERROR(C2),0,C2), but IFERROR is a much better solution.Excel is such a good tool in many ways, and such a bad tool in many ways. Really experienced power users can follow Excel formulas much easier than blocks of imperative code. But the more complicated it gets, the harder it can be to follow it all. But that also goes the same for software, too. I do think that there is something powerful about the "debugging" you always have turned on in Excel, in that you always know what value a formula has produced, even after it has run. And you can (usually) easily see at what point an error started in your calculations.
For the programmers out there who aren't fans of Excel, or aren't super familiar with it, if you haven't seen "You Suck at Excel with Joel Spolsky" [0] you might be pretty amazed at what you can do with Excel at an intermediate/advanced level.
⬐ rahimnathwani"to avoid repeating it I'd put that inside another cell"100%! I like to do that in other cases as well, just to keep formulae simple enough that someone else can easily audit the whole spreadsheet.
I'd rather have 5 extra columns in a calculation, then have a huge formula in a single column. This habit is so strong that I often do the same thing with Pandas: adding extra columns to a dataframe for intermediate calculations, when it would be better to write a larger function and .apply() it all at once.
⬐ will_pseudonymYep. Exactly the same reason we break up procedures and functions into smaller units in procedural code, to be able to reason about individual pieces, and understand each step.I've never used Pandas, but I imagine I'd have the exact same instincts to break calculations up as you do, since Excel was my "first programming language" that I was first exposed to in the 4th grade, haha. I certainly didn't learn much advanced stuff at that point, mostly because of the time period and being in a rural Midwest area there weren't a lot of programmers around to learn from and the internet was rather different in the mid 90's. :) But Excel planted the seed of programming in my mind, even though I didn't know what "programming" was.
Excel suffers from being accessible to "everyone", and having a low barrier of entry.While I'm not an auditor, I've been the go-to Excel person at all my internships and in full time roles. I've reviewed, enhanced, and fixed hundreds for tons of different functions (HR, supply chain, manufacturing production reporting). You can build a respectable system in Excel which has built in error flags that make it obvious to the end user. Here are some of my tips for people who deal with spreadsheets:
* Cell styles, much like headings in Word, are really great but nobody uses them. They clue end users about what cells do. If my end users see an orange cell, they know it's an input they can modify.
* Lock sheets, even without a password. 90% of the time will prevent people from breaking the spreadsheet in the first case.
* Stay up on new functions! =Unique, filter, sort, Switch, textjoin, and {sum|average|min|max}ifs have been added in the last ~18 months and can greatly clean up messy formulas.
* Document stuff like code should be documented. The Explanatory Text style is excellent for more in depth explanations but not visually clogging things up.
* Use tables. Name the tables. =Average(tbl_Production[Widgets Produced]) is self documenting, and the range expands with the table. Range references are notoriously obtuse.
--
The European Spreadsheet Risks Interest Group has some great information on best practices.
http://www.eusprig.org/best-practice.htm
Joel Spolsky's "You Suck at Excel" is also eye opening for most.
⬐ no_wizardI am just completely amazed but yet not surprised there is an entire organization dedicated to spreadsheet risks and best practices. What a world we live in. This just goes to show what the killer app is for computers, it seems.We have these wonderful machines and 80% of people use them for browsing the web/using web apps and producing spreadsheets. Thats the reality I tend to forget sometimes.
⬐ wintorez⬐ foucSpreadsheet and word processor were THE killer apps of microcomputers. As long as humanity uses letters and numbers, we will have spreadsheet and word processor in some shape and form.>* Cell styles, much like headings in Word, are really great but nobody uses them. They clue end users about what cells do. If my end users see an orange cell, they know it's an input they can modify.It would be interesting if excel had some sort of internal markup language perhaps. So those orange cells could be semantically represented as <input> or some such.
⬐ dwaltrip> * Lock sheets, even without a password. 90% of the time will prevent people from breaking the spreadsheet in the first case.Is there any way to lock all cells in sheet except for certain input cells? That seems like it would prevent many errors.
⬐ gegtik⬐ razakelyes. Go to cell properties and you will find there is a "locked cell" property that is on by default.When you Protect a spreadsheet, all Locked Cells become... locked.
So a typical use case is to only un-lock the cells you wish people to enter variables into, (color the cell to make it clear), and then protect the sheet so nothing else can be tampered with.
⬐ nhebbI'm reluctant to pimp my own product on HN, but one of the features of Spreadspeed [1] (my Excel add-in) is a Quick Protect tool which protects the sheet but will unlock individual cells based on styling (e.g., Input style).⬐ jen729wThe problem is that someone creates a sheet, locks all the cells, forces a bunch of people to use it via some process, and then either a) never touches it again or b) leaves the organisation and doesn’t tell anyone the unlock password.Example: a lookup list for “hardware models” which is protected. So you can’t add a new model, but some cretin forces you to keep using the sheet.
With great power comes great responsibility.
>Excel suffers from being accessible to "everyone", and having a low barrier of entry.I call this "baby with a nailgun syndrome". You end up with a bloody mess.
⬐ airstrikeLove it. I'm in Excel ~50+ hours / week and will definitely start using this sentence⬐ jen729wFifty hours a week? You might need to think about working less.⬐ qayxc...or becoming more efficient - who knows?⬐ airstrike⬐ m463In Excel? Impossible.He could be balancing his player stats.⬐ airstrikeI work this much out of my own volition and I am happy to do so
Many comments already talked at length on use-cases for Excel, so I won't go for a 'me too'.Instead, I just want to share this video from Joel Spolsky, aptly titled "You Suck at Excel".
Turns out, most people do suck at Excel. Myself included.
One of the best Excel tips video I’ve seen is by Joel Spolsky - aptly named “You suck at Excel”. I’ve been an Excel user forever and this video taught me about features that I never knew existed.Have a look: https://youtu.be/0nbkaYsR94c
⬐ WistarAgreed. I learned, and re-learned a lot about Excel from that video.⬐ mattalbieThat video is required viewing for my team.⬐ s1monCame here to post essentially the same thing. Considering how much of most people's Excel usage (in my work experience) is basically making very simple tables/databases, the stuff in here should be required.
No one knows how to use excel.
The best startup ideas are simply taking a piece of the massive office ecosystem and marketing it better than Microsoft.Joel jokes about it here.
⬐ tosers4yes, and they they are acquired by microsoft (or other giant) or microsoft simply makes a clone, just like what's happening here, or with teams, or todos or any other recent MS app. MSFT is just too big. Trillion dollar companies are a sin on this earth
I had the same thought -- it seems like many of these ideas overlap heavily with R1C1 mode, which is already how Excel internally manages formula copy-paste.Joel Spolsky explaining how it works: https://youtu.be/0nbkaYsR94c?t=567
You can do basically anything in Excel. My favorite video on this topic is from Joel Spolsky.You Suck at Excel with Joel Spolsky: https://www.youtube.com/watch?v=0nbkaYsR94c
Joel Spolsky has some examples in his talk about excel: (https://www.youtube.com/watch?v=0nbkaYsR94c).I particularly wish Google sheets had the tables feature.
This goes back to my old quip of the easiest way to increase GDP to 5% would be to force everyone to learn how to actually use Excel. HT "You Suck at Excel" - JS https://www.youtube.com/watch?v=0nbkaYsR94c
⬐ btownI can imagine though, that there's a real chance that the "fear" of people having their jobs automated would cause them to cut back on spending, perhaps causing an immediate recession!⬐ gkfasdfasdfPerhaps it should become a part of public middle school curriculum?⬐ tyri_kai_psomi⬐ wpietriIsn't it? In middle school they taught us typing, word processing, access and excel, and we had an election to take something else, so I took video editing.⬐ ClikeXExcel class was actually party of my junior high/high school (Netherlands). As well some programming in VB and PHP.⬐ Wistar... along with how to write a good search query⬐ nothrabannosirFunny you should mention this; we learned this in high school, 2 decades ago now. Quotes and OR and all. The task was to find some info on the teacher. It was in "computer science" class. Good fun.I got an F.
⬐ mattnewtonFunny enough, I had a class on this in grade school, taught by the school librarian. It included quoting, AND, NOT and OR style stuff you could use in library search systems and it is pretty much all irrelevant now, but it was cool at the time and I wonder if it helped to be exposed to that kind of boolean logic early.⬐ WistarI was thinking more about the thought process for how to come up with the right words to begin with and for refinements to make to yield better results rather than the boolean operators although I think they are good for the young minds, too.⬐ beamatronicSame, this was common in the late 70s and early 80s. Sure, you were on a mainframe terminal or a PC with a cd-rom, but the search principles are the same.Agreed! But the trouble for me with Excel is that it has an incredibly low ceiling. There are so many people out there doing what is essentially app development but with terrible tools. Tools that give them the wrong habits and mental models for working at scale.I would love to see the spreadsheet reinvented to be a) collaboration-oriented, and b) a good on-ramp to ever-growing programming skills. So that when Bob in the next department over makes that crucial internal spreadsheet, you can safely and usefully interact with it from your crucial internal spreadsheet. Basically, to make every spreadsheet a potential microservice.
I've spent some years failing to figure out how to make that work. So if anybody manages, please let me know.
⬐ monch1962If you haven't used it, Google Sheets is worth looking at closely. It's good for collaboration (within the boundaries of a "normal" spreadsheet interface), and has a lot of options for integrating with external systems⬐ reportgunner⬐ rorykoehlerFor me, Google Sheets don't have the signature features of Excel, it's like Excel lite.No VBA, no keyboard shortcuts, (no cross-workbook references).
Imho two people working inside the same worksheet at the same time was never an Excel's missing feature.
⬐ gkfasdfasdf⬐ wpietri> No VBAFWIW Google Sheets does have the ability to write macros in JavaScript. They even have an npm tool [0] that will let you edit offline via your preferred IDE and then push to your sheet.
⬐ reportgunnerI'm not saying JS is better or VBA, but JS is definitely not VBA.⬐ twojacobtwoGsuite uses 'Apps Script'[1], not pure JS, so it is quite similar in function to VBA.I have used it, and it's definitely a step in the right direction. But it lacks a lot for me, in that the meaning of the system somebody builds is implicit and contingent.E.g., in an OO language, I can be very clear about what a MailingAddress is. I can say what makes it well-formed. I can give rules about how it can be changed, and what happens when it does change.
But in a spreadsheet, that concept is mostly implicit. A human can look at it and say, "Oh, the mailing address is columns C-G, starting at row 2 and down to just before the first blank line." Until somebody edits the spreadsheet and inserts a phone number column, anyhow.
That means a Google Sheet can't really serve as a reliable microservice that knows about all the customer addresses, letting one's colleague's sheets read and update things. Sheet authors have all the necessary domain knowledge, but not enough of the technical knowledge, so at some point they have to bring in a professional programmer to re-express the domain knowledge in a language inaccessible to the people who formerly had control over their data and business processes.
Have you tried AirTable?⬐ LramseyerI have thought about this myself too, so I'll offer my two cents: Spreadsheets are really versatile, but they're a victim of their own versatility. You need to build something that is similar enough that people understand the concepts, but different enough so that people don't get frustrated by the fact that it's not Excel/Google Docs.I always envisioned something that functioned like a Jupyter notebook, and pulled some pages from the MatLab debugger tool ...kind of like how Light Table implemented them, but don't get too caught up in that analogy. That way, you could create and define tables that are stand alone objects, and run [code] operations on data in those objects to create new tables. Everything that is processed from the tables is it's own [new object] entity, and can be [output or not] viewed or hidden. It would move away from the idea of having few tabs of large continuous spreadsheets in favor of having many small tables [matrices] that would have identifiers [variable names]. And if the user really wanted, they could drag around tables on a spreadsheet like grid, but the data in those tables would not be editable in that view. Oh, and graphs are their own entity too. Users can use a GUI editor to make graphs, but the graph properties will be user accessible in some sort of markup language for the edge case where the user needs to make a ton of graphs, and then needs to make some styling change to every graph. Not something I've ever experienced though... \s
This will:
1. Reduce the need for Excel's reference gymnastics that are performed when you move or insert rows/columns in to referenced data.
2. reduce the headache that is excel formulas. Since each object will be a formula instead of every cell. Also, the formulas don't have to be a single line, so it will be more readable.
The trick is finding a good language to use - Julia maybe? and designing a good GUI for helping non programmers do useful things like accessing and referencing data from objects in an intuitive way. This could be where the traditional spreadsheet view could come in handy. I like how you mentioned that it needs to serve as a good on ramp for ever growing programming skills, because it needs to be intuitive enough that non programmers can understand the basics and accomplish something useful, but powerful and hackable enough that the mental models can transfer.
⬐ wpietriWe're definitely thinking along the same lines. I agree entirely about many small spreadsheets and the like. Another inspiration for me here is the long-lost Lotus Improv, which instead of being a blank page was more like a table, with explicit, named rows and columns. For me a general principle is to look at what people mostly do with spreadsheets and make explicit support for that.You're very right that choosing the right language is important. As much as I love the languages I know, most of them are incredibly fiddly. E.g., things like dealing with "if a=b" are fine for those of us steeped in the mysteries, but a nightmare for more casual users. I'll have to check Julia out. Thanks for mentioning it!
⬐ PamarThere was also something called "Framework" that went a bit farther than spreadsheets. Of course when I write Spreadsheet here I mean "Spreadsheet products available in the 80s": https://en.wikipedia.org/wiki/Framework_(office_suite)
You Suck at Excel with Joel Spolsky - https://www.youtube.com/watch?v=0nbkaYsR94c
⬐ vermilinguaDid anyone archive the Martin Shkreli video where he shows his Excel workflow? Think what you will of the guy, but that was one of the most info-dense videos I've seen, and it's a shame that YT saw fit to remove it.⬐ SPACE_LAWYER⬐ phnhttps://drive.google.com/drive/folders/0B--gYoJ7FqOBamhYNEpn...⬐ visualphoenixThis one? https://m.youtube.com/watch?v=3nfcCQ0Vyl8⬐ vermilingua⬐ 1337bizNo, he had a ~90min video that was purely overviewing how he uses excel. Wasn't really about finance anymore, and isn't available on youtube.⬐ lozfThere's one from him that might be adequate in my public folder on keybase.⬐ slowhand09Do we have to install keybase to see it? I'm not familiar with it, so hesitant to install till i have some time...⬐ AdamGibbins⬐ AdamGibbinshttps://keybase.pub/lozf/Finance%20Lesson%2011%20by%20Martin...Doesn't look to be the one, its analysing eBay in Excel, he navigates Excel well but its in no way a tutorial, there's no description to how he's doing anything. Nor is it particularly advanced.His tutorials were amongst the best I have seen on financial analysis. Problematic character but genius level skills and talents that he was willing to share.⬐ getlawgdonWhy do you say this? I'm close to people in finance and they derided the videos and said they were the equivalent of showing off how fast he could parse filings into Excel when real pro have scads of solutions that automate it at scale. The view is that he's a criminal with a folk hero reputation for people who were dazzled by his attitude and his facility with Excel.⬐ B1FF_PSUVM> I'm close to people in finance and they derided the videosLiving among the gentry with good private performers is fine, but the peasants have to amuse themselves with the traveling minstrels in the fair ... their technique may not be the best.
The video is amazing if you're a coder and might need to use excel at some point (access columns by name?! sign me up!).However, what I love about excel is to see what it enables people without formal programming training to do with data, and using functional programming on top of that!
Is there any tool that comes even close in terms of power, accessibility and ubiquity? What would such a tool even look like?
⬐ rch⬐ TAForObvReasonsFor larger datasets check out IGOR Pro.⬐ perl4everAfter about 10 months of learning to script Excel, I don't think functional programming is advanced Excel.A deep understanding of the object model for pivot tables is advanced excel.
Or writing a library to use Windows authentication and REST to interface with SharePoint to build your reports.
⬐ EnginerrrdI'd love to see excel running python under the hood with easy integrations to numpy and matplotlib and all that. There actually are a couple such projects I've been meaning to check out.In essence, I've spent a lot of my career modeling things in excel. For quick, easily shareable prototypes(to pseudo-technical people), excel is great. But add any real complexity and I rapidly find myself using some UGLY hacks to get the functionality that would be trivial if I could just iterate over the data more explicitly. If I could just click a cell and set the output equal to a python function return value, man would that be a tool. THE tool even. I don't really want to learn VBA or something clunky and not very useful outside of that niche context. I already keep too many programming languages in my head between python, c++, FORTRAN, and some bash scripts.
⬐ abakkerCheck out XLwings. It can do some pretty handy stuff.⬐ EnginerrrdThat is one of the projects I've been meaning to check out!Submitted many times, the most active discussion was in 2016 https://news.ycombinator.com/item?id=12448545⬐ eindiran⬐ jrumbutIn the 2016 discussion, the link to the Martin Shkreli video is down. These are some of the videos in that series where you can see him use Excel:⬐ giarcThe fact that there is a world champion Excel user tells me I suck at excel.https://www.youtube.com/watch?v=SysRS3lZm98&feature=emb_logo
I wish the Excel developers would bring some of the UI in line with other apps.The #1 thing here is the lack of smooth scrolling, scrolling by row actually gives me motion sickness whenever I use Excel (and that's not a problem I have with anything else I use).
You can find a legion of people begging for at least a setting to end snapping to rows here: https://excel.uservoice.com/forums/304921-excel-for-windows-...
If anyone from MS is reading this, this seems like such a tiny change but it would really make a lot of people happy. In the meantime, could anyone recommend a smooth scrolling spreadsheet program? I haven't used the alternatives too much.
⬐ magnetic⬐ gizmodo59Numbers (on Mac) has smooth scrolling.⬐ mattl⬐ clausokNumbers is a weird Quantrix/Improv inspired spreadsheet. Are people using it like Excel?⬐ AznHisokaI use it to view spreadsheets offline in my Mac, but use no other fancy features.Maybe not the same thing, but Excel's row scrolling with the arrow keys was driving me nuts until I discovered this setting: Options -> Advanced -> Display -> 'Disable hardware graphics acceleration'. After I checked that box to disable it, scrolling was much better.⬐ sixothreeI hate how you can't undo in a single document. You can only undo across all of your open documents.I hate how Excel breaks the concept of Alt-Tab.
I hate how Excel breaks your clipboard on any action.
⬐ DonHopkinsIt would be nice if the mouse wheel clutch (i.e. Logitech Master 3) controlled that, so you could feel each row snap by individually when you wanted to, or toggle the clutch button or spin the wheel fast to scroll smoothly.⬐ j88439h84Nice idea. I wonder what would be involved in making that happen.A must read: https://www.joelonsoftware.com/2006/06/16/my-first-billg-rev...⬐ emjThat video really helped me to understand the horror that is Excel, but why fight it instead create a linter for Excel: https://github.com/plasma-umass/ExceLint-addin https://www.youtube.com/watch?v=rEwUA0h2dsw It is telling how complicated that linter is.⬐ vonnikJoel's blog about software is also amazing for those unfamiliar with it. Still worth reading posts that are more than a decade old.https://www.joelonsoftware.com/category/reading-lists/top-10...
⬐ clausokThe worst Excel user I've ever seen was my brother. At film school he was producing a student film. He did the budget in Excel. One day I came home from work and he had some questions waiting for me. Looking through his sheet, I noticed that his summation row at the bottom had hard-coded values rather than the =SUM(...) formulas I was expecting. Thinking he had inadvertently done a 'copy -> paste values', I said, "Look, you killed your formulas here." He asked, "What are formulas?". Turns out, he had been using a calculator to tally up all the columns and was typing the total into the cell at the bottom.⬐ heavenlyblue⬐ Alt-F4It may not be relates to your brother, but I often find that the smarter people consider themselves to be, the more often they never open the manual for the tools they are using.The title reminds me of a part of The Website is Down #2: Excel Hell (slightly NSFW)⬐ jupedThe fact that the GUI button to make a table is called "Format as Table" and placed next to purely visual formatting options (and makes you pick a color scheme!) has been the roadblock for countless Excel users graduating to power users, I imagine.⬐ notyourgrandmaSuch a classic, and great to give to first-year analysts⬐ TurkishPoptartThis is interesting, but he comes off as a real jerk. "Baby mode", "most of you would quit here because you're a bunch of babies". I guess it would be funnier if you were in the same room, but watching as a YouTube video just makes him seem more annoying.⬐ duxupWhat always amazes me are the Excel wizards I find who show me a thing they want on their website in just a few minutes and I'm thinking:"It's going take me a hell of a lot longer to code that than it took him/her to show me it working in Excel...."
⬐ leeman2016I wish there was one for Access too. I find MS Access less intuitive than Excel.⬐ ExumaThis video is freaking amazing, and I already know a ton about excel.⬐ sonimanHis rude responses to questions aren't as adorable as he thinks.⬐ youeseh⬐ mongolHe isn't trying to be adorable.⬐ soniman⬐ unexaminedlifeHe succeeded.I noticed that too. But then I thought: What if, before he started his presentation, he told the audience to save questions for after the presentation.⬐ rndmizeIt's a parody of another web series called "You Suck at Photoshop". https://www.youtube.com/watch?v=MWn0lxRNqosWould be nice with a list of "best of" youtube videos for various topics. This is probably best in class in the Excel category.⬐ golemotronI wonder how much of this magic works in Numnbers or Google Sheets.⬐ notyourgrandmaSuch as a classic and great to give to first-year analysts⬐ qrbLPHiKpiuxDid you ever see Martin Shkreli on excel on YouTube?⬐ rekabisThis… is supposed to be difficult stuff??⬐ NullabillityPlease, just learn SQL instead of creating more Excel messes...⬐ DonHopkins⬐ andyreedWhy "learn SQL instead" and not "learn both"?SQL is a language. Excel (and Google Sheets) is a tool (that also supports SQL, the language). Why wouldn't you want a sleek Excel-like user interface to SQL data and queries?
https://www.lifewire.com/excel-front-end-to-sql-server-24953...
https://www.benlcollins.com/spreadsheets/google-sheets-query...
And no I don't mean like the steaming piece of shit called MySQLWorkbench (or PHPMyAdmin for that matter).
https://devrant.com/rants/1342177/mysql-workbench-user-frien...
⬐ Nullabillity⬐ ExumaBecause Excel's flexibility encourages bad behaviour that will come back to bite you.Input data should be separate from your calculations, and you should never deal with single cells in your results (fix your formula or input data instead).
Yes, Excel actually has a mode that supports that workflow: the Unified Get & Transform Experience[0] (just rolls off the tongue, right?). And it's basically drag-and-drop Pandas.
But I've only ever seen one person use it (after repeatedly bugging him about how we will need to repeat this analysis and how surely there must be a better way). And even then it's very easy to screw up and introduce bugs whenever the input data is replaced. Wanted to make a graph and only included the populated data region? Referred to the aggregation row somewhere in some stats sheet? Too bad!
Also, Colin's post ends up recommending SQL Workbench/J, which seems to be much closer to MySQL Workbench than Excel.
[0]: https://support.office.com/en-us/article/unified-get-transfo...
Ah yes, the old "please learn this entirely different thing even though it completely doent fit your use case, just because I'm good at it and makes me feel better about myself to make blanket suggestions that validate my skill"⬐ mobjackTry copying a pivot table in SQL.A lot of simple functions in Excel are incredibly complex in SQL. You might technically can pull most off put then you end up with a lot of ugly SQL.
Also each time you make a change with SQL, you have to query the database again which can be slow and inefficient.
I use both tools together, exporting SQL results and opening in Excel for further clean up. That way you can take advantage of the strengths of each one.
⬐ kristianp⬐ fencepostA Pivot table can be replicated using GROUP BY in sql. Not too hard, but not beginner-level sql either. Not just a few clicks either, it takes some code.Please, just learn SQL instead of creating more Excel messes...Absolutely, it's amazing the things you can do in Access!
⬐ slowhand09As a DB engineer/developer/architect/modeler... SQL is great. But Excel is great also. Its awesome to show a usr how to pull what they need from the database to populate a spreadsheet, and vice versa. We have multiple tools for a reason. And the Swiss Army knife is a perfect example of a non-optimal implementation.⬐ dbeleyWhat's the quickest way to query a simple CSV file with SQL ? Interested in any gui or cli tools.⬐ slowhand09⬐ perl4everIn Oracle I 'd just set up an External Table. Trivial to do.⬐ cgarvisnot sql, but xsv is awesome: https://github.com/BurntSushi/xsv⬐ hoistbypetardIf you're accustomed to windows/office you can just set up a CSV as an ODBC data source and use access.Otherwise [csvkit](https://csvkit.readthedocs.io/en/latest/) gives a pretty good command line interface to make sql queries against a CSV or dump the CSV into a database so you can use any standard database tool to query.
SQL is how you create Excel messes, at least in my world.⬐ enitihasExcel allows you to do interactive analysis though, which SQL simply comes no where near close. For example, after creating an Excel table, you add a filter, see the changed data. You can Ctrl-Z for undo. Add another filter and see changed data instantaneously, undo/redo, sort on many columns, create derived columns and sort on those, and all of this interactively.Also, excel supports far more mathematical functions.
⬐ Nullabillity> For example, after creating an Excel table, you add a filter, see the changed data.Add a WHERE.
> You can Ctrl-Z for undo.
Drop the WHERE. Hell, ctrl+z will work fine here too!
> sort on many columns
ORDER BY supports multiple columns too.
> create derived columns and sort on those
You can have arbitrary expressions in your SELECT clause.
> and all of this interactively.
Destructively*.
And SQL makes it clear exactly what you've done to the source data in order to get there.
> Also, excel supports far more mathematical functions.
Surely that's what extensions are for!
⬐ rabidrat⬐ nwallinIn SQL, how can I search all columns which contain some text?⬐ Nullabillity⬐ ALittleLightIf you don't have any meaningful column structure then you won't be able to do any useful analysis anyway.Earlier today I was looking at data from two different sources about the same thing. I wanted to see how similar the reports were. I grepped my logs, got columns of data, copied and pasted into Excel, created a line chart, copied and pasted the line chart into an email to a colleague. How would SQL let me do the same thing? With Excel this was probably less than a minute end to end. Would SQL be as fast? Keeping in mind that I wouldn't have a database for any of the data I was looking at.⬐ hoistbypetardConcur. For whacking out a quick analysis like that, it's hard to beat a spreadsheet. (I do like Excel, but no longer think it's all that far ahead of LibreOffice for most things I tend to use it for.)It's also good for creating what amounts to a quick interactive form.
But I've lost count of how many times I've run across a spreadsheet being misused as a shitty, ad-hoc database where it'd be less work (and less error-prone) to just use sqlite (or even postgres, honestly), so I have some empathy for GP's perspective.
No offense, but you sounds like someone who doesn't 'get' SQL.I'm reasonable at Excel. I've been using it in anger for close to 20 years. My most serious dressing down I've every received at work was related to a column of incorrect data in Excel. (I built a spreadsheet based on incorrect data. Management expected me to build correct spreadsheets from incorrect data.) I'm reasonable at SQL. I've been using it casually for 10 years. My cutoff at this point is that I'll use Excel if I expect my analysis to take less than five minutes. I'll use pandas or SQL for anything more complicated than that, because it's that much more powerful.
Excel stops scaling at about a million rows. SQL stops scaling when your indices no longer fit in RAM.
⬐ liamwestrayExcel is hands down the best financial analysis and modeling tool ever made. SQL is not really comparable here.Any attempt to use it as a database or for data analysis should be met with extreme caution. As soon as someone suggests a VB macro for anything they should be shot. Excel is not the right tool for those kids of jobs.
Python and R have just destroyed excel for data analysis and statistics (and most visualizations now) and are free. You can’t go back once getting used to that flexibility.
Jupyter Notebooks reproducibility and traceability put the nail in the coffin for excel as a data analysis tool.
⬐ enitihasI have written a lot of SQL over the years, and consider myself to be reasonably well versed with SQL and relational databases. SQL doesn't allow you to act on your data while seeing it. The interaction modes are totally different.In SQL: Type query -> See some results -> Repeat
Excel: Everything in front of your eyes. You are manipulating live data. That is not something you do in SQL. (SQL doesn't mean much though, as it is a standard and not a tool. Maybe you had some specific SQL based tooling in mind)
> Excel stops scaling at about a million rows.
A large amount of real world data has less than a million rows. Every tool will stop at some point. At that point the benefits of Excel fade away, even if it could handle the data, as 1M rows are too large for our brain to make a sense of live.
>My cutoff at this point is that I'll use Excel if I expect my analysis to take less than five minutes
A lot of real world analysis takes 5 minutes.
Of course, Excel is not perfect, and I too will never use Excel for anything that has to be done repeatedly (pandas or similar solutions are perfect here), but for quick and dirty data exploration, Excel is very good.
I don’t get it, he seems to use his mouse to navigate. Day 1 in finance you remove the mouse. Video was boring/slow for such a drastic title. Should read “snarky guy slowly uses different functions in excel and is proud of himself”. I got my hopes up for nothing :/.⬐ slowhand09⬐ enitihasYou do realize Joel Spolsky was the MS project leader for writing Excel...This is the best Excel tutorial I have ever found. Even if you don't remember much about Excel, the following from this video allows me to basically get started with any data very quickly:1. Ctrl A : Select all the data
2. Insert table : This will allow you to be able to filter or sort on any column, provide a row for stats if you want, and will auto format and color the table. You can also choose to go to a pivot table easily from here.
⬐ Analemma_Those two suggestions, plus “always work in R1C1 mode”, are enough to supercharge most inexperienced users of Excel. If you take nothing else away from Joel’s video, get those three.⬐ bitwize⬐ CraigRoodR1C1? I haven't seen that actually used since Multiplan...⬐ jasomill⬐ e12eA1? I haven't actually used that since 1-2-3…A1 users, please explain what the expression "$A$1 - A1" means in twenty-five words or less.
Hm, so that's using integers for rows and columns, rather than integer/letter (a1 vs r1c1). Is there a way to do indirect/computed references? Because if not, I'm not sure I see how it's much better (saner/easier maybe..)? What's the elevator pitch for r1c1?https://answers.microsoft.com/en-us/msoffice/forum/msoffice_...
Ed: ok, apparently the video starts with r1c1 - so I guess I've found my elevator pitch...
⬐ iudqnolqIn one sentence: Excel seems magic because when you copy and paste cells the references change to still work, but you can understand in less than a minute exactly how and why that happens if you learn R1C1.In two more sentences: Specifically if you're in cell A1 and you enter =B1 what is actually stored is =RC[1] (same row, one column more relative to this cell). So if you copy that into cell H50 it will still be RC[1] and now evaluate to I50.
CTRL-T creates the table against the selected data.⬐ enitihasJust tried this, and I think this will be my number 1 recommended shortcut to every Excel user. Thanks
Maybe you just suck at Excel ;)
https://www.youtube.com/watch?v=0nbkaYsR94cQualifies at 54 minutes!
I find MS Access not as intuitive as the other siblings though. Wish there was a "You Suck at Access" video like this one [https://www.youtube.com/watch?v=0nbkaYsR94c]
Start here! https://www.youtube.com/watch?v=0nbkaYsR94c (Joel Spolsky)
I recently watched his Excel tutorial on Youtube: "You Suck at Excel with Joel Spolsky" https://www.youtube.com/watch?v=0nbkaYsR94cDidn't intend to spend an hour on it, but we all know how these things often go.
Totally unrelated to this topic, but I absolutely love Joel's "You suck at excel" youtube video, done in the same vein as the old "You suck at photoshop" tutorials someone made.https://www.youtube.com/watch?v=0nbkaYsR94c I find it absolutely hilarious, and I can almost guarantee you'll actually learn something new you can do in excel.
⬐ vicarrionHaha it mostly makes me think how frustrating it is to work in excel⬐ xhgdvjkyin a way this is evidence that Excel is usually not the right choice⬐ LeonBI made this subsite: https://excel.secretgeek.net/ to make it easier to share the video internally at work.Sending a video about "Secrets of Mastering Excel" is less like to humiliate your colleague than sending them a link to "You suck at excel"
> One thing I never understood about Excel is why the cells which have formulas don't look different than cells which contain leaf-data.There are some styles actually meant for this, but you have to apply them manually.
> 2ndly why not allow users name their columns, why do they have to always be "A", "B", "C", .. ?
Named columns do exist actually! Not of the excel sheet itself - but in tables you can mark within that sheet. You can use the names in equations and everything. You can also name individual cells as well.
Joel Spolsky has a whole talk where he goes into this and some of the other stuff Excel has that you might not realize it has: https://www.youtube.com/watch?v=0nbkaYsR94c
⬐ jupedBy far the worst UI choice in Excel, which has stopped many potential power users from forming, is that "Format as Table" is named like a visual formatting option, is stuck on the ribbon next to visual formatting options, and prominently makes you pick colors and border formatting.Whereas the reality is that making a table is the on-ramp to many of Excel's more powerful features.
⬐ galaxyLogicI imagine that some developer or team had to hide such useful features under the name of "table formatting" because they could not get the ideas through in their organization. Don't know but I imagine.
I'd recommend watching this YouTube video; it's only one person's take on the right way to use Excel, but Excel has many ways to handle these issues:
⬐ J_cstThat's a classic, definitely to be watched!⬐ viraptorYup, love it, keep recommending it to people.⬐ LeonBThe link is to "You Suck at Excel" by Joel Spolsky, which is an excellent tutorial.I sincerely wish it had a different name, though!
I would love to send this video to some of my business colleagues inside a large enterprise. They need this information and they would enjou everything about this video. However, it would not be acceptable to send them a video entitled "You Suck at Excel."
If it had a more enterprise-friendly name I would even have a link and description to it in my email footer inside the enterprise. It would really help a lot of people.
⬐ TuringTestYou can embed it in a more professional page, and hope that no one will notice the title at the overlay ;-)⬐ LeonBGenius idea. I have a bunch of domains, combine it with the right subdomain... and we're onto something. Adding this to my list of things to do. Cheers!Update: okay check out https://excel.secretgeek.net/
I've re-badged it as "Secrets of Mastering Excel" and used absolute positioning to put a label to that effect over the video's title.
Ideally I'd detect when the video starts and remove the label. Hmm. Not sure of the right approach.
I recommend (at the foot of the page) that the viewer watch it in full screen (which will remove my dodgy title)
This video by Joel Spolsky taught me a lot of new Excel tricks/QoL improvements:https://www.youtube.com/watch?v=0nbkaYsR94c
It is amazing how rich the ecosystem is. I didn't know about pivot tables. Excel has always impressed me, and continues to do so the more I learn.
⬐ cosmieIt's unfortunately not available in the Mac client, but Excel also has an amazingly powerful and useful tool called PowerPivot/Get & Transform[1].You can connect to external data sources such as CSV files, Excel files, any database with an ODBC connector, APIs, all kinds of neat things. Ingest that data into your Excel file, create an enforce constraints and relationships within your data model, gives you incredibly robust data munging and analysis functionality[2], and then expose all of that as a PivotTable. And the functionality itself bypasses the limitations of Excel such as max data size or computationally inefficient formula implementations, as it uses a separate data storage and computational engine that's a highly compressed columnar data store.
The PowerPivot work is also mostly transferable to PowerBI and Analysis Services. Taken together, you've got all the tools to apply progressive enhancements for end users. Let them create their Excel-based stuff. When it starts to become more mission critical, non-performant, or error-prone, provide them with support to clean it up in the ways that video from Joel Spolsky mentions. When it hits growing pains from that, refactor it further to leverage the built-in data modeling capabilities to enforce some integrity, automation, and potential data volume scaling. And when you hit growing pains with that, or the underly process/usage finally matures to a state of stability, or you need to address security/access/audit-ability concerns, transfer that data model and everything to either PowerBI or an Analysis Server deployment and migrate the management to IT.
I don't see it in practice very often, but it's an incredibly effective and frictionless way to both enable your business users to innovate their work processes via the tools they know, while also providing a non-disruptive way to mitigate your business becoming reliant on apocryphal spreadsheets being passed around to support critical business functions. And by design alleviates many of the causes of "automation" projects failing.
[1] https://support.office.com/en-us/article/get-transform-and-p...
[2] It doesn't rely on the same functions exposed for Excel formulas, but rather a language called M for ETL-like needs and DAX for calculations. https://support.office.com/en-us/article/how-power-query-and...
[3] https://en.wikipedia.org/wiki/Power_Pivot#Product_history_an...
⬐ shaklee3The other classic example is the disgraced Martin schkreli:
Excel is the ultimate REPL, and is ubiquitous on business workstations. Your "code" can literally run anywhere Excel exists (hence why you see complex worksheets and excel files built and copied around orgs).Obligatory Joel Splosky "You Suck At Excel" link [1]. Mandatory viewing in my opinion!
I don't understand why spreadsheets are so fervourously bashed: most times they do the job and in a fast and inclusive way.The spreadsheet is certainly one of the best tools ever invented.
[0] Joel's Spolsky must-watch talk on Excel: https://www.youtube.com/watch?v=0nbkaYsR94c
⬐ arethuzaAnd like any tool they can be abused - I really like Excel but I've seen it used for things that were unwise.I've spent months reverse engineering a vast, complex, costing application that had evolved within a spreadsheet and needed (for very good reasons) to be turned into a proper application. The really entertaining part was trying to convince people that their spreadsheet didn't actually do what they thought it did....
Edit: It was costing a complex heavy industrial process so it had logic that was basically physics right through to finance.
⬐ None⬐ lowkeyokayNone⬐ maxxxxxI think the solution should be to have better excel reverse engineering tools. I have seen plenty of crazy complex spreadsheets that had hit a wall and were hard to refactor but the solution would never have happened if they hadn’t had excel enabling regular users to create it.I don’t think Excel is ever bashed for its primary use case. It’s when someone starts building complex, multi user applications things start to go wrong. It almost always becomes an entangled mess. And good luck to anyone taking over when the creator leaves. I’ve been both the creator and maintainer of such Excel applications, and I do not miss it.⬐ MrEldritch⬐ pravjOne thing that frequently comes to my mind - rather than trying to convince people to move to a general-purpose programming language (and ignoring all the reasons that people turn to Excel in the first place), is it possible to turn an Excel-style spreadsheet into a general-purpose programming environment that is powerful enough to do that sort of stuff?People really underrate Excel as a programming environment, honestly. Yes, it's crippled and leads people to produce massive gross un-debuggable hellsheets ... but there's reasons (beyond just "it was the only usable software that could be run on office computers") that end users with a problem to solve keep turning to it despite those flaws. The combination of reactive programming, data-first visibility, no hidden state, decent approximations to structured programming by way of click-drag-and-copy-paste, and being able to reference variables and values without needing to name them has some kind of magic to it.
There's quite a bit of interesting research I've seen from Microsoft on how to take something like Excel and turn it into a non-crippled programming environment - spreadsheet-defined functions (including recursion, lambdas, and higher-order functions natively in the spreadsheet environment, without having to drop into VBA!), dynamic arrays, an alternate computation-first textual view that exists simultaneously with the data-first spreadsheet view, first-class complex data structures ...
Between HyperCard, Jupyter/Mathematica notebooks, and Excel, there's a lot of common features that seem to point to a vision of a truly useful end-user-accessible programming tool, something that would let people write real, useful programs intuitively and quickly enough to be worth building them themselves for whatever they needed them for, without forcing them into condescendingly-simplified drag-and-drop "visual programming" codeless clunkfests.
⬐ pjc50Excel is a general-purpose programming environment, it has a full programming language inside it (VBA) with the ability to call out to the operating system and load functionality through DLLs.No, the way to tame Excel is to make it less general, to impose principles of structured programming on it. Separate code and data and presentation. Put some of the data in a database. Impose types on it, so Excel doesn't interpret genomes as dates etc. Make the control and data flow visible. Split it into pieces with defined responsibilities.
Excel is really a "two dimensional assembler": you put an operation in each cell, and they can read any other cell, and it all executes together.
⬐ jiveturkey> VBAAnd now javascript.
https://docs.microsoft.com/en-us/office/dev/add-ins/quicksta...
⬐ DebtDeflation>Separate code and data and presentation.This is the big one.
⬐ VBprogrammerIn my first job out of Uni I wrote some VBA to make requests to the "online diary" to pull out some of the information into some template Excel files we had to create.Some poor soul is probably still maintaining that.
After switching from technical to a product role, I have seen a complete change of perception towards excel/google-sheets. I still observe the passive hate towards excel by developer companions, but for me, it's a goto tool, the same as it was the case with Python, the goto language.I like to call it a fundamental or building block technology that enables you to develop things on top of it, my favorite being a finite-state-machine we modeled in a sheet. Won't be surprised if we end up talking about Google sheets being one of my favorite products.
⬐ cwyersBecause lots of people have lived through a spreadsheet that had grown too large and too important, and the original author might be gone, and reverse engineering a spreadsheet that's hundreds of megabytes in size and has a dependency graph that's longer than a page quickly becomes... burdensome.⬐ liveoneggsbecause the one you got emailed last year is out of date vs the one your boss got emailed last month which is also out of date from the finance team's version⬐ tracker1Not only that, but Excel can tether back to a traditional rdbms for pull/push data. It's often the preferred way for PMs to interact with, for example, VSTS/TFS. There is absolutely a LOT that can be done with Excel and other spreadsheets, and it shouldn't be underestimated.I'd rather not work with them more than basic/classic usage of a more well formed data dump. I use spreadsheets for monthly finances, some historical information that fits well, and a few other use cases. I'm not big on VBA and have little desire to work in the space.
I have, however, seen some amazing usage of Excel and other spreadsheets for everything from charting and pivot data, to multi-user applications tethering to other data stores.
⬐ cfarmAs usable data starts getting larger, the trade off between Excel and databases will get smaller.⬐ NoneNone⬐ monkeydustAgreed. The bashing does not really come from the end users / business people who benefit from the spreadsheet but those that are either competing against it (engineers) or having to take on risk through misuse (compliance).⬐ ibejoebAs other comments have noted, it gives rise to the new problem of the proliferation of stale, incomplete, or otherwise incorrect data.Google Sheets, on the other hand, is truly compelling, and a lt of the work I've been doing lately has been using Sheets and Apps Script to build really powerful "apps" on the super-cheap. This is the stuff we used to do with VB years ago, and it would take weeks. Now I can get at least a PoC of a fully multi-user system in front of a client in a day, and they don't pay anything to host it.
⬐ briandear> inclusive way..How is a spreadsheet “inclusive?” What does that mean?
⬐ ericras⬐ broheeIt means that non-programmers can do "programming".⬐ pavlov⬐ sokoloffThe quotes seem unnecessary. Programming isn't just an expert activity. Many applications offer macro recording and other facilities to create programs without scripting. Dataflow programming is often used in graphics and media applications. Spreadsheets are one very successful implementation of dataflow programming.What Excel doesn't let you do (without heroic effort in support toolchains) is software engineering...
⬐ mch82It also allows domain experts to program. So while they may not be expert programmers, they are experts in the problem area and will come up with expert solutions.I would wager that 10 times (perhaps 50 times or more) as many people can make a simple Excel worksheet as could program the same functionality in a mainstream, general purpose "programming" language.Excel doesn't have a compeling, standard way of doing version control that I know of, and most Excel files mix freely data and code in the same sheet, which makes debugging pretty hard. Excel files go from assets to liabilities when they start boxing over their own weight, which happens pretty fast...⬐ bartreadI met a guy at a wedding reception last year, where we were talking tech, and I was lamenting similar issues (we use fairly large multi-worksheet spreadsheets for configuration). He mentioned that his company makes some tooling that might help with this:We ended up going a slightly different route, but thought this might be worth sharing.
(I am not affiliated in any way and have nothing to gain by this.)
⬐ sheetjsAside from Excel's built-in "Track Changes" features (which stores the revision log within the file), the "Spreadsheet Compare" tool that ships with Excel is a decent diff engine that can be paired with any VCS. Sharepoint is another "official" solution.Unstructured use of Excel is a completely orthogonal problem.
⬐ _bxg1Makes me wonder if there's a market for an Excel clone whose files are easily machine- and human-readable, and version-control friendly.⬐ DoubleCribble⬐ smacktowardGoogle Sheets lets you download the workbook as an Excel file which can be automatically saved to a Google Drive folder. Hourly (?), daily, weekly, monthly snapshots of a shared workbook can all be easily scripted and stored for later retrieval. It eliminates any confusion over which is the latest workbook because the Sheets version is always the most up-to-date.⬐ dpoochieniO365⬐ robbiemitchellGoogle Sheets⬐ oblio⬐ jmkniHave you tried using Google Sheets? I have. You can't even cut and insert a bunch of rows. That's a primitive functionality that is very much needed that was available in Excel circa 1988 (I'm exaggerating, but it's been in Excel for at least 2 decades).⬐ lrem⬐ naravaraWhile sheets is nowhere near my idea of ergonomic, I've figured out this one every time I needed it. And, despite working for Google, I'm nowhere near a regular user.Maybe your browser, or some extension, got in your way?
Sheets is way less powerful than Excel. Losing a lot of the keyboard shortcuts also makes it way less useful for power users. It also doesn't do well at generating macros and its chart generation capabilities are much less flexible.⬐ robbiemitchellEveryone commenting: Google Sheets is not an exact copy of Excel, but it's the closest (I know of) that exists.For plain-old Excel with version control, save your Excel files in Dropbox. MSFT might also offer a cloud version with version control but I have no experience with it.
⬐ kenAre Google Sheets "easily machine-readable"? I've tried to add support for it to my application, and I could not get it to work at all. Maybe it's technically possible but it's sure not easy. You need to use Google's proprietary APIs, and the documentation and support are terrible.⬐ lrem⬐ bartreadI've pulled some data from a sheet into a python notebook by copy-pasting some code I've found. Sure, these are proprietary APIs, but how else would you talk to a proprietary web service?Disclaimer: despite working for Google, I don't know that much about the suite.
Google Sheets is the main reason I gave up on Google Docs and bought an Office 365 subscription for myself a few years back. It's just nowhere near as good as Excel. Frankly none of the Google Docs offerings holds a candle to Microsoft Office[1], so the latter is well worth the money to me.[1] I am tactfully not counting GMail as part of GDocs here because, honestly, Outlook is the one tool that wouldn't come off well in that comparison, and reason #1 is that Outlook's search is terrible; reason #2 is that Outlook doesn't perform at all well in the face of the modern "I want to keep all my mail so that I can search for and find relevant messages whenever I need them", which can be key when dealing with tricksy customers/partners/co-workers, and is handy even in mundane day to day use.
⬐ shostackWe use Google Sheets as a team for its collaboration functionality. However it still has a lot of room to improve with its visual customization for charts and formatting and such.I realize some people don't care about how beautiful a spreadsheet looks, but in presentations or sharing complex information, it matters. And formatting anything complex with charts and such is a major headache in Sheets (like my latest struggle to get annotations to properly appear, or move a single peak data label so it isn't cut off from displaying).
⬐ thrower123The thing about Google Docs is that I haven't seen any changes or updates to get toward feature-parity since I was using it in high school a decade and a half ago. I assume it has been exiled to Google's island of misfit toys that they can't quite take out back and shoot just yet.⬐ cafard⬐ rahimnathwaniI hope not. Google has fairly recently made the Docs API available."Frankly none of the Google Docs offerings holds a candle to Microsoft Office"Word and Excel are great for individual productivity. If you use documents and spreadsheets as tools for collaboration, though, Google Docs is much much better overall, even though it's missing many features to which power users have become accustomed.
Sure, you can save a file to OneDrive and have multiple people working on it at the same time. But:
- In my experience, simultaneous editing via OneDrive (whether using the browser or the desktop apps) is more laggy and buggy than with Google Docs
- The commenting functionality is missing lots of features that are essential to my desired collaboration workflow:
i) Ability to give someone access to add comments and suggest changes, but not to accept changes or edit directly
ii) Comment authors are identified, and only they can edit comments they have written
iii) Comments can be received and replied to by email.
The way commenting works in Google Docs encourages collaboration in a way that the simple comments in Word doesn't. If you've not worked somewhere that uses Google Docs for collaboration, it can be hard to know what you're missing.
⬐ NoneNone⬐ kjs3So Google is "much much better overall" for multiple users, as long as you can dumb what you're working on down enough to fit Docs significant limitations. That's totally fair, but isn't a trade off everyone can make.⬐ rahimnathwani⬐ bartreadYes, there's a trade-off. Note, I didn't say Google Docs is better for every use case. I said it's better "If you use documents and spreadsheets as tools for collaboration".The key difference I'm talking about is between:
A. Writing a document, and sending a version of that document to one or more other people, so that they can do something with it. (This might involve sending you back feedback or suggestions.)
B. Sharing a link to a 'living' document, with which people can interact in different ways (comment, participate in comment threads, add change suggestions, accept/decline changes).
In my experience, B is much harder to achieve, and unlikely to happen organically, if you use Microsoft Office.
Has anyone here witnessed an organisation that uses Microsoft Office, and where significant progress is made on people's thinking, through their online collaboration on docs/sheets?
> If you've not worked somewhere that uses Google Docs for collaboration, it can be hard to know what you're missing.I have (for a previous company), and it still hasn't proven worth it at other companies, including my current employer. We use Office 365 exclusively: it's not perfect but, as I say, Excel is streets ahead of Google Sheets in every other way, and that matters for our use cases.
If you are using XLSX files (Excel 2007 and later) you can use an Excel diff driver with Git for not terrible version control - http://programmaticallyspeaking.com/git-diffing-excel-files....⬐ brighter2morrowAwesome, I never knew you could change the diff drivers in git. Such a well-thought-out toolIIRC, Excel XLSX files are just ZIP archives with a bunch of XML inside. Therefore, if you can come up with a way for a version control system to transparently look inside ZIP archives, it ought to be possible to version-control XLSX files using the same tools you'd use to version-control any text files, no?(Though this only solves half the problem, the other half being that even if you can wrangle Git or the like to version files inside a ZIP, Git is far from being a tool that could be considered appropriately usable to dump on the Excel audience...)
⬐ sheetjs⬐ jgalt212It isn't as simple as that. There are numerous equivalent representations of the same underlying content (same data, same styling, same charts, etc) that have wildly different XML representations. You need a content-aware diffing tool.FWIW we've explored that idea in the past, using git textconv to diff spreadsheets https://www.npmjs.com/package/j#using-j-for-diffing-spreadsh... -- it seems nice on paper, but quickly becomes hairy
> mix freely data and code in the same sheetwhere is this unnecessary lisp hate coming from? /s
⬐ la_barbaWhat common scenario do you envision where version control is useful?⬐ brohee⬐ darkpumaA file that is edited by more than one person, of which at least one doesn't quite now what he does? It's pretty hard to see what changed from one version to another, and a destroyed formula can go undetected for a while...And obviously, merge would be nice if multiple people can legitimately edit concurrently.
⬐ la_barbaVersion control is extremely useful in software development because code modification by multiple authors is a primary use-case that happens multiple times throughout the day.Maybe your experience is different, I haven't seen a use-case where multiple authors are changing excel macros or formulas that often, or even often enough to where this is an issue. In fact I think adding version control would make things very very confusing for most people.
⬐ devereauxIn spreadsheets (excel) and notebooks (ipython/rstudio), having the data bundled with the code is a feature. It provides reproducibility.> a destroyed formula can go undetected for a while...
That is a problem in methodology, not with the tools. There are many solutions that do not require abandoning spreadsheets.
For the destroyed formula example, you need tests. Simple example to do that with a checklist: if you are doing a SUM(), require that the employee ticks a box saying "all the number were highlighted when clicking on the SUM formula".
For the out-of-sync version, you need a central repository and another box "I retrieved the latest version from the xx repository, and this version was: ... "
Then require that to be printed and signed (accountability), and you'll see mistake disappear.
⬐ broheeData bundled with the code is good, but code mixed with the data is a whole different thing. Notebooks are better than Excel in that regard I think.As for expecting people to religiously and accurately observe procedures, I work with human beings, you seem luckier...
⬐ devereauxAfter they received training when they learn that this step is important and not just a formality, and why it is done, I found that people do observe procedures, especially when they have to sign the checklist they filled themselves.Human beings observe procedures when they understand it's part of the job and they are held accountable.
⬐ hectormalotIs it any different with code? Developers don’t always stick to the procedures either.I have ‘unit tests’ in my excel files on the last tab. Eg, the sum of all lines in the Data tab must be the same as the sum of the annual revenues in the dashboard tab.
With a bit of conditional formatting it’s easy to see if a test is failing as well.
I like programming for lots of things, but ask me to do a business case or some one-off analysis and I’d use Excel in most cases.
All of that is forgivable because Excel has done something very few other tools have ever done; which is allow 'non-programmers' to effectively program their computers. Excel is empowering software that lets normal users solve novel problems. With excel, a non-technical small business owner can create software(a spreadsheet) that automates the solution to a problem nobody has ever had before. Solutions to programs the creators of excel never even considered.A lot of software is like single-use kitchen gadgets. So called "unitaskers". It has one purpose envisioned by it's inventors, and you'll be lucky if you ever figure out something else it's good at. Sometimes they're pretty slick, but still limited. My mother has a device that skins, slices and cores an apple all at once. With that device, you can process enough apples to make a pie in a minute or two... but the chances of you ever finding anything else that tool can do are slim. Maybe you could use it to slice curly french fries out of a potato, but that's about it. It saves your time if you're operating in the narrow problem space envisioned by the inventor, otherwise it doesn't have much of anything to offer you.
Programmers love creating unitasker software, particularly for end users, frankly because it takes much less thought. And when they do create powerful general purpose highly flexible software, their target audience is most often other programmers. Excel (and a handful of others like hypercard) are proof that this status quo can be broken.
⬐ scroot> Programmers love creating unitasker software, particularly for end users, frankly because it takes much less thought. And when they do create powerful general purpose highly flexible software, their target audience is most often other programmers. Excel (and a handful of others like hypercard) are proof that this status quo can be broken.Exactly. If we view personal computing more like a "medium for literacy," then it becomes clear that computing power is wildly imbalanced. This is especially egregious when we consider the original goals of personal computing. Spreadsheets, Hypercard, and things like them were a totally different direction for computing that has largely since been abandoned in favor of market-dictated "wants" and shrink-wrapped "solutions." The upshot is that the people who should be most in the know -- computer people, developers, etc -- are stuck in a rigid world of epistemic closure (where, for example, Unix is more or less the "End of History")
I have yet to find a better definition of programming than "telling a computer what to do." When put this way, a lot of things that the trade-school approach consider "programming" are really only a subset -- and perhaps the most regressive and uninteresting to boot.
⬐ z3t4There's a saying: do one thing well . Software doesn't weight or take up space. Then there's high level programming where you sew together high level modules.⬐ wtetzner⬐ brohee> There's a saying: do one thing well.The problem is that not everyone has the same needs, and there aren't enough programmers to write special purpose software for each person on the planet. Making software customizable is a way to give users a powerful tool that can be adapted to their needs.
In my experience, oftentime Excel is also used out of desperation from not being able to install proper tools, it's not necessarily the only programming solution the users know, it's the only one IT let them have (in the case of technical users, e.g. engineers).Restrictive IT policies are the root cause of some of the gnarliest Excel sheets I've seen.
⬐ la_barbaOut of curiosity, what industry are you in?⬐ brohee⬐ darkpumaConsultant, so I see lots of different customers, mainly industry.Many Excel sheets are a form of shadow IT, no proper way to get the app they need in a reasonable time, so people jury rig something with what they have.
I suspect I see more dysfunction than average still...
⬐ NoneNone⬐ bigger_cheeseI work in industry I've seen some of the same things. The IT policies at my work do tend to be overly restrictive but I think it is somewhat understandable there is maybe two thousand people that work at the plant I'm at. 10 to 15 years ago someone made the decision to outsource IT support (before this every department had IT specialists) there is just no way external support can scale to the level its even feasible to think about giving everyone on site admin access or similar. So we've ended up in enterprise like situation with creeping bureaucracy where everything is super locked down and if you need some software installed submit a ticket get a manger to sign off on it (and be prepared to argue with them about why it's necessary to get this software installed) and maybe 1 or 2 weeks later ticket will get actioned (if you are lucky and it doesn't end up sitting for a month in purgatory somewhere).Excel is part of standard environment so it is present on every fresh installation here so there is a tendency to reach for it. The "I can open up excel and start working on my problem now, or I can submit a ticket and wait two weeks" attitude is a real thing. The other issue is "I know I have excel installed and I know Bob has excel installed - who knows what else Bob has installed already so if I want to collaborate with Bob I better just use excel."
Personally (I work as an Engineer - the non software kind) I think excel is fine for rapidly prototyping something and as a first pass solution for when you need to quickly answer something but once you've reached the stage a spreadsheet is needed to run process it should be rewritten in something more robust - preferably before it reaches that stage.
⬐ dragonwriter> The "I can open up excel and start working on my problem now, or I can submit a ticket and wait two weeks" attitude is a real thing.Yes, one of the major problems for which Excel is a popular solution (and perhaps the biggest in enterprise environments) is IT service request friction.
If you really want to displace Excel, you don't need alternative software (I mean you do, but not anything novel), you need IT to not be an isolated distant mystery group which can only be invoked by time consuming, arcane rituals to which they give unreliable and untimely responses.
Most businesses using excel don't even have an IT department. I'm talking about the LONG tail of small business where the owner is using excel and nobody, except perhaps the size of their wallet, can tell them some other piece of software is off limits.
If your goal is to have your ego deflated in Excel, this is always a good place to start .. "You Suck at Excel with Joel Spolsky" .. https://www.youtube.com/watch?v=0nbkaYsR94c but I agree, Shkreli definitely had the whole speed run thing down with Excel.
⬐ umviIs that a parody of "You Suck at Photoshop" tutorials?⬐ schemathingsFunny, I never even noticed that, but apparently so .. his last comment in the description is "Apologies to Danny Hoyle" and a (now broken) link to "You Suck at Photoshop" videos.
If you like to get better in Excel learn some nifty tricks with tables then watchYou Suck at Excel with Joel Spolsky
https://www.youtube.com/watch?v=0nbkaYsR94c
Replace vlookup() with index() + match() combination.
https://chandoo.org/wp/vlookup-match-and-offset-explained-in...
If we're sharing awesome excel videos, You Suck at Excel with Joel Spolsky has to be among the best I've ever seen. Joel, being on the team that made excel, has an incredible amount of excel knowledge.The video is meant to be in the same vein as the You Suck at Photoshop videos. https://www.youtube.com/watch?v=0nbkaYsR94c
⬐ kbensonAnd here's the HN discussion from when the Spolsky video was posted[1], with the Shkreli link as the top comment. Over 400 comments.⬐ simik⬐ riazrizviHuh, I knew I saw this Spolsky vs Shkreli Excel mastery comparison. Thought it was just a dejavu, or something.sweats, "what don't I know?", watches hour long video (v. quickly), wipes brow "phew - it's just basic".⬐ havetocharge⬐ lucidguppyYes, the only thing I learned is that Spolsky is kind of an ass.⬐ lewisincYou mean we had access to Basic in Excel for all this time? I'm really behind the curve!⬐ riazrizvinot Access to Basic, just Basic⬐ drKarlI see what you did there :DYou get a perceived performance boost when you get a keyboard with a sonorous click.⬐ None⬐ mynameishereNone⬐ jjeaffYa, I never get comments anymore on how fast I type now that I switched to a chicklet style keyboard from my mechanical. You can still hear the clicking, but it doesn't sound nearly as fast.He's got a Bloomberg console and he's typing data manually from a PDF into Excel. That's just got to be the incorrect workflow. If not, there is room for huge improvement in financial tools.⬐ rahimnathwani⬐ ChuckMcMThere is value in thinking about the numbers as you build a model. Manually typing them in helps.Just like Zed Shaw's LPTHW tells you to type in the code from the book, and not copy-paste.
⬐ porluneI asked Shkreli about why he didn't automate his process, once, during his live stream. He quipped back that he'd been doing this for long enough to know how to do it and that I wasn't the first to ask him. Never did get a clear answer; but I'm assuming it has to do with what rahimnathwani said.⬐ hedvigI thought this too - those earning reports can't be put through an API?⬐ imronI used to work for a financial services company. Their main product was a 30 year old cobol program used by many large banks.Some of them would use our software to print various reports and then manually enter those printed values in to excel.
Even worse, the software could export to a format excel could process so it wasn't necessary in the first place.
Don't expect to be disrupting banking anytime soon though. Banks are incredibly risk averse, especially when it comes to messing with expensive legacy software that has been working for decades.
At one point, one of the company's customers did an investigation to see how much it would cost to replace the software, and it was close to 9 figures, and so they decided it was more cost effective just to continue paying the multi-million dollar yearly licenses.
⬐ badpun> At one point, one of the company's customers did an investigation to see how much it would cost to replace the software, and it was close to 9 figures, and so they decided it was more cost effective just to continue paying the multi-million dollar yearly licenses.A bank I've worked at had a project to replace lots of bank's legacy (Mainframes and Cobol) with Java on Linux. The project's budget was over 1 billion dollars and it was estimated to take 5 years.
⬐ imronThe company I worked for tried to rewrite everything in Java twice.Both attempts cost millions, both were abandoned.
The lessons (and pain) from the first attempt were ignored because not enough of the upper management were still around, and the only people who remembered the pain were the rank and file, who were ignored by the new upper management types looking to leave their mark on the product.
It is interesting to see how many of the things that could not be done in Google sheets when that video was made are now available.⬐ unixheroYew. But Sckirelli's are better.⬐ lph20 minutes in: the dawning horror that, yes, I'm watching a video about Excel for fun⬐ kkarakkusing excel isn't not fun, it's just that the mental complexity of excel is offloaded to the user. the user keeps track of the various computations and transformations. anything more than a quick check on some data exponentially taxes the brain leading to an ever increasing dislike of the softwarebeing handed someone else's excel sheet is an infuriating experience because they're also handing you whatever convoluted mental model they used
⬐ shostackWhen I was in the agency world, I was frequently inheriting spreadsheets from other teams that needed to be updated with new data, or often overhauled completely.You definitely have a lot of moments similar to reviewing others' code where you go "Hmmm, this doesn't seem to make sense. Am I missing something? Or did they just do this flat out wrong?" Often the answer was "yes."
There's also a radical spectrum of Excel skills (much like coding). I took a 50MB Excel report that took hours to update manually and brought it down to <1MB that refreshed when you clicked a button via web queries. There were significant savings for the agency as a result as that had to be updated weekly, simply from cleaning up a spreadsheet.
Whenever possible now, if I suspect a spreadsheet tool or report I've built is too complex to grok just by quick perusal of some formulas and clear naming conventions, I document the hell out of it in a separate sheet or our knowledgebase. The pain of deciphering those things is real, and I would never wish it on anyone.
⬐ db48xI would say that they're _not_ handing you their mental model. Instead using their spreadsheet forces you to come up with a mental model which is congruent to theirs.⬐ killjoywashere⬐ aaavl2821On ships they sound the tanks daily (water, oil, fuel) and use the depths to calculate the volumes. There are dozens of tanks. Part of my job was then to use that data to figure out the change in the ship's draft from day to day. Which could be done two ways: drawing lines on Xerox copies of an odd set of nomograms, or brute calculation. I found the nomograms fraught with opportunities for error. Luckily the oil king responsible for the soundings kept them in Excel. So I wrote a sheet that took his data and did all the brute force calculations. So when he printed the daily soundings, instead of handing them to me, the draft report just printed out as the next page. It was more accurate and saved me 1-2 hours a day. And looked nice and crisp coming off the laser printer. Everyone thought I was a magician.The guy who took over for me had a nervous breakdown.
In investment banking, one of the first things you are taught is excel formatting and how to write readable excel. How to write clear and concise comments, what color different cell types should be (black for formulas, blue for inputs, green for links to other cells, red for weird formulas), how to structure large files (when to make a new tab vs keeping a monolithic file), when to break up a formula into multiple cells so it is easier to understand, even that you need to hit "ctrl home" before you save so that the next person to open the file starts at the right place (the group that trained me called it "turning off the lights before you leave a room")So if someone is good at exce you can easily understand what they're doing and why they did it
⬐ joshgelThey have standardized their mental models⬐ _pmf_⬐ lenkiteWhich cannot be said for software engineers.⬐ askafriendThis is called "Architecture" or "Code Patterns".It can absolutely be said for Software Engineers. It just can't be said of every Software Engineering Org.
Would be nice if someone had documented these conventions somewhere.⬐ pimlottcI'm intrigued by this, are these standard codified and documented anywhere? Would love to know more about Excel best practices.⬐ aaavl2821I'm not sure if there is public documentation on this, but investment banks definitely have training materials that document it.A lot of it is just high level stylistic suggestions, sort of like the Python style guide, though some are more specific
The strangest one I remember is: when you are summing a column, put one row in between the cell with the sum and the bottom-most number to be summed. Change the row height to something like 10% of normal cell height and in the cell in the new row, right above the cell with the sum, add a set of dashes (-------).
I don't remember why we were told to do this but definitely remember doing it, and in my next job people laughing at that habit. I think that it ensures that, when you add a new row to the column to be summed, that your sum formula automatically picks up the new row
There is also a lack of what is considered "Excel best practices", for want of a better word. The only resource I've seen along those lines is Joel Spolsky's "You Suck at Excel" video (which is excellent, and should be necessary viewing for anyone who use Excel on a regular basis): https://www.youtube.com/watch?v=0nbkaYsR94c
⬐ cssYeah, great video. I often send this to colleagues. Another blog I recommend to others is https://itsnotaboutthecell.com/
Joel Spolsky used to be a Program Manager for Excel. He has a great, 1h long video doing an Excel demo a few years ago - here, now YOU can be the Excel guru https://youtube.com/watch?v=0nbkaYsR94c
Oh, also, I forgot.Learn Excel properly: https://www.youtube.com/watch?v=0nbkaYsR94c
When you're properly automating things you'll want to use programs and command line tools instead, but you should still know Excel for the one-off quick and dirty data inspection.
Also some of your colleagues will use it a lot, and won't have learned it properly. You can make yourself very useful.
You Suck at Excel with Joel Spolsky - https://www.youtube.com/watch?v=0nbkaYsR94c
It may also have been in a blog post, but Joel Spolsky makes that remark in his presentation "You Suck at Excel" https://www.youtube.com/watch?v=0nbkaYsR94c
> Aren't you all keyboard, all the time?No. Keyboard for the things i know by heart and use every few seconds, mouse for the things that happen from time to time, and explorability for everything else.
Also, link to that video?
Edit: I think you mean this: https://www.youtube.com/watch?v=0nbkaYsR94c
Very first thing i notice is he rides hard on the "Paste as Values" option being the one you want to use most of the time. However since he has the ribbon on top he has a gigantic paste button and needs to use the drop down every time to call it.
With normal toolbars you'd just add a "Paste as Values" button next to the normal paste button and skip the error-prone, slow dropdown process.
(I also note he has no keyboard shortcut for it.)
⬐ eisa01New ribbon has very discoverable keyboard shortcuts, opposed to the old layoutPaste as values: alt h v v
⬐ Mithaldu⬐ NoneHe never uses that in the video. The video also doesn't show the availability of it. Also a sequence like that for something that he uses all the time is not very good huffman coding.⬐ criddellBut he also starts the video saying this is at a basic to intermediate level. Plus, picking things from the tool bar rather than just using keystrokes is a little more video-friendly.⬐ MithalduThis is getting into tea leaves reading, but i am fairly certain that he isn't aware of or simply never uses a shortcut for it and clicks through the cumbersome dropdown every time. My evidence being that he's happy to show off multiple access modes for "fill down", but does not do that for "fill by values". Possibly he might also be aware of the keyboard chain to reach it, but doesn't mention it because it's fairly cumbersome itself too.None
You Suck at Excel with Joel Spolsky https://www.youtube.com/watch?v=0nbkaYsR94c
Also see You suck at Excel by Joel Spolsky
If Excel is even a small part of your job, it is worth taking an hour of your time to watch Joel Spolsky's "You Suck at Excel": https://www.youtube.com/watch?v=0nbkaYsR94cSome HN comments here: https://news.ycombinator.com/item?id=12448545
> If it's just making some formula in spreadsheet workExcel functionality is on part with some programming languages.
Excel also has named rows/columns and cells - one of the bunch of useful tips I learned from Joel Spolsky's "You Suck at Excel" presentation - here's the part where he covers that: https://youtu.be/0nbkaYsR94c?t=25m54s
⬐ mschaefExcel also supports Multiplan-style RC syntax for cell addresses, if you like. I find the syntax a bit more clear, but ultimately too verbose to be useful.R1C1 = Row 1, Column 1. R[1]C[1] = cell one row over and one row down.
One other point worth mentioning is that cell-relative references in Excel are more pervasive than they might appear. For example, you can specify a conditional format formula that highlights a cell if it has a different value than the cell immediately above. (Or a number of other more considerably complex scenarios.)
Range names work this way too... it's possible to define a range of the form "three cells to the right of the current cell".
⬐ apricot⬐ NoneMultiplan... now that's a name I haven't heard in a long time.None
Joel Spolsky's "You Suck at Excel", https://www.youtube.com/watch?v=0nbkaYsR94c
Learning keyboard shortcuts in Excel yields massive returns on productivity. For an intro, Joel Spolsky has a great tutorial: https://www.youtube.com/watch?v=0nbkaYsR94cInvestment bankers are famous for working without a mouse in Excel.
Have you seen the Joel Spolsky video You Suck at Excel [0]? He demonstrates how using tables can avoid many of the common pitfalls.
⬐ mschaefData tables are a huge win where they apply... they can result in significant performance improvements in addition to all the other benefits. (A former co-worker of mine once wrote a simulation using Excel, and switching to tables give the system several orders of magnitude better performance.)
Purely functional data structures by Chris OkasakiLet Over Lambda forgot by whom
https://www.youtube.com/watch?v=0nbkaYsR94c Not technically a book, but watch it anyway.
⬐ neutronicusLet Over Lambda is by Doug Hoyte.⬐ c3534l> Purely functional data structures by Chris OkasakiIs that really language agnostic? I don't know how you could follow along with it without knowing some ML or Haskell.
⬐ mtalantikiteYeah, I had a hard time getting into that book the first time around due to a lack of ML knowledge. Once you have some ML or Haskell I'd say it's worth a read.⬐ weavieYou would need a cursory knowledge of ml to be able to understand it, but you can implement the examples in plenty of other languages. The principles taught are fairly universal. (At least for the first half, I haven't made it beyond that yet...)
Haha, I was going to post the same thing! If you like Joel Spolsky you might like this video where he angrily abuses everyone about how bad they are at Excel and shows you some very cool tricks:
⬐ drewmateI considered myself reasonably good at Excel prior to watching this. Thanks for posting this; I learned a lot!⬐ NoneNone⬐ slededitFor most of that I was getting so annoyed "JUST USE A TABLE!!!", but he got there in the end.For those that don't know Joel Spolsky used to be a PM on Excel back in the early 90s.
Joel Spolsky has a surprisingly entertaining video about excel features: https://m.youtube.com/watch?v=0nbkaYsR94c
I found this [1] video to be a great watch.
I read the whole thesis, because on Saturdays I read papers, thats just how I roll. He does a great job identifying the problems (especially in finance) but his only source referencing Prior Work is Endnote #7 and that's an Excel resource.This is why learning the history of your field is so important. Lotus Improv(1) was the "power-users" 1-2-3 (god I'm dating myself, aren't I). Lotus Improv essentially what this gentleman mentioned (it semantically separated the segment UDTs&UDF's) except in a cleaner fashion.
Let's examine that screenshot Lotus Improv screenshot briefly and allow me to espose its structural advantages over a traditional spreadsheet-
RE: Share - Worksheet - Untitled 2 - You have your traditional values of the cells in the primary pane. The selected cell is in grey. The derived value is in the cell. The expression evaluated to generate that value is in the bottom pane.
- An important thing to note is that there is an inherent hierarchy to your data (is-a, not has-a, sadly), which makes aggregate analysis easy right out of the box. No need to move into a OLAP star-or-snowflake schema BI platform. (This structure is displayed in the 3 rows of the bottom pane of the [Right Window])
- In "Untitled 2" to the [Left-bottom], you have annotations so you're somewhat self-documenting.
Joel Spolsky had an hour long talk on Excel to more or less use excel "right" (2) which is worth a watch, certainly, as the world is stuck in Excel. But for the most part it's constructing hacks around fundamental problems that exist in the platform. (I.e., according to his best practice methodology, you have to leave a r+1,r-1,c+1,c-1 border around all of your data. That should never, ever be required.)
It's been years since I've used Lotus Improv, so forgive me if I'm forgetting the terminology. Laudable work for an undergraduate, but it always reminds me of the old adage - those who fail to learn history are doomed to repeat it. ;)
(1) https://en.wikipedia.org/wiki/File:Lotus_Improv_screenshot,_...
⬐ tnecniv> It's been years since I've used Lotus Improv, so forgive me if I'm forgetting the terminology. Laudable work for an undergraduate, but it always reminds me of the old adage - those who fail to learn history are doomed to repeat it. ;)I've heard tales of people I know presenting their work at a conference. At the end, an old Russian professor raised his hand and said: "Are you aware of this Russian paper from 1964 that wasn't translated due to Cold War tensions that already did this?"
⬐ groovy2shoesImprov was really great, far superior to any other spreadsheet program ever. Nothing that's come since could rival it. I've always been floored that nobody has come out with an Improv-alike after all these years.⬐ pjmlp> This is why learning the history of your field is so important.Alan Kay keeps stressing this point and how many regard computing as pop culture.
I agree, when one delves into the history of computing, programming language research and OS design, there are dozen of pearls waiting to be brought to life again.
We could be so much more advanced, if many startups took the time to research our history instead of poorly reinventing the wheel.
⬐ paulryanrogersCould it be the difference between engineering and research/science? Often programmers are just trying to get something that works on time and under budget. And the hardware today is less constrained than the programmer's schedule.Imagine being told to weave something overnight, the only needle is lost in a haystack. But there are plenty of thin sticks on the ground and some thread. Oh, and the product is expected to last 3 months at most.
⬐ sitkackFindability and access is also an issue. The old papers are still locked up behind an ACM and IEEE paywall. Only those lucky enough to have a campus wide access policy can search and even then, the search interfaces are slow and or horribly broken.Maybe with virtualization, containerization, etc we can archive executable environments that people can still play with in 20 years. The sands of time are brutal to papers, code, executables and people.
⬐ groovy2shoesIt doesn't help that terminology has changed drastically in all that time, and that oftentimes some original paper that slipped under the radar is the only paper that uses some terminology. Alas, there's no way to search based on raw ideas!⬐ sitkack⬐ pjmlpThis is why I think papers should be annotatable and linkable by end users. Often we don't know how a paper fits into the knowledge graph until 10 or 20 years after publication.I used to pay for ACM Membership, why does everything need to available for free?Also anyone that goes paper hunting and doesn't want to pay, can with some effort, also find drafts of most papers.
That is the biggest problem and it wouldn't change if ACM and IEEE were free.
People need to be willing to search for them.
⬐ sitkackI also used to pay for ACM Membership and I would often get banned for "excessive search activity". Semantic scholar, arxiv and citeseer are 10x better than the ACM. The ACM archive is total mess, bad search, slow access, no annotation. Looks like the same broken cold fusion site from the 90s. It is a chicken and the egg problem, making search better will encourage and enable more people to find untapped knowledge. The ACM could open up every paper older than 1990 and not have it effect their bottom line. So many other income streams than the library of old papers.
The reason for this is simple. Excel is1. An incredibly powerful tool.
2. No bar of entry (cost aside, true in corporate environment).
3. Very gradual learning curve.
4. The efficiency gain vs time invested is exponential.
Power Excel users, much like their VIM/Emacs counter parts don't use a mouse. It is just keyboard short cuts [1][2]. This makes them insanely productive.
⬐ gravypodI can vouch for this. I thought spreadsheets where stupid and useless until I was forced to use them by my boss at the CS department here for grading. I didn't realize how easy having your computation and data being in the same place.It makes it slow, but usually for these sorts of things you want your data to be available on the scale of days, not nanoseconds so it works out. We have a complicated grading sheet here that manages all of the students information all one the same sheet in a google drive. Grades, attendance, recitation attendance, and at the end of the class homework.
It also verifies the test answers against the correct ones to make sure we score exams correctly.
Excel is great for fixed sets of data that need simple map/reductions & input verification because that's all we really use it for. After that move to something like Python&Numpy/R/Julia/Matlab.
⬐ wuschel⬐ inopinatusThe nice thing about Excel is that it does resemble a functional programming tool.Would love to work with a replacement, even if it is some sort of Pandas/Python/Matplotlibb derivative - but it takes too long to set up things with these tools, and it seems not all operations are as trivial as I want them to be.
⬐ gravypod⬐ amyjessYea, for exel you can use "=(function)" and you're done! In Google Sheets you can also add in additional JavaScript code to run in the sheets.I used to work at an NLP company, and we made extensive use of Google Sheets for doing P/R/F calculations on the results of various tests. It was so useful.I know of a retail bank using a huge and complex Excel spreadsheet for their entire loan approvals process.Did it have bugs? No-one knew.
⬐ walterstucco⬐ adakbarcould be the same Bank I know where they use excel for everything⬐ ayuvar⬐ bsg75I can personally guarantee you it is literally every bank.Even if they have a fancy tool, someone is using an Excel spreadsheet to figure out how to subvert it.
> Did it have bugs? No-one knew.And there is the reason Excel (and spreadsheets in general) are dangerous.
⬐ gokhanI've seen software specifically made to tackle such complex processes and they were buggy as hell. And I've seen 100+ connected Excel spreadsheets managing $500M+ yearly transactions of the buying department of a white goods manufacturer, it was a work of art.⬐ porkerI didn't realise you could connect spreadsheets together. Thanks for new knowledge!⬐ goatloverWhy would anyone do that? There are much better tools for handling complexity on that level. That's insane.⬐ eon1Two words: Corporate IT.Another two: Onboarding Process.
Couldn't agree more, in Indonesia Oil and Gas Upstream Special Task Force use Excel for almost everything, we try to replace Excel with many good app, but alas, when an app failed we just switch to good old Excel, someone even created Montecarlo simulation in Excel!⬐ JackFrIndeed -- the most compelling reasons to move trading desks off Excel are non-technical. Model auditability and traceability are no longer 'nice-to-haves' but are compliance requirements.⬐ VMGIs there any open source framework that has an excel-like GUI and good integration with standard coding tools?⬐ taserian⬐ vegabookThere used to be a couple of them for Python, but it seems they've died out.- Resolver One ( https://en.wikipedia.org/wiki/Resolver_One )
- Project Dirigible ( https://github.com/pythonanywhere/dirigible-spreadsheet )
⬐ JBReeferLibreOffice, but it's not as good as Excel for the upper-end stuff. Microsoft doesn't have an empire built on Office by accident.The efficiency gain vs time invested is not exponential. It's linear at best and plateaus after about 5k hours. It is this plateauing of the curve that is the biggest reason power Excel users move to R or Python. That was certainly my experience after eeking everything I could out of Excel in 15 years of trading floor fixed income. A visual 2d paradigm is excellent for quick productivity but is severely limiting as complexity and data size rises. Even with VB.⬐ NoneNone⬐ skaPeople really love it because they can do business analysis without hiring programmers or becoming them. And spreadsheets are a great model for a constrained set of problems.However, it's not all sweetness and light. Excel even gets some basic calculations wrong - and those ignorant of its quirks happily propagate those errors. More problematically, it can easily be pushed to the degree that your modeling is really beyond the tool or spreadsheet's design strengths without knowing it. And debugging is a pain in the ass. As a result lots of erroneous outputs get presented as meaninful.
⬐ joelthelionCan you version/diff it though? Basically can you have a semi-sane software engineering process when it becomes big?⬐ moftz⬐ gaurYou can track changes within Excel and with Office 2016, there is a comparison and merge tool included (that's been long overdue). But since the files are binary encoded, there is no external way to track changes unless someone wants to write up a parser for the XLSX format that can keep up with all the new features that MS adds every release.⬐ kofejnik.docx and .xlsx are zipped xml, there are git plugins that produce reasonable diffs⬐ bri3dFor what it's worth, XLSX files aren't binary. They're just XML in a ZIP. They're not particularly nasty to diff once they're extracted - actually, I think they might do pretty well in Git.⬐ vsgxvhdxhEver tried editing the xml and rezipping? Also on examination you might notice the odd binary blob in the xml..⬐ bjoernsThe diff is actually not really straightforward - a sheet is two-dimensional and you have to work out column/row inserts/deletes (which themselves are intertwined) before you can even start looking at cell changes. So it's quite non-standard stuff.> 4. The efficiency gain vs learning curve is exponential.Does this actually mean anything?
⬐ ska⬐ walshemjIt's mostly hand waving, but I think the OP was trying to point out that at the low end, a modest investment in training/learning gives great results in efficiency.This is true! Then you hit a pretty hard wall with the limitations of the tool.
⬐ SkyMarshalI think it means the efficiency gain is exponential (assuming the learning curve is anything less than exponential).⬐ gaur⬐ NoneYou're just rephrasing nonsense. What is "the efficiency gain is exponential" supposed to mean, in concrete terms? This is just empty manager-speak.⬐ SkyMarshal⬐ theoh/sIn the traditional, original sense of the term, learning curves are (presumably) asymptotic to a horizontal line representing total competence.Somehow, sloppily, "steep" has come to mean difficult to learn, rather than quick to learn.
In the original version: A steep learning curve means quick learning at the beginning. A shallow curve means that it takes a long time to build up skill.
None⬐ DigitalJackYes. He is saying as you become more competent using excel, the efficiency increases exponentially.Probably true for smallish values of competency, but it must be logarithmic after that.
⬐ jackmottprobably a sigmoid function⬐ gaur> He is saying as you become more competent using excel, the efficiency increases exponentially.OK, does this mean anything? How have you quantified efficiency? How have you quantified "learning curve"? What data do you have supporting that the relationship is exponential?
⬐ DigitalJack⬐ threatofrainBeing obtuse is not a desirable attribute, less so deliberately.⬐ gaurAsking people to say clearly and concretely what they mean is not obtuseness.Nobody here has been able to elaborate on the initial statement "The efficiency gain vs learning curve is exponential". People are just rewording the sentence slightly and passing that off as an explanation. That seems to indicate that nobody knows what the statement means because the statement is vacuous.
To paraphrase for Guar, he's asking whether someone means "exponential" or exponential. By "exponential", I mean my feelings.There are also many downsides⬐ digikata⬐ bsg75Don't know if this is the best study, but it contains an overview of many studies that show that excel calcs tend to have huge error rates.http://panko.shidler.hawaii.edu/SSR/Mypapers/whatknow.htm
Of course that raises the question would any other software environment have a lower error rate?
⬐ gravypodYou can say that about every technology but the question is if the good out ways the bad in your specific usecase.⬐ valarauca1Yes and No.If all the data you receiving is also coming to you as an Excel format (csv, xls, xlsx), but with major differences in formatting, or wholly inconsistent formatting. Now you have a multi-month long project just to have a consistent import script. Replacing a 1 second task done 2-3's times a day with a 4month project has an ROI on the scale of decades. Not worth it.
Then you add visualization. What is 3-4 keystrokes in Excel is a lot of back of forth, learning a new library, ensuring it works on your system. Vetting the visualizing, dealing with that weird bug on the triple line double axis line chart.
Then you have to validate integer handling and mathematics to ensure your newly written Python, Julia, etc. handles the same as your well vetted Excel Spread Sheet.
Replacing that one slow bloated spread sheet is now nearly a year long project which requires a new employee who will have comparable pay to the person who ALREADY operates excel.
⬐ Swizec> spread sheet is now nearly a year long project which requires a new employee who will have comparable pay to the person who ALREADY operates excel.And now you have a scalable system. You can go from something one employee takes all day to look at 2x/day, to something anyone in the company can see in real time on a dashboard of some sort.
Is that worth it? Depends
All at the expense of reproducibility, testing, auditing.Quick and very, very dirty.
⬐ hyperbovineLol, seriously? Martin Shkreli? I had been wondering what hole he crawled into.⬐ flamedogedoes it have regex search though?⬐ JoBrad⬐ s_q_bIt's pretty easy to get RegEx exposed in Excel via VBA/extensions⬐ valarauca1This is the big key. A few short VBA macro's can give you regex's and cell swapping. Now you can conditionally swap the programs in other cells, via the contents of other cells.You have a Turing Complete spread sheet.
⬐ dsp1234At one point, VBA in Microsoft Word was my only available programming outlet. I was able, easily, to access DirectDraw and create a faux screen saver. DLL imports are available in VBA, so the entire win32 API is available (in addition to the normal Office automation stuff like sending email, modifying spreadsheets, etc)⬐ eon1Yeah, in a similar situation right now and I find the DLL thing is relatively unknown and incredibly powerful if used well. Recently this 'robotics' evangelist keeps trying to rope us into spending a few mil on his automation and I keep showing him up by automating the same stuff right out of Excel for little to nothing.This is digital Stockhold syndrome.Excel is something managers and executives can understand, so it became the default language for data analysis. Now technologists trapped using it have to create ex post facto justifications for why it's really "just misunderstood."
Excel is massively slow, makes it easy for beginners to make massive mistakes, computes lots of types in very odd manners, performs floating point operations wrong, and leads to spaghetti code that is a rat's nest of incomprehensible cross-references.
Worst of all, the lack of code path visibility usually leads to a bus factor of 1.
Sure, one can learn to operate Excel for data analysis with a decent level of efficiency, in the same manner one can cross the Pacific in a canoe, but both are still terrible tools for the job.
⬐ NtrailsExcel is terrifying. Each employee has taken the same concept and written their own bespoke tooling around it which probably has at least one bug. These are "copy and pasted" around a bazillon network drives and then passed on to other people who will modify the undocumented process based on their best understanding of what they think it does (or what it was meant to do...?).I can still take my ad hoc SQL query data and run decent analysis and produce graphical summaries in less time than it would take me to setup the boilerplate I'd need in C#.
Arguably something like Matlab or R would be similarly quick for a lot of things - but I'm not even slightly sold that they are safer based on my observations of their use. I've certainly seen plenty of formal code that was less readable than a decent spreadsheet.
I'm not really a fan of excel tools, and tooling. VBA has made me want to actually smash my computer in the past. But to claim that it isn't incredibly powerful at working with a few megabytes of raw data is flat out wrong.
⬐ valarauca1You are projecting so hard I could show PwerPoints off your forehead. I take it you are rather unhappy in your line of work?I'm just a lowly DBA re-posting and summarizing comments [1] for karma.
⬐ threatofrain⬐ oli5679Do these videos have anything to do with beginners making mistakes, floating points and other type conversions, bus factor of 1, spaghetti code, etc?All I see is the same old Martin Shkreli video that has been floated around before, and all you see him do is 'Vim' around as he explains his thoughts -- not on Excel, but on company financials.
Also, if you post a lopsided list of pros, it makes sense to the audience to see someone else post a lopsided list of cons. But then you reply with pettiness. Why?
Lots of the tasks carried out in offices are not technical enough to suffer from the issues you correctly identify after a given hurdle. I work as an economist in a government department, and a lot of the analysis involves ad-hoc projects processing data from different sources and doing some basic plotting/elementary calculations. Excel is perfect for this, but if something is too technical/repetitive it becomes less suitable.⬐ IndianAstronautNot to mention excel errors have huge consequences.http://www.bloomberg.com/news/articles/2013-04-18/faq-reinha...
⬐ leshowyou mean Stockholm?⬐ None⬐ jerfNone⬐ Smaug123I thought it was mildly witty, given the subject matter.Everybody's right. Excel is a powerful, flexible tool that also has almost no guard rails and all but begs people to make profound mistakes and huge messes. There's too many people who sneer at spreadsheets when they should be using them, and there's too many people who use them when they shouldn't.No contradictions.
It would be interesting to see if anyone could get some power Excel users together and construct a next-generation spreadsheet that encouraged better practices and worked to prevent huge messes. Spreadsheets are like SQL, where the initial release was so far ahead of its time that it managed to entrench itself into the very fabric of computing, even though it's long overdue for a reimagining.
⬐ claytonjyCould you say a bit more about what you'd like to see in a "reimagined" SQL? Are there any serious efforts to replace it?There was a comment thread around here a week or two ago where someone pointed out it's kind of insane SQL has stuck around so long, and no one could point to any worthy potential replacements.
⬐ gregw134⬐ s_q_bVisual query tools like Tableau don't seem to be going away. I'd love to see an effective open-source alternative to Tableau that doesn't require scripting your own D3 website.⬐ theoh⬐ nradovButler Lampson makes the point (in a recent set of slides) that relations are a good base for DSLs: they have enough complexity to model graphs, functions, sets etc.That thought has made me wonder if logic programmming has something to offer in the design of new APIs.
There's Tutorial D but it probably doesn't qualify as "serious".https://en.wikipedia.org/wiki/D_(data_language_specification...
⬐ jerfSQL in fact deviates from true relational theory, in which the "cells" of a table could themselves have additional structure rather than just being "a string" or "a number". Cells could also be truly absent. SQL's NULL, while something you can make your peace with, could use some tweaking with 21st century experience. SQL's syntax has acquired a lot of cruft over the years to deal with new features... in fact in that sense it reminds me of the evolution of OpenGL and the way it acquired extension after extension until finally it needed to be broken apart into Vulkan and CUDA pieces (to brutally summarize the situation to the point of inaccuracy; please try to see what I mean rather than pick nits with that).More controversially, I question the entire intent of making the core query language something that is putatively declarative, but then in practice often requires extensive engine-specific annotations to tell the engine how to actually do the query. (More on that https://news.ycombinator.com/item?id=3506345#3507281 ). I think RethinkDB's query language was much more imperative, because of the level of development resources they had, and I bet it actually worked out OK. However, even if I could not sell the development world on making SQL++/SQL-replacement non-declarative, we certainly could do a better job this time around of separating query strategy from query contents in some deliberate manner, rather than hacking crap up.
Imagine if, for instance, you could feed the query optimizer a query, get back a query plan that was actually manipulable and executable, tweak that to your tastes, and then send it back to the DB, rather than working via hints and circumlocutions and hopes and dreams.
It would also be nice if SQL were more composible. The serialized version of SQL is not practical to use string manipulations to combine two queries into a larger query. Many languages have libraries that permit this, but they're always second-class citizens. If I were redesigning SQL I'd want something that handled this more cleanly. I'd seriously consider something RethinkDB-esque in the sense that it didn't have an "english" serialization, but was purely symbolic, leaving it to language authors to figure out how to best represent it in the local language.
Also, bear in mind that most if not all features I describe in this post exist in databases already. (Not sure about that last one.) What I'm saying is that SQL integrates poorly with all that, not that the features don't exist. Recursive queries and common table expressions also seem ripe for some serious rethinking. Plus I think for a long time SQL really kinda limited the sort of DBs that would be produced because if a feature integrated poorly with SQL, it was a lot less likely to come out. (In particular, structured cells took IMHO forever to come out. Possibly the massive market failure of "object databases" also scared DB developers off from that feature too, though. They aren't the same thing but may be closely enough related.)
⬐ nradov⬐ coldteaMost modern relational databases now allow the cells of a table to have additional structure through the use of SQL/XML. We can query into the contents of a cell using XQuery.>There was a comment thread around here a week or two ago where someone pointed out it's kind of insane SQL has stuck around so long, and no one could point to any worthy potential replacementsSQL is based on relational algebra -- so it's the model with the best theoritical justification out there, even if the syntax could be improved.
It's the other ad-hoc solutions that is crazy that they keep getting suggested. SQL/RDBMS were invented because we had those (key stores etc, tree dbs) and they were crap.
I fully concur. This is an excellent summary and suggestion for future progress.The barriers to moving beyond Excel can be overcome, but it will take some serious effort on many fronts. Both Excel and SQL embody genius concepts, but are such poor implementations that it is easy to conflate the cruft with the advantages.
⬐ eon1⬐ lmmAlmost all of these are done or do-able, though. They're just not right there on the surface.⬐ jlg23SQL is not an implementation but a specification and thus cannot be compared to Excel, a very specific implementation of non-monotonic dataflow programming.Regarding your "stockholm syndrome" comment above: Someone in his car hears a PSA about "some guy wrong-way driving" on the very road he is on and thinks "one? hundreds!". Unless you can beef up your argumentation you are that guy.
⬐ Lio> SQL is not an implementation but a specificationFor clarification, is the GP referring to Microsoft SQL Server when then they say 'SQL' or do they actually mean SQL?
Microsoft's product naming convention is confusing IMHO.
⬐ EdHominem> Unless you can beef up your argumentation you are that guy.That's fallacious too. I can be right, even if my argument is incorrect or unconvincing.
Warren Buffet and Nate Silver are both driving against traffic and both of them are righter than everyone else combined.
> SQL [...] cannot be compared to Excel
What Excel and SQL have in common is that they're both a first attempt at a solution to (different) problems, and they've been too successful to properly iterate on. That's why everyone uses some proprietary extensions to SQL and everyone extends Excel with VB or C#.
https://www.herculus.io/ was doing the rounds a few days ago - the idea seems to be a spreadsheet with a type system.⬐ willhsladeI'm an Excel power user. I think Microsoft is moving in the right direction, with the addition of Tables, PowerBI, PowerPivot and R in SQL Server. What I'd like to see in Excel is:Hotkey training built into Excel Python as an optional language along side VBA Proper Data Tables with Types and Indices, or even SQL in Excel. Regex Search over Columns PowerPivot use case training Web publishing of reports made stupid easy
⬐ eon1Both Python and R can be used in Excel via several addons.Python:
R:
http://rcom.univie.ac.at/download.html
However, officially MS have chosen to move ahead with Javascript for add-ins and VBA-like automation:
https://dev.office.com/docs/add-ins/develop/understanding-th...
http://rockthecode.io/blog/javascript-and-excel/
As for SQL, you can use Data Connections from the GUI or ADO with JET/ACE in VBA to query Excel sheets, CSVs, etc as you please.
Regex can be used with the VBScript.Regexp object, but it's a slightly funky (perl-like) syntax and not a great implementation.
I hate this trend.IMHO there is mainly one way to get attention - it's to get (great and instant) emotion from the user. You can give good emotions or bad emotions.
Personally, I think, that to create a good emotion takes much more effort than to create a bad one. The website/product can say how great am I, but it will not 'click' as instantly as someone telling me I am a dumb baby and I suck [0][1] or I am not superior mere mortal baboon [2], for which most people will get instant rage and will start flame wars in whatever comment section, as there "is no such thing as bad PR".
Most popular writer/bloggers in my country have created these dipshit arogant characters (I tend to believe that they are "normal" people, but they clearly know what sells) who always say that they are richer, smarter and better than you. They create stories about "cheap restaurant breakfast for 60€" and so on, though the most interesting thing is that people buy their shit and then rage on whatever websites about how dared the writer call them a dumbass homeless bum.
[0] https://www.youtube.com/watch?v=0nbkaYsR94c
⬐ tominousIn my line of work I often see tech vendors and consultants trying to plan infrastructure changes using Excel (or worse, tables in Word, but we won't go into that). The results are horrendous.They start with a manual inventory of the configuration in question, type it ad-hoc into a spreadsheet, then go through making manual changes, notes and additions until it feels good. There's lots of highlighting, different coloured text, etc like layers of sedimentation. Formulas are only used for sums and counts in an ad-hoc way.
The end plan always contains mistakes and omissions that really hurt during deployment time.
The correct way requires discipline: immutable input sheets (from machine-generated config dumps), a parameter sheet, output tables (with consistent usage of formulas with "$" notation to lock rows and columns), and cross checks with conditional highlighting.
Unfortunately this is really hard for people without programming instincts/experience. Good job security I guess. But if you can do it, it's both faster and safer than the manual free-form method.
⬐ astrobe_⬐ IANADSpreadsheets put together two aspects: - data (raw and calculated) - presentationThe original mistake is that both were made interdependent. Excel tries to fix that the hard way by introducing various reference modes, a lot of "do what I mean" magic and even nested spreadsheets/databases (aka "tables").
But it still leads to scary bugs, as the video shows: if you do things the wrong way the numbers you derive from your data are wrong and you don't even see it if you don't pay attention.
While watching the video I was wondering if all those features were brilliant or insane. The answer to me is that it's insanity.
If nothing else, HTML has shown for a long time already how one can - one should - make the content (data, programming) separate from the presentation. This idea has to be back-ported to spreadsheets now.
The first half of the video is really just keyboard and drag'n'drop tips, while the second half is really just brutal hardcore programming: "match", "if", "lookup", references, indirections, "debug mode", ...
Acknowledge that spreadsheet documents really are programs. Make a spreadsheet DSL then apply ideas from Lighttable to make it as "visual" and "easy" as possible.
⬐ Senji⬐ pjmlpYou can import SQL dumps/CSVs in excel⬐ greggybExcel formulas are a spreadsheet DSL, and one with massive adoption.Worse, try putting all the user stories and bug reports into Jira or similar tools only to have managers request to duplicate it and have email ping-pong about project status with Excel sheets as attachments.Or place those Excel sheets on Sharepoint.
⬐ harperleeBut the problem you point to seems more related with the process than with the technology, right?In any case, there is this Excel alternative, Resolver One, which has what I believe it is a killer feature: you can expose the Excel sheet as a web server. (With Excel, you would need to have a Sharepoint server for that, I believe).
That way any user can iterate a lot on an initial excel sheet but then their group can just work with the web version as an "immutable structure" sheet, and if a change needs to be done, you don't need to go and contact someone with appropriate permissions to upload a new version to the server, etc.
⬐ jlongster⬐ vehementiThat product is dead: https://www.resolversystems.com/⬐ harperleeDidn't know! It would have been good if they had open sourced the product, seeing that they no longer support or sell it...> They start with a manual inventory of the configuration in question, type it ad-hoc into a spreadsheet, then go through making manual changes, notes and additions until it feels good. There's lots of highlighting, different coloured text, etc like layers of sedimentation. Formulas are only used for sums and counts in an ad-hoc way.Oh man that hits home hard. And all the sums and counts are in random cells "beside" the tables right?
⬐ unixheroThank you for the tidbit! Picked up a few things here :)⬐ cm2187In my line of work I see an organisation regularly burning $50m in IT budget for developping a software that is completely flawed, horribly designed, not fit for purpose, though take years to develop and for that amount of efforts and money, could be much more efficiently done in Excel for 1/10000th of the cost!This week I am trying to extract data from a data cube that was created for reporting. Problem, the cube was designed so that only one node can be retrieved at a time, and so if I need a bulk retrieval, which I need, it needs to be queried a billion times, sinking the server, timing out the queries, taking hours. The IT team is trying to figure out a way to run an overnight job so that it sinks the server during quiet hours. Basically these guys designed a vault where you can only add data but never retrieve it. They should be fired.
[edit] and before someone starts to think "yeah but this is big data", the underlying populated numbers in this cube would fit in a 5MB spreadsheet.
I am not sure that the audience of hacker news realises how terrible is the reputation of IT and developers in large companies from the business side. They are seen as a world of hyper-bureaucratic, common sense-free, business sense-hostile, half-assed lemmings.
Excel is not great but one has to have a hard look at the alternative.
⬐ TeMPOraLAs a programmer I deeply appreciate the hate towards IT in companies and replacing Excel with complex internal apps. Those apps pretty much never capture the actual workflow (by virtue of requirements being provided by management instead of actual users), and even if they did, the workflow constantly changes. Excel spreadsheets may be cringeworthy sometimes, but they're used by people because you don't have to be a trained expert to make them work, and you don't have to submit requests to other departments to change something simple.⬐ cm2187⬐ flukusFrustration would be a more appropriate term than hate.⬐ pc86⬐ MagnumOpusRarely.> As a programmer I deeply appreciate the hate towards IT in companies and replacing Excel with complex internal appsYou have to know the right point at which to make the transition. We had an Excel spreadsheet used for managing 10-digit sums of investments, held together with sticky tape, integrating SQL queries, XLL C++ addins (plural), Corba, creative use of the Excel solver and pivot tables in addition to custom data overrides (being Excel, without version control or sane error handling).
That point where operational risk exceeds the benefits of flexibility is a sign that you want to get some programmers on it - and we did, even at a cost of 10 programmer person-years or more in replacing that spreadsheet, and a horrendously slow and cumbersome process to get even trivial bugfixes or features added.
> I am not sure that the audience of hacker news realises how terrible is the reputation of IT and developers in large companies from the business side. They are seen as a world of hyper-bureaucratic, common sense-free, business sense-hostile, half-assed lemmings.Usually decisions like building a $50m application aren't made by devs but by clueless managers.
I do agree with the sentiment though, I see a lot of wheels reinvented everywhere I look. Recently I had to deal with a scheduler and a file remover/archive that were built in house. God know how many hours were wasted on this but the whole thing would work better as a batch script and a scheduled task.
⬐ cm2187⬐ user5994461I agree that there are often absurd requirement on the business side, just because they don't understand the tech and the problems a minor feature in their mind will cause down the line.That's where project managers/change people are supposed to help, acting as an intermediary, understanding both the tech and the business. But in my experience they usually understand neither the tech nor the business and are adding more confusion, the whole thing works better without them.
But the state of corporate IT is sad to look at. I work in a large organisation where one of the executives was telling us proudly at a townhall that we have more developers than Facebook and Google together (though we are in the financial sector). Well, to say the least, we have little to show for it. Nothing works properly. Our core systems still run on programs written decades ago and we have to pull people from retirement if, after having exhausted all other possible options, we have to make a minor change. The network drives have a top speed of 1MB/s, and hang sometimes for minutes. As for the business applications. Any minor change results in ETA in months if not years and multi-millions quotes.
I remember discussing with some devs about an Excel VBA addin they were taking over from the business. They re-wrote most of the features in .net, which is very good, but somehow half of their application was still written in VBA, and I mean tons of VBA. I wondered why. They told me the library they were using only allowed to expose a function to Excel, but not to hook to or manipulate the UI. So they have written a VBA wrapper around all of their features that calls the .net excel functions.
There was a long silence after I showed them how they can get a handle to the COM interface of Excel in a single line of .net code using that library.
⬐ anexprogrammerDon't forget the political.A lot of large corporate IT is outsourced to the likes of HP. It's not unknown for them to put in the least qualified folks they think they can get away with - which can often mean in the case of unusual infrastructure, libraries or languages that they sent someone on a basic training course the week before starting. This leads to the quality you might expect.
The two most "interesting" contracts of my career were in financial. One was trying to reverse 25+ years of bad decisions and no end of duplicated, wrong, and unsanitised garbage in the live DB along with some dependency on CICS that fortunately I was far away from. The other will remain undiscussed. :)
⬐ flukus> though we are in the financial sectorThat explains it. I'm working in finance at the moment too, it's where you find the worst developers/management/products.
I'm betting the current situation there is caused by some combination of technical debt and the dead sea effect (also coined by spolsky I believe). Once things get to a certain point competent people leave and by attrition you're left with the ones unemployable elsewhere.
⬐ lordnacho⬐ bpizzi>> though we are in the financial sector > That explains it. I'm working in finance at the moment too, it's where you find the worst developers/management/products.I second that opinion. There's a lack of depth of knowledge on the IT/Coding side, and people think it's just another little cog that they need to get their deals done.
So you get things like
- Complex derivatives calculated on Excel. You wonder why people make millions on this? It's because you never really know what the heck you're calculating.
- Missed deadlines that should be cron jobs. I remember a guy coming into the office, down a good few hundred grand because he'd forgotten to exercise options that had a dividend on them. I mean that's just stupid, if you had a proper system it would tell you, and the calendar would be synced with Bloomberg.
- Ridiculous budgets. A friend at a major bank is on a $27M project that is essentially a calculator that adds two numbers together. Other people are attempting to break into HFT with zero competent coders, no network other than what can be leased cheaply, and no systems engineers.
- Balls of spaghetti of unimaginable proportions. One c++ guy at a big bank told me he takes 7 hours to compile. He's writing execution code, which you'd think would be relatively snappy.
⬐ flukus>- Balls of spaghetti of unimaginable proportions. One c++ guy at a big bank told me he takes 7 hours to compile. He's writing execution code, which you'd think would be relatively snappy.How long should it take? The last time I did anything of note in c++ was on a computer of 2000's vintage and 7 hour builds were pretty common.
⬐ lordnachoA few minutes. And that's for a pretty comprehensive HFT system, cleaned.If you're taking hours there's just a lot of dependencies that could probably be refactored.
⬐ rwallaceIf you're taking seven hours to build, you have too few computers per programmer. Compiling C++ parallelizes well, and computers are dirt cheap compared to the cost of employing people.> Our core systems still run on programs written decades ago and we have to pull people from retirement if, after having exhausted all other possible options, we have to make a minor change. The network drives have a top speed of 1MB/s, and hang sometimes for minutes. As for the business applications. Any minor change results in ETA in months if not years and multi-millions quotes.That's what happen when IT is treated as a cost center instead of a co-leader of innovation together with business. Bad hires, no real objectives, no liberty to innovate: do that constantly for decades and you end up in the situation you describe.
You can't really blame the IT guys that are thrown in that mess. It's almost always a top management fault.
> There was a long silence after I showed them how they can get a handle to the COM interface of Excel in a single line of .net code using that library.
Looks like they are lacking a good tech lead, why not apply yourself? :)
> how terrible is the reputation of IT and developers in large companies from the business side. They are seen as a world of hyper-bureaucratic, common sense-free, business sense-hostile, half-assed lemmings.Large non-tech hyper-bureaucratic common-sense-free companies are especially hostile toward good IT and dev so good people just don't work there ;)
[and even if they did, they still couldn't get anything done]
⬐ pb8226⬐ NumberCruncherThis is exactly the truth. The environments are suffocating and good people are only moderately more productive and don't stick around long.⬐ cm2187Somehow IT manages to be an order of magnitude more bureaucratic than the rest of the company. Perhaps a case of IT people trying to be more catholic than the pope in a bureaucratic company!Some people in corporate BI, when confronted with a problem, think "I know, I'll build a cube." Now they have two problems.Credit goes to: https://blog.codinghorror.com/regular-expressions-now-you-ha...
⬐ x1798DE> This week I am trying to extract data from a data cube that was created for reporting. Problem, the cube was designed so that only one node can be retrieved at a time, and so if I need a bulk retrieval, which I need, ...Sorta OT, but this is one of the more sci-fi sentences one could say that actually makes sense.
⬐ pb8226As consultant having to advise and work with IT departments. Could agree more on the reputation comment.⬐ carlesfeSomebody said that before starting a startup you should evaluate what your competition is, and that includes Excel.Many, many startups are competing against a moderately-complex Excel sheet, and they don't even know that.
⬐ cm2187I am frustrated I can only upvote once!⬐ carlesfe⬐ davidivadavidUpvotes mean nothing, displays of gratitude mean a lot. Not my quote, but I'm glad you liked it!⬐ snowwrestlerIf HN were an Excel sheet, you could upvote as many times as you want! :-)⬐ cm2187I could automate in VBA me upvoting. Try to do that in a business application!Many startups are a nicely designed version of an Excel built-in template.⬐ mcmoose75Absolutely agree- however, in many cases these types of companies can still be decent businesses. Being able to work with and adjust "moderately complex" Excel sheets requires a certain level of training and skill- often these startups can remove the requirement for expertise to perform a task, which can be worth quite a bit to a business.⬐ TeMPOraLThe biggest feature of Excel is its flexibility. That moderately complicated spreadsheet can and does change all the time, to accomodate new requirements and optimize workflow. A startup (or the internal IT department) doing one-size-fits-all solution can't really do that.⬐ emodendroketYeah, but a programming language would be even more flexible, and yet nobody says they don't need software because they have all the tools to implement its functionality themselves.In fact, I worked on a project that largely aimed to replace a bunch of internal spreadsheets because giving groups a spreadsheet to maintain is a little like handing Phaeton the reins to Apollo's chariot.
⬐ ChoHag⬐ elmigranto> Yeah, but a programming language would be even more flexibleThen it's convenient that Excel both is a and has a programming language, eh?
⬐ TeMPOraLSure a programming language would be even more flexible, but it does not have an instant feedback loop like Excel does. Not to mention that the extra flexibility comes with the cost of dealing with all the arcane bullshit programming involves - toolchains, APIs, what have you. Excel is flexible enough to be useful by non-tech people and doesn't bring in so much cruft.⬐ emodendroketMy intent was to highlight the flaws of the argument that Excel is better because it is more flexible with a more extreme example.> The biggest feature of Excel is its flexibilityThis is probably its biggest flaw also — chances are you don't need all that flexibility within a scope of particular spreadsheet; and sometime you may prefer a solution that was specifically targetting this exact usecase.
First, I learned a lot from this. But, here's some light criticism:1. Joel saying "I didn't understand that question" and then moving on might have been succinct and practical, but it was just not a good reflection of him.
2. He acts like R1C1 mode is the only way handle relative references for the first 13 minutes. One of the first things I learned in Excel was $ to pin a reference to row or column in what he calls "baby mode". I think it's not babyish to use $ which is more succinct; you can edit the formula and see the calculated value right away. It seemed like he waited a long time to talk about that.
3. "Almost none of which you can do in Google spreadsheets" at 18:15. Sounds so pro-Microsoft, right? Yet, if you look, he's obviously using OS X, which is surprising to me, because MS Office has historically sucked on OS X compared to its Windows counterpart, and it's been incomplete: https://9to5mac.com/2016/01/21/windows-mac-ipad-microsoft-of... even though, yes, it's a lot better than it used to be. Also, Google docs is free.
⬐ ageektrapped⬐ graham1776> 1. Joel saying "I didn't understand that question" and then moving on might have been succinct and practical, but it was just not a good reflection of him.He's presenting in the style of You Suck at Photoshop, a series of Photoshop videos in the same style.
⬐ ythl⬐ jaydles> He's presenting in the style of You Suck at Photoshop, a series of Photoshop videos in the same style.In fact, I think he directly quotes YSAP at 25:34
https://youtu.be/0nbkaYsR94c?t=1534
I'd have to find the right YSAP episode, but I'm 90%+ sure that Donnie says something extremely similar.
⬐ jevinskieReading your single comment (and watching the linked clip) was very much worth my time! I never before realized that you can address Excel cells via a symbolic name instead of row/column-wise. That is very cool and probably very basic. Now I'm going to watch the rest of Joel's video!⬐ 0xcde4c3dbAt the beginning he says something like "this is basic to intermediate, but for you this is going to be stupid hard", which is from the first (?) YSAP, "Distort, Warp, & Layer Effects" [1].Regarding point 1, blowing off that question was an inside joke. I get why it comes off as dismissive and rude without context - it was originally an internal presentation - but the asker was Michael Pryor. He's Joel's co-founder at FogCreek and the CEO of Trello. It was playful banter between very senior peers.⬐ nhebbAs mentioned elsewhere, he was a Program Manager for Excel. His story ("My First BillG Review") about spearheading the effort to bring VBA into Excel is a great read:⬐ SteveCr48⬐ harryhThanks for sharing the link. I thoroughly enjoyed reading his blog. Thanks again!!4. All the stuff he starts describing at 18:40 Google Sheets will actually do just fine except the Growth Trend bit on the heights.5. 33:25 - Nope, that works in Google Sheets too. Just like Excel.
6. OK that table shit was cool though. Sheets can't do that.
⬐ spydum⬐ cdolan92tables is one of my most sorely missed features from excel. I'm really surprised it's still lacking in sheets⬐ viraptor⬐ shostackIt's missing everywhere. Sheets, libreoffice, office for Mac, anything... Ok, office for Mac does have tables, but I keep running into issues when referencing them - some values just end up as error until you change the source (even to the same value)Google Sheets is still painful in many areas. Cell/chart/pivot formatting is a big one.then there are little things like the pivot table interface. Sheets has come a long way but when I need to be efficient and effective, I roll up my sleeves and dive into Excel.⬐ blahiI remember watching this video a while ago and it is super basic. I can assure you that there are great many things that google sheet lacks.⬐ cm2187⬐ r00fusDoes it have array formulas now?⬐ harryhYa I know. Was just pointing out that Google Sheets isn't quite as basic as people think.It really should be better though. I wish Google would commit to it more.
This video is a year old; Google sheets has notably improved in that time.⬐ harryhI'm fairly confident that points #4 and #5 both worked a year ago. I guess I can't be 100% sure, but I'm preeeeety sure.Agreed on the R1C1 mode. Its very helpful to explain "this is what is actually going on behind the curtain", which is likely why he stresses it so much when speaking with developers. However, its really unnecessary in the 'real world'.⬐ JBiserkov>Also, Google docs is free.And so is Office Online. Always has been, six years now.
⬐ savanalyI am curious about the reason for the repeated potshots at Google Sheets. The way he talks about it he's playing the taunts for laughs, but usually there's an interesting story or grain of truth behind something like that.I do notice the big google watermark in the top left of the video so at first I thought maybe he was doing this presentation for some google engineers and playfully poking fun, but the description says this is a presentation he gave for the benefit of his companies Fog Creek and Trello. So I don't know.
⬐ gk1⬐ nnessThe Google watermark is there because this was recorded using Hangouts on Air.⬐ Pica_soO⬐ bitwizeYou know, how google walked into the whole GIS and Geography business and clicked its heels together once - closing the shop. I guess the very same thing is going on here- wounded pride. We fought and worked to make this happen- you can not give it away for free.⬐ jeremy_wiebeIs that really true though? As far as I know ESRI is still alive and doing quite well.⬐ roel_vI think the gp means that Google came in gis, looked around and decided 'nope, nothing for us here', and left? I mean, there's google maps, but that's it - while widely used, not exactly special.Google Sheets isn't running large chunks of the world economy yet. Maybe he'll stop mocking it when it is.⬐ jthomas0717Joel worked for Microsoft on the Excel team so it's probably a combination of professional pride and playfulness.Office for Mac was certainly a third-class citizen for a long time. Thankfully its improved quite a lot Office 2016 for Mac, and they are somewhat comparable now. That is until you have a MSSQL connection in your Excel sheet... it was surprising to find people that do that.⬐ magnetix⬐ seancI am still waiting for tables in Outlook 2016 to be returned.The big win in Google spreadsheets is that functions are nearly universally composable. Unlike Excel.⬐ sedatk⬐ throwaway287391What does that mean?⬐ seancIn google spreadsheets I can do something like:=count(filter(a1:z30>0))
where in Excel I'd have to make an extra column to store the result of the filter, and then count those.
⬐ greggybYou can use array formulas to achieve the same result in Excel.Re 3: he does say (at some point in the latter half of the video, after the OS X beach ball spins for several seconds when he tries to do something very simple) that Excel for OS X is a lot less stable than for Windows.⬐ hasenjI think he's wrong on the R1C1 mode. At least in the latest version of excel on mac, you can observe this behavior:1) In cell A, reference cell B
2) Cut (not copy) cell C and paste it over cell B
3) Observe cell A throwing a #REF! error
To be concrete, type 10 in cell A1, then type `=A1` into cell A2. A2 now shows 10. Now click cell B1, cmd-x to cut, then move the cursor to A1, cmd-v to paste. Now cell A1 is empty and cell A2 shows #REF!
This will not change if you use the R1C1 mode.
So internally it's neither R1C1 nor is it A1. Internally it's a reference to the cell object directly.
⬐ MOARDONGZPLZ⬐ rimantasI just tried this. In "baby mode" it does what you say. In R1C1 mode, A2 becomes 0 when the blank cell B1 is cut/pasted to A1. So in my experience (Windows 10, Excel 2016), R1C1 references the cell, not the object, whereas baby mode references the object.Wasn't historically MS Office first to appear on Macs?⬐ TazeTSchnitzel⬐ craigmccaskillYes. Word and Excel were ported to Mac OS before Windows even existed to be ported to, and PowerPoint was a Mac-exclusive product created by another company.He's presenting this to his company. I don't think this video was originally intended for mass consumption, but I am glad it was made available.So this is the CEO presenting to a group of people who know him and his presentation style, I think at that point much of the stuff you're complaining about can be thought of as humor or house style, especially when you consider that Joel worked at Microsoft on Excel. Context matters.
⬐ NoneNone⬐ gbrayutAs someone who watched the live stream of the original presentation from the Stack Overflow lunchroom I can confirm that the video is missing some context. Definitely a bit of house style and a throw back to some old school memes, but overall an informative and humorous training session that I am glad was made public.Joel usually gives great presentations, and recently he even started personally editing our internal company update videos (and complaining when we don't show appreciation by up voting them :-) because the first one was a bit dry.
Is there a standardized excel knowledge test and or certification? As a senior analyst excel expertise is requisite, but is very hard to interview for...I had to make an excel test just to empirically rate our analyst candidates. I know there are the excel competitions (http://www.modeloff.com/) and great resources (http://www.chandoo.com/wp), but no rating system for knowledge (even basic) of excel. Think the big investment banks would be interested in "testing" their analysts empirically before hiring them?⬐ cm2187⬐ cm2187Investment Banks do not really expect juniors to have advanced excel skills before they join. In fact they normally do not expect them to know anything about their job, outside of having a personal interest and curiosity about finance and be eager to learn.Now in practice it doesn't always work like that but I still agree with that philosophy. We should be hiring smart people, not people with knowledge. Smart people can always acquire or build the knowledge they need quickly. Knowledgeable people can't really get smarter.
⬐ bigger_cheeseFrom what I've seen at my work most of the analysts tend to use specialized BI tools rather than excel.Excel is mostly used for data transformations. Pretty much every tool can "speak" excel so typical work flow is to extract data using tool A dump it into excel. Save it and Upload from excel into tool B.
This is for business analytics rather than Finance/banking. Is excel really that common in that world?
⬐ intended⬐ soaredExcel is hugely common. More "code" (simple logic or transformation) is probably done in excel than anything else.I'd be surprised to find a normal (non tech, non SV) firm which didn't work on excel.
⬐ bigger_cheeseI work at an industrial plant. Excel is used a lot here too. But not for technical analytic work. Nothing you'd need to pass an exam to know how to do.The Analysts here tend to use programs like Cognos, SAP Business Objects etc.
Can I take your test? I work in an entry level analyst role and am curious how I'd stack up.⬐ cdolan92⬐ unfuncoyou can take mine if you want, whats the safest way to share email addresses on this site so I don't get a million ZipRecuiter invites?⬐ saganusYou could use something like mailinator to create a disposable address and go from there.⬐ soaredAwesome, thanks. You can email me, listed hereInterestingly, as part of my college (pre-University, as I'm UK, and I'm unsure what the American equivalent is (think the gap between high-school and University)) – we had to do a module called ECDL (European Computer Driving License) which was basically the Microsoft Office suite, this would apparently be a boon for employers wanting to guarantee a basic level of competence... and I still don't know Excel (but passed that course.)⬐ noahmbarrSimple, timed exercises are highly effective. I (and almost everyone I know) has one built that they use. I can look through their work and know (1) if they are really an export, and (2) if they're even trainable.Happy to share noah (dot) barr (at) GOOG's email service
⬐ stevedhIt looks like that should be http://chandoo.org/wp/⬐ somedangednameWeird - the chandoo.com link is blocked by my Internet filter at work. "Category: Made for AdSense"⬐ cdolan92everyone employed to a relevant position at our company takes a pretty in depth excel assessment so we know what they need taught on Day 1.Named ranges/tables is always top of the list!
edit: business schools usually make you take an excel course/exam your first year. Its always awful, using some absurdly priced flash-based webapp from 2003 that has such strict validation on answers/inputs, you often struggle just to input your answer because you solved it in a different manner than they expect. So, the IBs likely think you know Excel before getting in the door (which is not always the case).
⬐ oli5679Hi, I'm interested in taking your test too?There are lots of things he does that are still rookie mistakes! Like index/match without doing a strict match (microsoft: when are you going to add a shorter syntax for that??).Then he probably does that to make it visual but he should be using his mouse a lot less. F2, CTRL Enter on a range to apply the first formula without applying the formatting.
Also two data tables one above the other. As one expands it will hit into the other. That's where excel lacks a feature that apple introduced in numbers: not using a unique grid but a table being its own grid, placed as a shape on a sheet. That solves lots of problems.
Range names are not a good solution with experience. You get name clashes when merging spreadsheets, ambiguous duplicate names when duplicating tabs. For formula auditing F2 is your friend. And there are some third party add-ins to go to a reference within a formula and come back with keyboard shortcuts.
And no demo of excel is complete without showing the power of array formulas. In particular SUM(Col1 * Col2 * (Filter1=Col3) * (ABS(Col4)<4)) to do a sophisticated conditional sumproduct.
⬐ roel_v⬐ vba"index/match without doing a strict match"I that the same as for vlookup? I never understood how that is supposed to work - when do you want to not have a strict match?
⬐ abricot⬐ NamTafIt's great to categorize number ranges, like people's age for example.You have a line with each person and their age.
In you list you have age groups, looking like
Then you use vlookup to find the group that matches the age, and the non-strict will match 12 to 19, 33 to 39 and so on, returning the age group for that value.Age Age Group ... 19 10-19 29 20-29 39 30-39 49 40-49 ...⬐ FabHKIf you have large sheets, you might also care that a strict match necessarily involves a linear search, O(N), while a "non-strict" match uses a binary search, O(log N).Strict search is slower, in other words. You just need to know what you want, really. (Even if you want a strict match, it might be faster to sort once, then do non-strict match, then compare for identity).
⬐ cm2187There are cases where it is useful. Like I want to retrieve the FX rate for a given day but my time series might be missing days, so finding the nearest result is practical.Also the default assumes that the values are in ascending order, which is computationally efficient as it can do a binary search.
But very dangerous in the most common scenario.
What would be even better is to be able to tell Excel to create a hash table / dictionary in memory from a table so that it can be queried very efficiently.
But as I posted everywhere on this page, Microsoft doesn't seem to be minded to add new features. Changing the color scheme between versions, making minor improvements, keeping bugs as they are, incrementing the version and shipping it!
⬐ roel_vOh I never thought about using vlookup for anything but categorical values, but yeah your example makes sense.I like the new functions that make you not having to wrap everything in ISNA(), like ISERROR()...
FYI your last point's equation is displaying wrongly - you need to put astericks either side of "Col2". HN parses text inside * as italics⬐ cm2187⬐ willvarfarThanks!> There are lots of things he does that are still rookie mistakes!A fun allegation, given Joel's Excel history :) Joel invented VBA: http://www.joelonsoftware.com/items/2006/06/16.html
However, its quite likely he's not the world's best Excel poweruser :)
⬐ cm2187Well, that's the case for many developers, they are often not the user of their own software.The most spectacular exception is Visual Studio, written by developers for developers and one can tell. They re-write it from scratch regularly and add major features that really make sense, catering for both basic and very advanced users. Visual Studio is by far the best software that Microsoft makes.
Office on the other hand is a software written by developers who are trying to imagine what a user does with it. And it shows. No major new feature since Excel 2007, and particularly very bad at certain scenarios that are really core usage of excel (linking a powerpoint deck to a spreadsheet, formula auditing (visual studio had "go to reference" forever), a VBA editor that hasn't changed since 1999, etc.
If the dog doesn't die it means the food is good enough!
[edit] VBA is a fantastic tool, but it's frozen in time. The worst is that Microsoft did attempt to fix it, it was called VSTA, and was basically a light version of visual studio embedded in office like the VBA editor, to be able to script office in .net. That would have been a great feature. And they shipped a version, if I remember the early version of visio had VSTA. But for some reason they killed it. Let's not change the dog's habits!
⬐ clappskiWhile Visual Studio is a testament to understanding your users, it's not great software. Randomly slow downs (even with Visual Assist turned off) and hanging every time you open a moderate (~50 project) solution.⬐ cm2187⬐ waivekI am sure we can find flaws. In fact the VS2015 intellisense is a major step back. But compared to the VBA editor which litterally hasn't changed since the 90s, I mean litterally not a single new feature in 20 years! Like if that was a feature no one was using!Can you provide a source on Visual Studio being re-written from scratch? I'd like to read more on this.⬐ greggybThere are two major improvements to Excel that came in 2010 and 2013.These are Power Pivot and Power Query (Power Query is now backported to 2010 as well).
Power Pivot straight up is SQL Server Analysis Services. It's a heavily optimized in-memory columnstore database engine that is exposed natively through pivot tables and charts in Excel. The fun demo is loading up a table of several hundreds of millions of rows in Power Pivot and interacting with it in a pivot table in Excel. That was back when I had 8GB of RAM.
Power Query is a data shaping and transformation tool, with an expression language largely based on F# (though with an entirely different standard library and some major functionality changes). The single best feature of Power Query, though, is that it provides an audit trail of all steps applied to a dataset. This completely bypasses one of the major problems in Excel, that intermediate steps are impossible to recreate accurately.
Full disclosure I am a consultant with a Microsoft partner and spend a lot of time with these tools.
⬐ cm2187I appreciate that and I am sure it may be useful to people who do data analysis against a SQL server, but I see very few of those around me. Most use excel to calculate something and for the analysis behind a powerpoint deck. None of that is ever a simple sum or average of a column. If it does involve data, it usually involves non trivial calculations that are not really suited for a database. And if it involves things like aggregating P&L or any financials, unlikely an IT dept in a large organisation will leave that in a SQL server available for any user to mess with.I don't know what proportion of users Microsoft thinks it caters to with these features but I haven't met any in my 10 years in banking.
Users who do vlookups, use VBA, who need to audit a formula, who need to link a powerpoint deck to excel, etc, that I see every day. And to those, no, no new feature since 2007.
⬐ greggybAnd I see people every day using exactly these pieces of functionality to do nontrivial summarization of data, a thriving blog and forum ecosystem, and Microsoft knocking down my door because their own sales teams can't keep up with the customers wanting to use these features and the Power BI ecosystem.We all live in bubbles. I recognize mine as the above, but I don't deny that yours exists.
Joel played a part in creating VBA for Excel (and likely the other Office clients) when he was a program manager at Microsoft in the early 90s (see http://www.joelonsoftware.com/items/2006/06/16.html).I'm a developer on XL (at Microsoft), only half way through the video and have learnt a thing or two. Out of academic curiosity I sometimes ask old-timers if they ever worked with Joel. So far nobody recognizes his name, though there is next to none left in Excel who were XL in early 90s or before.
⬐ flukus⬐ oli5679Does MS not follow that old habit of C developers to put stuff like last edited by and lust updated in the comments?You'd think his name would be everywhere on the internals.
⬐ TorKlingberg⬐ yuhongVersion control systems are better these days, so you don't need to. Even us C programmers have stopped putting names, dates, revisions numbers and other gunk in the comments.⬐ vbaThey did have the code owners' name in nice formatted comments but they don't do that anymore, at least not in the Excel codebase. There are plenty of artifacts with the code owners names left behind (many having since moved on from Excel and Microsoft). Mr Spolsky was a program manager, so I doubt he wrote much if any code that made it into the Excel binaries, but I'm interested to know if he did.⬐ IntermernetHis name doesn't appear in either the Excel 97 flight simulator monolith credits, or the Excel 2000 "dev hunter" driving game, so he may have joined the Excel team after that, or they may not have been exhaustive lists.They're still very cool ways to put developer credits into enterprise software :-)
As a side note, current versions of Excel still opens and even can save Excel 5.0 files by default but no longer ships with VBACV10.DLL that converts the VBA code. It was easy to find bugs in this code when I was fuzzing using HxD years ago.⬐ vba⬐ harry8By "this code" you mean the VBACV10.DLL binary? You were using a hex editor to fuzz .XLS files?⬐ yuhongYes.I'm sorry, you're probably a nice person, kind to animals and children but still I kind of, well...Excel, just say NO. Friends don't let friends use excel. Ever.
Microsoft has no interest in fixing the bugs, the bugs are extremely costly. People regularly misallocate resources based on excel bugs. These screw ups happen at vast scale with hugs sums of money and worse.
Here's a couple of links I didn't read closely. Any web search will find so very many it's silly.
http://andrewgelman.com/2013/04/17/excel-bashing/ https://oit.utk.edu/research/documentation/Documents/ExcelSt...
Gnumeric is better than excel.
⬐ harry8⬐ mistermann"Microsoft has lots of top researchers so it’s hard for me to understand how Excel can remain so crappy." --Andrew GelmanIt really is a real problem.
People working on Excel at Microsoft don't even know of him, like never heard of his name before? That can't be possible can it?⬐ vbaI think I semi-subconsciously left that ambiguous on purpose. A fair amount of people have certainly heard of him, but when I asked a couple of old-timers in the recent past they didn't seem to recognise the name.Excel is super useful for the range of tasks where speed and transparency to business execs is more important than flexibility or maintenance.Its great to rattle something off in a couple of hours that you can email to your boss to tinker with
It's absolute hell flicking through tabs trying to find a bug or trawling through stack overflow looking at grim VBA contortions which have one-liner equivalents in python.
⬐ abricot⬐ circaWith named ranges and named tables it is a hell of a lot easier to debug a sheet.(Not commenting on VBA)
⬐ oli5679The problem is if enough people have been working on a sheet for long enough it becomes very difficult to replicate your work. this is particularly true for projects involving data processing and if they have poor version control practices.You instantly brought me back to the "You suck at photoshop" series.Enjoy and you're welcome!
https://www.youtube.com/watch?v=U_X5uR7VC4M&list=ELME28FkTdZ...
⬐ gberger⬐ kevin_thibedeauWoah, the introduction is exactly the same⬐ gfodyAlong the same line - here is Scott Hanselman with some Word basics: http://www.hanselman.com/blog/VIDEODoYouReallyKnowHowToUseMi...Paste values isn't almost always what I want. When copying formulas around I usually want the actual formulas which have been judiciously constructed with fixed row and column references where needed. The default paste is ideal for people doing actual calculation in Excel.⬐ EnFinlay⬐ rtpgThat's half the skill!⬐ LukeShuI've often wanted some references in the formula to shift when I paste, while having others be fixed. This can be accomplished by putting the fixed references as string argument to the INDIRECT function (ex `indirect("F7")`).⬐ philsnowI forget the syntax but you can also write cell references that shouldn't change as $A$5 ; that fixes both row and column. Just put a $ in front of the dimension fixed.⬐ FabHKDefinitely don't want to use INDIRECT in large sheets, as it is volatile (i.e. always dirty).(Just to clarify: Excel maintains a DAG (directed acyclic graph) of dependencies, and upon changes marks cells dirty, then only recalculates what is required when F9 is hit. Cells containing volatile functions, being always dirty, always trigger a recalc of all their children.)
⬐ gbergerYou can also use $F$7.Or if you do just F$7, only the column changes when pasting. Or $F7, only the row changes.
⬐ DiabloD3This right here is maybe the most important thing anyone can learn in Excel.The second most important thing is how to use vlookup() for cross-sheet lookups, using a column in the other sheet as a foreign key.
⬐ vehementi> vlookupI guess you didn't watch the video?
⬐ DiabloD3⬐ NamTafI did, and I understand why he mentions an alternate way of doing that.I still prefer vlookup.
⬐ abricotIf they made it optional to use column and e.x. made it possible to use column name references, i'd go back to love it too.But manually counting columns is annoying.
Stop using vlookup. Use index and match, either through named references as Joel does it or just manually even. It's more powerful, faster and less prone to failure because vlookup relies on an assumed order of sorting but most people won't know this and will wonder why it's cocking up.⬐ DiabloD3Fourth argument, set to false.Fixes the ordering issue.
⬐ FabHKVLOOKUP has a parameter for exact matches.⬐ yreadYou really should use MATCH with last argument 0 (exact match) and use it only within IF(ISERROR:IF(ISERROR(MATCH(Key;Keys;0));"null";INDEX(Values, MATCH(Key; Keys))))
As a person who doesn't get to use Excel much but wants to... is there such thing as "fun" Excel problem sets? Like Eurler Problems but for Excel?Some kinda meaty stuff rather than just "here's how you sum"
⬐ infinite8s⬐ robterrinHave you tried solving the Euler problems in Excel?Does anybody else feel that the vast majority of these things are either available in Google sheets or worth just doing in R? My last consulting project was on a VaR model built in VBA/SQL with a bunch of Excel sprinkled about and it was horrendous. Not Excel, VBA or SQL's fault, but it felt like a more thoughtful architect would have used better tools.⬐ criddell⬐ eriknstrI went to Google Sheets and tried it and it was a pretty bad experience compared to what Joel shows in the video. Excel Online worked very well though.⬐ msl09Yeah if excel was coughing with a few dozen tables I wonder what starts to happen when you process thousands or hundreds of thousands of values.The pivot table got me jealous though.
⬐ greggybCheck Power Pivot if you just want an Excel interface to lots of data. I posted elsewhere in this thread about it, but the fun demo is to load up a dataset with a largest table of several hundred million rows. This is still quite snappy on a typical desktop machine.⬐ vehementiYou can do pivot tables in Google sheets⬐ blahi⬐ em500If you are a masochist .Both R (https://www.rforexcelusers.com/make-pivottable-in-r/) and Python (http://pandas.pydata.org/pandas-docs/stable/reshaping.html#p...) can do pivot tables.I think the most interesting thing in this video was the explanation of R1C1. I've always thought of the apparent automatic changing of references as almost magic. Now I know.For me, the most useful things he talked about was, in order; giving names to cells and columns, use of INDEX and MATCH, leaving space around tables, defining and using tables, pivot-tables, copying formatting with the paintbrush, goal-seek, and finally, control-semicolon to insert today's date.
The various forms of paste special, I knew about from before, and I also knew about the dragging to fill in values stuff.
All in all, a very good video with a lot of things I didn't know.
⬐ greggyb⬐ criddellThis is not a judgment on you, but I've seen this echoed in a couple places in this thread and yours is a top-level comment that starts with it.I really grokked the concept illustrated with R1C1 notation within a day or two of being exposed to $-pinning with A1 notation, and the concept of a relative offset was my mental model in Excel for a long time before I even learned about R1C1.
Yes, R1C1 notation is helpful because you literally type in an offset if you want it, but it seems pretty apparent in playing with Excel that the automatic reference rewriting is based on the relative offset of one cell compared to another. Seeing it rewrite a range as you drag a formula around seems to illustrate this concept very clearly.
On the other hand, I came from a non-technical background and came up as an analyst in a sales department. The person I learned Excel from didn't know about pivot tables, so I didn't learn about pivot tables for some time. What I did learn was how to make my own (much less flexible) pivot tables by hand very quickly. I got to the point that I put off learning pivot tables better once I was introduced to them, because they were an inefficiency for me. I eventually learned better.
⬐ eriknstrI should probably have phrased what I said a bit differently. I have not had trouble understanding relative offset and how to use it. What I've always wondered about and finally got an answer to in this video is how the implementation works so that Excel is able to preserve the offsets even when pasting a cell in a random location into a spreadsheet :)⬐ greggyb⬐ mark-rAh gotcha. Yeah, for that I just had an implicit mental model of R1C1 without realizing it was physically implemented.I interpreted you (wrongly it seems) as saying that the relative offsets were confusing and I responded to that. You were curious about the implementation instead.
I didn't care about the implementation when I was learning Excel, and simply assumed a mechanism like R1C1 without much interest in it.
I thought it was instructive to see that in A1 mode the cell formula magically changed as it was copied, while in R1C1 mode it did not; it was good circumstantial evidence that R1C1 is what Excel uses internally. But then I tried an experiment, deleting a column that was referenced by a formula - the formula was replaced with =#REF! indicating that there's still magical changes being applied.That's a pretty good demonstration of some of the strengths of the ribbon interface.After watching this, I went to Google Docs to try to reproduce some of this and it felt very clunky. The first thing I realized I didn't have is a styles manager but I was able to install an add-on. For other parts (like creating tables) I was stuck.
I wonder if a similar demo in Libre Office would be as impressive?
Edit: I just tried Excel Online and it worked surprisingly well. Last time I tried it, Google Sheets felt way better but now I'm not so sure.
⬐ ErwinI had a chuckle at Joey calling @ "a bagel". I guess that's a New York thing.Over here we call them "elephant-trunk-A" (interestingly elephant symbols are quite common here in e.g. royal/government coats of arms and the "Order of the Elephant" being the highest chivalric order).
⬐ duncanawoods⬐ TallGuyShortI liked it too... but its clearly either a Chelsea Bun or a Cinnamon Swirl :)I've been using Linux on the desktop for years and have never missed Microsoft Office (except for Project, a little bit). Now seeing R1C1 notation is making me want Excel... Any recommendations for FOSS spreadsheet software that supports R1C1 notation or that is generally better than LibreOffice Calc?⬐ cdolan92⬐ GlobzTo be honest I'd recommend an Office 365 subscription and use Excel in the browser, if you are unwilling to get a mac/windows installation with a desktop app.Perhaps its a 'purest' mentality, but if you're going to learn how to use a spreadsheet app, may as well learn the one that is most well known and widely used. As the video shows, Google Apps, Numbers, and the rest of them really are missing core features.
Joel didn't even get into VBA scripting - thats some advanced stuff that once you realize you need it, but you're stuck in Google Apps...... well, maybe this crowd is smart enough just to make a web app to solve the issue haha! Myself? I stick to Excel.
⬐ TallGuyShort⬐ catern>> Perhaps its a 'purest' mentality, but if you're going to learn how to use a spreadsheet appIt's not any more purist than sticking to FOSS :)
It looks like LibreOffice Calc does actually support R1C1 notation.There is also the spreadsheet support in Emacs org-mode.
⬐ flukus⬐ saint_fiasco+1 for org mode. There is also a table mode plugin for vim that's great (on smallish tables).LibreOffice has R1C1 notation too.Go to Tools, Options, LibreOffice Calc, Formula and select "Excel R1C1" in the Formula syntax dropdown menu.
⬐ TallGuyShort⬐ jlduggerAppreciated! Thought I had thoroughly checked that dialog yesterday. Not the first time I've wanted a "buy them a beer" button on HN.R1C1 isn't much different than normal calculation. You can already use $ for absolute row / column references.⬐ Tomte⬐ Tuna-FishThe point is that it is much more intuitively understandable what happens when you fill whole columns with that formula.In R1C1 notation it's obvious because the formula doesn't change. In normal notation the exact same thing happens under the hood, but the cell references you're seeing change.
⬐ jlduggerMaybe, but named ranges make the whole thing somewhat moot. Your formulas should be human readable, ideally.http://ccm.net/faq/24592-libreoffice-calc-switch-to-excel-r1...I recently had to use a spreadsheet for a first time in a few years, and LO Calc seemed to be dramatically better than OOo Calc back when I last used it.
⬐ riboflavahttp://www.gnumeric.org/ is pretty good.⬐ ams6110⬐ bzbarskyUnless it's improved quite a bit in the last few years, no it isn't very good.⬐ mschaef⬐ TallGuyShortIt's been a year or two since I've used it, but the last time I checked, it hasn't improved all that much. It was generally rather slow and limited.This is really a shame, because of Gnumeric's history. It was (along with Gimp) one of the very first GTK applications, and present at the beginning of the Gnome desktop suite.
Thanks - I used Gnumeric on a netbook for a long time. It was indeed a good choice at a time when I needed something very lightweight. I do recall it being quite limited, however... I'd be pretty surprised if it had more of the features Joel Sposky was showcasing than Open/LibreOffice Calc.Gnumeric supports R1C1 notation. It's a checkbox under the "Format" menu, "Sheet" submenu.⬐ codemacThe most interesting spreadsheet is http://siag.nu/siag/, but it's obviously not what you're looking for.Honestly? Excel is a premier application, that microsoft spends extreme amounts of effort making great. There isn't a FOSS spreadsheet program that really comes close. It's not that there can't be, it's that the excel line is moving faster forward than current FOSS spreadsheet projects.
Great video, it actually reminded me of CTRL+D!One of my many assignments at my job is to work with excel on a weekly basis exporting data dumps from MYSQL to excel ranging from 50k to 100k rows.
At first it was a daunting task but once I learned how to build some solid templates then all I had to do was to dump the data set into a tab and let the formulas do the work, everything was already formatted so I could just start validating the data then email it to the sales team for further analysis.
If you take the time to analyze your data sets and get to know how to handle each type of data then you can build some really solid template in a fairly short amount of time.
I try to avoid using excel as much as possible but this damn thing is so deeply rooted into the "business analyst" world that you can't really escape it so this is why I told myself that I would be better off mastering it and build solid reporting templates instead of trying to change their mind about using alternative software.
On a side note we started using WebFocus... that's another monster to tame...
⬐ partycoderFor many uses, I dropped Excel and replaced it with R. Doesn't hang with thousands/millions of rows.⬐ roel_v⬐ ForFreedomOnce you know what to do, yes. But for scenario analysis and prototyping, anything with a code/run cycle is just painful. I've tried switching many times.Plus, Excel has a 'gui' (of sorts) build in. (Shiny is not a substitute for that - here too, shiny is fine for productizing once you know what you want to do, but not for quick one offs)
⬐ em500> for scenario analysis and prototyping, anything with a code/run cycle is just painfulFor scenario analysis and prototyping, you'd use RStudio as GUI and the command line for REPL. But personally, I find Jupyter notebooks with Pandas even better.
⬐ roel_vRStudio is not a gui, it's an ide. If you change a parameter somewhere you still have to re-run the code and hunt for whatever it is you're calibrating. In Excel, you just change values and you see immediately what has changed. In Excel, you can trivially show subsets (with a dropdown that show you all values in a range), or checkboxes for booleans, do conditional formatting to highlight special values (< 0, within ranges etc). All of which is a pita with R and with anything that has a code/view report cycle, basically.⬐ em500The table viewer in RStudio shows any changes in dataframes live as you change them in the REPL, so I don't really see your point.He says,"I am not taking your questions because your questions are stupid and check the internet"⬐ pgtThis is great. I just made a little internal slideshow for everyone at our company to get them to watch this. Link:https://docs.google.com/presentation/d/1d00Cetvp8_4fW7Y854tF...
Thanks, Joel!
⬐ aeinstein1⬐ thomasthomasBulle! ;-D⬐ riettaThis is the first time in my computer science career that I've heard Excel (and spreadsheets in general) called a functional programming language! What an excellent, excellent point!⬐ pgtThe lambda calculus is incredibly value-oriented. Consider that it is impossible to write a formula that injects another formula into a blank cell: all values flow from other values. To overcome this, you need to write a side-effecting Excel macro. This is why some people have integrated Python into Excel. It's a shame that the CLR (and more functional languages like Clojure) is not better integrated into Excel.Story time (MS rant):
When Microsoft pulled Access (the best relational DB UI) to avoid competition with SQL Server, stopped adding features to IIS (still no wildcard domains!) and did not fix the botched Visual Studio deployment story (publish 10MB binaries for 1-line changes when you control the web server and the OS?), they ceded the "IDE" wars. Excel is sort of a business IDE, except sharing spreadsheets is a nightmare, which is what Google Sheets is really good at. I reluctantly started switching to other "IDEs" like IntelliJ (still worse than VS) and the Google Apps suite. However, SQL Server Management Studio is still the best SQL writing interface for me.
i've worked for many banks. amazing how reliant the whole industry, therefore the world economy, relies on excel.http://www.zerohedge.com/news/2013-02-12/how-rookie-excel-er...
⬐ FabHK⬐ mooneaterThat is very true, and scary indeed, given how easy it is to make mistakes and how hard to find them in Excel.⬐ FabHK⬐ dagwJust to expand on that - many banks employ Excel basically as a frontend for their own analytics/database (using own functions with addins). A popular trick is to allow functions to return handles that stand in for objects. Using that, you can run not only Monte Carlo simulations and PDE solvers in a pricing sheet, but even run a whole trading book including risk in Excel.Of course, you can then also overwrite certain cells to pad your PnL, as a few Credit Suisse traders did in 2007/2008 to hide their losses in mortgage backed securities. They basically overwrote bond prices to some fictitious value, overstating the PnL of their position by $540m or more...
And despite this, and despite the mind boggling amount of money at stake, no one seems to making moves to actually do something about it. It's like the finance industry has collectively decided that a few billion in losses each year is an acceptable price to pay to get to keep using Excel.⬐ FabHKNot quite right, there is considerable pressure from auditors to switch from Excel to "systems".⬐ stenI’m in the industry, everyone here uses spreadsheets for everything. They only have us working on other stuff now because we’re reaching memory limits and row limits for calculations.But primarily we have a few factors working in excels favor. 1. Inertia. 2. Difficulty in switching to a more advanced solution for non-technical folks. 3. Speed. If you can get a MVP up and running before another firm you have an advantage. Even if it’s in Excel and held together with spit and string.
The Goal Seek part was painful to watch. Why would rounding completely break that? And why would it end up at 9 billion?⬐ princeb⬐ hkmurakamilike he said the rounding made 975000 an impossible result.if you have rounding or some kind of non-continuous function the right feature to use is actually Solver (which is an included add in you have to enable in Excel options) which provides you with an fmin function (or several) as opposed to goal seek's fzero.
Things in which people consistently overrate their skills: Super Smash Brothers, Scalable Backend Systems, Microsoft Excel.⬐ scarmig⬐ Hydraulix989Are there things where people consistently underrate their skills?⬐ hkmurakami⬐ ourmandaveCourage./straight face
⬐ taneqProcrastination.⬐ loegMath.⬐ edrocheRating things⬐ xbryanxEmpathy⬐ harry8silly replies⬐ TorKlingbergTo give a serious answer I read somewhere: unicycling. Very few people can actually ride one, so if you can ride a regular bicycle you are probably above average at unicycling.⬐ nommm-nommmUnicycling is seriously difficult! I failed at learning to ride one.⬐ x1798DE> Very few people can actually ride one, so if you can ride a regular bicycle you are probably above average at unicycling.I don't understand, are you saying that the average person can't ride a bicycle?
I'm a 23rd level VBA ninja-rockstar and epically hate myself.⬐ cmrdporcupineMemory management in C.⬐ TomteSSB? No.I've bought it because of everyone posting about how awesome it is and how their six year old sister is good at it and so on.
I've read all kinds of tips and tricks guides.
I still haven't found out how coming back from a fall off the platform works. I can hammer the key that the Nintendo leaflet and all the online guides say and nothing happens. Probably there is some other prerequisite to it. Or whatever.
⬐ rezashirazianI would add Photoshop to this list.⬐ swampthingMicrosoft Word⬐ xiaomaHumor is possibly the most egregiously self over-estimated skill.⬐ collywPersonally I don't want to get good at Excel. Its a horrible piece of software for the majority of the tasks it is used for. It would probably make a decent spreadsheet but I hardly ever see it used as that.⬐ misterdataSex⬐ mathattackAdd Predicting Completion of Tasks and Driving.⬐ xutopia80% of drivers think they're better than average.This guy was an ex PM for Excel at Microsoft.He even had Bill Gates review his spec:
⬐ erikbHe is so entertaining despite only having taught me one or two minor things.⬐ avindrothIs there Excel with vim bindings? Or anything with equivalent speed/modal editing?Or is the default Excel good enough to warrant relearning all the navigation hotkeys?
And how is org-mode as an alternative?
⬐ MagnumOpus⬐ GustomaximusNo Excel with vim bindings or model editing. Excel has its own set of arcane keybindings that make using it really fast. If you are a heavy user, you can print out a reference sheet just like you did with vim when you started out.Org-mode is not an alternative, there is zero overlap in functionality between it and Excel.
I can't agree with'always name a cell/range' and dont put numbers. This gets very confusing for a second person working on the workbook. Typically sheets get large and they get all these names building up over time. You end up with no idea with what someone is referring to.Also for 'paste special' I dint notice him mention Crt+Alt+V which is really shortcut vs using menu dropdown.
Ctr+D was new to me. Thats a great one.
⬐ Tomte⬐ ishtanbulRe: seeing what it refers to: Do you know the trace to predecessor/successor feature in the Formula tab?⬐ Gustomaximus⬐ TeMPOraLYeah I'm familiar. It can be useful. I just find it easier to move around workbooks/sheets via sheet/cell reference, especially with multiple users. I used to do the naming thing but I found it less efficient.I disagree with your disagreement. The principle of naming your cells/ranges is basically the exact same principle as "don't put magic numbers in code, use named constants".⬐ coroxoutI've thought that before but every time I've named important ranges I've come to regret it.Someone at some time will decide to nuke an entire worksheet and paste it back in without the named ranges, and then everything will look the same but various lookups and macros will break. (And they will somehow be able to do this even though you've password-protected everything in sight, too. Not sure how that happened.)
Or someone will insert a row somewhere and the named ranges will be missing the last row of the data and any named ranges beneath them won't shift down. That kind of thing.
Maybe I'm just missing some named range best practices.
⬐ kilgor13I mostly agree with you. Where the names are clear and obvious, naming is very helpful.However, when it is no longer obvious what the name refers to, there is no more benefit than the original cell reference. In fact it is even worse because it's not immediately apparent where the cell is now the reference isn't showing. I often run into this problem with very large spreadsheets where every cell is named.
Our workplace has a strict "everything has to be named" in Excel and it really makes life much more difficult when working with extremely large calculations. Often I will want to know what a certain formula is doing. I will look at the formula. There are about 6 named cells that I have no idea what they refer to. I then have to use the drop down list (which includes about 500 names) to find the cell it refers to. Looking through this list is a lot slower than just reading the cell reference and looking at that cell. It's also quite easy to pick the wrong name in a list of 500, because there is likely to be a lot of similarly named cells.
⬐ aninhumer>There are about 6 named cells that I have no idea what they refer to.Maybe I'm misunderstanding, but doesn't that mean they're just badly named?
talking about the importance of variable input cells in minute 43 smh. working in finance this is not very impressive. also excel on a mac is inferior.I think only about 1% of excel users actually build models with it so the need for advanced skills in using the program just aren't necessary, such as for storing data and maybe making pivot tables...
⬐ swingbridgeBeing an Excel jockey was "cool" in the 90s and 00s but all the cool number crunching kids are much more likely to be using something like Jupyter Notebook, R or homebrew command like stuff these days.⬐ riettaOne thing I find interesting is that on the Mac OS X Excel, Cmd+T is the toggle for the relative/absolute cell references, but in Google Sheets it's F4, just like Windows Excel.⬐ hellofunkI take offense at this. Mr. Spolsky, you do not know me! King Kong ain't got nothing on me! I excel at Excel!⬐ trymasGood content, but could he please stop calling listener a dummy baby!I understand that he is presenting to his employees or to some company, but it's still disrespectable. If he is not my greatest buddy - don't call me a dummy baby.
⬐ ahoka⬐ unixheroIrony is at 'I' in the dictionary.⬐ rblatzHe is presenting in the style of the popular You Suck at Photoshop videos. Check them out they are hilarious.⬐ trymasI have not watched that series, though I was not laughing while listening to the Joel's talk..Yeah. He probably has a crowd that sucks at Excel because that goal seek stuff is not exactly a revolutionary Excel trick.⬐ TimesOldRomanWow I've read stuff by Joel but never watched; he comes across as a total prick.⬐ FabHKDid he cover volatile functions? Did he cover F9 vs Shift-F9 vs Ctrl-Alt-F9?⬐ lgomezmaI really suck at Excel⬐ s_chaudharypandas anyone?⬐ branchlessInteresting, any recommended resources to take it up a level?⬐ youdontknowthoThis is a great video. If you look at what they are doing with Excel services in the Microsoft Graph it will finally mean that you can use whatever platform and whatever language you want to generate the data.⬐ hyphocasne2007Hey, buzzard, are you cloth-eared? Come on, look at hotchas here http://9pneic.skhit.in/⬐ HunnertVery educative, I hope I don't forget all this until the next time I have to use Excel.⬐ madengrMany years ago there was a harmonic balance (circuit) simulator in Excel. Pretty crazy.⬐ fu9aridk im pretty good at exporting to CSV.⬐ taneq⬐ andythro77Dunno why you're getting hammered, this is part of a good Excel workflow. Do what you can using the nice GUI, if you have bulk changes to make that are easier to script than find-and-replace then dump it to CSV, do the grunt work with some command line tools or a script, and re-import it.⬐ rspeerI have never seen anyone with an Excel workflow that involves exporting to CSV that does not immediately mojibake every non-ASCII character.Yeah, I know there's a well-hidden option to export in tab-separated UTF-16. Nobody uses it.
So I guess you're fine if your data is just numbers, and if it's not labeled with words, names, or places.
⬐ Falkon1313⬐ orbitingplutoOften, you can strip the BOM (which isn't supposed to be there to begin with), convert from excel's proprietary character set to UTF-8, and translate the line endings and things may work - if it hasn't decided to randomly change things that weren't dates into dates, strip leading 0's from your zip codes, reformat things that you didn't want reformatted, and otherwise mangle your data. (Nevermind that Excel can't handle dates properly anyway.)What's worse is that many people play with excel and use things like coloring, fonts, and visual layout to distinguish things, and/or combine multiple unrelated chunks into one sheet so that they won't have to switch tabs (which of course makes for a scrambled mess of data). I try to suppress the memories of it taking 36 tries to get a usable CSV from business people (after having given clear instructions in advance and doing postprocessing afterward to clean it up).
Excel should never be used for working with data. But it is, so that's job security if you can clean up the messes that it makes.
⬐ taneq> So I guess you're fine if your data is just numbers, and if it's not labeled with words, names, or places.Whoops, busted. I very rarely deal with localised data so I can get away with ASCII. Excellent point, though, my case doesn't generalise as well as I thought it did.
⬐ garyclarke27I use LibreOffice just for this purpose. ie Creating csv files from xls files. Open Excel file in LibreOffice Save as text >> UTF-8 >> quote all text cells. Works perfectly every time, Excel is brilliant for most things but is useless with csv, all kinds of strange bugs will arise.⬐ yen223Excel also messes with CSV values that look like integers but aren't (think serial numbers or IDs), leading to many frustrating days of support.⬐ bigger_cheeseThere is also a pretty gnarly bug in excel when it tries to import a CSV with the asci string "ID" as the first two characters.http://superuser.com/questions/210027/why-does-excel-think-c...
Very Frustrating
⬐ nommm-nommm⬐ intendedHacker news discussion on that bug:Oh this was fun.Excel has issues with numerical figures greater than 13 digits, and can't distinguish from a serial number in a csv or an integer.
Importing a small (30k rows of so) set of of tax information thus became hell.
Worst part is that I didn't find it till halfway through the migration. I love excel on average, but that was just a painful fight.
⬐ jhbadgerNot to mention the famous mangling of gene names that look like dates. Yes, I know scientists shouldn't be using Excel -- I agree. But I'm a computational biologist. Experimentalists know how to use exactly three programs 1) Microsoft Word 2) Microsoft Excel 3) Microsoft Powerpoint.⬐ jgammani was reading a paper about how Excel gets some standard statistical tests wrong too - the US Stats Association i think did a compare/contrast with a range of packages. some of these bugs have been there since 2002 or so. i lived in Excel for many years but wasn't a power user - i got to like it but sadly, it got used for everything even when it really, really shouldn't have.⬐ intended⬐ NeeekI read an article on that, but it was clearly mentioned that this was an issue with older versions of excel (excel 03 07? iirc).⬐ jgammanthis link seems quality and you're right - seems like the excel team took it seriously in... 2010. that's still pretty bad!I find myself working with four letter codes to identify wards and facilities at my work, inevitably I have to compare my SQL output to someone's spreadsheet. No excel, I didn't mean March 02, just give me MAR2 like I asked. Drug codes that start with a '.' are another headache, they will be auto formatted as a decimal number. Worse is when they want to put info from a spreadsheet in to the database, no one understands the need for consistent field formatting :'(⬐ mistermannHave you tried bringing in with PowerQuery instead? Much more flexible.⬐ Neeek⬐ coroxoutI haven't tried pulling Excel -> SQL with PowerQuery yet, only really dabbled in presenting data the other way. Do you know if this works easily with a document that utilises a lot of macros and VBA? Or are we still talking flat CSV.⬐ mistermannWhat are you talking, Excel with VBA is importing/consuming the data, or it is the datasource for something else?I assume Excel with VBA is importing/consuming the data in which case Power Query / Power Pivot would likely solve most of your problems. Refactoring a large complicated existing spreadsheet to use PQ rather than your existing peocess, hard to say how tough that would be.
I feel your pain. We have a lot of ID values with leading zeroes, which Excel loves to strip, and data with numbers in parentheses, which Excel's CSV loader thinks should be negative numbers (this is apparently common in book-keeping).I keep getting sent spreadsheets which were made from CSV data by people who didn't know how to set the columns to text - to be fair, if the file extension is .csv then Excel won't even let them set the column formats - and having this latter category turn up as -ve numbers not only looks weird but ruins the sorting. Even 1, 11, 2 is better than sorting "backwards".
Exporting from and importing to Excel from CSV becomes a life-saver sometimes. Once upon a time, I had to work on a 200k by 50 line Excel 2007 spreadsheet on a IBM Core Duo laptop with only 1GB of RAM and XGA scren. It would take ten minutes to apply a single filter. Writing VBA to export and then letting Cygwin do my work: curling regexing and sorting was the only option.⬐ MagnumOpus⬐ ekingr> 200k by 50 line Excel 2007 spreadsheet on a IBM Core Duo laptop with only 1GB of RAM and XGA scren. It would take ten minutes to apply a single filter.This is implausible. I worked with far bigger datasets, and far more complex calculations, and it didn't take minutes - several seconds, maybe.
⬐ phonon"1 GB RAM"⬐ dx034⬐ orbitingplutoThat's the key point here. We recently upgraded all computers to at least 16GB ram solely because of huge Excel spreadsheets that we use.I'm talking about a filter, not calculations. And I'm so glad you mystically decided that your calculations were much more complex than whatever I was doing.Actually, one of the safest option I have found to import/export from Excel without the encoding going wild is to just copy/paste TSV data, and treat it as UTF-8 in your programming language of choice.⬐ fu9arSpreadsheet programs are great for data entry if we take the care before hand to design the data structures well so that they easily export and can be easily analyzed by a computer. Easy stuff like small org budgets can be done with spreadsheets, but masse data analysis simply requires industrial grade tools.⬐ smallnamespaceWhile this is a fine workflow if you're more familiar with command line tools than Excel, bulk changes are usually easily doable in Excel with judicious use of cell formulas and copy-pasting of values.On a lark, I did a couple dozen Euler problems in Excel a couple years ago and found it was actually quicker and easier than doing it in a 'real' programming language; at least part of that is that loops and recursions are easy to visualize and debug in Excel because you can just break intermediate calculations into cells.
hi, am Anderson, i had my friend help me hack my ex's email, facebook, whatsapp,and his phone cause i suspected he was cheating. all he asked for was a his phone number. he's email is ([email protected])..IF u need help tell him Anderson referred you to him and he'll help. Am sure his going to help you do it, good luck⬐ GrumpyNlwow, i want a i-phone so i can play a 25 year old game on it.⬐ guyzmowell, the main feature Excel really suck, and makes it almost impossible to use: Undo/Redo. Why the hell did they make undo/redo work across spreadsheets (eventually reopening closed spreadsheets)?And then, why is it impossible once you wrote some content, to copy it, undo writing it and paste it? The copy gets undoed!
To me, that behaviour makes Excel unusable.
And anyway, why would any decent SW engineer want to use Excel, which is like a 2 dimensional view of the world, whereas after a career designing software I can see the multiverse.
⬐ orbitingplutoReal men use PowerShell to edit their Excel files. :jedit: I did do this once, but it didn't scale to larger files. Also, it was the only option on those Windows RT Surface tablets.
⬐ jackinmyshoesThat was incredibly boring, why is this getting so many up-votes?⬐ whorleater⬐ BenjammerBecause it was moderately enjoyable, I learned quite a bit, and I assume other people did too? That table shit in excel? Blew my fucking mind.⬐ Graphon1What's the table shit? vlookup() ? edit: oh, 37:00, I see.⬐ whorleaterThe actual tables function, it's about 2/3rds of the way through the video.This video of Martin Shkreli using Excel [1] is what really made me realize I suck at Excel...[1] https://www.youtube.com/watch?v=jFSf5YhYQbw&feature=youtu.be...
⬐ hawkiceThat is an oddly fascinating video. He's quite good. I mean, he's out on bail pending charges for securities fraud, but in a way it makes sense, this would be an important skillset for that sort of grift.⬐ mapt⬐ wodenokotoIs he on bail for charges that were uniquely mendacious?Or is it just that everyone in the industry that's competent breaks certain rules, and he's been targetted for being a giant asshole in a public way.
⬐ Senji⬐ denzil_correaThe second.⬐ hawkiceHe's being charged with running a Ponzi-like scheme and being "engaged in multiple schemes to ensnare investors through a web of lies and deceit". A lot of people in his business are aggressive and less than forthright. Securities fraud is _quite a bit more serious and less common than those personality flaws_.I didn't know about him. I made a search and found out that he challenged Chris Evans (Captain America star) to a fight!http://www.thewrap.com/martin-shkreli-challenges-captain-ame...
⬐ NamTafHe's efficient at doing some financial analysis and knows what to look at. It's logical that he was able to find good deals and then exploit them.The name Shkreli didn't immediately ring a bell for me, and I found this very candid interview with him on CBS News. He is very open and no-filter on every question, some of it makes a lot of sense, some of it is quite far out.⬐ r_smart⬐ kozikowHe did a podcast episode on the Milo Yianoupoullis (I tried okay) podcast. It was...interesting. Like you said, some of it makes sense, some of it is out there. Was really engaging to listen to though.⬐ Veen⬐ MIKarlsenI honestly can't think of two people I'd less like to listen to than Milo Yiannopoulos and Martin Shkreli.⬐ r_smartWell, I'm not going to force you, so feel free to abstain. Personally I like listening to all kinds of people and in different contexts. I found it interesting, and was recommending it to someone else who seemed to be in the same boat.It's not a "good deal" because it's not consumption for the sake of consumption. High drug prices hit those unfortunate enough to be born with a sickness, and some of those people might have a hard time with their lives if majority of their time is spent earning enough to pay their medicine.However, I like that he then goes on to say that there should be a system that takes care of the people who CANNOT afford the drug (government-funded or a corporate-driven solution of some sort).
The whole business take on medication and health care just seems crazy for a scandinavian citizen like myself.
⬐ kbensonThe free market take is that competition allows for faster development of more newer, better, cheaper drugs overall. That may mean the occasional crazy pricing, especially in a constrained market where only a few entities can supply a need.The socialist take is that the government should intervene in cases where a need isn't being met in a sustainable way by the market, and people are suffering.
Another way to look at it might be that one prioritizes the future over the present.
I don't think it's controversial to say that both these are true in certain situations, and sometimes those situations overlap (such as with medicine). In a perfect world, we would have all the benefits of both the free market and socialist takes, without the downsides associated with each. It's possible we might be able to approximate this somewhat, such as with Shkreli's suggestion that the government take over production of generics. Other options are better public funding of research and development, so drug companies can shoulder less of that cost (and thus charge less to recoup investment and make a profit).
In the end, I think neither system is perfect, and both have very real advantages that we need to be careful not to discount.
⬐ rdm42116Some CBS news affiliate brought him on to talk about the Epipen situation.Honestly, he's very candid, and he says things people don't want to hear. Sometimes he says things that are just wrong and inconsiderate, but other times he says things that are simply inconvenient truths.
For example, he brings up the Epipen situation in a different light: He says $300 ($600 for 2) is significantly cheaper than the alternative (a trip to the ER, which can be thousands of dollars). He goes on to say it's a relative bargain, and that insurance companies should be more than willing to pay for Epipens because of the potential cost savings. To him the problem isn't Mylan (which has become the target of everyone's ire) but the insurance companies or medicaid.
Whether that's true or not it's a different perspective, and as a society we need to hear things that the masses don't necessarily agree with. The US system of healthcare is highly flawed, it's not a good idea to just make a boogeyman who will hide the real issues.
⬐ tamanaGiving a mugger $300 is also cheaper then treating a gunshot wound. Doesn't make it right. These companies are exploiting corners of IP law to block competition, using the US armed law enforcement as the muscle in their mugging.Although somewhat impressive, majority of us are similarly skilled with our editor/IDE of choice. My Emacs and IntelliJ workflows would probably look similarly advanced to non-programmers.What's more, Emacs have its own spreadsheet app that I like more than excel: http://orgmode.org/manual/The-spreadsheet.html .
⬐ lindbergh⬐ Kiroorg is really slow when it gets to calculated column on a moderately sized table however.⬐ kozikowAh, interesting. Tbh I rarely used spreadsheet capabilities and I usually used ob-ipython to load table to pandas in python and after manipulation write it back to org. I mostly use org tables for occasional manual data manipulation.One problem with excel is that temporary steps you went through are hidden - it's hard to reproduce the excel spreadsheet. With org mode + ipython workflow I can trace all transformations that led to the end table, but hide them when they are not relevant.
I believe this gets me the best of both worlds - some form of WYSIWYG tables like excel + IDE-like programming environment in python and version control. Excel obviously lacks proper programming tools. Jupyter notebook does not treat printed tables in a special way org does.
I wrote once a too long blog post about my org-mode data analysis workflow, part about pandas integration: https://kozikow.com/2016/05/21/very-powerful-data-analysis-e... .
What do you tend to use instead of org spreadsheet?
Does everyone love this guy all of a sudden? I thought he was the most hated person in the world but I don't see a single bad comment about him here or in the YouTube comments. Quite on the contrary.⬐ nocman⬐ unixheroMight be of help to someone else to realize that you are talking about Martin Shkreli, rather than Spolsky.I missed the parent you were referencing, and thought one of the child comments was saying that Joel Spolsky was out on bail for securities fraud (which was the result, obviously, of me not catching who you were talking about)
oops!
:-D
⬐ greenshackle⬐ kchoudhuOoooh thanks. I've been reading a lot of Spolsky's blog posts from 2005-2006... I was thinking, what's happened to this guy in the last 10 years? Everyone hates him? Securities fraud? What?I'm not a fan, but I do see and appreciate his point of view.His twitter is usually a gas too.
⬐ FLUX-YOUNo, he has a following. I'd say most people have no idea he does lessons and streams video games on youtube.⬐ BartweissIt feels like people react to him the way they do Al Capone or Jordan Belfort. Obviously he's a jerk, it looks like he was probably eyeballs deep in securities fraud, but it's so audacious and disconnected from real life that it's like watching a scumbag in a movie.Probably we're all better off with the law coming down on him, but that doesn't preclude a day-to-day fascination.
⬐ harryfPerhaps this helps http://www.vanityfair.com/news/2015/12/martin-shkreli-pharma...> The identity he creates, he says, is “an extremely weird form of sarcasm.”
⬐ refurbI'm sure this helps....https://www.thestreet.com/story/13696336/1/retrophin-with-he...
⬐ kolbeMy opinion of the guy rose sharply after I learned that his drug pricing practices were standard for the industry, and that he did nothing that other companies like Valeant and Allergan have been doing at 100x the scale.⬐ unixheroSee his video linked above. It's really good and its clear the guy is bright.⬐ wodenokotoWell, first of all, he is mentioned here solely on his Excel process, and in that regards, his tweets about Harambe or purchase of a hip hop album are completely irrelevant, and honestly it is nice to see a community that can discuss a part of a persons work without tainting the view of that with other workings of said person.But if you do hate him, you can apparently call him and tell him why and he'll take the time to discuss with you why you hate him, and he does this quite calmly and rationally (given the situation)[1].
I totally enjoyed that Shkreli video. Didn't watch it all but I think I will.I always wondered how do you do this horizontal or vertical fill with formula's using the keyboard?
This inspired me to look for more Excel hacking videos on YouTube.
⬐ tedsanders⬐ rsp1984Ctrl+R fills right from the leftmost selected cell. Ctrl+D fills down from the topmost selected cell.⬐ ableal⬐ DanBCAnd OpenOffice used to omit those, if memory serves.(Checking ... Libre Office 4.4 has Ctrl-D ... ah well.)
⬐ unixheroThanks!⬐ robocaptainHoly crap that's super useful. For whatever reason I never could figure out (or remember) a shortcut for the 'filldown button'. Instead I would copy the cell I wanted, and then control+arrow my way to the fill destination, shift+control+arrow my way back, and then paste.This way is a lot simpler and should cause fewer mistakes.
⬐ to3mHandy in conjunction with End,<arrow key>, which offhand (I don't have Excel on this PC) does something like: find the next entirely blank row/column in that direction, then extend selection to it (exclusive). If you've got gaps you can just repeat it.So, if you've got one column filled in for every row, and one row filled in for every column, you can quickly select the whole box and fill it down from the first row.
⬐ NamTafYou don't need to press end to enter end mode - holding down ctrl toggles it. So you can hold down ctrl and use arrow keys and, like it does with text, it will jump entire logical blocks - either continuous cells with content or continuous empty cells. Add shift as well as you select all that stuff instead of just bouncing across it.⬐ to3mGood point. That's probably a better tip, given that you can do the same thing with text.I think the fill handle is the only thing in Excel that doesn't have a keyboard shortcut.At least, that was the case in 2000.
⬐ cm2187Most of the time if you want to apply the same formula, you don't necessary want to carry the formatting, select the range, F2, CTRL ENTER.⬐ patrickkIt's worth watching his entire finance series and even his chemistry series if thats your thing: https://www.youtube.com/channel/UC8gjB1PSXv_oAUSAQ16S0fA/vid...Yes he acts like a giant dick, but he's got great knowledge of how to analyse an investment, and recommends great books and so on. If his life would've turned out a little different and he was less publicly abrasive, he could've been celebrated as a child prodigy or an investment wizard...sort of like this Japanese trader cis: https://www.youtube.com/watch?v=S-e1MxcjFDk (an amazing guy).
⬐ hellofunk> If his life would've turned out a little differentYou know you can say the same thing about Pablo Escobar, and Hitler, and many others. Just because someone has an innate talent at something, perhaps even a genius, does not make them worthy of the rhetoric "if only things had turned out a bit different for them...". People do what they do, we shouldn't question what they could have done.
⬐ tomjen3⬐ et-alIf Hitler had a video series about how to do public speaking, I would watch that, and I hope every politician would.⬐ unixhero⬐ aethersonGodwin's law (or Godwin's rule of Nazi analogies) is an Internet adage asserting that "As an online discussion grows longer, the probability of a comparison involving Nazism or Hitler approaches 1"—that is, if an online discussion (regardless of topic or scope) goes on long enough, sooner or later someone will ...His method is known. This is why the study of history is important.
Stipulating that Shkreli is guilty of securities fraud, theft, and is a dick of a person, it's still awful to compare him to people who are responsible for the deaths of hundreds or millions.⬐ hellofunkI wasn't making any direct comparison, just making a general point using well-known historical figures.⬐ unixhero⬐ NoneIn fact ... You induced Goodwins Law.--
Godwin's law (or Godwin's rule of Nazi analogies) is an Internet adage asserting that "As an online discussion grows longer, the probability of a comparison involving Nazism or Hitler approaches 1"—that is, if an online discussion (regardless of topic or scope) goes on long enough, sooner or later someone will ...
NoneYeah, everyone condemns Martin Shkreli, but having watched the video Benjammer5 linked to, he seems like a regular bright dude / techbro (ugh..). I would not be surprised that a significant number of people who lambasted Shkreli, if given a similar situation, would end up making the same choices for that $$$.⬐ icantdrive55I have found that most people make horrid moral decisions when it comes to money/career. They go into this weird denial, or fall back on, "Hay--it's legal!". I've had my fill of people like this, including a particularly greedy sister.It seems like ethics, and morals are just missing in some people, and most of these people wear ties, and do it all in a legal fashion.
I have watch a few of his videos months, maybe a year ago? It seems like it was a while ago.
I watch his videos for two reasons; He was obviously opening up his life to the world, and I was curious to what this guy was all about.
I came away with, like myself, he seems to have a real lack of friends. And like myself, he definetly needs a girlfriend. Not someone he could buy, but an honest caring relationship. I definetly got the feeling he wanted people out of his league, and it really bothered him. Maybe I'm completely wrong?
⬐ tunnuzSo, on the topic of people making horrid decisions when it comes to money, I recommend a movie called "The Box" inspired by a sci-fi short novel by Richard Matheson called "Button! Button!" (Wikipedia https://en.wikipedia.org/wiki/Button,_Button_(The_Twilight_Z...). Not a spectacular moving, but two hours or so well invested, I believe.⬐ barrkeldefinitely⬐ r_smart>I have found that most people make horrid moral decisions when it comes to money/career. They go into this weird denial, or fall back on, "Hay--it's legal!"I was just having a discussion with someone last night who was complaining about executive bonuses (in health insurance specifically), and how he thought that was unfair. He then went on to describe how he's moving into government work for the high pay, low effort, and how he would try to do the same pension spiking practices that's got California in so much trouble. After all, it's not illegal.
Were it not my house, I feel like I might have put my head through a wall.
For those who don't recognize the name Martin Shkreli:⬐ chrisan⬐ flycaliguyThe biggest excel lesson I learned from himA1=13.5 B1=A1*55.5555
⬐ tempodoxWhat's so special about 749.99925?⬐ boflynnhttp://www.nytimes.com/2015/09/21/business/a-huge-overnight-...The drug, called Daraprim, was acquired in August by Turing Pharmaceuticals, a start-up run by a former hedge fund manager [Shkreli]. Turing immediately raised the price to $750 a tablet from $13.50, bringing the annual cost of treatment for some patients to hundreds of thousands of dollars.I finally understand what ASMR is.⬐ unixhero⬐ NamTafAsberger?⬐ exmuslimIn case you were serious;https://en.wikipedia.org/wiki/Autonomous_sensory_meridian_re...
⬐ unixheroI was. Thanks.I really like the subtle animation on all updated cells as he types in a value. By default excel just updates it instantly but that makes it hard to see the scope of your change by eye.A quick scope through the options doesn't show what makes it do that. Anyone know?
⬐ m_gloeckl⬐ awl130IIRC those animations have been introduced with the current version, Office 2016.I think they make it really easy to follow, especially when someone else is editing and sharing their screen.
⬐ cm2187⬐ NamTafExactly, office 2016 is office 2010 with another color scheme and "new features" like this...⬐ QuppaOffice 2013 introduced these animations. Sadly, it also ditched ClearType in most of the UI.⬐ JensRexWhich is why I'm sticking with Office 2010.Microsoft totally wrecked font rendering in Windows 10 (W8 too?). Edge browser doesn't have ClearType either, which is braindead for a program you spend so much time reading in.
⬐ QuppaEdge 14 in the Anniversary Update actually brings back ClearType support for at least some text, so there's still a glimmer of hope that Microsoft will work to improve text rendering in Windows. Raymond Chen had an interesting post on how subpixel scrolling makes ClearType smoothing impossible: https://blogs.msdn.microsoft.com/oldnewthing/20150129-00/?p=...Update to answer my question: my work PC had 'turn off all unnecessary animations (when possible)' ticked in the ease of access centre under the 'make your computer easie to use'. I don't know if this is by default. Unticking it made these animations occur but wow it creates a bit of lag in excel even on my workstation. This is probably some configuration thing on my work's SOE and why they have it disabled in the first place.⬐ roel_vBy default this animation is on, yes. It's one of those (many) subtle things that makes Excel so awesome for non-trivial spreadsheet use, despite it being derided in certain circles.unbelievable that he's manually inputting all the numbers. either import directly from yahoo / google finance or capital iq.⬐ slap_shot⬐ bpizziIt's entirely intentional. The point isn't just to transpose the numbers from one medium to another; it is to understand what the numbers mean. The best way to do that is the way Martin does it in that video. That's how anyone who does this professionally does it.⬐ eru⬐ snowwrestlerIsn't this more error prone?⬐ beepboopbeepAbsolutely. But the OPs comment is spot on. Do it manually when you start out and you'll quickly learn both the importance of the numbers you're typing in and common issues you can run into with either manually inputting data or in structuring the model (or that one time you fat fingered a number and blew out everything driven by it)I assumed it's because he's teaching a lesson. Doing it by hand shows the video viewers how to go find these numbers in the public record, and along the way, what each number means and why it matters.Just pulling them all in at once from an API would be like a "magic" step to most people who are trying to learn about the basics of analyzing a stock.
⬐ roel_vI work as a scientific programmer and I used to have that reflex; even deriding others who couldn't or wouldn't automate such things for wasting time. To me, 'data' was just a blob, to be looked at as little as possible.I've done a 180 on that. The other day I started work on making a plant database, by hand; from designing the schema (columns and sheets in Excel really - blasphemy!) to typing in the values from encyclopedias, wikipedia and books by hand (ok, the latin names I copy/paste). Yes I could just use one of the several large, well-known databases; or one of the hundreds of specific-purpose ones. But making this database has taught me so much already, things I never would have learnt if I'd spend that time on writing import scripts.
Nowadays I let my students/analysts first do extensive eda, which is usually lots of tedious work that seems a waste of time to the programmer instinct. But it's not.
⬐ dermybabyCan you talk more about this? What do you your analysts do in terms of EDA ==> Python+ R + db work? Would you do it old school i.e writing it down?I practice writing things down when I cannot digest a huge piece of information. Or do visual notes like Mike Rohde.
⬐ roel_v⬐ SturgeonsLawData is mostly spatial so sometime with ArcGIS or QGIS, or R depending on what exactly it's about; R for statistical properties of data sets, scatter plots for variable relationships etc. Or Matlab for people with engineering backgrounds. I don't generally care what tools people use and everybody has their own experiences and background. Never need nosql databases (or RDBMS for that matter) for our type of work.I'm experimenting with physical notebooks myself since a few weeks actually. No electronic note taking has ever really worked well for me, although I've been getting by for 15 years. I'm not sure I'll ever find a system I'll really like - it always feels that as long as I find a way that forces me to get intimately familiar with data sets (to the point where I'm re-doing or at least re-thinking the ways the data was made to begin with), the insights bubble up by themselves. In other words I've come to the (regrettable) conclusion that methods and tools don't matter that much, it's the elbow grease that does. (of course I'm not claiming that I could analyze 15gb of data spread over 50 tables with Notepad...)
⬐ infinite8sThe best part about paper notebooks is being able to page through them years later. I experimented briefly with a computational notebook (tiddlywiki) in grad school, since my work was primarily computational, and unfortunately those were lost when my laptop died.That makes a lot of sense, and is why retyping code snippets from SO or wherever is more helpful than a straight copy-paste. There's something about manually typing information that makes you instantly more familiar with it.⬐ nommm-nommmWhen I was in college I noticed that when professors allowed you to make "cheat sheets" I rarely had to use said cheat sheet - the act of actually writing out the cheat sheet put the material in my head. It ended up being the most effective study method for me.I don't get why he's drawing a line instead or putting a proper border... Anyone got the subtility or is it that he just don't know about borders?⬐ duncanawoods⬐ shostackPossibly because a border is part of a column so if you insert a new column adjacent to a border you have to remove the old one and redraw it which is pretty annoying because its tucked behind a few clicks and menus.A line however floats relative to the columns so you don't have to remove/redraw it. At worst you might have to move it.
⬐ roenxiVisually both are good enough.Moving a line is 1x mouse click and a wrist flick,
Borders are more work than that.
⬐ squimmyHe explains that by using a line he can more easily move it "when the quarters move".Using a border would require removing the border and adding a new one every three months. A line can be dragged around at will. This also makes it more useful for miscellaneous illustrative purposes.
⬐ davejI'm an excel noob but doesn't table mode fix all this.Interesting bit at 53:40ish about eBays SEO strategy as it relates to their collecting structured data and solving a product shelf time problem.⬐ leshowHe could really use a tiling wm like i3 in his setup. my other tiling bros will agree that the mouse is for suckers.⬐ hood_syntax⬐ agumonkeySeems like it would be right up his alley too. xmonad is my personal favorite, but there are a lot of good ones out there (i3 included)More about finger speed. The excel part isn't that impressive. It is impressive, just that Excel has a lot to offer in terms of formulas and matrix generations that I didn't see here.I have to show this to emacsers, they're gonna feel jealous a bit.
⬐ youdontknowthoIs this the Turing Pharma guy? Weird. He's got crazy Excel skills, though.⬐ refurbVery common in investment banking. You're trained to use excel without a mouse or by navigating the menus. All the shortcuts are memorized.⬐ TomteUnfortunately, these shortcuts are language-dependent. Just as the function names.And you cannot switch Excel to English, but leave Outlook in German. Language setting is global over all Office programms.
And even if you could, at work the IT department has greyed out that setting via some policy. Even though I am Administrator on my work desktop, I cannot change the language!
⬐ gcb0⬐ baldfatit wasn't IT. on XP Microsoft realized everyone used the computer in English on all markets, so they started to charge for it as an extra language pack if you bought the os license in another region.sleazy, but still better than Apple.
⬐ tigershark⬐ coldteaApple doesn't charge you for a language pack in OS X, it ships with all the supported languages included. And also doesn't charge for OS X at all. I can't see how MS is better given that you pay for it.⬐ izacusThat's a sensible comparison only if you forget that you must to pay Apple premium for the hardware on which OSX is capable of running - the price is folded in there.⬐ coldtea>only if you forget that you must to pay Apple premium for the hardware on which OSX is capable of runningYou suggest they give their hardware for free? Or that they are forced to have their OS run in competitors hardware?
⬐ mantasSomeone pays for the Windows licence too. It's folded somewhere in there too.>And you cannot switch Excel to English, but leave Outlook in German. Language setting is global over all Office programms.Non native english speaker, but I use English menus for all programs, and English menu language for the OS.
As long as I can write and spellcheck in my native language, I am fine.
Unless I didn't speak english at all, why would I chose to see my language's word for Edit instead of "Edit", and never be able to take advantage of 2.000.000.000 tutorials, instruction videos, forum posts, that almost all assume english menu items?
⬐ V-2I do the same thing, and one of the reasons why I always do that is that it makes it way easier to troubleshoot stuff when you're using English locale.Googling will give you a much broader pool of solutions, and having to translate the names of options and commands you need help with just to gain access to this pool is an unnecessary extra step
⬐ methylIf you have your os locale set to non-native language, then most of the sites will default to English language as well. That's something that annoys me as hell and I wonder if there is any workaround...⬐ coldtea⬐ liotier...a different setting on the browser level?⬐ AdiraelThere's an option on the browser that will set the appropriate headers. Fallback is the OS language.> Non native english speaker, but I use English menus for all programs, and English menu language for the OS.So do I - on workstations I control. Alas the mandatory corporate Windows laptop isn't one of them, so I have to constantly switch between English on my own devices and French on that laptop... Definitely hampers shortcut memorization.
Also, a curse on Microsoft for localizing Excel formulas - what were they thinking ?
⬐ Kurtz79"Also, a curse on Microsoft for localizing Excel formulas - what where they thinking ?"+1. It's like programming in Java or C and all the keyworks (if, interface, etc...) were changed to the local language.
Madness.
⬐ brians⬐ acqqThat was a thing, for a while: ALGOL, COBOL, a few others had alternate keyword sets for non-English languages.⬐ skocznymroczny⬐ TomteI saw a "translation for C", which was a header that #defined the keywords to their local language equivalentsMS actually did that with Visual Basic for Applications in Excel 95.Umlaute in keywords, yay!
Example stolen from Wikipedia:
Prüfe Fall wd Fall 1 ' Auf Sonntag wird Datum vom letzten Freitag zurückgegeben VorherigerGeschaeftstag = dt - 2 Fall 2 ' Auf Montag wird Datum vom letzten Freitag zurückgegeben VorherigerGeschaeftstag = dt - 3 Fall Sonst ' Andere Tage: vorheriges Datum wird zurückgegeben VorherigerGeschaeftstag = dt - 1 Ende Prüfe⬐ yessqlUgh, I did some VBA in Germany around 2000. So crazy that keywords were in German (and command and periods have different meanings in numbers). Totally unportable code.The non-programmers in other countries are actually more confused with the English formulas than with their native ones. I know as, not living in the English speaking country, I had to explain various Excel functionality (and formulas) to such people.And it's true even for those who during some time in their life spent some years in some English speaking country.
⬐ HeavyStormI second that curse.⬐ harperleeWell I see the point to try to cater to users that might not be fluent in english - the target userbase for Excel is much more broad than a programming language, and if you want someone with low knowledge of both computers and english to be using Excel effectively, it helps a lot that e.g. your rows column is called "rows" and not asdfghjkl or something gibberish for you.You seem to be french - so I am confident you can picture someone (dad, mom, an old uncle perhaps?) that would have an easier time remembering that a function is called RECHERCHEV than if it were called VLOOKUP or ASDFNASF!#^E3 or what have you.
⬐ zhte415As long as there are not namespace clashes, it seems to make sense to allow both, simply as a parent said the pervasiveness of English language documentation (that MS do a lot to promote through awards and certificates).⬐ harperlee⬐ liotierI don't know who downvoted you without any comment, I for one believe that that could be a good idea: just support all languages as alias and hide what is not your language - but don't cap the use of the foreign functions. If the interface is gentile whenever you see a spreadsheet from another country, it could be feasible.> picture someone would have an easier time remembering that a function is called RECHERCHEV than if it were called VLOOKUPYes, but then make that language a user-switchable parameter... It is not like Microsoft is shy of adding parameters to the settings dialog...
Sadly Excl has caused so much human induced error that is not audit-able. Learn to program in a statistical language. Billions are lost due to using the wrong tool for the wrong job.http://www.centage.com/billion-dollar-errors-from-spreadshee...
⬐ cowsandmilk⬐ rb808Because:- programmers never make copy and paste errors in other languages
- programmers never reverse a plus and minus sign in other languages
- programmers never break things with a last minute change
- there are never errors due to the size of code bases
Oh, right. Nearly all of those errors happen no matter what language you are using. Because they are humans giving instructions to a computer, who just does what the human says.
(Ok, the version control complaint might be better handled by merging in git or other DVCS. And there are troubles when people give you a csv where a field is the text 1e24 and excel turns it into a number because you told it to be magic when importing, rather than specifying that column was text)
Edited to add: the focus of that article is on accounting. Double entry accounting was created to make you input everything twice to help ensure you are doing the correct thing. You mess up one of the two items and stuff doesn't balance. Modern accounting software automatically creates the second entry, so you have no idea whether you did something wrong. That's the real issue. We came up with an accounting system that is slower to help make sure you did everything correctly, then we automated it so you only have to input one entry so accounting is faster and all of a sudden, we are surprised that more mistakes are made!
⬐ baldfatYour missing the point PEOPLE make mistakes. Where will you see the mistakes in Excel or a program. In Excel it is or close to impossible to see the steps someone did to get to the output. In programming you can see every single step and have version control.> Because: > - programmers never make copy and paste errors in other languages
But they are discover-able and you can audit them and then fix them
> - programmers never reverse a plus and minus sign in other languages
But they are discover-able and you can audit them and then fix them
> - programmers never break things with a last minute change
Version control!
> - there are never errors due to the size of code bases
Yes this one is just STUPID
⬐ kbensonTL;DR - Excel is just a really accessible programming language, and as such it's used by a lot of amateurs. Like Perl in the 90's, the ease of getting started means a lot of people can use it, but the average quality of their code is abysmal, since the average level of experience is very low. That doesn't mean the tool itself is necessarily bad.-
Did you watch the Spolsky video? I only ask because this is all child comments from a top level comment about a Martin Shkreli video, and since I watched that first, you might have as well and not gotten to the original video. The way Joel suggests using Excel is very like how developers program when using good practices. Always name ranges and cells before using them. Never use numbers directly in formulas (unless obvious, like 365), use the named cell/range you set up. Use tables when you want tabular data so you are prevented from accidentally using the wrong formula for portions of ranges. Use the included named formatting themes for cells that are meant to be user editable, and those that are computed.
I watched the video and very quickly came to the conclusion that Excel is an IDE+Interpreter, like what people refer to when talking about Smalltalk (I assume, I've never actually used Smalltalk). The other thought I had is that it's probably the closest we've ever gotten to what people mean when they describe a visual programming language that just about anyone can use. Predictably, we often see the counterarguments to a system like that exhibited in Excel spreadsheets, which is poorly architected and designed spaghetti-code from people that don't have enough experience to know any better because they are amateurs.
Copy and paste errors look far more discoverable when you use R1C1 mode, and look to be outright prevented in a lot of cases when using tables.
Version control might be a good argument if it actually applied. Excel is a language, and I don't see any special version control baked into other languages programmers often use. It sits on top of them, and I don't see any reason why it couldn't sit on top of Excel as well. Export to a format that is version control friendly (perhaps with the help of an extra tool) and commit to VCS. Want to make a change? Check out the spreadsheet, convert as needed, and start working. Problem solved.
Really, I came away with a much clearer picture of what Excel specifically is after the Spolsky talk, and it probably wasn't even that advanced. What it did do was give a crash course for programmers that let you make the clear associations to what you do in other languages, so there was communication going on at a few levels.
⬐ mark-rVersion control is about more than just holding different versions and being able to restore them. It makes it so much easier to discover what changed between one version and another. Excel files are too opaque for that.⬐ kbensonI'm not sure how they are too opaque for that. If you are converting into an intermediary format, and saving that, depending on the format, it may even be easy. Here's someone that rolled their own solution, and they say they can edit it through a regular text editor[1]. Here's a company that provides a solution, which also purports to offer team collaboration[2]. Here's a free set of tools that provide versioning, change tracking,and diffing[3]. That's from a single google search, the first three results. I'm dubious that there's not a way to deal with this problem that fits most the sensible criteria you could come up with, given that this is obviously an need people have been working to serve for quite some while now.1: http://stackoverflow.com/questions/131605/best-way-to-do-ver...
I've worked a lot on trading floors the last 20 years. Excel was king in 90's and 2000's, recently there is a lot of Python & R around. Not that most of the really complex analytics and data come from C++/C#/Java DLLs or libraries, so many spreadsheets are complex but not that bad.⬐ KhalilK⬐ shostackIs there a way to reach you? I am a student with a few questions to ask if you don't mind.⬐ infinite8sExcel is still king on the trading floors I've seen (mostly fixed income). There's always a push from management away from trader created spreadsheets but it's so easy for them to mock up a quick calculation, and then they start using it daily...Not just IB. I'm in digital media and many managers/analysts have comparable skills. Really we are just looking at different metrics. Lots of the same data munging and modeling.A lot of what he was doing wasn't even that fancy, just lots of holding ctrl to navigate cell ranges quickly and pasting of formulas and such. I'm surprised he didn't format his data more nicely though, but if this is just for his own personal use I get it. Color formatting for inputs vs calculated metrics are really helpful though.
⬐ darthtoeI actually got into trouble at a bank because my excel plugin overrode a shortcut key⬐ ddeckI've had colleagues that disconnected their juniors' mice to ensure this.The reliance on excel in all aspects of investment banking is remarkable. From the simple stuff in corporate finance (merger analysis, capital issuance etc.) to complex derivatives pricing on the market side.
On the pricing side, external libraries typically handle the heavy lifting, but excel is the glue that brings it together. Certainly not the most efficient tool - I've had spreadsheets that take over an hour to calculate. You'd hit F9 and head out to lunch.
⬐ ricksplatI'm curious, is there a way to "profile" an excel application? If you suspect a sheet is taking longer to calculate than it should are there tools that can help you drill down to discover the bottleneck?⬐ ricksplat⬐ blowskiI googled it: "Excel Profiler" turns up a number of 3p products such as this http://www.decisionmodels.com/FastExcelV3Profiler.htm⬐ cm2187⬐ tjicOn decisionmodels, the website looks like it is coming straight from the 90s (but after all so does excel) but it is a gold mine of stuff to know before one can claims to really understand excel. I highly recommend the reading to anyone aspiring to be a "poweruser".⬐ osullivjSeconded. decisionmodels.com has excellent stuff on the difference between F9, sh-F9 and ctrl-sh-F9, and explains when the formula graph gets rebuilt. When you're building calc heavy XLL addins it's important to have a clear understanding of the behaviour of the calc engine code invoking your addin.Great question; I'd love to read an answer.⬐ imglorpI'm curious if that analysis could feed a parallelization step. Then you could offload work chunks to a compute farm.⬐ ricksplatI don't think you even need to go that far. Can you optimise your implementation so that parallelisable chunks could run across all your cores? This sounds like the kind of think Excel may even do "automatically" but like many such things you usually need to structure your implementation in a suitable fashion.⬐ imglorp⬐ doppenheIt would be kind sad if MS didn't already do that.⬐ ricksplatIt's a NP complete problem. What I mean is, there is no generalisable solution to this. Optimising algorithms employed by sophisticated technical users (think `-O`optimisation; Java hotspot) typically employ sets of heuristics that run the code in more optimal ways based on certain patterns. To trigger these you usually have to be aware of them and write your code in a certain way.Given that Excel isn't really targeted at these kinds of user (heavy lifting typically offloaded to external libraries) it wouldn't be "Sad" if they didn't already do it, and I would in fact be pleasantly surprised. Which is why I'm asking the question.
EDIT: Turns out you're right! https://msdn.microsoft.com/en-us/library/office/bb687899.asp...
⬐ dastbe⬐ ddeckCompiler writers also look for common patterns in people's code and figure out how to optimize them. when you have both years (decades) of legacy code and developers who don't even know what patterns are optimized, you as a compiler writer need to optimize the code that is being written.To trigger these you usually have to be aware of them and write your code in a certain way.⬐ ricksplatYes that's what I meant. Common patterns = Heuristics as a developer you need to be aware of the common patterns the compiler is looking for.Excel uses multiple cores for calculations. You can configure how many to prevent it sucking up the entire CPU for long calculations.It's a pretty easily parallelizable problem since the sheet is just a dependency tree with the cells as nodes.
The build-in functions (e.g. probability distributions etc.) could also be multi-threaded, although I'm not sure if they are. Our external API called from cells was written in C++ and already multi-threaded.
More info here:
https://msdn.microsoft.com/en-us/library/office/bb687899.asp...
ex Excel PM. There is a project that was between the Excel team and the high performance computing team at Microsoft for exactly this purpose. Not surprisingly mostly used by investment banks and insurance companies. (https://msdn.microsoft.com/en-us/library/ff877825(v=ws.10).a...)Exactly - its efficiency is that the analysts can write the formulas themselves instead of briefing a development team and waiting a week.⬐ BartweissGood point. The upside of Excel is that in return for predictably slow execution, you get a tool that can be used on the fly without learning SQL or adding a DBA layer to every task.⬐ mack73If you and your parent poster are right about why Excel is so prominent, and I personaly think both of you are, then we might be able to see a shift in economics and businessmanship and in the way Excel formulas play a large part of what it is that makes the world go 'round, once business men start to learn how to program, or when Excel start's to learn to talk "business", but no earlier than that.Which movement do you think is strongest? The AI camp or the ones that think programming should absolutely be a part of a proper education in business or economics? I hope for the latter, because I think it will be the end of the huge gap there is today, between the decision makers and the engineers, two groups who quite often but for no good reason feel alienated from each other.
⬐ RetraThe division of labor here is due to time constraints, not a failure to understand value. A lawyer can also be a doctor, if not for the fact that they'd get little of either done.If you make programs that 'speak business', then you make programs that are significantly more complex than those that 'speak computer'. Learning how to handle this complexity and optimize this kind of code is not something a business/economics person wants to spend their time doing. So they leave it to someone who specializes in it.
⬐ mack73Yes, I see what you mean. But surely, just adding Programming to the curriculum of businessmen, who I'm sure are quite intelligent or else they should be considering other occupations, does not equate to a person having to learn Another Occupation. Or do you think programmers, good ones, just know Programming?⬐ blowskiSome folks do combine excellent programming skills with excellent business skills, but that's not necessarily what everyone should aim for.I find that you can score someone on a whole range of skills, much as you can on computer games. The more overall skills you have, the more you will cost. If a job requires excellent programming skills but doesn't need any business skills, then paying for someone that has excellent business sills would probably be a waste of money.
In terms of your parent point about whether all business analysts should learn some programming, I think that programming skills are definitely helpful for business analysts, but they are neither necessary nor sufficient. Different skills help in different situations.
See https://www.youtube.com/watch?v=0nbkaYsR94c
Joel Spolsky did a really good one-hour primer on Excel. If you squirm at the thought of spreadsheet anything, this video is for you https://www.youtube.com/watch?v=0nbkaYsR94c
⬐ joshschreuderJust wanted to come back and say thanks for posting this.I don't think I've ever learnt so many great tips about a program I use quite frequently in this short of a video.
⬐ ericzawo⬐ NoneI took a course on Excel in high school and was just as shocked as you. It's an excellent, highly informative video. Pass it on!None
> One feature that would be cool is an Excel addin to which you would designate a bunch of cells from a spreadsheet as inputs and one output, would follow the logic and emit code to replicate this logic as a function.From having "you suck at excel" run in the background a few weeks back, I learned that feature is already in excel, there are "input", "calculation" and "output" cell formats/styles: https://youtu.be/0nbkaYsR94c?t=2756