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.
Quick Analysis is a contextual tool that provides
single-click access to data analysis tools, many of which you’re already
- 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:
- Click the File tab and
choose Options from the left pane.
- Choose General in the left
pane (the default).
- In the User Interface
Options section, uncheck the Show Quick Analysis Options On Selection
- 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
Application.ShowQuickAnalysis = False
Or, offer a toggling macro using the following statement:
Application.ShowQuickAnalysis = Not (Application.ShowQuickAnalysis)
10 favorite things about Office 2013
things that are hard to find (or gone altogether) in Office 2013
first nine things I do to default settings in Word 2013