EXPLORE THE FEATURES OF MS-EXCEL
[Link]: 1
Date:
Aim:
To explore the features of MS-EXCEL
PROCEDURE:
Step 1 : Start Ms Excel application in Ms- office.
Step 2 : Enter text or a number in a cell.
Step 3 : Change the width of a column.
Step 4 : Enter And Edit Formula in Excel.
Step 5: Wrap text in a cell.
Step 6 : Enter And Edit Formula in Excel.
Step 7 : Perform Auto fill and custom fill In Excel.
Step 8 : Save the file in desired location
Step 9 : Close the Ms Excel application
Result:
The features of MS-EXCEL explored successfully and displayed desired output in neat format.
GET THE INPUT FROM USER AND PERFORM NUMERICAL OPERATIONS
(MAX, MIN, AVG, SUM, SQRT, ROUND)
[Link].1
Date:
Aim:
To use Microsoft Excel for performing numerical operations (MAX, MIN, AVG, SUM, SQRT,
ROUND) with input provided by the user.
Procedure:
1. Open new Microsoft Excel spreadsheet
2. Type the required numerical data to perform numerical operations
3. Perform the following numerical operation (MAX, MIN, AVG, SUM, SQRT, ROUND) using
microsoft Excel functions
4. Verify the output manually.
5. Stop the experiment
RESULT:
Thus the numerical operations like MAX, MIN, AVG, SUM, SQRT, ROUND are performed
successfully.
1
PERFORM DATA IMPORT/EXPORT OPERATIONS FOR DIFFERENT FILE FORMATS
[Link].2
Aim: To perform data import/export operations for different file formats.
Procedure:
1. Importing Data from CSV File and Export it into Microsoft Excel Worksheet (.xlsx):
a. Download a sample .CSV file from the internet or Open Microsoft Excel, Type sample dataset and save as
“employee_data_csv.csv”
b. Go to the "Data" tab.
c. Click on "from text" and select the .CSV file
d. Choose a sample CSV file and click "Import."
Result:
The CSV file is imported and then exported into a Microsoft Excel file (.xlsx). The Text File (.txt) is
imported into Excel and then successfully exported into CSV (Comma-Separated Value) files.
PERFORM STATISTICAL OPERATIONS - MEAN, MEDIAN, MODE AND STANDARD
DEVIATION, VARIANCE, SKEWNESS, KURTOSIS
[Link]
Aim :
To perform statistical operations in Microsoft Excel, allowing them to gain practical experience in
calculating and interpreting measures like Mean, Median, Mode, Standard Deviation, Variance, Skewness,
and Kurtosis.
Procedure:
Statistical Operations:
Input dataset:
The below dataset is used for perform statistical operation in Microsoft excel. Open New Spreadsheet and
type the data from A1 to K1.
Mark 50 45 78 89 90 20 98 100 60 70
Mean:
The AVERAGE function in Excel calculates the average (arithmetic mean) of a group of numbers.
a. Select a cell (B3)
b. Type =AVERAGE(
c. Select a range (B1:K1) and type close bracket )
d. And then Hit enter
RESULT:
The statistical operations - Mean, Median, Mode and Standard deviation, Variance, Skewness, Kurtosis are
performed successfully.
2
PERFORM ANOVA, T-TEST, AND Z-TEST IN MICROSOFT EXCEL WITH
SAMPLE INPUT DATA
[Link]
AIM: To perform ANOVA, t-test, and z-test in Microsoft Excel with sample input data.
PROCEDURE:
Install data analysis tool from excel add-in for excel 2007
ANOVA
A single factor or one-way ANOVA is used to test the null hypothesis that the means of several
populations are all equal. Below you can find the salaries of the data scientist who have a degree in
statistic, computer science or physics.
H0: μ1 = μ2 = μ3
H1: at least one of the means is different
Result:
The ANOVA, t-test, and z-test in Microsoft Excel with sample input data are performed successfully and
the output is verified.
PERFORM DATA PRE-PROCESSING OPERATIONS
HANDLING MISSING DATA
Exp No: 5.1
AIM:
To perform data pre-processing operations: Handling Missing data in Microsoft Excel
Procedure:
Handling missing data is a crucial step in the data pre-processing phase. In Microsoft Excel, there are
several methods to deal with missing data. Here we handle missing value by replace with mean, median and
mode .Here's a step-by-step guide using a simple example:
Result:
The data pre-processing operations: Handling Missing data by replace data with
excel functions in Microsoft Excel is preformed successfully and the output
verified.
3
PERFORM DATA PRE-PROCESSING OPERATIONS
NORMALIZATION
[Link].2
Aim:
The aim of this data pre-processing operation is to normalize numerical features within a dataset using Min-
Max Scaling. Normalization ensures that features with different scales are brought to a standard range,
typically between 0 and 1, facilitating fair comparisons and improving the performance of certain machine
learning algorithms.
Procedure:
It is the process of scaling data in such a way that all data points lie in a range of 0 to 1. Thus, this
technique, makes it possible to bring all data points to a common scale. The mathematical formula for
normalization is given as:
where X is the data point, Xmax and Xmin are the maximum and minimum value in the group of records
respectively. The process of normalization is generally used when the distribution of data does not follow
the Gaussian distribution.
Let’s have a look at one example to see how can we perform normalization on a sample dataset.
Suppose, we have a record of the marks of 8 students inside a class as shown below:
Result:
The data pre-processing operations: Normalization is performed and the output is verified
successfully.
[Link]
Perform dimensionality reduction operation using PCA, KPCA & SVD
AIM:
To Perform dimensionality reduction operation using PCA, KPCA & SVD
PCA :
Principal Component Analysis (PCA) is one of the most popular data mining
statistical methods. Run your PCA in Excel using the XLSTAT statistical software.
KPCA:
Kernel PCA is an extension of PCA that allows for the separability of nonlinear data
by making use of kernels. The basic idea behind it is to project the linearly inseparable data
onto a higher dimensional space where it becomes linearly sep4arable.
SVD:
Singular value decomposition takes a rectangular matrix of gene expression data (defined as
A, where A is a n x p matrix) in which the n rows represents the genes, and the p columns
represents the experimental conditions. The SVD theorem states: Anxp= Unxn Snxp VTpxp.
4
PROCEDURE:
Step 1 : Start Ms Excel application in Ms- office.
Step 2 : Open XLSTAT . Select the XLSTAT / Analyzing data / Principal
components analysis command. The Principal Component Analysis dialog
box will appear.
Step 3 : Select the data on the Excel sheet.
Step 4 : Select Observations/variables in the Data format field because of the
format of the input data and Select Correlation in the PCA type field.
Step 5: In the Outputs tab, activate the option to display significant correlations in
bold characters (Test significancy).
Step 6 : In the Charts tab, in order to display the labels on all charts, and to display all
the observations (observations charts and biplots), uncheck the filtering
option.
Step 7 : If there is a lot of data, displaying the labels might slow down the global
display of the results. Displaying all the observations might make the results
unreadable. In these cases, filtering the observations to display is
recommended
Step 8: Click OK to launch the computations.
Step 7 : Save the excel file and Close the Ms Excel application.
RESULT:
The given dataset was performed dimensionality reduction operation using
PCA, KPCA & SVD and the desired output was displayed in neat format.
[Link]
PERFORM BIVARIATE ANALYSIS ON THE DATASET
AIM:
To Perform bivariate analysis on the dataset using MS-EXCEL.
PROCEDURE:
Step 1 : Start Ms Excel application in Ms- office.
Step 2 : Create datasheet for student marks in Ms Excel application.
Step 3 : If you haven't already installed the Analysis ToolPak , Click the Microsoft
Office button, then click on the Excel Options , and then select Add-Ins , Click
Go, check the Analysis ToolPak box, and click Ok
Step 4 : Select Data tab, then click on the Data Analysis option, then selects
Descriptive Statistics from the list and Click Ok. [Data tab >> Data Analysis
>>]
5
Step 5: In the Input Range we select quantity as x range and discount as y range
then select Output Range where you want the output to be stored. If you don’t
specify the output range it will throw output in the new worksheet.
Step 6 : Then select Output Range where you want the output to be stored. If you
don’t specify the output range it will throw output in the new
worksheet
Step 7 : When you click Ok, you will see the result in the selected output
range.
Step 8: Save the excel file and Close the Ms Excel application.
Result:
The bivariate analysis and multivariate analysis on dataset was performed successfully using
MS-EXCEL and the desired output was displayed in neat format.
APPLY AND EXPLORE VARIOUS PLOTTING FUNCTIONS ON THE DATA SET
[Link]
Aim:
The aim of this data analysis task is to visually explore and analyze the dataset through various plotting
functions in Microsoft Excel. This aims to provide a graphical representation of the data, aiding in the
identification of patterns, trends, and potential insights.
Procedure:
Step 1:
Open Microsoft Excel.
Load the dataset you want to explore into a new or existing worksheet. Let the following data can be taken
as a dataset.
Result:
The data analysis task is to visually explore and analyze the dataset through various plotting functions in
Microsoft Excel are performed successfully.
[Link]
EXPLORE THE FEATURES OF POWER BI DESKTOP
Aim:
To explore the features of Power BI Desktop.
PROCEDURE:
Step 1 : Start Power BI Desktop
Step 2 : Connect to data.
Step 3 : Transform and clean data to create a data model.
Step 4 : Create visuals, such as charts or graphs that provide visual representations
of the data.
Step 5: Create reports that are collections of visuals on one or more report pages.
Step 6 : Share reports with others by using the Power BI service.
Step 7 : Save the file in desired location
6
Step 8 : Close the Power BI Desktop application
RESULT:
The features of Power BI explored successfully and displayed desired output in neat format.
DATA PREPARATION AND LOADING IN POWER BI DESKTOP
[Link]
Aim:
To learn the process of preparing and loading data into Power BI Desktop. Students will also
learn how to import data from various sources, clean and transform it, and create a foundation for
analysis.
Step 1 :
Connect to data in a file
In Power BI, on the Home tab, select Get data. In the list that displays, select the option that you
require, such as Text/CSV or XML.
Result:
Thus the steps for preparing and loading data using Power BI Desktop , importing data from
various sources, clean and transform it effectively, and load it into Power BI is executed
successfully.
DEVELOP THE DATA MODEL USING POWER BI DESKTOP
[Link]
Aim:
To develop a simple data model using power BI and create powerful visualizations and gain
insights into your data
Procedure:
Step 1: Open Power BI Desktop.
Step 2: Load Data
Result:
Thus the steps for developing the data model are practiced successfully.
DAX CALCULATIONS
[Link]
Aim:
To develop DAX (Data Analysis Expression) used to solve many basic calculations and data
analysis problems.
Procedure:
Step 1: Open Power BI Desktop.
Step 2: Load Data/Transform data
Result:
Thus the steps for DAX (Data Analysis Expression) used to solve many basic calculations
and data analysis problems were executed successfully.
DESIGN A REPORT
[Link]
Aim:
To develop DAX (Data Analysis Expression) used to solve many basic calculations and
data analysis problems.
RESULT:
The report was created successfully using Power BI Desktop and displayed desired
output in neat format.
CREATE A DASHBOARD AND PERFORM DATA ANALYSIS
[Link]
Aim:
To create of a dashboard and perform data analysis using Power BI Desktop
PROCEDURE:
Step 1 : Start Power BI Desktop
Step 2 : Go to to home menu , select get data and then choose excel worksheet
Step 3 : Select your file to load into Power Bi application and press load button
Step 4 : Now the file is loaded and now click report view in the left panel
Step 5: Go to data panel and right click on data file and choose team filed and slicer
visualization
Step 6 : Now team wise slicer was displayed. Next copy the visualization and change to card
visualization
Step 7 : Now copy the visualization and change to gauge visualization
Step 8 : Next copy the visualization and change to donut visualization and drag gender field
and change value filed as count of gender
Step 9 : Now change and display the visualization as per team wise
Step 10 : Save the file in desired location
Step 9 : Close the Power BI Desktop application
RESULT:
The dashboard was created and data analysis was performed successfully
using Power BI Desktop and displayed desired output in neat format.