EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Excel Tools Excel Tool for Data Analysis
Secondary Sidebar
Excel Functions
  • Excel Tools
    • Excel Shortcut Redo
    • Reduce Excel File Size
    • Quick Analysis in Excel
    • Goal Seek in Excel
    • Compare Two Lists in Excel
    • Excel Quick Analysis
    • Estimate Template in Excel
    • Pivot Table Count Unique
    • CSV Files into Excel
    • Excel Business Plan Template
    • Excel Export to PDF
    • Free Excel Template
    • Excel Repair
    • Color in Excel
    • Form Controls in Excel
    • Timecard Template in Excel
    • How to Unhide All Sheets in Excel?
    • Power Query in Excel
    • Power View in Excel
    • XML in Excel
    • Excel Evaluate Formula
    • Examples of Excel Macros
    • Consolidation in Excel
    • Ribbon in Excel
    • Excel Conditional Formatting for Dates
    • Protect Sheet in Excel
    • Data Model in Excel
    • Pivot Table Examples
    • Pivot Table Slicer
    • Pivot Table Filter
    • Watch Window in Excel
    • Slicer in Excel
    • Print Gridlines in Excel
    • Convert Numbers to Text in Excel
    • Freeze Columns in Excel
    • Spelling Check in Excel
    • Name Box in Excel
    • Solve Equation in Excel
    • Excel Spreadsheet Examples
    • What If Analysis in Excel
    • How to Print Labels From Excel
    • Excel Named Range
    • Excel TRANSPOSE Formula
    • Excel Merge and Center
    • Excel Freeze Rows
    • HLOOKUP Formula in Excel
    • Excel Create Database
    • Excel Gridlines
    • Excel Spreadsheet Formulas
    • Excel Sort By Number
    • Excel Concatenating Columns
    • Excel AutoCorrect
    • Conditional Formatting For Blank Cells
    • Excel Icon Sets
    • Excel CTRL D
    • Excel Accounting Number Format
    • Excel Regression Analysis
    • Excel Import Data
    • Excel Freeze Panes
    • Excel Calendar
    • Excel Developer Tab
    • Excel Enable Macros
    • Excel Autosave
    • Heat Map in Excel
    • Excel Toolbar
    • Excel Error Bar
    • Excel Status Bar
    • Excel Unprotect Sheet
    • Check mark in Excel
    • Excel Column Filter
    • Excel Header and Footer
    • Excel Drawing
    • Range in Excel
    • Timeline in Excel
    • Excel Lock Formula
    • Excel Table styles
    • Insert New Worksheet in Excel
    • Excel Column Lock
    • Excel Forms for Data Entry
    • QUOTIENT in Excel
    • Excel Sorting
    • Excel Sort by color
    • Excel Data Bars
    • Excel Tool for Data Analysis
    • Excel Flash Fill
    • Excel Auto Fill
    • Excel Quick Access Toolbar
    • Excel Wrap Text
    • Excel Exponential Smoothing
    • Excel ANOVA
    • Excel Merge Two Tables
    • Excel Conditional Formatting in Pivot Table
    • Dynamic Tables in Excel
    • Excel Sort by date
    • Excel Dynamic Range
    • Record Macro in Excel
    • Two Variable Data Table in Excel
    • Merge Cells in Excel
    • One Variable Data Table in Excel
    • Excel Fill Handle
    • CheckBox in Excel
    • Excel Table
    • Excel Combo Box
    • Auto Format in Excel
    • Advanced Filter in Excel
    • Excel AutoFilter
    • Excel Data Filter
    • Excel Data Validation
    • Excel Radio Button
    • Data Table in Excel
    • Text to Columns in Excel
    • Excel List box
    • Excel Solver Tool
    • Scrollbar in Excel
  • Excel Functions (12+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Lookup and Reference Functions in Excel (36+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (220+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • EXCEL ADVANCED Training
  • EXCEL Training COURSE

Excel Tool for Data Analysis

By Jeevan A YJeevan A Y

Data Analysis Tool in Excel

Excel Tool for Data Analysis (Table of Contents)

  • Data Analysis Tool in Excel
  • Unleash Data Analysis Tool Pack in Excel
  • How to Use the Data Analysis Tool in Excel?

Data Analysis Tool in Excel

In excel, we have few inbuilt tools which are used for Data Analysis. But these become active only when you select any of them. To enable the Data Analysis tool in Excel, go to the File menu’s Options tab. Once we get the Excel Options window from Add-Ins, select any of the analysis pack, let’s say Analysis Toolpak and click on Go. This will take us to the window from where we can select one or multiple Data analysis tool packs, which can be seen in the Data menu tab.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

If you observe excel on your laptop or computer, you may not see the data analysis option by default. You need to unleash it. Usually, a data analysis tool pack is available under the Data tab.

Data Analysis 1

Under the Data Analysis option, we can see many analysis options.

Data Analysis 2

Unleash Data Analysis Tool Pack in Excel

If your excel is not showing this pack, follow the below steps to unleash this option.

Step 1: Go to FILE.

Data Analysis Step 1-1

Step 2: Under File, select Options.

Data Analysis Step 1-2

Step 3: After selecting Options, select Add-Ins.

Data Analysis Step 1-3

Step 4: Once you click on Add-Ins, at the bottom, you will see Manage drop-down list. Select Excel Add-ins and click on Go.

Data Analysis Step 1-4

Step 5: Once you click on Go, you will see a new dialogue box. You will see all the available Analysis Tool Pack. I have selected 3 of them and then click on Ok.

Data Analysis Step 1-5

Step 6: Now, you will see these options under the Data ribbon.

Data Analysis Step 1-6

How to Use the Data Analysis Tool in Excel?

Let’s understand the working of a data analysis tool with some examples.

You can download this Data Analysis Tool Excel Template here – Data Analysis Tool Excel Template

T-test Analysis – Example #1

A t-test is returning the probability of the tests. Look at the below data of two teams scoring pattern in the tournament.

Data Analysis Example 1-1

Step 1: Select the Data Analysis option under the DATA tab.

Data Analysis Example 1-2

Step 2: Once you click on Data Analysis, you will see a new dialogue box. Scroll down and find the T-test. Under T-test, you will three kinds of T-test; select the first one, i.e. t-Test: Paired Two Sample for Means.

Data Analysis Example 1-3

Step 3: After selecting the first t-Test, you will see the below options.

Data Analysis Example 1-4

Step 4: Under Variable 1 Range, select team 1 score and under Variable 2 Range, select team 2 score.

Data Analysis Example 1-5

Step 5: Output Range selects the cell where you want to display the results.

Data Analysis Example 1-6

Step 6: Click on Labels because we have selected the ranges, including headings. Click on Ok to finish the test.

Example 1-7

Step 7: From the D1 cell, it will start showing the test result.

Example 1-8

The result will show the mean value of two teams, Variance Value, how many observations are conducted or how many values taken into consideration, Pearson Correlation etc.…

If you P (T<=t) two-tail, it is 0.314, which is higher than the standard expected P-value of 0.05. This means data is not significant.

We can also do the T-test by using the built-in function T.TEST.

SOLVER Option – Example#2

A solver is nothing but solving the problem. SOLVER works like a goal seek in excel.

Look at the below image. I have data of product units, unit price, total cost, and the total profit.

Example 2-1

Units sold quantity is 7550 at a selling price of 10 per unit. The total cost is 52500, and the total profit is 23000.

As a proprietor, I want to earn a profit of 30000 by increasing the unit price. As of now, I don’t know how much units price I have to increase. SOLVER will help me to solve this problem.

Step 1: Open SOLVER under the DATA tab.

Example 2-2

Step 2: Set the objective cell as B7 and the value of 30000 and by changing the cell to B2. Since I don’t have any other special criteria to test, I am clicking on the SOLVE button.

Example 2-3

Step 3: The Result will be as below:

Example 2-4

Ok, excel SOLVER solved the problem for me. To make a profit of 30000 I need to sell the products at 11 per unit instead of 10 per unit.

In this way, we can do the analyze the data.

Things to Remember

  • We have many other analysis tests like Regression, F-test, ANOVA, Correlation, Descriptive techniques.
  • We can add Excel Add-in as a data analysis tool pack.
  • Analysis tool pack is available under VBA too.

Recommended Articles

This has been a guide to Data Analysis Tool in Excel. Here we discuss how to use the Excel Data Analysis Tool along with excel examples and a downloadable excel template. You may also look at these useful articles in excel –

  1. Pareto Analysis in Excel
  2. What-If Analysis in Excel
  3. Excel Regression Analysis
  4. Excel Quick Analysis
Popular Course in this category
Excel Advanced Training (16 Courses, 23+ Projects)
  16 Online Courses |  23 Hands-on Projects |  140+ Hours |  Verifiable Certificate of Completion
4.8
Price

View Course

Related Courses

Excel Training (23 Courses, 9+ Projects)4.9
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

Download Data Analysis Tool Excel Template

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download Data Analysis Tool Excel Template

EDUCBA

डाउनलोड Data Analysis Tool Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more