Analyzing data in Excel has never been easier if you take
advantage of 2013’s new Quick Analysis tool. You’ll need no special training – select
the data, make a choice, and that’s it. If you’re not paying attention though,
you might not know the tool exists! After selecting a data range, you’ll notice
a small icon appears. That’s Quick Analysis! If you’re like many Excel 2013
users, you’ve ignored it, thinking it was one of Excel’s annoying error smart tags.

Editor’s note: The
demo file Susan is using for this article is available as a free download.

Quick Analysis

Quick Analysis is a contextual tool that provides
single-click access to data analysis tools, many of which you’re already
familiar with:

  • Format: Preview and apply
    some of Excel’s most popular conditional formats.
  • Charts: Preview and apply
    specific chart structures. Here’s a quick tip: most of the time, you’ll
    want to select the header text when choosing Charts.
  • Totals: Preview and insert
    basic calculations like sum, count, average, and so on.
  • Tables: Preview pivot
  • Sparklines: Preview and
    insert sparkline graphics.

None of these tools are new, but they’re now available via
the icon – no more ribbon surfing! Some of the options are automatic; some
require a bit more information from the user. For instance, if you choose
Greater Than from the Formatting tab, Excel will prompt you for specific values
– greater than what? You’ve probably used this conditional format before, but you
didn’t have such quick access to it.

To quickly insert sparklines, select the data, open the
icon, click the Sparklines tab, and choose an option. Notice that Excel knows
where to put them without additional input from you.

Quick Analysis is more than a shortcut – it’s also smart. It
won’t offer the same options for every data set. It fine-tunes options based on
the selected data. For example, Excel recommends column charts for the sample data.
It doesn’t offer a pie chart or other chart types – on purpose. It’s pretty
smart, so if you have trouble choosing charts, you’ll definitely benefit from
the help. (Other charts are still available.)

By default, this feature is enabled and I think most users will
benefit from it, once they know it’s there. If, however, you want to disable
it, you can do so quickly:

  1. Click the File tab and
    choose Options from the left pane.
  2. Choose General in the left
    pane (the default).
  3. In the User Interface
    Options section, uncheck the Show Quick Analysis Options On Selection
  4. Click OK.

If you support users and you want to inhibit this feature
programmatically, use the ShowQuickAnalysis property. It’s a Boolean property
and TRUE means the feature’s enabled. The following statement will disable
Quick Analysis:

Application.ShowQuickAnalysis = False

Or, offer a toggling macro using the following statement:

Application.ShowQuickAnalysis = Not (Application.ShowQuickAnalysis)

Also read: