You've probably been using Excel for quite a while now. Much like Microsoft Word, Excel has maintained much of its familiarity with each Office release – the current release being Office 2013. But are you keeping up to date with all the really useful features?
Microsoft recently ran extensive analysis into how people were using excel and found that many people aren't familiar with the variety of data analysis features that Excel has to offer, or are hesitant to create or insert new features, even a chart, and some were simply unwilling to go looking for interesting features & capabilities.
Quick Analysis is a dynamic tool that allows a quick and reversible insert of Excel’s analytic capabilities. The Quick Analysis galleries are dynamic: what appears in them changes depending on the type of data you've selected, making it painless to see what’s going to happen to the workbook before committing to a change.
These features will be explained in more detail below with a template workbook available to follow while reading through the guide. However, if you have Office 2013 already, it’s very simple to try the features for yourself:
If you do not have Office 2013 or Excel 2013, you can use the sample workbook to experience these features.
1. Getting Started: Select a Range of Data
The way to bring up the Quick Analysis interface is to select a range of data, and then click the icon that appears near the bottom right of the selection:
Clicking the button brings up a gallery of options to explore:
By default the Formatting gallery comes up, showing just a handful of choices, but there are other galleries to explore as well. The Charts, Totals, Tables, and Sparklines tabs in this callout each show a handful of choices from those respective categories to explore.
Looking at the sample data, I might first be interested in taking a closer look just at the numbers, so I select G4:I20, and click the button. Hovering my mouse over the various icons in the gallery gives me a live preview of what choosing that option will do. Below are some examples with Data Bars and Colour Scale. If you like what you see, click the button to add that to your selection, otherwise if you just move your mouse away the live preview will disappear.
Dynamic Conditional Formatting Gallery
In the example above I’ve selected numbers, so it makes sense that I’m seeing formatting options that work well with numeric values. If I select all Text or all Date values, the items in the Formatting gallery change to work better with those types of data:
(Text values in the “Territory” column selected, showing a live preview of the “Text Contains” Conditional Formatting rule)
(Date values in the “Dates” column selected, showing a live preview of the “Last Week” Conditional Formatting rule)
Now let’s say I want show my data as a chart, but I’m not yet sure exactly what I want. I’ll select the entire range of data from B3:I20 (Names column all the way to Sales 2011 column), and take a look at the CHARTS gallery in the Quick Analysis callout. This gallery is dynamic as well - in the background Excel is doing some light weight analysis of the data in the selection to determine some good chart recommendations to get started.
At first glance it seems that the Clustered Column chart is being recommended over and over again:
Looking through the live previews though shows me that these are different Clustered Column PivotChart recommendations. The indicator that a PivotChart (instead of a regular chart) is being recommended is the little PivotTable icon in the upper right corner of the chart preview:
Incidentally, the choices you see here are a subset of the recommendations you get when you navigate to the Insert ribbon tab and choose “Recommended Charts” (the maximum number of recommendations in the Quick Analysis gallery is 5, whereas clicking the ribbon button doesn’t limit the recommendations to 5). Also, if the recommendation engine has fewer than 5 recommended charts, then fewer recommendations will appear in the Quick Analysis Charts gallery.
To explore the Totals gallery in the Quick Analysis callout, let’s first select all the numbers for Sales 2009, 2010, and 2011 (range G4:I20). Looking at the icons in that gallery, I’m thinking that this part of the feature will put totals either at the bottom or to the right of my selection:
Live previews confirm my guess:
This particular gallery has more choices, with right/left scrolling arrows to see the additional options. Hovering over the 2nd “Sum” button shows a live preview of the totals placed to the right instead of at the bottom:
This gallery offers “Percent of Total” as well as “Running Total” at the click of a button - - which is a first for Excel.
Exploring the Tables gallery quickly shows me that this is the place to get recommendations about how to summarize my data in a PivotTable. This gallery is dynamic as well - - in the background, Excel is doing more lightweight analysis of the data in the selection to determine good recommendations to get started. Even if I don’t know what the word PivotTable means, I can see from the live previews that it’s putting subtotals into a nicely formatted layout:
Again, just like recommended charts, this is a subset of the options you get when you navigate to the Insert ribbon tab and choose “Recommended PivotTables”. Also, if the recommendation engine has fewer than 5 recommendations, then fewer recommendations will appear in this gallery. As an extreme example, if the engine doesn’t recommend any PivotTables, there won’t be any recommendations in the gallery; only the option to insert a blank PivotTable. This can happen if your data contains only unique values, column-wise, since in that case there isn’t anything to consolidate and subtotal.
I’m really excited that you don’t have to know the word “PivotTable” in order to get good summary information about your data, and you get live previews of a good number of options to choose from.
Finally, let’s take a quick glance at the Sparklines gallery. I’ll select the numbers again, and hover over the “Column” icon to see a live preview of where the Sparklines will be inserted, and get a better understanding of what Sparklines even means:
Content on this guide is thanks to Microsoft Office 365 Blog.