MS Excel Data Analysis: Foundational Basics

Introduction

Data analysis is one of the in-demand skills in modern technology-driven organizational setups.
MS Excel is a powerful tool for data analysis that facilitates
 Data processing
 Data manipulation
 Data visualization.

Data Preparation

Data cleaning is a critical requirement before any data analysis. MS Excel is a fundamental tool for ensuring that missing values and duplicates are corrected.

How to Clean Data in Excel

Remove Duplicates: Use Data > Remove Duplicates to eliminate redundancy.
 Use TRIM and CLEAN Functions:
 TRIM removes unnecessary spaces.
 CLEAN removes non-printable characters.
 Sort and Structure Data: Convert your dataset into an Excel Table (Insert > Table) for better organization.

Basic Data Analysis Methods.

1. Charts and Visualization
   Charts make it easier to identify trends and relationships in your data:
   • Select your dataset and go to Insert > Charts.
   • Choose from bar charts, line charts, or pie charts.
   • Customize the chart for clarity and impact.

2. Conditional Formatting
   Go to Home > Conditional Formatting.
   Select any column from the table. Here we are going to select a Quarter column. After that, go to the Home tab on the ribbon, then in the Styles group choose Conditional Formatting, and then in the Highlight Cells rule select the Greater Than option.
   Then a greater than dialog box appears. First, write the quarter value and then select the color.

3. Sorting Data
   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.
   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.
   Using text, numbers, dates, and times, you can sort data in one or more columns by custom list, format, cell color, font color, or icon set.
   Step 1: Select Data > Data Tab> Sort
   Select any column from the table. Here we are going to select a Month column. After that, go to the data tab at the top of the ribbon, then in the Sort & Filter group, choose Sort.

Step 2: Select the Order
Then a sort dialog box appears. First, select the column, then choose Sort on, and then Order. After that, click OK.

Step 3: Preview Results
As you can see, the months column is now arranged alphabetically.

1. Filtering Data Filtering to pull information from a given Range or table that satisfies the specified criteria in Excel data analysis. Step 1: Select your dataset and go to Data > Filter Select any column from the table. Here we are going to select a Sales column. After that, go to the data tab at the top of the ribbon, then in the Sort & Filter group, choose Filter.

Step 2: Select the Filter Option
The values in the sales column are then shown in a drop-down box. Here, we will select several filters and then use the greater-than operator.

Step 3: Select the Options
Then a custom auto-filler dialog box appears. Here, we are going to set the sales value to greater than 70, then click OK.

Step 4: Preview Results
As you can see, only rows greater than 70 are shown.

Essential Excel Functions for Data Analysis

=AND -Returns TRUE or FALSE based on two or more conditions
=AVERAGE -Calculates the average (arithmetic mean)
=AVERAGEIF-Calculates the average of a range based on a TRUE or FALSE condition
=AVERAGEIFS-Calculates the average of a range based on one or more TRUE/FALSE conditions
=CONCAT-Links together the content of multiple cells
=COUNT-Counts cells with numbers in a range
=COUNTA-Counts all cells in a range that have values, both numbers and letters =COUNTBLANK-Counts blank cells in a range
=COUNTIF-Counts cells as specified
=COUNTIFS- Counts cells in a range based on one or more TRUE or FALSE conditions =IF-Returns values based on a TRUE or FALSE condition
=IFS-Returns values based on one or more TRUE or FALSE conditions
=LEFT-Returns values from the left side of a cell
=LOWER-Reformats content to lowercase
=MAX-Returns the highest value in a range
=MEDIAN-Returns the middle value in the data
=MIN-Returns the lowest value in a range
=MODE-Finds the number seen most times. The function always returns a single number
=OR-Returns TRUE or FALSE based on two or more conditions
=RIGHT-Returns values from the right side of a cell
=SUM -Adds together numbers in a range
=SUMIF-Calculates the sum of values in a range based on a TRUE or FALSE condition
=SUMIFS-Calculates the sum of a range based on one or more TRUE or FALSE conditions
=TRIM-Removes irregular spacing, leaving one space between each value
=VLOOKUP-Allows vertical searches for values in a table
=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 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).