Excel is a powerful spreadsheet program created by Microsoft that helps you organize, calculate, and analyze data.In Data Analysis Excel structures data in a meaningful way by letting you:
-Collect, store, organize, and sort information.
-Identify patterns and piece the data together in a way that works for each specific data project.
-Create data visualizations, like graphs and charts.
Excel Overview
The Ribbon provides shortcuts to Excel commands.This can for example be to: insert a table, change the font size, or to change the color of a cell.
The Sheet is a set of rows and columns.
Rows are organized horizontally and are ordered by number while columns are ordered vertically and are ordered by letters.A cell is a single box at the intersection of a row and columns and is referenced by combining the column letter and the row number where it is located.For example,In the above image the word row is in cell D4.
Excel Functionalities Explained
1. Working with Formulas
A Formula is a set of instructions used to perform a calculation using the data in a spreadsheet.Formulas always start with the equal sign = typed in the cell, followed by your calculation.
Creating formulas, step by step
1.Select a cell
2.Type the equal sign (=)
3.Select a cell or type value
4.Enter an arithmetic operator
5.Select another cell or type value
6.Press enter
You can make formulas with arithmetic operations such as:
Addition(+)
Subtraction(-)
Multiplication(*)
Division(/)
2.Using Functions
A function is a preset command that automatically performs a specific process or task using the data in a spreadsheet.
SUM - Add Numbers Together
Syntax: =SUM(number1, number2, ...)
AVERAGE - Calculate Mean
Syntax:=AVERAGE(number1, number2, ...)
MIN and MAX - Find smallest and largest values
Syntax: =MIN(range)
Syntax: =MAX(range)
COUNT - Counts cells containing numbers
Syntax: =COUNT(range)
Text Functions for Data Cleaning
LOWER function is used to lowercase text in a cell
Syntax: =LOWER(cell)
UPPER function is used to uppercase text in a cell
Syntax: =UPPER(cell)
PROPER function capitalizes the first letter of every word in a text string and converts all other letters to lowercase
Syntax: =PROPER(cell)
TRIM function is used to remove irregular text spacing and keep single spaces between words.
Syntax:=TRIM(cell)
The CONCAT function is used to link multiple cells without adding any delimiters between the combined cell values.
Syntax:=CONCAT(cell1, delimiter, cell2)
Conditional Math Functions
These calculate only for rows meeting specific criteria.
SUMIF - Sum based on one condition
Syntax: =SUMIF(range, criteria, [sum_range])
SUMIFS - Sum based on multiple conditions
Syntax: =SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...)
COUNTIF counts cells as specified.
Syntax=COUNTIF(range,criteria)
COUNT IFS is used to count cells in a range based on one or more true or false condition.
Syntax=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)
Logical Functions
IF Function returns values based on a true or false condition.
Syntax=IF(logical_test, [value_if_true], [value_if_false])
AND - Returns TRUE only if ALL conditions are TRUE
Syntax=AND([logical1], [logical2], ...)
OR - Returns TRUE if ANY condition is TRUE
Syntax=OR([logical1], [logical2], ...)
Lookup Functions
VLOOKUP - Search vertically in a table
Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, FALSE)
HLOOKUP - Search Horizontally in a table
Syntax: =HLOOKUP(lookup_value, table_array, row_index_num, FALSE)
INDEX + MATCH-retrieves data from a table based on vertical or horizontal criteria
Syntax=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
3.Data Organization and Management
Excel is useful in organizing data in structured ways.
Sorting data
Arranging data into a meaningful order to make it easier to understand,analyze and visualize.
Ranges can be sorted using the Sort Ascending and Sort Descending commands.
Sort Ascending: from smallest to largest.
Sort Descending: from largest to smallest.
1.Select Data: Select the entire range of data, including headers, or just a single cell in the range.
2.Open Sort Dialog: Go to the Data tab and click the Sort button (the icon with A/Z and an arrow).
3.Configure Sort: In the dialog box:
- My data has headers: Check this if your selection includes header rows. -Sort by: Choose the column you want to sort by first.
Filtering data
Showing only the data that meets a specific criteria
-Select the header row of your dataset.
-Go to the Data tab and click Filter.
-Click the arrow in the header cell of the column you want to filter.
-Uncheck "Select All" and select the desired criteria, or use "Text Filters" / "Number Filters" for specific criteria.
-Click OK
4.Conditional Formatting
Spreadsheet tool that changes how cells appear when values meet specific conditions.
5.Pivot Table
Data summarization tool used in data processing.They are used to summarize,sort,reorganize,group,count,total or average data.
How to create a pivot table
1.Insert: Click any cell in your data, go to the Insert tab, and select PivotTable.
2.Configure: Choose to place it on a new or existing sheet, then click OK.
3.Field List: Drag fields from the list into the four areas: Filters, Columns, Rows, or Values.
Key Components & Functions
Rows/Columns: Define the categories for organizing data.
Values: The numerical data to be summarized (e.g., Sum of Sales).
Filters: Restricts the data displayed.
Calculated Fields: Create new data points
Slicers: Visual filters for creating interactive dashboards.
6. Creating Charts and Graphs
Visual representations make data patterns immediately obvious.
Common Chart Types:
Column Charts - Vertical bars comparing categories
Best for: Comparing sales across different products
Bar Charts - Horizontal bars comparing categories
Best for: Showing rankings or when category names are long
Line Charts - Show trends over time
Best for: Tracking monthly revenue growth
Pie Charts - Show parts of a whole
Best for: Displaying market share percentages
Scatter Plots - Show relationships between two variables
Best for: Analyzing correlations
Creating Charts in Excel
1.Select Data: Highlight the cells containing the data you want to visualize.
2.Insert Chart: Go to the "Insert" tab on the ribbon and select a chart type (e.g., column, line, or pie).
3.Customize: Use the "Chart Design" and "Format" tabs to add titles, axis labels, legends, and adjust styles.
.
Top comments (0)