How to Use Analyze Data in Excel (5 Easy Methods)

While working in Microsoft Excel you might find it hard to visualize data or apply formulas to find results in your worksheet. Excel does have a tremendous feature named Analyze data to solve your problems. You can use the analyze data feature in excel to find any types of solution you want. In this article, I am going to share with you how to use analyze data feature in excel.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Introduction of Analyze Data Feature in Excel

Analyze Data Feature is used in excel to display visual charts and pivot tables. It is also used to find solutions using the question bar. To work more effectively the analyze data feature can calculate trends, rankings, patterns, and more. In a nutshell, you can get the total sum, average, possible outcomes, and so on.


5 Simple Methods to Use Analyze Data Feature in Excel

In the following article, I have described 5 simple methods to use analyze data in excel.

Suppose we have a dataset of some Student’s Names and their Marks for Quiz exams. Now we are going to use analyze data from this dataset to calculate and visualize different outcomes.

Use Analyze Data in Excel


1. Insert Pivot Table from Analyze Data Command for Data Analysis

In order to get a pivot table in your excel workbook, you can use the analyze data feature. Follow the steps below-

Steps:

  • Select your whole dataset from the worksheet and click the “Analyze Data” option from the Home tab.

Insert Pivot Table to Analyze Data

  • You will find a task pane on the right side of your workbook.
  • From the “Discover Insights” click the “Insert Pivot Table”.

  • A new pivot table will be created in a new sheet.
  • This way you can create a pivot table using the analyze data feature.

Insert Pivot Table to Analyze Data

Read More: [Fixed:] Data Analysis Not Showing in Excel (2 Effective Solutions)


2. Select Chart Option from Analyze Data Feature for Visual Data Representation

You can also get chart options of different types from this feature.

Steps:

  • Open the “Analyze Data” task pane just like method 1.
  • From the pane choose “Insert Chart” of your choice.

Select Chart Option to Analyze Data

  • Here we have successfully inserted a chart of our choice utilizing the analyze data feature.


3. Insert Graph Option to Analyze Data

When working with a large dataset sometimes we need to visualize data in a graph. We often use the insert option and then choose a graph. But excel’s built-in “Analyze Data” option has made this easier to work. Find the steps below to do so-

Steps:

  • Go to the “Analyze Data” option and select a graphical view from suggestions.
  • Press the “Insert Chart” to get the output.

  • Thus, we can get our desired output in the worksheet.

Insert Graph Option to Analyze Data


Similar Readings


4. Search for a Result According to Your Demand from Analyze Data Feature

Often you will not find the solution in the suggested right-side panel you are looking for. But don’t worry. You can naturally ask questions in the “Analyze Data” feature to get your desired solution.

Steps:

  • Selecting the whole dataset opens the “Analyze Data” feature just like the previous methods.
  • Choose the “Ask a question about your data” to type what you are looking for.

Search for a Result According to Your Demand from Analyze Data Feature

  • Here I have typed “Sum Quiz 1 (Out of 30)” as I am searching for the sum result of the “Quiz 1” column.
  • As you can see just below the question bar it will show the answers I am looking for.
  • Finally, we have our precious output in our hands using this tremendously useful feature.

Search for a Result According to Your Demand from Analyze Data Feature


5. Check Out the Suggested Questions List from Analyze Data Command

Sometimes using this feature you will find some suggested questions too. You can choose any of the questions to find your desired solution.

Steps:

  • Open the “Analyze Data” tab.
  • Just below the search question bar, you will find “Suggested questions”.
  • Choose any of the suggested questions to find answers.

 Check Out the Suggested Questions List from Analyze Data Command

  • Here I have selected “Percentage Distribution of Student Name”.
  • Below the question bar, you will find answers.
  • Choose “Insert PivotChart”.

  • This way you can use analyze data feature to find the solution you are searching for.

 Check Out the Suggested Questions List from Analyze Data Command


Things to Remember

  • This “Analyze Data” feature can be used if you have access to Microsoft 365. Otherwise, you won’t be able to use this feature.

Conclusion

In this article, I have tried to cover all the simple methods to use to analyze data in excel. Take a tour of the practice workbook and download the file to practice by yourself. Hope you find it useful. Please inform us in the comment section about your experience. We, the ExcelDemy team, are always responsive to your queries. Stay tuned and keep learning.


Related Articles

Wasim Akram

Wasim Akram

Hi! my name is Wasim Akram. I am a graduate in mechanical engineering from Ahsanullah University of Science & Technology. I am passionate about learning new things and writing articles. I write article about Microsoft Excel and it's my favourite thing to do.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo