It can be hard to get a lot of meaning out of numbers in a worksheet. But Excel provides a bunch of ways to quickly analyze your data using conditional formatting. For example, you can use a color scale to differentiate high, medium, and low temperature values.
Here’s how you can quickly apply this type of formatting:
When the Excel Options window opens, click on Add-Ins located on the left pane. At the bottom of the window select Excel Add-ins from the drop-down to the right of Manage:, click Go to proceed. The Add-Ins window will open, add a checkmark to the check box next to Analysis ToolPak, click OK.
Start by selecting your data. The Quick Analysis button appears on the lower-right corner of the selection.
Click the Quick Analysis button , or press Ctrl+Q.
On the Formatting tab, move your mouse over the different options to see a Live Preview on your data, and then pick the formatting you like best.
Notes:
- Excel 2016 from Scratch Quick Analysis An Excel tutorial by Peter Kalmstrom In this demo in the Excel 2016 from Scratch series, Peter Kalmstrom explains how to present data in a more visualized way, so that it will be easier to grasp with a quick glance. Visualization is the most powerful way to study data.
- The R scripting accent makes it accessible to save and echo analyses on adapted abstracts sets, and it has the adeptness to accommodate R and C.
To remove any conditional formatting, click Clear Format in the Quick Analysis gallery.
For additional formatting options and colors, select your data, and then click Home > Conditional Formatting.
This blog post is brought to you by Chad Rothschiller a Program Manager on the Excel team.
As part of our planning work at the beginning of the Office 2013 project, we (the Excel team) participated in several customer visits. We went in small groups to someone’s work environment (whether that was a large corporation, a small business, or even a home office), watched them use Excel, and talked with them a bit about what they were doing. Of course several patterns and themes were observed, but here are a few that caught my interest and really resonated with me and with several others on the team:
· Many people aren’t familiar with the variety of data analysis features that Excel has to offer
· Many people are hesitant to create / insert new features, even a chart. Some people feared they wouldn’t be able to change it back if they didn’t like the result
· Many people are simply unwilling to navigate away from the home tab to go looking for interesting features & capabilities
These are the main motivations behind Excel 2013’s Quick Analysis feature. We set out to create a dynamic interface that allows fear-free exploration of Excel’s analytical capabilities. The Quick Analysis galleries are dynamic: what appears in them changes depending on the type of data you’ve selected. Live preview makes it quick and painless to see what’s going to happen to the workbook before committing to a change.
Feel free to open the workbook and come along as we take a closer look at how it all works.
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 little button that appears near the bottom right of the selection:
Clicking the button brings up a gallery of options to explore:
By default the Conditional 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.
Conditional Formatting
Looking at my 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 Color 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.
(Data Bars)
(Color Scale)
Dynamic Conditional Formatting Gallery
In the example above I’ve selected numbers, so it makes sense that I’m seeing Conditional Formatting options that work well with numeric values. If I select all Text or all Date values, the items in the Conditional 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)
Charts
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.
Totals
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 (OK, since I helped design those icons, I’ve got an unfair advantage J):
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.
Tables
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.
Sparklines
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:
Wrap Up
One aspect of this feature that was challenging for us to dial in exactly right was how and where the Quick Analysis button would initially show up: On the one hand we want people to see it and click it. We don’t want to be too subtle. On the other hand we don’t want to annoy people with a button always appearing and following them around as they work in Excel – – that would be a distraction. We tried to keep it toned down by having the button fade/disappear as the mouse moved away from it.
What do you think:
Analyze Tab Excel
· Did we get the balance right?
· Do you think that people will notice it?
· Did you notice it?
· If so, did it draw you in to explore the feature?
Where To Find The Quick Analysis Tool In Excel
· Or is it simply distracting? (you can turn it off in Excel / Options)
Where Is The Quick Analysis Tool In Excel
Thanks everyone for reading and providing comments!
Quick Analysis Tool In Excel
–Chad