Excel Quick Analysis tool with examples

Formatting

This category allows you to enhance data visualization by applying conditional formatting. The available options depend on the data type you’ve selected:

• For numeric data, you can use Data Bars, Icon Sets, Greater Than, and Top 10%, to highlight the relevant numbers in your dataset.
• For text values, you’ll have Highlight Duplicates, Unique Values, Cells That Contain, or Exact Match to identify and filter your data based on text criteria.
• For dates, you’ll be able to highlight the ones occurring Last Month, Last Week as well as Greater Than, Less Than, or Equal To a particular date.

Keyboard shortcut for Quick Analysis Formatting: Ctrl + Q then F

Charts

Apart from the traditional ways to create charts in Excel, you can also use the Quick Analysis tool to insert a graph. While Quick Analysis provides a limited range of options, it intelligently suggests the most suitable chart types and makes the process faster.

Based on your selected data, Excel’s Quick Analysis tool will display the chart types that are the best fit to represent your data graphically and help you visualize the patterns, trends, and relationships. You can hover over each graph type to see a preview of how it would look like in your worksheet.

If the chart type you are looking for is not there, click on the More Charts option, which will open the Insert Chart dialog box, where you can select from a variety of other graph types, such as scatter plots, histograms, radar charts, and others.

This feature can help you create effective and attractive charts without spending too much time on formatting and customizing. However, if you want more control over the appearance and functionality of your charts, you can use the Chart Tools tab to access more advanced settings and features.

Keyboard shortcut for Quick Analysis Charts: Ctrl + Q then C

Totals

With Totals, you can easily calculate and display various summary statistics for your data, such as sum, average, count, percentage total, and running total.

Depending on the type and format of the data in the selected range, you will see different options for applying totals. For example, if you only have text data, the only option available will be Count, which shows the number of cells with text values.

Totals can be calculated for rows and columns. For vertical summaries in columns, you have several options in the blue color. For horizontal summaries in rows, click the right navigation arrow first, and then you can choose from the options in the yellow color that suit your data best.

Keyboard shortcut for Quick Analysis Totals: Ctrl + Q then O

Tables / PivotTables

Transform your tabular data into a formatted Excel table, equipped with filtering, sorting, and auto-expanding features for professional data management. Additionally, you can insert a PivotTable in a new sheet using the selected data as the source for more comprehensive data analysis.

Keyboard shortcut for Quick Analysis Charts options: Ctrl + Q then T

Sparklines

Sparklines are a great way to show data trends in a compact and elegant way. Insert these tiny in-cell charts to visualize patterns within your dataset, choosing from three types: Line, Column, or Win/Loss.

Sparklines can help you highlight important information, such as seasonal variations, outliers, or progress towards a goal. They can also enhance the readability and aesthetics of your reports or dashboards, without taking up too much space.

Keyboard shortcut for Quick Analysis Sparklines: Ctrl + Q then S

Change a regular range into Excel table

The first thing we do is to turn our sample dataset into an Excel table. Transforming a regular data range into a fully-functional table offers numerous advantages, including automatic expansion for new records, meaning that all the adjustments you make for the current data will be automatically applied to new records without you having to do anything extra.

Here's how you can quickly create a table using the Excel Quick Analysis tool:

1. Select your data.
2. Click the Quick Analysis button or press the Ctrl + Q shortcut.
3. Within the Quick Analysis menu, navigate to the Tables category and select Table.

This action inserts a table with the default style. You can easily change the table style using Excel's Table Styles gallery or remove table formatting, such as banded rows, shading, and borders, while retaining all other features.

Calculate percentage total for columns and rows

In the same dataset, suppose you want to find out two things: what percentage of total sales is contributed by specific items and within specific months. To achieve this, you can calculate percentage total values for both columns (months) and rows (items). Below are the steps to do this:

1. Select the dataset and click the Quick Analysis icon.
2. Under the Totals category, you'll find the % Total option in the blue color. Click on it to calculate the percentage total for columns.

This will insert a new row displaying the sales percentages for each month.

To add percentage total for rows, follow the same steps, but select the % Total option in the yellow color. This will insert a new column displaying the percentage-wise sales for each item.

To investigate the formula that the Quick Analysis tool created for you, select any cell with the % Total and look at the formula bar. In our case, the formula contains specific table references:

=SUM(Table2[@[Jan]:[Apr]])/SUBTOTAL(109,Table2[[Jan]:[Apr]])

If you are working with a normal range, this would be a lot simpler formula with standard range references like this one:

=SUM(B3:E3)/SUM($B$3:$E$15)

This formula calculates the percentage of each row's total in relation to the grand total of all rows. The numerator is the sum of the values in the current row, and the denominator is the sum of the values in all the rows. The dollar signs in the denominator make it an absolute reference, so it does not change when copied to other cells.

Highlight cells greater than a specified value

To highlight cells with values above a certain number, follow these steps:

1. Select the range of cells you want to format and activate the Quick Analysis button.
2. Under the Formatting group, choose Greater Than.
3. In the dialog box that appears, type the number you want to compare and select the formatting style (the default is light red fill with dark red text).
4. Click OK to confirm your selection.

These steps will promptly highlight all cells with values surpassing the specified threshold.

Create a pie chart

To create a pie chart with the Quick Analysis tool, carry out these steps:

1. Select the data range that you want to use for the chart, including the labels and values.
2. Click on the Quick Analysis button.
3. In the Quick Analysis menu, go to the Charts tab and hover over the Pie chart option. You will see a preview of the pie chart on your worksheet.
4. Click on the Pie chart option to insert the graph in your sheet. Alternatively, you can click on More Charts to see other types of graphs that are available for your data.

As we’ve created a chart based on a table, it is dynamic by nature – every time you add or remove new records to the table, the chart will automatically update to reflect the changes. This is a very useful feature when you want to visualize data that is constantly changing or growing.

After inserting the chart, you can modify its style, layout, and colors to suit your preferences and needs. You can also add or edit titles, labels, legends and other elements to make your graph more informative and attractive. For detailed instructions, check out Customizing Excel charts.

Insert sparklines

To swiftly create sparklines, i.e. mini-charts within individual cells each representing a row of data from your selection, this is what you need to do:

1. Highlight the data range that you wish to visualize with sparklines.
2. Click on the Quick Analysis button.
3. Under the Sparklines tab, choose the preferred type: Line, Column, or Win/Loss.

This will create sparklines in the adjacent column, providing a condensed visual representation of your data. You can format and customize your sparklines using the Sparkline tab on the ribbon.