Ultimate Excel Bracket Round 3: The Great Eight

Posted on by David Ringstrom

Oh, snap! #1 seed Pivot Tables is out! Here’s a rundown of the standings:

Worksheet Function
#10     VLOOKUP
#13     LEFT

Keyboard Shortcut
#1    Ctrl-S
#2     Ctrl-Z

Data Analysis Tools
#2     Table
#12     Filter

Other Features
#6     Format Painter
#8     Format Cells

Don’t get too comfortable folks, only half of these contenders will advance to the Final Four.  Read on to partake from our buffet of bracket analysis:

Round 3 – Worksheet Functions

Only one top 10 seed survived the third round:

  • #10 seed VLOOKUP vanquished #8 seed SUMPRODUCT in the third round. VLOOKUP vilified #2 seed IFERROR and #15 seed DATEDIF in earlier rounds.
  • #13 seed LEFT continues to leave carnage in its wake, lopping off #8 seed SUMPRODUCT, #12 seed DATEVALUE, and #5 seed OFFSET.

Up next in Round 4: VLOOKUP versus LEFT—nothing to see here folks, move along.

Round 3  – Keyboard Shortcuts

The #1 and #2 stay in after the third round:

  • #1 seed Ctrl-S stomped #4 seed Ctrl-Tab, after safely surpassing #9 seed Ctrl-Enter, and #16 seed Alt-Enter in the earlier rounds.
  • #2 seed Ctrl-Z zeroed out #11 seed F5, which had dominated its opponents in the earlier rounds. Ctrl-Z has also zipped past #7 seed Ctrl-A, as well as #15 seed Alt-Down Arrow.

Poised for battle in Round 4: Ctrl-S versus Ctrl-Z—choose wisely, my friends.

Final Four Preview: The winners of VLOOKUP vs. LEFT and Ctrl-S vs. Ctrl-Z will be taking the gloves off.

Round 3 – Data Analysis

Too much! The #2 and #12 seeds remain after the third round:

#12 seed Filter fought a battle royale against #1 seed Pivot Table to advance to the 4th round. Filter also forced out #4 seed Text to Columns and #5 seed Remove Duplicates in the earlier rounds.
#2 seed Table tangled with #6 seed Pivot Chart and lives to tell the tale. In earlier rounds Table took out #7 seed Chart and #15 seed Consolidate.

It’s practically a circular reference for Round 4: Filter vs. Table—no lo contendere.

Round 3 – Other Excel Features

It seems incomprehensible, but only #6 and #8 remain:

  • #8 seed Format Cells fixed #4 seed Groups wagon, that’s for sure. Format Cells has also fitted #16 seed Merge Cells and #1 seed Fill Handle into a box.
  • #6 seed Format Painter feasted on #10 Linked Workbooks. Format Painter also pummeled #3 seed Quick Access Toolbar and #11 seed Hyperlink in earlier rounds.

I didn’t see this Round 4 coming: Format Cells vs. Format Painter.  Fantastic!

Final Four Preview: The stronger of Filter vs. Table and Format Cells vs. Format Painter will survive to fight another day.

 

David H. Ringstrom, CPA — @excelwriter on Twitter — heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm.

Related Posts

An Argument for Techie Accountants

My one piece of advice for the next generation of accountants right now is, enroll in some Computer Science courses. Learn to code. Learn how to manage a small server farm. Learn APIs, SQL, HTML5, JAVA, etc.

Drop that Poli-sci course right now.

Technicalntant’s best friend nowadays. It should be self-evident as to why. Data. Data. Data.

My first accounting gig was in a tax firm. We had an old mainframe crunching numbers and all the programming was in COBOL. In industry, reports would have all been generating output as text files; but who cares, there’s no ‘export to file’ function anyways.


Actually, that’s a good test. If you want to know whether your current software vendor is investing in the future of their product, look at the file output from your reports. If they come out as text files (you open Excel and each line of output is just one cell), this means the reporting architecture is really, really old.

It’s kind of like when you and your friends one-time go for an afternoon horseback ride. Inevitably, one person gets plunked on the beatest, tired old nag you ever seen. Yeah, technically she still rides but she ain’t even long in the tooth. All the teeth, they fell out.

Consider it the carbon dating of your accounting system.

You see, just because there has been consolidation in the ownership of companies in the enterprise software space does not mean the units have consolidated their products. Most units (purchased or raised) continue to operate independently. Revenues are generated from new sales obviously; but equally important, from a big, juicy installed base of maintenance contracts within the business units. You know that. And it’s fine. The amount of new investment in the product however, would be a corporate management decision from head office. Some products are the equivalent of that tired old nag.

Back to the point on technical skills though. Unlike back in the day, technology is no longer just auxiliary to what we do. It’s central and 100% pervasive. A commenter last week summed it up really well when I talked about the accounting tools:

“Three letters for you bitches, S to the Q to the mofo L.”

Getting a bit more techie will help you appreciate the humor in this quote. It’ll also help you recognize the tired old nag before you saddle up and ride.

In practice, normally we’re simply subject to whatever system happens to be installed. You deal with it, right? And that’s fine too. Recognition goes hand in hand with acceptance.

The reports kick out to Excel in text files; you find the delimiters, execute a ‘text-to-columns’ command, split up what you can and do your reporting. In the past, I’ve also had to occasionally create an Excel formula for pulling out text that’s really buried using the LEFT, RIGHT, functions. Then, I write a macro to automate as much as possible. Poor tired old nag.

Technology and data are just like riding a horse. With the correct instruction, you can get the horse to do what you want. But you’ll always be limited by what the horse is physically able to do.

If you don’t know anything about horses, this analogy might not make much sense at all. Which, I would say, just proves my initial point. Learn your technical skills now while you’re still in school. Leave all the fluffy horsebackriding and philosophy courses to the guys who’ll be serving you coffee after graduation.

In my view, technology skills are just as important for accountants as debits and credits. You may or may not like it, but it’s time to see how the dog food is made.

Enjoy.

Old farm adage: “If you’re going to have livestock,… you’re going to have deadstock.”

Geoff Devereux as been active in Vancouver’s technology start-up community for the past 5 years. Prior to getting lured into tech start-ups, Geoff worked in various fields including a 5 year stint in a tax accounting firm. You can see more of his posts for GC here.