Jeff Prom's SQL Server Blog

Sharing knowledge and tips on SQL Server, Business Intelligence and Data Management.

Using the ‘Quick Analysis’ feature in Excel 2013 for some quick, yet powerful, analytics.

Posted by jeffprom on March 22, 2013

Last November while I was at PASS Summit, one of the presenters used Excel and added some data bars right on top of the data. I thought, ‘Wow, that’s cool!’ This is a new feature in Excel 2013 called ‘Quick Analysis’. This handy little tool offers some really quick and easy, yet powerful ways to visually analyze data. Let’s take a look at what it can do.

First, pick a set of data to analyze. For this example we are going to analyze some January metrics over a five year span. Highlight the actual data and not the headers. Notice on the bottom right there is a little icon. If you hover over it, you will see that it is the Quick Analysis tool. You can also use the Ctrl + Q shortcut.

quick analysis selecting data

The first menu option that pops up is the Formatting tab which has some neat features. The first option on the left is Data Bars. This gives a nice visual bar chart representation of each data value.

quick analysis data bars

The next option is Color Scale. This colors the data with red representing the low values, white for mid-range values, and green for high values. With a quick glance at the data we can easily see which values are high and which ones are low.

quick analysis color scale

The third option is Icon Set. KPI icons are shown such as a red arrow pointing down for low values, a yellow right-facing arrow for mid-range values, and a green arrow pointing up for high values.

quick analysis icon set

The fourth option is called Greater Than. This option highlights in red some of the higher values.

quick analysis greater than

Finally, the last formatting option is the Top 10%. This highlights in red the top 10% values.

quick analysis top 10 percent

Now let’s take a look at some of the other tabs in Quick Analysis. Let’s jump to the last tab called Sparklines. On this tab you have three options: Line, Column, and Win/Loss. Line shows a sparkline to the right side of your data and indicates over the data the trend, up/down/steady. In this screenshot we selected Column which shows a mini column chart to the right and indicates your data trend.

quick analysis sparkline columns

If we jump over to the Totals tab we can quickly add some totals to the bottom of our data such as Sum, Average, Count, % Total, Running Total, and more. In this example we are adding a % Total.

quick analysis percent total

As you can see our bland set of data is quickly turning into something we can easily analyze. To top it off why not add a quick chart? For the chart select all of the entered data including headers. Now on the Charts tab, we are presented with options such as Line, Stacked Area, Clustered Column, Stacked Column, etc. We are presented with a preview.

quick analysis line chart

Once selected, it adds the chart to the worksheet. The screenshot below shows the final product. One last tab that we didn’t show is Tables. There are two available options on this tab; Table and Blank PivotTable. Table in our case just adds a filter on our table headers. The second option, Blank PivotTable, allows us to create a PivotTable on a new worksheet with our data. One last thing to note is that you can stack all of the Formatting options. For example, you can do a Color Set with Data Bars and the Icon Set. It starts to get a little cluttered, but at least you have some options.

quick analysis final

Let’s review what Quick Analysis just helped us do. We can tell annual growth % from the totals on the bottom, the sparkline tells us the annual trend by day, the color formatting let’s us easily identify high and low values, and the chart gives us a great visual comparison of all of the metrics together. Talk about some powerful visuals, and it only took about a minute to do! Quick Analysis is a powerful new tool in Excel 2013 that provides a quick and easy way to analyze data.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: