Annnnnnddd we’re back with the second half of your winners from Round 1 of Going Concern March Madness: The Ultimate Excel Bracket. This time Data Analysis Tools and Other Excel Features duked it out amongst themselves, with these features advancing to the second round (see the first batch of winners here):
DATA ANALYSIS
#1 Pivot Table
#2 Table
#3 AutoFilter
#4 Text to Columns
#6 Pivot Chart
#7 Charts
#9 Slicers
#12 Filter
OTHER FEATURES
#2 Range Names
#3 Quick Access Toolbar
#4 Group
#5 Text Box
#6 Format Painter
#8 Format Cells
#10 Linked Workbooks
#16 Merge Cells
If your NCAA bracket was busted yesterday you might as well drown your sorrows here by picking up a few new tricks:
Round 1 – Data Analysis Tools
No surprises here, #1 seed Pivot Table powered over #16 seed Solver. Think of Solver as Excel’s Goal Seek feature on steroids, but the first key is to know where to turn Solver on within Excel (hint: look at the Add-Ins menu). Pivot Tables advance to take on Slicers in Round 2.
#8 seed Slicers shaved #9 seed Sparklines out of the competition in a head-to-head battle between two features introduced in Excel 2010. Slicers provide a visual means of filtering pivot tables in Excel 2010 and later, as well as tables in Excel 2013. Sparklines are in-cell charts often used with dashboard applications.
#12 seed Filter crushed #5 seed Remove Duplicates. Remove Duplicates is a new relatively new contender first offered in Excel 2007, while the Filter command advances to take on Text to Columns.
#4 seed Text to Columns trounced #13 seed Text Import Wizard, as voters prefer to dissect data once it’s already in Excel. The Text Import Wizard provides an automated means to import data into Excel from lowly text files.
In one of the closest battles of the competition, #6 seed Pivot Chart prevailed over #11 seed Advanced Filter. Either voters are still using Advanced Filter for its Unique Values Only capability (superseded by Remove Duplicates), or we have some heavy duty analysts in the crowd. Pivot Chart advances to take on AutoFilter.
#3 seed AutoFilter—a stealth feature in Excel 2007 and later— showed #14 seed Microsoft Query the door. AutoFilter can be added into to the Quick Access Toolbar to enable filtering with a keyboard shortcut or mouse click. Microsoft Query is buried in Excel’s Data menu, but offers the unique ability to query text files, spreadsheets, and databases. Better luck next year, Microsoft Query.
Much to Caleb’s chagrin, #7 seed Charts conquered #10 seed Goal Seek. Many voters simply don’t venture into the What If Analysis section of Excel’s Data menu to uncover Goal Seek. Charts will go up against Excel’s Table feature.
#2 seed Table toppled #15 seed Consolidate. Voters overwhelmingly recognized the superpowers that the Table feature—introduced in Excel 2007—adds to lists of data in Excel. The Consolidate feature returns to warming the Data menu bench again. Give it some love the next time you need to mash-up data from multiple locations in a workbook.
Round 1 – Other Excel Features
#16 seed Merge Cells busted brackets left and right by masticating #1 seed Fill Handle. The toxic ability to combine cells and cause side effects in Excel spreadsheets was deemed superior over the various ways the Fill Handle enables users to power through spreadsheet development. Merge Cells will challenge #8 seed Format Cells in Round 2.
#8 seed Format Cells crushed #9 seed Center Across Selection’s aspirations to challenge Merge Cells in Round 2. Granted that Merge Cells and Center Across Selection are both options within the Format Cells dialog box, but this challenge was designed to determine who uses dialog boxes versus icons.
#5 seed Text Box totaled #12 seed Scenario Manager. It’s possible that Text Box could do battle with Merge Cells in Round 3, only time will tell. Text Box offers a far more effective means to enter blocks of text in a spreadsheet, while Scenario Manager, tucked away under What If Analysis enables users to swap different sets of up to 32 inputs in and out of a worksheet, instead of spawning multiple versions. Text Box will try it’s luck against Group in Round 2.
#4 seed Group garnered nearly all of the votes over #13 Text to Speech. Voters relish the ability to expand or collapse rows and columns a worksheet with a single mouse click, while the hidden Text to Speech feature just couldn’t overcome obscurity. Add it to your Quick Access Toolbar the next time you need to cross-check numbers in a spreadsheet against a piece of paper.
#6 seed Format Painter flattened #11 seed Hyperlink. Many voters extend Format Painter’s functionality by double-clicking the icon, which in turn makes it easy to format multiple sections of a spreadsheet at once until the press Escape. The Hyperlink feature is often used as a navigation aid in creating quick links within large workbooks, or streamlining access to Internet-based resources. Format Painter will confront Quick Access Toolbar in Round 2.
#3 seed Quick Access Toolbar quashed #14 seed Ribbon Customizations, partly because Excel 2007 doesn’t offer the ability to reconfigure the ribbon unless you want to spin up some XML and VBA code. The Quick Access Toolbar offers the ability to add buried features back to Excel, such as the aforementioned AutoFilter and Text to Speech. As shown by the voting results, Ribbon Customizations can go awry if you frequently use Excel on multiple computers.
Another potentially toxic feature, #10 seed Linked Workbooks, leveraged its way past #7 seed Custom Views. Although considered crucial by some, Linked Workbooks are definitely a feature to be used with care. Custom Views delivered a strong showing as voters like the ability to hide and unhide multiple worksheets with ease, along with other capabilities. Linked Workbooks will see if it can work its magic against Range Names in Round 2.
#2 seed Range Names fought a close battle against #15 seed Fill Series. Range names serve as bookmarks, simplify formula creation, and can even serve as hidden locations for storing data. Fill Series bucked obscurity on the Home tab to bring attention to its ability to handily create a series of numbers, dates, or AutoFill items.
David H. Ringstrom, CPA — @excelwriter on Twitter — heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm.