EXCEL KEY POINTS

-

📊 Microsoft Excel Cheat Sheet (for Data Science & Analytics)

🧩 1️⃣ Basic Excel Shortcuts

ActionShortcut (Windows)Shortcut (Mac)
CopyCtrl + CCmd + C
PasteCtrl + VCmd + V
CutCtrl + XCmd + X
Undo / RedoCtrl + Z / Ctrl + YCmd + Z / Cmd + Y
SaveCtrl + SCmd + S
Select AllCtrl + ACmd + A
Find / ReplaceCtrl + F / Ctrl + HCmd + F / Cmd + H
Insert Row / ColumnCtrl + Shift + "+"Cmd + Shift + "+"
Delete Row / ColumnCtrl + -Cmd + -
Filter ToggleCtrl + Shift + LCmd + Shift + F
Move to End of DataCtrl + Arrow keyCmd + Arrow key

🧮 2️⃣ Basic Functions

FunctionDescriptionExample
SUM()Adds up a range=SUM(A1:A10)
AVERAGE()Calculates mean=AVERAGE(B1:B10)
COUNT()Counts numeric cells=COUNT(C1:C20)
COUNTA()Counts non-empty cells=COUNTA(C1:C20)
MIN() / MAX()Minimum or maximum=MAX(A1:A10)
ROUND()Round to given decimals=ROUND(A1, 2)
ABS()Absolute value=ABS(A1)

📅 3️⃣ Date & Time Functions

FunctionDescriptionExample
TODAY()Current date=TODAY()
NOW()Current date & time=NOW()
YEAR() / MONTH() / DAY()Extract components=YEAR(A1)
DATEDIF()Date difference=DATEDIF(A1, A2, "D")
EDATE()Add months=EDATE(A1, 3)
NETWORKDAYS()Working days=NETWORKDAYS(A1, A2)

🔍 4️⃣ Text/String Functions

FunctionDescriptionExample
LEN()String length=LEN(A1)
UPPER() / LOWER()Convert case=UPPER(A1)
PROPER()Capitalize each word=PROPER(A1)
LEFT() / RIGHT()Extract characters=LEFT(A1, 5)
MID()Extract substring=MID(A1, 2, 3)
CONCAT() / &Combine strings=A1 & " " & B1
TRIM()Remove spaces=TRIM(A1)
TEXT()Format numbers=TEXT(A1, "0.00")
FIND() / SEARCH()Locate substring=FIND("x", A1)

📈 5️⃣ Logical & Conditional Functions

FunctionDescriptionExample
IF()Logical condition=IF(A1>50, "Pass", "Fail")
AND() / OR()Multiple conditions=AND(A1>10, B1<5)
IFERROR()Handle errors=IFERROR(A1/B1, "Error")
IFS()Multiple IFs=IFS(A1>80,"A",A1>60,"B",TRUE,"C")

🧠 6️⃣ Lookup & Reference Functions

FunctionDescriptionExample
VLOOKUP()Search vertically=VLOOKUP(A1, B:C, 2, FALSE)
HLOOKUP()Search horizontally=HLOOKUP(A1, B1:F2, 2, FALSE)
XLOOKUP()Modern lookup=XLOOKUP(A1, B1:B10, C1:C10, "Not Found")
INDEX()Get value at position=INDEX(A1:C10, 3, 2)
MATCH()Get position of value=MATCH("John", A1:A10, 0)
INDIRECT()Dynamic cell reference=INDIRECT("A" & B1)
OFFSET()Offset a reference=OFFSET(A1,2,1)

💰 7️⃣ Statistical Functions

FunctionDescriptionExample
MEDIAN()Median value=MEDIAN(A1:A10)
MODE()Most frequent value=MODE(A1:A10)
STDEV.P() / STDEV.S()Standard deviation=STDEV.S(A1:A10)
VAR.P() / VAR.S()Variance=VAR.P(A1:A10)
CORREL()Correlation=CORREL(A1:A10, B1:B10)
COVARIANCE.P()Covariance=COVARIANCE.P(A1:A10, B1:B10)
RANK()Rank value=RANK(A1, A1:A10)

🔄 8️⃣ Data Cleaning Functions

FunctionPurpose
TRIM()Removes leading/trailing spaces
CLEAN()Removes non-printable characters
SUBSTITUTE()Replaces text
VALUE()Converts text → number
TEXTJOIN()Combines with delimiter
REMOVE DUPLICATESFound in Data tab
TEXT TO COLUMNSSplit text by delimiter

📊 9️⃣ Data Analysis Tools (Menu Based)

ToolLocationPurpose
Pivot TablesInsert → PivotTableSummarize data dynamically
What-If AnalysisData tabGoal Seek, Scenario Manager
Solver Add-inAdd-insOptimization problems
Data Analysis ToolPakAdd-insRegression, ANOVA, Descriptive Stats

🔢 🔟 Common Data Visualization

ChartUse Case
Column / BarCompare categories
LineTrends over time
PiePercentage composition
ScatterCorrelation
HistogramDistribution
Box PlotSpread & outliers (Excel 2016+)

Tip:
Go to → Insert → Choose appropriate chart → Add labels, trendlines, error bars via Chart Elements (+).

🧮 11️⃣ Pivot Table Tips

  • Drag fields into Rows, Columns, Values, and Filters.

  • Apply Value Field Settings → Sum / Count / Average.

  • Use Calculated Fields for custom metrics.

  • Use Group By for dates or numeric ranges.

⚙️ 12️⃣ Data Manipulation Techniques

TaskFunction / Tool
Filter dataCtrl + Shift + L
Sort by columnData → Sort
Conditional FormattingHome → Styles
Remove duplicatesData → Remove Duplicates
Split text to columnsData → Text to Columns
Combine dataPower Query (Get & Transform)

📊 13️⃣ Power Query (Get & Transform Data)

🔹 Use for:

  • Merging multiple files or sheets

  • Data cleaning & transformation

  • Automating refreshes

🔹 Access:

Data → Get Data → From File/Workbook/Web → Power Query Editor

🧠 14️⃣ Power Pivot & DAX (Advanced Analytics)

DAX FunctionDescription
CALCULATE()Custom aggregations
FILTER()Dynamic filters
SUMX()Row-wise aggregation
RELATED()Access related table
ALL()Remove filters
IF(), AND(), OR()Logical conditions

📈 15️⃣ Regression & Correlation Analysis

Using Data Analysis ToolPak → Regression

Outputs:

  • R, R²

  • Adjusted R²

  • Coefficients

  • Standard Error

  • Significance F

Shortcut to enable:
File → Options → Add-ins → Analysis ToolPak → Enable

🧮 16️⃣ Array & Dynamic Functions (Excel 365)

FunctionDescription
UNIQUE()Get unique values
FILTER()Filter array by condition
SORT()Sort dynamically
SEQUENCE()Generate sequence
RANDARRAY()Random numbers
LET()Define variables in formula

Example:

=FILTER(A2:B10, B2:B10>100)

🧰 17️⃣ Error Handling

FunctionDescription
IFERROR()Replace errors with custom text
ISERROR() / ISNA()Check for errors
NA()Returns #N/A intentionally

💡 18️⃣ Data Science Relevance

✅ Exploratory Data Analysis (EDA) → Pivot tables, filters, descriptive stats
✅ Regression / Forecasting → Data Analysis ToolPak
✅ Data Cleaning → TRIM, CLEAN, SUBSTITUTE
✅ Data Visualization → Charts, conditional formatting
✅ Automation → Power Query, VBA Macros
✅ Integration → Import/export CSV, Power BI link

📘 19️⃣ Bonus: Excel to Python / Pandas Mapping

Excel ConceptPandas Equivalent
VLOOKUPmerge()
Pivot Tablepivot_table()
Filterdf[df['col']>x]
Averagedf['col'].mean()
Sortdf.sort_values('col')
Remove Duplicatesdf.drop_duplicates()
Text Splitstr.split()
CountIfdf['col'].value_counts()



Comments

Post a Comment

Popular posts from this blog

Resume Work and Project Details

-
 Conceptual / MMM Basics Q1. What is Media Mix Modeling (MMM)? MMM quantifies how different marketing channels and external factors drive business outcomes such as sales or conversions, using regression-based or Bayesian models on historical time-series data. Unlike attribution models, MMM doesn’t require user-level data and works even with aggregated spend data. Q2. What problem does MMM solve? It helps marketers measure ROI by channel and optimize future spend allocation while accounting for carryover, seasonality, and external effects. Q3. What is the dependent variable and what are the independent variables in your models? Dependent variable: Sales or revenue. Independent variables: Marketing touchpoints (TV, Digital, Print, Radio) and control variables (Price, Competitor, Macro factors, Seasonality). Q4. How is MMM different from digital attribution models? Attribution models rely on user-level tracking (cookies, clicks). MMM uses aggregate, privacy-compliant data and...
Read more

Time Series and MMM basics

-
🕒 1. What is Time Series Data? Definition: Time series data is a sequence of observations recorded at specific time intervals (daily, weekly, monthly, etc.). Each data point has two key components: Time (t): a timestamp (e.g., date, hour, quarter) Value (y): a measurement (e.g., sales, ad spend, temperature) In Media Mix Modeling: time series data might include weekly sales, ad impressions, or marketing spend across channels. Example: Week TV Spend Online Spend Sales 1 1000 500 15000 2 1200 600 16200 3 900 550 14800 4 1100 700 17000 Here, Sales is the time series variable we want to analyze or forecast. 📊 2. What is Time Series Analysis? Definition: Time series analysis is the study of data points collected over time to identify patterns , trends , seasonality , and dependencies —and to model or forecast future behavior. In MMM, this helps us understand how marketing efforts (TV, digital, print) influence sales over time and fore...
Read more

LINEAR REGRESSION

-
Image
Machine Learning Machine Learning (ML) is a branch of artificial intelligence that focuses on building systems that learn from and make decisions based on data. There are several types of ML, but let’s focus on the two main categories:  Supervised  and  Unsupervised Learning . Supervised Learning Definition : In supervised learning, the algorithm is trained on labeled data. This means the input data is paired with the correct output, and the goal is to learn a mapping from inputs to outputs. Examples : Classification : Predicting whether an email is spam or not spam. Regress...
Read more