7 ms·

I quote, "Excel has actual programming affordances now"... Range names "fixes one of the biggest problems that makes spreadsheets illegible" "Instead of writin

I quote, "Excel has actual programming affordances now"... Range names "fixes one of the biggest problems that makes spreadsheets illegible" "Instead of writing the formula =A1B1, you can do =WidthHeight like you should have been able to 30 years ago." Not sure what this dude is talking about. Range names have been in Excel for decades.

moomin 4y ago
He’s conflating it with LET and LAMBDA, which are new. I worked for a firm that did extremely expensive training course for Corporate Finance back in the 90s. We taught people how to use named ranges. There’s probably still good money in that business.
- chiph 4y ago
  In 2022, there's probably a new generation working at those clients who have never seen the advanced features in Excel. You should put some feelers out to see if there's any demand.
listenallyall 4y ago
It's like a TIL post on the front page of Reddit that states something you assumed everybody knew, yet has 30,000 upvotes.
- mathteacher1729 4y ago
  XKCD 1053 is relevant here. You and I knew it, but there are going to be lots of people who learn about it for the first time today, and that's a net positive.
  - ms512 4y ago
    https://xkcd.com/1053/ https://xkcd.com/1053/ (to make a link available)
  - deleted 4y ago
    [deleted]
  - datavirtue 4y ago
    Yeah, but not a post for HN. Did you know Excel has a programming language!!!??? Whahhh!!!
    - Strom 4y ago
      HN still accepts new accounts. It's not an exclusive club for old people. Even beyond that, stories have been repeating here since the beginning. Not everyone has the same life experiences, and not everyone checks HN at the same time.
    - Aeolun 4y ago
      I didn’t know about named ranges, and think it’s pretty cool. Of course, when I thought of places where I could apply them I realized that by the time I use enough Excel to use named ranges, I should probably switch to a real programming language.
- hwayne 4y ago
  Yes but did you know about the yoga pose button
  - kurupt213 4y ago
    i still don't know what that meant
    - hwayne 4y ago
      The feature name is "linked data types", under the "data" tab. Put, say, "downward dog" into a cell and click the "yoga" linked data type. There's other, more useful data types, like cities and ZIP codes and stocks, I just listed the yoga one because it's the funniest.
      - cookie_monsta 4y ago
        Looks like the yoga button is a thing of the past :( > After June 11, 2023, data types by Wolfram will no longer be supported and can't be refreshed. However, Bing, Power Query, and Organization data types will still be supported. https://support.microsoft.com/en-us/office/what-linked-data-types-are-available-in-excel-6510ab58-52f6-4368-ba0f-6a76c0190772 https://support.microsoft.com/en-us/office/what-linked-data-...
        yen223 4y ago
        Perhaps there are time zone differences, but it's not 2023 yet where I am =)
        cookie_monsta 4y ago
        haha good point, but I can't see those data types?
- turtleyacht 4y ago
  It's the learn TLA+ person :) Awesome to hear he is exploring Excel. Is that the manual version of crunching models for formal consistency? Management science is all about modeling stuff in Excel and using solvers. Imagine if somehow we could meld the robustness of TLA+ with the immediacy of an in-built macro-lang, ubiquitously installed .exe that is the spreadsheet. Future sprint planning days: everyone prototypes in the spreadsheet! No one estimates until the rules and formulae make sense.
monkey_monkey 4y ago
Excel has also finally added sum() and other functions that allow the spreadsheet to do the hard work instead of making you use a calculator to add all the numbers up.
- _dain_ 4y ago
  You joke, but: https://www.reddit.com/r/excel/comments/a0wot5/excelgore_stories_in_the_office/ealyi57/ https://www.reddit.com/r/excel/comments/a0wot5/excelgore_sto... >A elderly guy - maybe in his 60s - was writing his book of poems on his computer and brought in a floppy disk because he wanted some advice on printing. We managed to find a plug in floppy drive but there was only an Excel file on the disk. I opened the file and he had written his poetry book in Excel cells, with widened columns and rows, complete with spaces to center text and indent paragraphs etc. When one cell got full of text he moved to the next. New poems were started a couple of columns over. I remember he also asked how to change the size of the font for the initial letter of each verse. He must have been using Excel 2003 or something because when he saw the ribbon, which was new to Excel 2007 he said it might not work properly because he used Excel. I tried explaining he should use MS Word. He said "oh I got a disk with that on." He pulled out another floppy and there was a file called houseke~.doc. I feared the worst. He had a Word table over several pages where he kept his home accounts, all beautifully typed in by hand, decimal points all lined up (hell I can't even do that now), not a calculation in sight - they were all done by a calculator and hand-entered.
  - smikhanov 4y ago
    I wish I would be like that in 20 years (I’m in my 40s now)
  - themadturk 4y ago
    I long for the days of all-in-one word processor/spreadsheet/database apps. Eons ago I owned the long-forgotten Cambridge (formerly Sinclair) Z88, their 1987 entry into the laptop market. Its main software was called Pipedream, and as I remember did everything in what was essentially a spreadsheet, with all three application types available in the same file. The software was available for DOS as well.
detaro 4y ago
The structure of that initial part isn't great given the headline, but I don't think its intending to claim that that's really new, just fairly unknown. And then talks about about the new stuff.
- anamexis 4y ago
  The "like you should have been able to 30 years ago" bit suggests otherwise, since you have been able to for at least 20 years.
andylynch 4y ago
but it’s somehow underused. Often people don’t even realise they should be creating tables at all. Power query and friends are black magic at that point. It sounds slightly absurd but advanced Excel training is something I think many people should do. At $oldfinancejob the guys who ran the client professional development business clearly picked up on this and ran a very nice ‘Excel for financial modelling’ course as a free intro kind of thing - after using Excel for decades there were plenty of things I didn’t know about and now use. Excel is an incredibly deep product.
- tda 4y ago
  At my former company I first naivly tried to get more (non software) technical experts to use python and databases. That was quite an uphil battle, and we quickly pivoted to teach them how to make better use of excel. First and foremost: use tables. That easily leads to clearly deliniating input data, computations and output. Then we proceded to provide template sheets that use powerquery to fetch shared input data from centralised API's. This way we could let the end user do all the work, even though it might be a bit more messy/error prone. For well extablished workflows I could then take the excel program as a spec, and build a e.g. a webservice from that. Ss all the exceptions etc are already dealt with by the end-users, you can basically reverse engineer the spec from the excel sheet. As long as you can guide them to making legible excel sheets, it saves so much misunderstandings vs writing specs from scratch and building from that.
  - MrsPeaches 4y ago
    > I could then take the excel program as a spec, and build a e.g. a webservice from that That sounds like a potentially great approach for consultancy business/product discovery.
    - lappet 4y ago
      you can already do this with Google Sheets. I wonder if Office 365 has similar capabilities.
Stratoscope 4y ago
Just an HN formatting note... To have * characters taken literally instead of italicizing the text in between, prefix each one with a backslash, like this: Instead of writing the formula =A1\*B1, you can do =Width\*Height which formats as: Instead of writing the formula =A1*B1, you can do =Width*Height
jbverschoor 4y ago
Always reminds me of this video.. https://www.youtube.com/watch?v=0nbkaYsR94c https://www.youtube.com/watch?v=0nbkaYsR94c Excel has proper tables, named columns, better structured data etc.
zweifuss 4y ago
Namend cells and ranges was one of the major features of Microsoft Multiplan (1982). See p. 60 in [1]. MS Excel 2.0 (1987) could do the same, but entering names for ranges was not as easy as in Multiplan. Look in the menu for Formula | Names… [1] https://usermanual.wiki/Manual/MicrosoftMultiplanmanual.388070212.pdf https://usermanual.wiki/Manual/MicrosoftMultiplanmanual.3880...
- fencepost 4y ago
  Naming ranges in Excel is pretty darn easy, at least if you're doing full columns or rows - you select the range you want named (including by clicking the row/column header), then click into the field just to the left of the formula button that shows the selected cell. Just edit the name right there, it'll apply that name to the selected range. Related, if that area shows a little dropdown arrow at the right side by clicking the dropdown you get a list of the named ranges on the current sheet and can choose a name to select that range of cells. Edit: Editing ranges is a little trickier, for that go to the Formulas tab and look for Name Manager (it's the main icon in one of the tab bar sections).
mcdonje 4y ago
Custom formulas have also been around for decades, it just required VBA.

moomin 4y ago

He’s conflating it with LET and LAMBDA, which are new. I worked for a firm that did extremely expensive training course for Corporate Finance back in the 90s. We taught people how to use named ranges. There’s probably still good money in that business.

chiph 4y ago
In 2022, there's probably a new generation working at those clients who have never seen the advanced features in Excel. You should put some feelers out to see if there's any demand.

listenallyall 4y ago

It's like a TIL post on the front page of Reddit that states something you assumed everybody knew, yet has 30,000 upvotes.

mathteacher1729 4y ago
XKCD 1053 is relevant here. You and I knew it, but there are going to be lots of people who learn about it for the first time today, and that's a net positive.
- ms512 4y ago
  https://xkcd.com/1053/ https://xkcd.com/1053/ (to make a link available)
- deleted 4y ago
  [deleted]
- datavirtue 4y ago
  Yeah, but not a post for HN. Did you know Excel has a programming language!!!??? Whahhh!!!
  - Strom 4y ago
    HN still accepts new accounts. It's not an exclusive club for old people. Even beyond that, stories have been repeating here since the beginning. Not everyone has the same life experiences, and not everyone checks HN at the same time.
  - Aeolun 4y ago
    I didn’t know about named ranges, and think it’s pretty cool. Of course, when I thought of places where I could apply them I realized that by the time I use enough Excel to use named ranges, I should probably switch to a real programming language.
hwayne 4y ago
Yes but did you know about the yoga pose button
- kurupt213 4y ago
  i still don't know what that meant
  - hwayne 4y ago
    The feature name is "linked data types", under the "data" tab. Put, say, "downward dog" into a cell and click the "yoga" linked data type. There's other, more useful data types, like cities and ZIP codes and stocks, I just listed the yoga one because it's the funniest.
    - cookie_monsta 4y ago
      Looks like the yoga button is a thing of the past :( > After June 11, 2023, data types by Wolfram will no longer be supported and can't be refreshed. However, Bing, Power Query, and Organization data types will still be supported. https://support.microsoft.com/en-us/office/what-linked-data-types-are-available-in-excel-6510ab58-52f6-4368-ba0f-6a76c0190772 https://support.microsoft.com/en-us/office/what-linked-data-...
      - yen223 4y ago
        Perhaps there are time zone differences, but it's not 2023 yet where I am =)
        cookie_monsta 4y ago
        haha good point, but I can't see those data types?
turtleyacht 4y ago
It's the learn TLA+ person :) Awesome to hear he is exploring Excel. Is that the manual version of crunching models for formal consistency? Management science is all about modeling stuff in Excel and using solvers. Imagine if somehow we could meld the robustness of TLA+ with the immediacy of an in-built macro-lang, ubiquitously installed .exe that is the spreadsheet. Future sprint planning days: everyone prototypes in the spreadsheet! No one estimates until the rules and formulae make sense.

monkey_monkey 4y ago

Excel has also finally added sum() and other functions that allow the spreadsheet to do the hard work instead of making you use a calculator to add all the numbers up.

_dain_ 4y ago
You joke, but: https://www.reddit.com/r/excel/comments/a0wot5/excelgore_stories_in_the_office/ealyi57/ https://www.reddit.com/r/excel/comments/a0wot5/excelgore_sto... >A elderly guy - maybe in his 60s - was writing his book of poems on his computer and brought in a floppy disk because he wanted some advice on printing. We managed to find a plug in floppy drive but there was only an Excel file on the disk. I opened the file and he had written his poetry book in Excel cells, with widened columns and rows, complete with spaces to center text and indent paragraphs etc. When one cell got full of text he moved to the next. New poems were started a couple of columns over. I remember he also asked how to change the size of the font for the initial letter of each verse. He must have been using Excel 2003 or something because when he saw the ribbon, which was new to Excel 2007 he said it might not work properly because he used Excel. I tried explaining he should use MS Word. He said "oh I got a disk with that on." He pulled out another floppy and there was a file called houseke~.doc. I feared the worst. He had a Word table over several pages where he kept his home accounts, all beautifully typed in by hand, decimal points all lined up (hell I can't even do that now), not a calculation in sight - they were all done by a calculator and hand-entered.
- smikhanov 4y ago
  I wish I would be like that in 20 years (I’m in my 40s now)
- themadturk 4y ago
  I long for the days of all-in-one word processor/spreadsheet/database apps. Eons ago I owned the long-forgotten Cambridge (formerly Sinclair) Z88, their 1987 entry into the laptop market. Its main software was called Pipedream, and as I remember did everything in what was essentially a spreadsheet, with all three application types available in the same file. The software was available for DOS as well.

detaro 4y ago

The structure of that initial part isn't great given the headline, but I don't think its intending to claim that that's really new, just fairly unknown. And then talks about about the new stuff.

anamexis 4y ago
The "like you should have been able to 30 years ago" bit suggests otherwise, since you have been able to for at least 20 years.

andylynch 4y ago

but it’s somehow underused. Often people don’t even realise they should be creating tables at all. Power query and friends are black magic at that point. It sounds slightly absurd but advanced Excel training is something I think many people should do. At $oldfinancejob the guys who ran the client professional development business clearly picked up on this and ran a very nice ‘Excel for financial modelling’ course as a free intro kind of thing - after using Excel for decades there were plenty of things I didn’t know about and now use. Excel is an incredibly deep product.

tda 4y ago
At my former company I first naivly tried to get more (non software) technical experts to use python and databases. That was quite an uphil battle, and we quickly pivoted to teach them how to make better use of excel. First and foremost: use tables. That easily leads to clearly deliniating input data, computations and output. Then we proceded to provide template sheets that use powerquery to fetch shared input data from centralised API's. This way we could let the end user do all the work, even though it might be a bit more messy/error prone. For well extablished workflows I could then take the excel program as a spec, and build a e.g. a webservice from that. Ss all the exceptions etc are already dealt with by the end-users, you can basically reverse engineer the spec from the excel sheet. As long as you can guide them to making legible excel sheets, it saves so much misunderstandings vs writing specs from scratch and building from that.
- MrsPeaches 4y ago
  > I could then take the excel program as a spec, and build a e.g. a webservice from that That sounds like a potentially great approach for consultancy business/product discovery.
  - lappet 4y ago
    you can already do this with Google Sheets. I wonder if Office 365 has similar capabilities.

Stratoscope 4y ago

Just an HN formatting note... To have * characters taken literally instead of italicizing the text in between, prefix each one with a backslash, like this: Instead of writing the formula =A1\*B1, you can do =Width\*Height which formats as: Instead of writing the formula =A1*B1, you can do =Width*Height

jbverschoor 4y ago

Always reminds me of this video.. https://www.youtube.com/watch?v=0nbkaYsR94c https://www.youtube.com/watch?v=0nbkaYsR94c Excel has proper tables, named columns, better structured data etc.

zweifuss 4y ago

Namend cells and ranges was one of the major features of Microsoft Multiplan (1982). See p. 60 in [1]. MS Excel 2.0 (1987) could do the same, but entering names for ranges was not as easy as in Multiplan. Look in the menu for Formula | Names… [1] https://usermanual.wiki/Manual/MicrosoftMultiplanmanual.388070212.pdf https://usermanual.wiki/Manual/MicrosoftMultiplanmanual.3880...

fencepost 4y ago
Naming ranges in Excel is pretty darn easy, at least if you're doing full columns or rows - you select the range you want named (including by clicking the row/column header), then click into the field just to the left of the formula button that shows the selected cell. Just edit the name right there, it'll apply that name to the selected range. Related, if that area shows a little dropdown arrow at the right side by clicking the dropdown you get a list of the named ranges on the current sheet and can choose a name to select that range of cells. Edit: Editing ranges is a little trickier, for that go to the Formulas tab and look for Name Manager (it's the main icon in one of the tab bar sections).

mcdonje 4y ago

Custom formulas have also been around for decades, it just required VBA.