There’s a tiny icon that shows up every time you select a range of data in Excel. It sits in the bottom-right corner of the selection, looks like a small lightning bolt, and most of us instinctively ignore it. After all, Microsoft’s Office programs are riddled with such random icons that don’t mean anything to a lot of us using them.
You might dismiss it as a stray UI element, or click it once, don’t immediately understand what you’re looking at, and never look at it again. That’s a mistake, because that little icon is your gateway to one Excel feature I’d be lost without: the Quick Analysis tool
This tiny box does more than you think
What Quick Analysis actually unlocks
Yadullah Abidi / MakeUseOf
When you click the Quick Analysis icon (or press Control + Q to summon it), a small floating menu will appear with five tabs: Formatting, Charts, Totals, Tables, and Sparklines. Each one of these tabs is a shortcut to a specific kind of analysis that would normally require navigating through an otherwise cluttered Excel ribbon menu.
Instead of heading up to the ribbon and fumbling your way through submenus, you can use the Quick Analysis menu to quickly visualize and summarize what your data will look like with options for conditional formatting, charts, totals, tables, and more. You can consider it a context-aware assistant that reads your selected data and shows the most relevant options instantly. It doesn’t give you all the Excel features you need, just the ones that might make sense based on your selected data range. That’s the whole point of the Quick Analysis tool—it meets you where you already are, mid-selection, mid-thought, right when you need it.
If the tool doesn’t appear on selection, head to File > Options > General and make sure the Show Quick Analysis options on selection box is checked.
Screenshot by Yadullah Abidi | No Attribution Required.
Now, if all of this sounds a little too good, keep in mind that the Quick Analysis tool won’t show up if your selection contains merged cells. It also won’t activate if you select non-contiguous ranges, say you’re holding Control to pick two separate columns. It needs a clean block of data to work with. Apart from that, the tool works on any non-empty selection, consistently cutting down time spent on common tasks by eliminating the need to interact with the packed ribbon at the top.
Formatting without the usual pain
Excel’s formatting tab is a rabbit hole of interesting features, half of which you accidentally discover when you’re trying to achieve a specific type of formatting. The problem with that is that it needs time and quite a bit of fumbling around to get what you need. Using conditional formatting properly can be a handful, too, if you haven’t done so before.
The Quick Analysis tool, as the name suggests, is a much quicker way of doing this. Select a column of sales figures, open Quick Analysis, and you can instantly use data bars. These are in-cell progress bars that turn a column of raw numbers into a visual comparison in one click. Alternatively, you could apply a color scale where lower values are shaded red and higher ones are green, making it obvious at a glance what metrics are doing better and where they’re not.
Another icon set option can drop small arrows or traffic light symbols into your cells—quite useful when you’re sharing a spreadsheet with someone and don’t want them to get lost in the numbers. There are Great Than and Top 10% options too that let you highlight outliers instantly without having to write complicated conditional formatting rules.
All of these actions can be done from the Excel formatting tab, but it would take a few minutes, half a dozen clicks, and some configuration to get them to work. With Quick Analysis, they’re ready to go in a two-second hover-and-click.
Charts in a couple of clicks
Turn raw numbers into visuals almost instantly
Screenshot by Yadullah Abidi | No Attribution Required.
Another rather impressive section of the Quick Analysis tool is the charts tab. Once you select your data and open the tool, Excel will automatically suggest several chart types based on what it detects in your selection. You can hover over each selection and preview how your data will look in real time. When you find one you like, click it, and your chart is ready to go.
It doesn’t always get it right, meaning sometimes you’ll have to do it the old-fashioned way. But more often than not, you’ll find a suitable chart type within seconds. The preview option is also great, as it lets you see exactly how the data is rendered without having to create multiple charts or peek at the small preview icons Excel shows in the Insert menu ribbon.
Related
You’re picking the wrong charts — let Excel’s new Agent Mode decide for you
Agent Mode reads your data and builds what actually fits.
The live previews remove any guesswork required. If you’ve ever inserted a bar graph only to realize a line chart would illustrate what you’re trying to show better, Quick Analysis solves that problem before you face it by showing previews of both charts right there at your selection. And if the instant recommendations don’t cut it, you can click the More Charts button inside the tool to see Excel’s full list of recommended charts. If that’s not enough either, you can switch to the All Charts tab to take full control.
Totals—no formulas needed
Quick sums and averages without touching the ribbon
Screenshot by Yadullah Abidi | No Attribution Required.
Now I’ll admit that typing =SUM() or =AVERAGE() doesn’t take a lot of time, but you still have to provide cell ranges for these formulas to work properly, and that’s where a lot of mistakes get made. With Quick Analysis, you select your data, head to the Totals tab, and instantly add a sum row, average row, count, running total, or a percentage total either below your columns or to the right of your rows, easily.
You do, however, have to pay attention to the two rows of icons. The top row adds totals to the bottom of your column, and the bottom row adds them to the right of your rows. It’s easy to mix this up at first, but you’ll develop the memory required in just a couple of uses at best. Excel formulas also break all the time, so using the Quick Access tool gives you an easy way to get perfectly functioning results.
Sparklines deserve more attention
Mini charts that make trends obvious at a glance
Screenshot by Yadullah Abidi | No Attribution Required.
A sparkline is a mini line chart that lives inside a single cell. In my opinion, it’s one of the most elegant ways to show data that Excel has ever shipped. And while it’s usually buried within chart options, the Quick Action tool puts it front and center.
Select a row of data, say, sales figures for a particular product, and Quick Analysis can add either a line chart, column chart, or a win/loss chart directly into the cell at the end of that particular row. You can apply this to all your rows to quickly create individual trend indicators for each row in your spreadsheet. It’s great for quickly picking up what’s growing, declining, and performing okay.
Nothing new, just faster
Familiar tools, now actually convenient to use
The Quick Analysis tool doesn’t ask you to learn anything new; it just gives instant results for what you’re already doing right. Instead of memorizing yet another keyboard shortcut or digging through the ribbon, you let Excel suggest the next step and show a preview before you commit.
Related
Excel finally fixed its biggest data entry problem, and it’s a lifesaver
One click in the Data tab can catch almost all issues.
Once you get used to clicking that little box, it becomes hard to go back. So the next time you’re staring at a boring grid of numbers, don’t reach for a formula, reach for the Quick Analysis tool and see the wonders it does for you.
