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 table. After that go to the Insert tab on the top of the ribbon then in the charts group select any chart. Here we are going to select a 3-D column chart.

Step 2: As you can see, the excel table has been converted to a 3-D column chart.

Bar Graph

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 select a Quarter column. After that go to the home tab on the top of the ribbon and then in the styles group select conditional formatting and then in the highlight cells rule select Greater than an option.

Step 2: Then a greater than dialog box appears. Here first 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 select a Months column. After that go to the data tab on the top of the ribbon and then in the sort and filters group select sort.

Step 2: Then a sort dialog box appears. Here first select the column, then select sort on, and then Order. After that click OK.

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 select a Sales column. After that go to the data tab on the top of the ribbon and then in the 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 custom auto filler dialog box appears. Here we are going to apply sales greater than 70 and then click OK.

Step 4: Now as you can see only the rows greater than 70 are shown.

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 length of cell A2, for that we need to 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 remove all spaces from cell A2, for that we need to write the function of trim.

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 convert the A2 cell to upper text, for that we need to write the upper function.

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 convert the A2 cell to proper text, for that we need to write the proper function.

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)

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

Step 1: Use the COUNTIF function on the range B2:B20 to get the number of regions we have of each type.

Step 2: The COUNTIF function will now be used to count the different sorts of Regions in the range F5:F9.

Step 3: Now as you can see the 4 East Region has been correctly enumerated using the COUNTIF function.

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])

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

Step 1: Use the AVERAGEIF function on the range B2:B10 to get the average speed of vehicles.

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 62.333 Car average has been correctly enumerated using the AVERAGEIF function.

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])

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

Step 1: Use the SUMIF function on the range B2:B10 to get the sum of the vehicle’s speed.

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 187 Car sum has been correctly enumerated using the SUMIF function.

VLOOKUP

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

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

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

Step 1: To locate the Festival names depending on their search ID, use the VLOOKUP function. The Festival names in this instance are determined by their search ID.

Step 2: F5 was chosen as the lookup value. The search query is typed in this cell. Table array, in this case, A2:C20, is designated as the table’s range. The col index number is set to 3, which is entered. The information being searched is in the third column from the left. Range lookup is entered as 0 (False).

Step 3: The #N/A value is what the function returns. This is the result of the Search ID F5 having no value entered.

Step 4: The Homegrown Festival, which has 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 Pivot table dialog box appears here select the new worksheet and then click OK.

Step 3: Now you can see it creates a pivot table.

Step 4: Just drag the Country field to the row area and the 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.