Microsoft

Analyze data instantly with Excel 2013's Quick Analysis

Excel 2013's new Quick Analysis tool gives quick access and contextual choices for analyzing your data.

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.

2013147.jpg

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 tables.
  • 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.

2013148.jpg

2013149.jpg

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.

2013150.jpg

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.)

2013151.jpg

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 option.
  4. Click OK.

2013152.jpg

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:


About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

3 comments
TerenceCraven
TerenceCraven

Great post. 

Excel's quick analysis tools make visualising and reporting your data a doddle. And these are just a few in a long line of improvements made to the Office 2013 suite. Think presenter view in PowerPoint and a new Read Mode in Word.

if you need some step by step guides and how to's, Best STL are giving away their complete library of MS Office manuals for anyone to download.

http://www.microsofttraining.net/microsoft-training-manuals.php

Ryk
Ryk

Huh -- I never noticed that.  Thanks!!

Mark W. Kaelin
Mark W. Kaelin moderator

Be honest now, did you know there was a Quick Analysis tool in Microsoft Office 2013?

Editor's Picks