Why is my quick analysis tool not showing up?

By On Monday, October 3rd, 2022 Categories : Tanya Jawab

Why is my quick analysis tool not showing up? – Pa kabar sobs semua, Met Datang di website TrendWisata ini. Kali ini, kita di blog Trend Wisata dot com ingin memberikan pertanyaan dan jawaban yang cool yang membahas Why is my quick analysis tool not showing up?. Sebaiknya Agan cek di bawah ini:

  • Excel Tips
  • Excel Functions
  • Excel Formulas
  • Excel
    Charts
  • Word Tips
  • Outlook Tips

In Excel 2013 and later version, when selecting a range of cells, a Quick Analysis button is displayed in the lower-right corner of the range as below screenshot shown.
How could you turn off or disable this feature in Excel?

Turn off or disable the Quick Analysis feature in Excel


Turn off or disable the Quick Analysis feature in Excel


To disable this Quick Analysis feature in Excel workbook, please do as this:

1. Click File > Options to go to the Excel Options dialog box.

2. In the dialog, click General from the left pane, and then uncheck Show Quick
Analysis options on selection
option under the User Interface options section, see screenshot:

3. Then click OK button, and the Quick Analysis feature has been disabled at once.


The Best Office Productivity Tools

Kutools for Excel Solves Most of Your
Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails…
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to
    Filtered Range
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns… Prevent Duplicate Cells; Compare Ranges
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select…
  • Exact Copy Multiple Cells without changing formula reference; Auto Create
    References
    to Multiple Sheets; Insert Bullets, Check Boxes and more…
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic…
  • Combine
    Workbooks and WorkSheets
    ; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same
    window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!

Comments (3)

No ratings yet. Be the first to rate!

Excel Quick Analysis Tool allows you to quickly analyze data.

It gives you quick access to some of the commonly used data analysis functionalities in Excel so that you don’t have to go into the ribbon and find the correct option (hence saving some clicks and a little bit of time).

In this tutorial, I will tell you everything you need to know about the Quick Analysis Tool in
Excel
. I’d also cover different use cases where you can use this tool to be more productive and save time.

  • Where is the Quick Analysis Tool in Excel?
  • What Can You Do with Quick Analysis Tool in Excel?
    • Conditional Formatting
    • Charts
    • Totals (Calculations)
    • Insert Table/Pivot Table
    • Sparklines
  • Using Quick Analysis Toolbar in Excel – Some Practical Examples
    • Get the SUM or COUNT of Multiple Rows/Columns in One Go
    • Calculate Percentage Total For Multiple
      Rows/Columns
    • Highlighting All the Cell With Value Greater Than a Specified Value
    • Add a Running
      Total in Column or Row
    • Add a New Column to Get Sum of Rows
    • Highlight Dates That Occur Last Month or Last Week
    • Highlight All Cells That Contain a Specific Text
    • Highlight Cells with Duplicate Text
    • Highlight Cells with Unique Text
    • Remove Conditional Formatting from the Selected Range of Cells
    • Quickly Insert a Cluster/Stacked chart, Pie Chart, or Scatter Chart
  • Quick Analysis Tool Not Showing Up in Excel – How to Fix?
  • Can I add More Options to the Quick Analysis
    Tool?
  • Can I Disable the Quick Analysis Tool?

Excel Quick Analysis tool was introduced in Excel 2013, so you won’t find it in versions before Excel 2013.

If
you’re searching for the Quick Analysis Tool in the ribbon in Excel, you won’t find it.

It appears automatically whenever you select a range of cells an icon in the bottom right part of the selection.

You can also use the
keyboard shortcut Control + Q to show the Quick Analysis Tool icon, in case it doesn’t show up.

By default, this tool is enabled in all the Excel versions (in and after Excel 2013). In case you still can’t access the Quick Analysis Tool, you can
follow the steps here to enable it.

Quick Analysis Tool tries to give you some commonly used functionalities in Excel.

Note that the data analysis options you see in the Quick Analysis Tool
are not unique and you can also find these in the ribbon in different tabs in Excel. The point of showing this tool is to enable you to access these faster.

Let’s now look at all the options you have available in the Quick Analysis Toolbar:

Conditional Formatting

The first option you get in the Quick Analysis Toolbar is Conditional Formatting. Within this, you will see five commonly used Conditional Formatting options.

Note that these options would change based on the data you have selected. For example, if you have numeric data, you will see options such as Data Bars, Icon set, Greater than, Top 10%, etc.

If you have text data, then the options would be to highlight duplicate or
unique values or highlight cells that contain specific text or matches a specific string.

And similarly, in case you have dates data, the options would accordingly change.

Keyboard Shortcut - Control + Q + F

Charts

Based on the data in your selected range, you will be shown some commonly used chart types in Excel.

In case you don’t find the chart type you’re looking for, you can click on the More
Charts option, which will open the ‘Insert Chart’ dialog box, where you get all the charting options.

Keyboard Shortcut - Control + Q + C

Totals (Calculations)

This could be quite useful if you quickly want to get calculations such as sum, count, average, running total, etc.

Again, the options you see here would be dependent on the data in the range you have
selected. If you only have text data in the selected range, then this would only show you the option to get the Count.

Keyboard Shortcut - Control + Q + O

Insert Table/Pivot Table

If you have tabular data that you quickly want to convert into an Excel Table, it can be done using the Quick Analysis Tool (option in the Table’s header).

You also get an option to
quickly insert a new Pivot Table in a new sheet using the selected data as the source.

Keyboard Shortcut - Control + Q + T

Sparklines

And lastly, you have the sparkline option where you can quickly insert
these in-cell charts that can do wonders in making your data more comprehensible.

You can use it to quickly insert the Line, Column, or Win/Loss sparklines.

Keyboard Shortcut - Control + Q + S

Now let’s look at
some practical examples where using the Quick Analysis Toolbar can save you some time.

Get the SUM or COUNT of Multiple Rows/Columns in One Go

Below I have a dataset where I have the sales of different product lines (printers, scanners, laptops, and projectors) for seven different states in the US.

I want to get the sum of each row and each column. This will tell
me what was the total sale value of each product line as well as the total sale value for each state.

Below are the steps to do this using Quick Analysis Tool:

  1. Select the entire dataset
  2. Click the Quick Analysis icon that shows up at the bottom right of the selection. If you don’t see it, use the keyboard shortcut Control + Q
  1. Go to the Totals group
  2. Click on the ‘Sum’ option (the one in blue color)

The above steps would instantly add a new row that will show you the sum of total sales of each product.

Similarly, if you want to get the sum by states, you can repeat the same steps, and in step 4,
instead of the first Sum option (the one in blue color), select the second Sum option (the one in the yellow color).

This will add a new column that will show the sum of all values for each state.

Note that when you use this option, Excel creates and enters the
formula for these in the cells. If you click on any of these cells filled by the Quick Analysis tool and check the formula bar, you will see the formula it has used.

In this example, I have shown you how to get the sum of values in rows or columns, and you can follow the same process to get the count of values in rows or columns. Just change the selection in Step 4 (and select the Count options instead)

Also read: How to Sum Only Positive or Negative Numbers in Excel (Easy Formula)

Calculate Percentage Total For Multiple Rows/Columns

Just like we added the sum and count values for rows/columns, you can also add the % Total values.

Below I have the same dataset and I want to get the percentage total value for each product as well as each state.

This will tell me what percentage of total sales is because of
the Printers or Scanners. Similarly, having the percentage total value in the column would tell me what percentage of sales is contributed by which state.

Below are the steps to do this:

  1. Select the dataset
  2. Click the Quick Analysis icon
  3. Click on the Totals option
  4. Click on the % Total option

The above steps would insert a new row that will show the percentage total of all the products.

Similarly, if you want to get the percentage total of all the states, you can repeat
the same steps, and in step 4, choose the second % Total option (the one in the yellow color). This will insert a new column that will show the percentage-wise sales by each state.

Also read: Calculate Percentage Change in Excel (% Increase/Decrease Formula)

Highlighting All the Cell With Value Greater Than a Specified Value

If you’re working with numeric data and want to quickly highlight all the cells with a value greater than a specific value, you can do that with a few clicks using the Quick Analysis Tool.

Below I have a dataset where I have the sales figures for different states, and I want to highlight all the cells where the value is more
than 100000.

Here are the steps to do this:

  1. Select the range that has the sales value
  2. Click the Quick Analysis icon that shows up at the bottom right of the selection. If you don’t see it, use the keyboard shortcut Control + Q
  3. Make sure you are in the Formatting group
  4. Click on the ‘Greater Than’ option
  1. In the ‘Greater Than’ dialog box, enter 100000 in the field
  1. Specify the formatting (or you can use the default Light Red Fill with Dark Red Text)
  1. Click OK

The above steps would instantly highlight
all the cells that have a value greater than 100,000.

Note that the above steps have used the Conditional Formatting option to highlight the cells. If you want to modify the conditional formatting rule, you can do that by clicking on the Conditional Formatting option (in the Home tab) and then clicking on ‘Manage Rules’

Add a Running Total in Column or Row

Below I have month-wise sales data and I want to add a running total column for the sales values.

Having a running total column is useful when you want to track how the sales are progressing. For example, I can quickly scan through this column and figure out when the total sales value crossed 30,00 or 50,000.

Below are the steps to add a running total column using the Quick
Analysis tool:

  1. Select the entire dataset
  2. Click on the Quick Analysis icon at the bottom right of the selection (keyboard shortcut Control + Q)
  3. Go to the Totals group
  4. Get more options by clicking on the small arrow icon at the right (where all the options end). Clicking on this would show you more available options
  1. Click on the ‘Running Total’ option

The above steps would instantly add a new column that will show the running totals.

Note that for this to work, the adjacent column (where the running total values get filled) needs to be blank. In case it’s not blank, you will see a prompt that will ask you to overwrite the data or cancel the operation

In case you see the hash symbols instead of the values in the running total column, expand the column width to make sure it’s wide enough to accommodate the numbers.

Just like I showed you how to add a running
total column, you can also add the row that shows the running total. For this, you need to select the blue-colored Running Total option (there are two running total options – the blue one for rows and the yellow one for columns)

Add a New Column to Get Sum of Rows

Another useful use of the Quick Analysis tool can be to add a row or column that gives the sum of the values in that row/column.

Below I have the
sales data for different product lines and different states in the US.

It would be useful to have an additional row and an additional
column that shows the sum of all the values. This would help me know what’s the total sales for each product line and total sales for each state.

Below are the steps to do add a row that shows the sum of all values in each column:

  1. Select the dataset
  2. Click on the Quick Analysis icon at the bottom right of the selection (keyboard shortcut Control + Q)
  3. Go to the Totals group
  4. Click on the Sum option

The above steps would instantly add a new row with the title ‘Sum’ that will give you the sum of all the columns that have product-wise sales data.

Similarly,
if you want to add a column that shows the sum of all values in the row, repeat the same steps, but after step 3, click on the small arrow icon at the right to get more options, and then click on the Sum option (the one in Yellow color)

Note that in case you see hash symbols instead of the values, the column width needs to be increased to make the values visible.

Also read: How to Multiply a Column by a Number in  Excel (2 Easy Ways)

Highlight Dates That Occur Last Month or Last
Week

Quick Analysis tool has some useful options when working with dates.

One that I find particularly useful is the option to highlight dates that occur in the last week or the last month (where Excel picks up the current date value from your system’s setting).

Below I have a dataset where I have the dates in column A and the task list in column B, and I want to know what dates occurred in the past month or the past week.

Here are the steps to highlight all dates in the previous month:

  1. Select
    the dataset
  2. Click on the Quick Analysis icon at the bottom right of the selection
  3. In the Formatting group, click on the ‘Last Month’ option

The above steps would instantly highlight all the dates that occur in the previous month.

Note
that the dates being highlighted are using the current date of my system at the time of taking this screenshot

A few things you should know when using the Quick Analysis tool to highlight cells using Conditional Formatting:

  • When you use any of the options in the Formatting group, it uses conditional formatting to create a rule for the selected cells. This rule remains in place unless you remove it by clicking on the ‘Clear Format’ option (it’s also there in the Formatting
    group)
  • When you click on more than one option, more than one rule is applied to the selected cells. For example, if I first click on the option to highlight cells with dates occurring in the last week and then click on the option to highlight cells with dates occurring in the last month, both the rules would be in place.
Also read: How to Highlight Weekend Dates in Excel?

Highlight All Cells That Contain a Specific Text

So far, we
have seen examples of using the Quick Analysis tool with numeric data and dates.

But you can also use this with text data.

Below I have a dataset where I have the product id in column A and the price in column B.

I want to quickly highlight all the cells where the product id contains the text
‘KL’.

Below are the steps to do this using Quick Analysis options:

  1. Select the data in column A, that has the text data. Note that you shouldn’t select the entire dataset (i.e., columns A and B), as it wouldn’t show you the text-related right options in the Quick Analysis tool.
  2. Click on the Quick Analysis icon
  3. In the Formatting group, click on the ‘Text Contains’ option
  1. In the ‘Text That Contains’ dialog box, enter KL
  1. Select the
    formatting in which you want the cells to be highlighted. In this case, I will go with the default ‘Light Red Fill with Dark Red text’
  1. Click OK

The
above steps would instantly highlight all the cells in column A that contain the text ‘KL’

Also read: How to Count Cells that Contain Text Strings

Highlight Cells with Duplicate Text

Identifying duplicates in a dataset is a common task for many Excel users. While there are multiple ways to highlight duplicates in Excel, the Quick Analysis tool makes it really quick.

Below I have a dataset where I
have employee names in column A and the training dates assigned to them.

Here are the steps to quickly highlight all the
cells with duplicate names:

  1. Select the names in column A (don’t select the entire dataset, only column A that has the text data)
  2. Click on the Quick Analysis icon
  3. In the Formatting group, click on the ‘Duplicate Values’ option

That’s it! Done.

It will highlight all the cells that have the names that have been repeated.

For this to work, the text in
the cells needs to be exactly the same. So make sure there are no leading or trailing space characters in the cells. If there are extra space characters in one cell with a name, and not there in the other one, Excel would consider these as different.

Also read: How to Filter Cells that have Duplicate Text Strings (Words) in it

Highlight Cells with Unique Text

Just like we highlighted cells with duplicate text, you can also highlight cells that have
unique values.

Let’s take the same example (dataset below), where I have the employee names in column A and their training date in column B, and I want to identify names that only occur once.

Below are the steps to do this using the Quick
Analysis tool:

  1. Select the names in column A (excluding the header)
  2. Click on the Quick Analysis icon
  3. In the Formatting group, click on the ‘Unique Values’ option

The above steps would instantly highlight all the names that only appear once in the list.

Remove Conditional Formatting from the Selected Range of Cells

Conditional Formatting is an amazing tool that I use quite often, and a lot of times, I need to remove the Conditional Formatting rules that have already been applied.

Sometimes I just don’t need these, or I need to start from a
blank slate that requires removing all the previously applied rules.

Quick Analysis tool makes it really easy to remove conditional formatting by giving you access to that option with a single click.

Below are the steps to remove Condition Formatting using the Quick Analysis tool:

  1. Select the data from which you want to remove the Condition Formatting rules
  2. Click on the Quick Analysis icon
  3. Click on Clear Format

Note that the above steps would only remove the Conditional Formatting, and not the regular formatting such as borders
or cell colors or font size/type, etc.

Also read: How to Remove Cell Formatting in Excel (from All, Blank, Specific Cells)

Quickly Insert a Cluster/Stacked chart, Pie Chart, or Scatter Chart

Apart from all the other ways to insert charts in Excel, you can also use the Quick Analysis tool to insert a chart.

While you only get some limited options in the Quick Analysis tool, it could be faster if you want to insert common chart types such as a line chart or a
clustered column chart.

And in case you want more charting options, there is a More Charts option available as well.

Below I have a dataset where I have the month names in column A and the sales values in column B, and I want to create a line chart using it.

Here are the steps to do this:

  1. Select the entire dataset
  2. Click on the Quick Analysis icon
  3. Go to the Chart options
  4. Click on the ‘Line Charts’ option

The above steps would insert a line chart using the selected dataset.

Note that when you hover over the charting options in the Quick Analysis tool, it will show you a preview
of how each chart would look like using the selected data.

Also read: How to Make a PIE Chart in Excel

In case you’re using the latest Excel version (Excel 2013, 2016, 2019 or Excel with Microsoft 365), And you do not see the Quick Analysis Tool when you select a range of cells, most likely it’s disabled.

And this has an easy fix – you enable it.

Below are the
steps to enable the Quick Analysis Tool in Excel:

  1. Click the File tab in the ribbon
  2. Click on Options
  3. In the Excel Options dialog box, make sure General is selected in the left pane
  4. Enable the option – ‘Show Quick Analysis options on selection’
  5. Click OK

Note that even if the Quick Analysis tool is disabled in your workbook, it would still show up when you use the keyboard shortcut Control + Q (hold the Control key and press the Q key)

At the time of writing this article, unfortunately, you cannot add more options to the Quick Analysis Tool.

However, just like many features or functionality in Excel, there is a good possibility that they might allow users to add custom options to the Quick Analysis Tool.

Even if it doesn’t allow users to add custom options, I am
confident that based on user feedback, the team at Microsoft Excel would add more useful options.

As an alternative, you can consider using the Quick Access Toolbar which allows you to add custom options and even macros that can be accessed with a click or a keyboard shortcut.

If you don’t want the Quick Analysis Tool icon to show up whenever you select any range of
cells, you can disable it.

The steps are exactly the same as I covered above in the ‘Quick Analysis Tool Not Showing Up in Excel – How to Fix?‘ section:

  1. Click the File tab in the ribbon
  2. Click on Options
  3. In the Excel Options dialog box, uncheck the option – ‘Show Quick Analysis options on selection’
  1. Click OK

Even when it’s disabled, you can still access it using the keyboard shortcut Control + Q

In this article, I showed you how to use the Quick Analysis tool to get access to some useful
options. I also covered some practical examples and how these can be used in your day-to-day work.

Overall, it’s quite useful and can help you become more productive when working with Excel.

And although at the time of writing this article, there are limited options in the tool and it does not allow adding custom options to it, I have seen continuous improvements being made to the tool and it may allow this functionality in the future.

Other Excel tutorials you may
also like:

  • Excel Quick Access Toolbar – 5 Options You Should Consider Adding
  • Apply Conditional Formatting Based on Another Column in Excel
  • Search and Highlight Data Using Conditional Formatting

Why is quick Analysis not showing?

Excel will not show you the quick analysis button if you select blank cells. And, if you have selected the entire column or a row.

Why isn’t my quick Analysis showing up on Excel?

To enable the Quick Analysis tool check the tick box on “Show Quick Analysis Options on Selection”, under the General Tab in the Excel Options menu. There click on the “Show Quick Analysis Options on Selection” tick box to activate it.

How do I enable quick Analysis?

Quick Analysis takes a range of data and helps you pick the perfect chart with just a few commands. Select a range of cells. Select the Quick Analysis button that appears at the bottom right corner of the selected data. Or, press Ctrl + Q.

Why isn’t my quick Analysis showing up on Excel on Mac?

If you are wondering about the location of the Quick Analysis Tool in Excel on Mac, you still won’t find it on the screen. When you want to use the Quick Analysis Tool in Excel on Mac, you need your keyboard or mouse. Go into the tools menu, and select Add-Ins.

Why is my quick analysis tool not showing up? | admin | 4.5
shopee flash sale gratis ongkir
x