# Data Analysis in Excel

Data Analysis with Excel is a detailed lesson that gives readers a clear understanding of the newest and most sophisticated functions offered by Microsoft Excel. It describes in detail how to use MS-capabilities Excel to carry out various data analysis tasks. The guide includes a good amount of screenshots that step-by-step demonstrate how to use various features. One of the most used programs for data analysis is Microsoft Excel. You can simply import, browse, clean, analyze, and display your data using this all-in-one data management tool.

## What is Data Analysis?

Data analysis is the process of inspecting, cleaning, transforming, and modeling data to discover useful information, draw conclusions, and support decision-making. It involves a variety of techniques and methods for extracting insights from raw data, with the ultimate goal of gaining a deeper understanding of patterns, trends, relationships, and characteristics within the dataset.

## Excel for Data Analysis

Data analysis with Excel is a common and accessible way for individuals and businesses to analyze and visualize data. Microsoft Excel provides a range of tools and functions for performing basic to advanced data analysis tasks.

The software enables users to seamlessly import and organize data from various sources, facilitating a structured foundation for analysis. Data cleaning becomes an intuitive process with Excel’s capabilities, allowing users to identify and rectify issues like missing values and duplicates. PivotTables, a hallmark feature, empower users to swiftly summarize and explore large datasets, providing dynamic insights through customizable cross-tabulations.

## Methods of Data Analysis

### Charts

Any set of information may be graphically represented in a chart. A chart is a graphic representation of data that employs symbols to represent the data, such as bars in a bar chart or lines in a line chart. Excel has several different chart types available for you to choose from, or you can use the Excel Recommended Charts option to look at charts specifically made for your data and choose one of those.

** Step 1:** Select a

**After that go to the**

**table.****on the top of the ribbon then in the**

**Insert tab****Here we are going to**

**charts group select any chart.**

**select a 3-D column chart.**** Step 2:** As you can see, the excel

**table has been converted to a 3-D column chart.**### Conditional Formatting

Patterns and trends in your data may be highlighted with the help of conditional formatting. To use it, write rules that determine the format of cells based on their values. In Excel for Windows, conditional formatting can be applied to a set of cells, an Excel table, and even a PivotTable report. To execute conditional formatting, adhere to the instructions listed below.

** Step 1:** Select any column from the table. Here we are going to

**After that go to the**

**select a Quarter column.****on the top of the ribbon and then in the**

**home tab****and then in the**

**styles group select conditional formatting**

**highlight cells rule select Greater than an option.**** Step 2:** Then a

**dialog box appears. Here first**

**greater than**

**write the quarter value and then select the color.**** Step 3:** As you can see in the excel table

**Quarter column change the color of the values that are greater than 6.**### Sorting

Data analysis requires sorting the data. A list of names may be arranged alphabetically, a list of sales numbers can be arranged from highest to lowest, or rows can be sorted by colors or icons. Sorting data makes it easier to immediately view and comprehend your data, organize and locate the facts you need, and ultimately help you make better decisions. Both columns and rows can be used to sort. You’ll utilize column sorts for the majority of your sorting. By text, numbers, dates, and times, a custom list, format, including cell color, font color, or icon set, you may sort data in one or more columns.

** Step 1:** Select any column from the table. Here we are going to

**After that go to the**

**select a Months column.****on the top of the ribbon and then in the**

**data tab**

**sort and filters group select sort.**** Step 2:** Then a

**appears. Here first select**

**sort dialog box****After that click OK.**

**the column, then select sort on, and then Order.**** Step 3:** Now as you can see the

**months column is now arranged alphabetically.**### Filter

You may use filtering to pull information from a given Range or table that satisfies the specified criteria. This is a fast method of just showing the data you require. Data in a Range, table, or PivotTable may be filtered. You may use Selected Values to filter data. You may adjust your filtering options in the Custom AutoFilter dialogue box that displays when you click a Filter option or the Custom Filter link that is located at the end of the list of Filter options.

** Step 1:** Select any column from the table. Here we are going to

**After that go to the**

**select a Sales column.****on the top of the ribbon and then in the**

**data tab**

**sort and filters group select filter.**** Step 2:** The values in the sales column are then shown in a drop-down box. Here we are going to

**select a number of filters and then greater than.**** Step 3:** Then a

**dialog box appears. Here we are going to**

**custom auto filler****and then click OK.**

**apply sales greater than 70**** Step 4:** Now as you can see

**are shown.**

**only the rows greater than 70**## Excel functions for data analysis

### LEN

=LEN quickly returns the character count in a given cell. The =LEN formula may be used to calculate the number of characters needed in a cell to distinguish between two different kinds of product Stock Keeping Units, as seen in the example above. When trying to discern between different Unique Identifiers, which might occasionally be lengthy and out of order, LEN is very crucial.

=LEN(Select Cell)

** Step 1:** If we want to see the

**for that we need to**

**length of cell A2,**

**write the function of length.**** Step 2: **Now as you can see it

**shows the length of the cell A2.**### TRIM

=TRIMÂ function will remove all spaces from a cell, with the exception of single spaces between words. The most frequent application of this function is to get rid of trailing spaces. When content is copied verbatim from another source or when users insert spaces at the end of the text, this is normal.

=TRIM(Select Cell)

** Step 1:** If we want to

**, for that we need to write the function of trim.**

**remove all spaces from cell A2**** Step 2: **Now as you can see after

**using the trim function, it removes all spaces.**### UPPER

The Excel Text function “UPPER Function”Â will change the text to all capital letters (UPPERCASE). As a result, the function changes all of the characters in a text string input to upper case.

=UPPER(Text)

** Text (mandatory parameter): **This is the text that we wish to change to uppercase. Text can relate to a cell or be a text string.

** Step 1: **If we want to

**for that we need to write the upper function.**

**convert the A2 cell to upper text,**** Step 2:** Now as you can see after using the upper function,

**the text is changed to the upper text.**### PROPER

Under Excel Text functions, the PROPER Function is listed. Any subsequent letters of text that come after a character other than a letter will also be capitalized by PROPER.

=PROPER(Text)

** Text (mandatory parameter):** A formula that returns text, a cell reference, or text in quote marks must surround the text you wish to partly capitalize.

** Step 1:** If we want to

**for that we need to write the proper function.**

**convert the A2 cell to proper text,**** Step 2:** Now as you can see after using the proper function,

**the text is changed to the proper form.**The PROPER function changes the initial letter of every word, letters that follow digits, and other punctuation to uppercase. It could be where we least expect it. The characters for numbers and punctuation remain unaffected.

### COUNTIF

Excel has a built-in function called COUNTIF that counts the given cells. The COUNTIF function can be used in both straightforward and sophisticated applications. The fundamental application of counting particular numbers and words is covered in this.

=COUNTIF(range,criteria)

The size of the cell range to count.Range:The standards by which cells are selected for counting.Criteria:

** Step 1:** Use the

**to get the number of regions we have of each type.**

**COUNTIF function on the range B2:B20**** Step 2:** The

**will now be used to count the**

**COUNTIF function**

**different sorts of Regions in the range F5:F9.**** Step 3:** Now as you can

**using the COUNTIF function.**

**see the 4 East Region has been correctly enumerated**### AVERAGEIF

An Excel built-in function called AVERAGEIF determines the average of a range depending on a true or false condition.

=AVERAGEIF(range, criteria, [average_range])

The size of the cell range to count.Range:The standards by which cells are selected for counting.Criteria:TheÂ range in which the function computes the average is known as the average range. But the average range is not required.Average Range:

** Step 1:** Use the

**to get the average speed of vehicles.**

**AVERAGEIF function on the range B2:B10**** Step 2:** The AVERAGEIF function will now be used to find the

**average of Vehicles in the range H4:H7.**** Step 3:** Now as you can see the

**using the AVERAGEIF function.**

**62.333 Car average has been correctly enumerated**### SUMIF

A built-in Excel function called SUMIF determines if a condition is true or false before adding the values in a range.

=SUMIF(range, criteria, [sum_range])

The size of the cell range to count.Range:The standards by which cells are selected for counting.Criteria:The range that the function uses to calculate the total is known as the sum range.Sum Range:

** Step 1:** Use the

**to get the sum of the vehicle’s speed.**

**SUMIF function on the range B2:B10**** Step 2:** The SUMIF function will now be used to

**find the sum of Vehicles’ speed in the range H4:H7.**** Step 3:** Now as you can see the

**using the SUMIF function.**

**187 Car sum has been correctly enumerated**### VLOOKUP

VLOOKUP is a built-in Excel function that permits searching across several columns.

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Choose the cell that will be used to input the search criteria.Lookup_value:The whole table range, which includes each and every cell.Table_array:The information being searched for. The column’s number, starting from the left, is the input.Col_index_num:FALSE if text (0), TRUE if numbers (1).Range_lookup:

** Step 1:** To locate the

**The Festival names in this instance are determined by their search ID.**

**Festival names depending on their search ID, use the VLOOKUP function.**** Step 2: F5 was chosen as the lookup value**. The search query is typed in this cell. Table array, in this case,

**The**

**A2:C20, is designated as the table’s range.****which is entered. The information being searched is in the third column from the left.**

**col index number is set to 3,**

**Range lookup is entered as 0 (False).**** Step 3:** The

**This is the result of the Search ID F5 having no value entered.**

**#N/A value is what the function returns.**** Step 4:** The

**, which has**

**Homegrown Festival**

**Search ID 6, has been located through the VLOOKUP tool.**### PIVOT TABLE

In order to create the required report, a pivot table is a statistics tool that condenses and reorganizes specific columns and rows of data in a spreadsheet or database table. The utility simply “pivots” or rotates the data to examine it from various angles rather than altering the spreadsheet or database itself.

** Step 1: **Select any cell and then

**go to the home tab and then select Pivot table.**** Step 2:** Create

**here select the**

**Pivot table dialog box appears**

**new worksheet and then click OK.**** Step 3:** Now you can

**see it creates a pivot table.**** Step 4:** Just drag the

**and the**

**Country field to the row area**

**Days field to the value area.**** Step 5:** Now you can see the

**proper pivot table with Country and days fields.**## Conclusion

In conclusion, Microsoft Excel is a powerful tool for data analysis, offering a range of functions, visualization tools, and features for efficient and insightful data exploration.

## Frequently Asked Questions(FAQs)

### 1.How do you do data analysis on Excel?

Excel facilitates data analysis through functions like PivotTables, charts, and statistical functions. Import, clean, and visualize data to draw meaningful insights.

### 2.Which tool is used for data analysis in Excel?

Excel itself is the tool for data analysis. It provides functions like PivotTables, charts, and various statistical functions for comprehensive analysis.

### 3.How do you organize data in Excel for analysis?

Organize data in Excel by using tables, sorting, filtering, and creating PivotTables. These features help structure data for effective analysis and interpretation.

### 4.What is the course content of Excel data analysis?

An Excel data analysis course covers topics such as importing data, cleaning, using functions, creating charts, PivotTables, and advanced features for in-depth analysis.

### 5.What is analysis data table Excel?

An Analysis Data Table in Excel is a tool for exploring various input values in a formula to observe how changes impact the results, aiding in sensitivity analysis.