Inside the World Excel Championships (Yes, You Read That Right) The excitement is off the charts at the Olympics of competitive ‘spreadsheeting’

Wall Street Journal ^ | December 11, 2023 | Robert McMillan

[Migraine]

I, too, know enough Excel to be competent (old wheezer that I am, I’ve been at it since its first appearance).

But I wish I knew just 1% of what these whizzes know. I’d be so doggone proud.

[RainMan]

I was a consultant for Microsoft. Excel God was given to me by my Microsoft Customer. I did the analysis of all the bid desk records (non-normalized data) to determine the discount structure for Disti’s and Resellers globally based off of bid desk data that would be a net neutral cost to Microsoft when a Big 4 accounting firm on retainer dropped the ball. I followed up the analytics work with making the presentation to Finance for her where we got final approval to go live.

For myself, I used Solver to create a spreadsheet where I could input 20 variables about a foreclosed property to determine the price I should pay at auction to achieve the returns that I was looking for. On TV, the returns they show are mostly bogus, because there are costs they are not always showing (usually commissions) that would make the deal not profitable. In six years of flipping, I never lost money on a single property. My spreadsheet was usually within +/- $5K of what reality ended up being.

[Migraine]

I was half-competent on Lotus 1-2-3. Moving over to Excel, I already had the “concept” down, but had to relearn a lot. As of now, I know about 1% of what these competitors know. I envy them that.

[rlmorel]

I never knew there was such a thing. Fascinating.

All pasty white guys like me.

I like Excel and Word. Excel is extremely useful in a wide variety of ways. I used to like Outlook, but they only let us use the Web version now. I dislike it intensely.

I often export values from one thing that have to be imported into something else, and the data exported is missing values that must be present for the import. You can select each cell with a value and drag it down until another value is populated, which is okay if you have ten or twenty rows. But if you have 10,000 or 100,000, it is a maddening task which can take a looooong time and is prone to error.

One of the most useful tricks I ever learned is, if you have a columns sorted in a particular way, and all the values in a column should all have the same value (as it is sorted) but only the first line in each group has a value and all the rest are blanks...

In this example below which was exported from something, we have all the Conference Rooms in a facility, the person responsible for them, and the color code area the room is in.

We have all the rooms, but because of how we extracted them from some system, they are missing either the name of the person responsible or the color coding. But you know Rooms 1-100 are covered by Smith, and are in the Blue zone, 101-200 are covered by Jones in the Gold zone, etc.

To populate some other system, you need to have EVERY cell in every row populated or the import will fail (or be useless if it succeeds)

You can select the entire column(s) with the missing values (that have one at the top of each grouping or maybe some randomly intermixed). Then, from the Find& Select menu, you choose Go To Special.

When the dialogue comes up, you select Blanks from the choices.

When you can see the blank cells highlighted, type the = sign, then type the up arrow on your keyboard, and then finally, hit CTRL-Enter. It fills in all the blanks with the value above it, so you have everything populated on every row.

Then, you have to select the ENTIRE row(s) (in this example, rows B and C) copy them, then Paste Special (values) to overwrite the cells with formulas in them. If you don't do this, your cells will be formulas instead of text or number values.

Over the years, I have saved so much time with this one thing, I thought I would share it. Of course, I recognize this may be known by nearly everyone who has to use Excel seriously, but...until someone told me, I didn't know about it...:)

[Uncle Miltie]

In 1987 on Lotus 123, I created the first full company integrated income statement, balance sheet, and cash flow statement forecasts that Donaldson Lufkin Jenrette had ever seen for an IPO.

The President of the company called it “The BumFum” for BMFM or Big Mother F***ing Model.

We scored $120,000,000, which I then managed.

Good times.

[cgbg]

I want to know if they have a Special Olympics for folks who have uh...

challenges....

;-)

[The Truth Will Make You Free]

That’s a VERY neat trick. I’ve never used Select Special - Blanks.

I have used Paste Special a lot. To get rid of those formulas, you could copy the data, and then use Paste Special - Values over the same region. That would allow you to sort the data without issues.

[The Truth Will Make You Free]

A client for whom I built an Excel sales commission program once called me “God’s own computer son.” LOL.

[dennisw]

See their 27” LED displays. Anyone know what brand they are? I can only see the large diamond shape on the back of them. I had Acer displays fail on me. HP displays have been solid for me.

[Uncle Miltie]

We all have our claims to fame. You have to score your wins to stay sane.

Another of my wins was that the Solution Selling book by Michael Bosworth has a chapter about establishing a financial proof with an “ROI” (really an IRR, but that doesn’t sell as well). That chapter is 100% me, I taught the Bosworth brothers how I did that with the Capital Budgeting techniques I learned in my Econ degree at Claremont McKenna College. Salt that with some salesmanship, marketing and Excel skills, and you have the CFO of Fortune 500 companies in the palm of your hand.

I retired this year. I’m going to the gym next for my daily 2 hour work out. I’ve never felt better. 62 and free as a bird.

[Noumenon]

I cut my spreadsheet teeth on VisiCalc loaded from 5 1/4” floppies back in the day. Then along came Lotus 123. Excel after that.

[dfwgator]

As one who uses Excel a lot in my work, I might actually enjoy this.

[dfwgator]

I do a lot of programming involving creating and manipulating Excel Spreadsheets.

[Political Junkie Too]

Two words: data tables.

-PJ

[WhoisAlanGreenspan?]

I started with Office 2003. Created a payroll sheet for my business among many others, as well as an ordering system in Access. At some point I switched to Open Office Calc which I’m currently using for a number of things including the Pigskin Pickem Thread weekly tally sheet. You can take anything from Excel 365 open it with Calc and then save it there, there’s very little difference.

[libertylover]

I consider Excel to be the most useful program ever written and I use it at home all the time, but I realize I only scratch the surface of what it can do.

[Poser]

The most valuable tool for most casual users of Excel is absolute references in formulas. They give the users an easy way to calculate and display periodic changes in loan balances, interest payments, and the value of retirement savings over time. I taught accounting and finance to college freshmen and sophomores for 35 years. Nothing penetrated their thick skulls like a display of their accumulated interest payments on a 30 year home loan or the value of saving a few dollars a week with compound interest for 45 years.

My favorite example was the spreadsheet answer to this question: How much will you still owe on your (any amount) 30 year home loan at (any interest rate) after making payments for 10 years?

Then I ask: If you pay an additional $100 per month, when will your 30 year loan be paid off?

Without excel, they would have to use math that they don’t know or understand to answer those questions.

[MD Expat in PA]

I had a job working for a 3rd party benefits administrator in the early to mid 2000’s. I had some what I though were good Excel skills but had to take it to a much higher level.

This company offered employers semi-customized websites for their employees to sign up for benefits (new hires or at open enrollment) and for their HR department to update enrollments electronically through our portal and we then in turn sent electronic files to the various insurance companies (EDI – electronic date interfaces) and provided some employers with custom reports and self-billing reports.

My job was managing the department in charge of providing the custom billing and self-billing reports and auditing the enrolment data the insurance companies had vs. our enrollment as we were supposed to be the authoritative enrollment source.

I learned many Excel skills there that have served me well over the years - V-lookups, H-lookups, pivot tables, data modeling, VBA and macros, and various formulas to format data. And also importing data into a MS Access database base and running customs queries I had written for custom reports and auditing.

When performing enrollment audits, the insurance company might send me a coma delimited txt file or an Excel file, but their data often did not match the data I was able to extract from our system - SSNs without dashes, full names in one field vs last name, first name, etc., dates of birth as a text field vs a date field. So would have to use various formulas such as CONCATENATE and TEXTJOIN an TRIM and such to format the data I received to match our data output so I could then perform v-lookups or other Excel data functions to determine if the insurance company’s enrollment data matched ours.

And if it didn’t match, I would research why – a deep diving into the insurance company’s EDI requirements and testing to determine if the files we were sending met the insurance company’s requirements and looking for things like why a terminated employee was still being billed on the company’s insurance and why – were we sending a termination date or should have the insurance company stopped billing when that ee was no longer on the enrollment file – an actual term with a term date vs an implicit term, i.e. dropping off the next file and working with our EDI team and or the insurance company to rectify.

In my job doing audits I often worked closely with our programmers, those writing the EDI scripts and the programmers were amazed at what I could do in Excel, and I became the company’s go to for all their Excel questions.

We also took on a new client, a large government/ military DHS contractor who had very specific reports they wanted. Our sales department told them, without consulting me first, that we could provide these reports.

I was given only some sample report outputs and had to reverse engineer and write custom scripts in both Excel and Access to give them what they needed. But even with as much automation as I could come up with, their reports still took me a full 3 days of formatting and data crunching to get to the end results.

Before I left this job, I wrote up documentation and trained my replacement on how to provide the reports but kept getting emails and phone calls for many months after asking for help because it was so complicated.

[dfwgator]

I’ll add two more words: Apache POI.

[Migraine]

That’s all good info; thanks!