More Hidden Excel Gems

To follow up on last week’s discussion of hidden gems in Excel, this week I want to introduce to you something called Quick Analysis. According the Microsoft, Quick Analysis is a tool that lets you easily analyze your data using some of Excel’s built-in analysis tools such as charts, conditional formatting, pivot tables and sparklines. While the Quick Analysis shortcuts may not get you everything you need, they can get you started creating visualizations of your data, especially in regards to building charts.

So how do you use Quick Analysis? First you have to enter your data into a spreadsheet. Unlike last weeks tip which required that the data be converted into a ‘table’, this gem does not. In fact, after you enter the data into a spreadsheet, all you need to do is to highlight that portion of the data by dragging through the rows and columns that you want to analyze. When you have done this, an icon box for the Quick Analysis tools appears in the bottom right corner of the selected area as shown in the following figure.

This icon will appear when you release the mouse button after dragging through at least two cells. When you click on the icon, the following dialog box appears which shows five different analysis tools across the top of the dialog. Within each of these areas, you will see 1 or more analysis options in the lower portion of the dialog. The number of options will vary depending on the structure of your data and the area that you selected.

The figure below shows the options available to format the selected cells. This tool is essentially a shortcut to many of the more common features found in Conditional Formatting. For example, the first formatting tool displays data bars whose width defines a relative percentage of the total value of all the values in the selected column If you have more than one column selected, the relative width is based on the largest value in all of the columns. It is not calculated on a column by column basis. If you want to display relative widths based only on values within a column, you must select the data in each column separately and apply the Data Bars formatting tool to each column individually.

Similar rules apply to using the color scale formatting tool along with all of the others. You can apply more than one formatting to an individual range of cells. However, note that applying multiple formatting to a range of cells can lead to confusion. Therefore, the last icon (tool) in the formatting set is to clear all of the formatting from the selected cells.

Also keep in mind that even after you use one of the Quick Analysis tools, you can often go to the commands in the ribbon and provide further customizations. For example, suppose you select the Top 10% to highlight the top ten percent of the cells by value compared to the overall value. However, you really want to highlight the top 20%. These is no icon to do this in the Quick Analysis dialog. However, you can change the percent by following these steps:

From the Home ribbon, click on the Conditional Formatting button to open the dropdown menu. From this menu, select the last option in the list, Manage Rules.

This displays the Conditional Formatting Rules Manager as shown in the next image. Click on the rule that you want to modify keeping in mind that if you applied more than one formatting, each format option appears as a separate rule in this dialog. In this case, I want to click on the Top 10% rule to select it and then click Edit Rule to make changes to it.

In the Edit Formatting Rule dialog that appears next, look at the bottom half of the dialog. Note that you can change the rule to format either the top or the bottom group of values. You can also change the percent from the default 10% to 20% by simply replacing the value found in the dialog. You can even change the formatting colors if you do not like the default pink background with read characters.

After you have made your changes, click the OK button to apply them. You will be returned to the Conditional Formatting Rules Manager as shown below which now shows the title Top 20%.

The following figure shows what your data may look like with the top 20$ of the data conditionally formatted.

Another formatting tool is to display the selected data as a chart. Again, remember to select the data you want to appear in the chart first. It will show one or more possible charts it thinks are possible with the existing data. When you click on a chart type, a preview of the chart appears on the screen. The following chart shows a line graph created from US debt data (in 2005 dollars)

A slightly different grouping of the data allows more chart options as shown in the following image in which a clustered column chart was selected. Note that the preview appears automatically when you move your mouse over any of the chart options. The chart is not physically added to the spreadsheet until you click on the one of the chart options. Note also that many of the chart labels are either not defined or have been given generic names. After you add the chart to your spreadsheet, you can then select the various parts of the chart to edit what appears for example to add a chart title, x- or y- axis labels, a different legend, etc. by using the commands in the Design and Format ribbon of the Chart Tools that now becomes available at the top of the screen when the chart is selected.

You can even add sparklines to your spreadsheet. A sparkline is a miniature graph of the data and can either be a line, column or a win/loss chart. By default, Quick Analysis only creates sparklines for horizontal data series.

If you use the commands in Insert ribbon, you can create sparklines for a vertical series of data as well as shown in the image below.

Another interesting feature is the ability to create a pivot table from the data using Quick Analysis. I’m going to save a detailed discussion of this shortcut for a future blog. In the meantime, please try the different Quick Analysis tools the next time you work within an Excel Spreadsheet.

One last trick, when you select any range of cells, the average, sum and count of the values in that range will appear in the status bar at the bottom of the Excel window as shown in the following figure. So if all you need is a quick average or sum of values, there is no need to do anything other can just select the cells you want.

C’ya next time.



By sharepointmike Posted in Excel

One comment on “More Hidden Excel Gems

Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s