Lots of talk about that Formula 1 result, but here’s a sports story from the weekend I wasn’t expecting to read about — the Microsoft Excel Financial Modeling World Cup. You can try it for yourself, if you like. Or find help on TikTok.
Tag: spreadsheets
You can count on them to help
Meet the Excel warriors saving the world from spreadsheet disaster – Wired UK
Research suggests more than 90 per cent of spreadsheets have errors, and half of spreadsheet models used in large businesses have “material defects”. Given some 750 million people use Excel globally, there are plenty of errors needing attention. One prominent researcher calls spreadsheets the dark matter of corporate IT. And that’s why people like Lyford-Smith have become defenders of the spreadsheet, mitigating the risks by fixing everyone else’s mistakes.
“Without adult supervision”
I enjoyed, if that’s the right word, this description of us from across the Atlantic, taken from The New York Times Morning Briefing: Europe Edition newsletter. Their description of us certainly feels quite accurate. Unfortunately.
Trump, Coronavirus, Sanda Dia: Your Monday Briefing – The New York Times
Britain, operating without adult supervision
Coronavirus cases in Britain are rising rapidly, with a record 12,871 new cases reported on Saturday evening. But as our correspondent Peter Goodman writes, you would scarcely imagine it on the streets of London, where masks hang below chins, punters cluster in pubs and cafes and rules around mask wearing or social distancing are frequently ignored.Beyond the obvious ways that this cavalier behavior is disconcerting, it has enhanced a widely shared sense that Britain — famously rule-abiding — is now operating without adult supervision. Public confidence has plummeted, with more than half of respondents in a recent survey declaring the government has botched its handling of the pandemic, up from 39 percent in May.
The current crisis seems exacerbated by an offshoot of the very virtue celebrated in Britain’s conventional historical narrative — an admirable refusal to bend. A national mantra, “keep calm and carry on,” seems to have been reconfigured into the misguided notion that nothing is amiss.
And as if to further illustrate the point about a lack of supervision.
Botched Excel import may have caused loss of 15,841 UK COVID-19 cases – Ars Technica
Public Health England admitted on Sunday that the agency has under-reported COVID-19 infections by 15,841 cases in recent days due to a “technical issue.” The missing positive tests were conducted between September 25 and October 2 and have since been added to national statistics, the agency said.
Excel: Why using Microsoft’s tool caused Covid-19 results to be lost – BBC News
“Excel was always meant for people mucking around with a bunch of data for their small company to see what it looked like,” commented Prof Jon Crowcroft from the University of Cambridge. “And then when you need to do something more serious, you build something bespoke that works – there’s dozens of other things you could do. But you wouldn’t use XLS. Nobody would start with that.”
Photo Tolga Akmen/Agence France-Presse
To err is — costly
Spreadsheet error led to Edinburgh hospital opening delay – BBC News
An NHS Lothian-commissioned review found a “human error” in a 2012 spreadsheet with the specifications for air flow in critical care rooms. The mistake was missed in what auditors describe as a “collective failure”. It was only when the hospital had been handed over to NHS Lothian, and £1.4m monthly repayments had started, that independent checks found the critical care rooms were operating with the wrong air flow. Remedial work worth £16m has since been carried out and the new Sick Kids building started hosting outpatient appointments in July.
Say ‘I do’ with a spreadsheet? I’m glad I didn’t
I happily sit and play work with spreadsheets all day, but some people can take things too far.
Inside the hyper-organised world of wedding planning spreadsheets
I, too, initially scoffed at the wedding spreadsheets. It’s just a party! Why do people get so crazy about weddings?! I’m going to be a chill bride! But then I started to think about the venue. And the guest list. And the food. And the drink. And the music. And the transport. And the photographer… Of course, you can do away with as many of the details as you want (one spreadsheet I came across included tips on matching your tiara to your skin tone), but as much as you may wish to rail against the wedding-industrial complex, if you are getting married and want to do anything other than elope in secret, then mark my words, you will end up using a spreadsheet. […]
As a result, while the spreadsheets can be generally helpful, they can also quickly spiral out of control, becoming a bullet-pointed reminder of all the social expectations heaped on people (and especially women) preparing for what they’re conditioned to believe must be a perfect event. Got a misalignment between guest numbers and venue capacity? You fail, back to square one! Didn’t factor in a hair and make-up practice session? What sort of bride are you? Forgot a flower buttonhole for the groomsmen? Game over, perfection not achieved! In one spreadsheet shared with me, you can almost hear the pleading of an embattled bride trying to keep a grip as she adds yet another entry: “Who will move presents and cards during cocktail hour, and to where?” Who will move the presents during cocktail hour? To where? Hang on a minute, I’m supposed to have a cocktail hour?
It was a while ago now, but in my case we didn’t so much want to get married, as be married — makes for a much simpler and calmer approach.
Excel errors are everywhere
I know that Excel is only trying to be helpful when it ‘corrects’ what it sees as formatting errors, but it really needs to pack it in.
An alarming number of scientific papers contain Excel errors
A team of Australian researchers analyzed nearly 3,600 genetics papers published in a number of leading scientific journals [and] found that roughly 1 in 5 of these papers included errors in their gene lists that were due to Excel automatically converting gene names to things like calendar dates or random numbers.You see, genes are often referred to in scientific literature by symbols — essentially shortened versions of full gene names. The gene “Septin 2” is typically shortened as SEPT2. “Membrane-Associated Ring Finger (C3HC4) 1, E3 Ubiquitin Protein Ligase” gets mercifully shortened to MARCH1.
But when you type these shortened gene names into Excel, the program automatically assumes they refer to dates — Sept. 2 and March 1, respectively. If you type SEPT2 into a default Excel cell, it magically becomes “2-Sep.” It’s stored by the program as the date 9/2/2016.
Excel timesavers
I sit and stare at Excel for a significant proportion of my day. I can’t believe I’ve not been aware of this simple trick with copying formulas without messing up cell references. It’s saving me an immense amount of time.
Copy Excel formula without changing cell references (or without file references)
It’s quite simple actually!
- Highlight the are you’d like to copy
- Go to Home / Find & Select / Replace (or press Ctrl + H)
- Search for = and replace with a text that’s not in your file – in this example I chose “notinfile” (note as mentioned in the comments in YouTube, you can also replace with ” =”, i.e. a space before the equal sign)
- Go back to Home / Find & Select / Replace (or press Ctrl + H) – search for your text – in my example “notinfile” and replace with =.
- That’s it!
Here are a few more tips and tricks.
10 easy Excel timesavers you might have forgotten
Microsoft has packed Excel with all kinds of different ways to get things done quicker. However, you can’t take advantage of these features if you don’t know about them. These ten techniques may only save you a few seconds every time you use them. That might not sound like much, but if you can integrate them into your workflow, you’re sure to reap the benefits over time.
Office moves?
How many of us spend all our working days with Microsoft Office products? It’s sobering to think that I’ve been staring at monitors full of Outlook emails, Word documents and Excel spreadsheets for more than 20 years now. Might that all be changing soon? We’ll see.
The new word processor wars: A fresh crop of productivity apps are trying to reinvent our workday
Nearly 30 years after Microsoft Office came on the scene, it’s in the DNA of just about every productivity app. Even if you use Google’s G Suite or Apple’s iWork, you’re still following the Microsoft model.
But that way of thinking about work has gotten a little dusty, and new apps offering a different approach to getting things done are popping up by the day. There’s a new war on over the way we work, and the old “office suite” is being reinvented around rapid-fire discussion threads, quick sharing and light, simple interfaces where all the work happens inside a single window.
The article lists the alternatives as Quip, Notejoy, Slite, Zenkit, Notion and Agenda for documents and Smartsheet, Airtable, Coda and Trello for spreadsheets.
Their informal, cartoony visuals and emphasis on chatty messaging collaboration makes everything feel a little juvenile and jokey.
I wonder if my demographic is supposed to be represented on that Coda homepage by the grey-haired, casual-suit-no-tie coffee-drinker in the bottom right-hand corner. I’ve certainly never taken an ice-cream, a skateboard or a basketball to work, so I guess it must be, fist-bump-at-the-stacked-area-chart notwithstanding.
Excel-lent architecture
I love this new take on an old art form.
The spreadsheet architecture of Emma Stevens
Normally the mere mention of a spreadsheet can bring a distant glaze across the eyes of most creatives – the file format perceived as the antithesis of imagination by those desiring to create rather than tabulate. But Australian landscape architect Emma Stevens imagined the mundane Excel spreadsheet as an opportunity rather than an impediment to exploring art, using a tried and true technique of type as a medium to create a vast skyline out of text and cells.
It’s hard to believe that it’s the same tool, but used in a very different way, as the one this Japanese artist uses.
Meet Tatsuo Horiuchi, the 77-year-old artist who ‘paints’ Japanese landscapes with Excel
For over 15 years, Japanese artist Tatsuo Horiuchi has rendered the subtle details of mountains, cherry blossoms, and dense forests with the most unlikely tool: Microsoft Excel. The 77-year-old illustrator shunned the idea of paying for expensive painting supplies or even a basic drawing program for his computer, saying that he prefers Excel even over Microsoft Paint because it has “more functions and is easier to use.” Using simple vector drawing tools developed primarily for graphs and simple shapes, Horiuchi instead draws panoramic scenes of life in rural Japan.
Sadly, all the spreadsheets I create are far more conventional.
Excel’s getting interesting. No, really
News that Excel will soon be expanding its range of data types, enabling a much richer and more dynamic experience.
Excel Data Types
AI powered Excel Data Types will transform the way we work with Excel by enabling a cell to contain much more than text, numbers or formulas.There are currently two Excel data types available to Office 365 users; Stocks and Geography. Let’s start with the Geography Data Type that can take a table of countries and return rich data that can be referenced in Excel formulas and expand into further columns.
Mynda takes us through many other examples of how these new data types can be used and referenced in our spreadsheets. And it seems like this is just the beginning.
The Excel team have big plans for Data Types with more coming, including the ability to create your own data types unique to your organisation. Imagine data types for Employees, Products, Stores, Regions… the list is endless.
Excel tips and tricks
Often with these kinds of training videos it’s the ‘ooh I’d forgotten you could do that‘ moments that are the most useful. The overall topic of the tutorial itself might not be what you’re after (do you really need to know how to make a Christmas Tree in Excel?), but just simple reminders about things like F9 or index match are just the ticket.
Here are some of my recent favourites:
From Exceljet‘s channel, How to fill in missing data with a simple formula
From Leila Gharani‘s channel, Excel index match advanced: lookup multiple criteria in rows or columns
From the My Online Training Hub channel, Labeling events in Excel charts
From the Excel Is Fun channel, Excel magic trick 1474: Excel twinkling Christmas Tree with star & formulas as presents!
A time before Excel?
An article from 1984 about these crazy new electronic ledger sheets, or “spreadsheets”.
A spreadsheet way of knowledge
There is no doubt that the electronic spreadsheet saves time and provides insight; there is no doubt that even greater benefits will one day be derived from these grids. Yet all these benefits will be meaningless if the spreadsheet metaphor is taken too much to heart. After all, it is only a metaphor. Fortunately, few would argue that all relations between people can be quantified and manipulated by formulas. Of human behaviour, no faultless assumptions – and so no perfect model — can be made.
I wonder what the author would have made of the Quantified Self movement, that attempts to incorporate data from a person’s daily life into various formulas and models. Spreadsheets are everywhere now, not just in the office of accountants from Cincinnati.
But who could possibly hate Excel?
This tool makes it stupid simple to turn data into charts
Here’s something handy for people who hate Excel. Venngage, the company that lets you make infographics with the click of (a few) buttons, recently launched a new product called Beam. Beam does for charts what Venngage does for infographics as a whole. Which is to say, it makes the process of transforming data into useful visuals very, very easy.
And they look really spidery too
Another great Excel article from Mynda Treacy, this time about her views on radar charts. It’s safe to say she’s not a fan. Some great points here about data visualisation and how to get messages across. I’ve never used a radar chart, but that’s more because I’ve never really understood them.
Excel alternatives to radar charts
Radar charts display data in a circular fashion, which is the opposite of the straight line comparisons we’re able to subconsciously perform. This means we have to work hard to make any comparisons and as a result we’re likely to make mistakes in our assumptions.
She mentions this article by Stephen Few, who weighs in further but does recognise when these radar charts might have their uses.
Keep radar graphs below the radar – far below (pdf)
This one advantage motivates me to ease up just a bit on my repugnance toward radar graphs.
If only
PowerPoint should be banned. This PowerPoint presentation explains why.
It is estimated that more than 30 million PowerPoint presentations are given every day. But as PowerPoint conquered the world, critics have piled on. And justifiably so. Its slides are oversimplified, and bullet points omit the complexities of nearly any issue.
A great PowerPoint about PowerPoint, with examples that really hit the mark. “Make these slides the last ones you ever read”? If only. It’s far too ingrained and we’re far too lazy. And don’t think Prezi’s any better, unless you’re a fan of motion sickness.
But wait, here’s a different view.
Tools are not to blame, blame laziness
PowerPoint and Excel aren’t the enemy, though a lot of people to seem to think they are. Frequently, I hear others say “don’t use PowerPoint” or snicker when someone mentions conducting analysis with Excel. This is wrong. There is nothing wrong with Excel or PowerPoint; I am a fan and user of both programs. The problem is the creativity and capability of those using those programs and it’s time to realize that.
And here’s a great piece on PowerPoint from Russell Davies.
29 Bullets
9. Lingua franca PowerPoint is how organisations communicate. Email someone a PowerPoint file and there’s a high chance that they’ll be able to open it. Most organisational knowledge is probably stored in PowerPoint files. (Though it feels like I should be writing that as ‘knowledge’ and ‘stored’) Microsoft understand this power, of course, and they work very hard to make PowerPoint backwardly compatible. You can open a PowerPoint 3.0 file from 1992 in today’s version on an iPad Pro and all the animations will run as intended.
Clever lists in Excel
Create dependent drop down lists in Excel
“Limit the choices in an Excel drop down list, by using named ranges and the INDIRECT function, to create dependent data validation lists. For example, select Fruit in column A, and only Fruit items appear in the drop down list in column B.”
Another Excel solution in search of a problem. I must be able to try this out on something…
Miscellaneous Details 