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.
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-
- Select your whole dataset from the worksheet and click the “Analyze Data” option from the Home tab.
- 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.
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.
- Open the “Analyze Data” task pane just like method 1.
- From the pane choose “Insert Chart” of your choice.
- 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-
- 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.
- How to Analyze Sales Data in Excel (10 Easy Ways)
- Analyze Likert Scale Data in Excel (with Quick Steps)
- How to Analyse Qualitative Data from a Questionnaire in Excel
- Analyze Time-Scaled Data in Excel (With Easy Steps)
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.
- 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.
- 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.
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.
- 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.
- 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.
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.
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.