How to Perform Case Study Using Excel Data Analysis

In the latest Excel 365, performing any case study for business or research, it’s quite easier and smarter. It includes more exclusive features which give our required case studies a better way. I hope, you will learn the easiest way to perform a case study using Excel data analysis with some clear illustrations.

What Is Data Analysis in Excel?

The Analyze Data tool in Excel 365 is a powerful tool to manage our data by writing our queries directly in a search box. So we’ll be able to ask anything about our data without using any complex commands or formulas. Along with that, we can get very enriched and vivid visual illustrations or patterns of our data. In the earlier versions of Excel, the tool is named Data Analysis.

How Data Analysis Helps in Excel

It can search according to the queries through our natural language.
Provides high-level graphical illustrations and patterns.
Easy to get the Pivot Tables and Pivot Charts.
Faster steps save time.
The interest of Fields can be changed easily.

How to Use Excel Data Analysis for Case Study

Now let’s see how to apply the Analyze Data tool to perform a data analysis case study. But first, get introduced to our dataset that represents some categories’ yearly sales and profits of a company.

With Default Analyze Data Option

First, we’ll see the default analyses that Excel makes automatically. Excel shows the most commonly used analyzes by default.

Steps:

Click any data from the dataset.
Next, click as follows: Home > Analyze Data.

Soon after, you will get an Analyze Data field on the right side of your Excel window. Where you will see different kinds of cases like- Pivot Tables and Pivot Charts.

Look, there is a sample Pivot Table of Sales and Profit by Category. Click on Insert Pivot Table.

Now see, the Pivot Table is inserted in a new sheet.

Click on Insert Pivot Chart from the Sales by Category section then you will get the Pivot Chart in a new sheet.

Here’s the chart.

Scroll down more and Excel will show you more possible Pivot Tables and Charts.

Feel free to use them if you prefer.

Analyze by Inserting Queries

Here, we’ll learn how to analyze data by inserting queries in the ‘Ask a question about your data’ box.

Steps:

When you will click on the question box, it will show some default questions. Click one of them and it will show the answer according to the question. See, I clicked Total ‘Sales’ of ‘Accessories’ over ‘Year’.

It’s the answer from Excel.

Or you can write your question. I asked- Profit chart according to Year.
After that, hit the ENTER button.

Now you see, it’s showing the chart of profit by year. Click on Insert PivotChart.

Soon after, a new sheet will open up with the PivotChart.

Also, there is a Setting icon in the Discover insights part, click it and a dialog box will open up to select the customized fields of interest.

Mark your desired fields from here. I marked Category and Profit.
Finally, just click Update.

Now it is showing the answers only about Category and Profit.

Things to Remember

The Analyze Data tool is only available in the latest Excel 365. But in the earlier versions, it is named Data Analysis ToolPak and available as Add-ins by default.

Download Practice Workbook

You can download the free Excel workbook from here and practice on your own.

Data Analysis for Case Study.xlsx

Conclusion

I hope the procedures described above will be good enough to perform a case study using Excel Data Analysis. Feel free to ask any question in the comment section and please give me feedback.

<< Go Back to Data Analysis with Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!