Everything Everywhere All At Once — Excel Series (Published)
~/Pal Version
Home
Table of Contents
· Home
∘ Clones
∘ Challenges
∘ Golden Rules on Tabular Data
∘ Rules of Chart Design
∘ Dataset Links
∘ Add-ins
∘ Social Media Handles
∘ Self-Study Track
∘ Auditing Features
∘ Limitations
∘ Key Steps in Data Analysis
∘ Books
∘ Try-outs
∘ Projects
∘ How to approach a new Challenge
∘ [End]
Clones
Q21: What are the clones of Excel ?
A21.a: Google Sheets
A21.b: Ethercalc
A21.c: Apple Numbers
Challenges
Q12: What are the challenges that can be tried while learning Excel ?
a12.1 String Reverse [..]
a12.2 Sanskrit names detector if female or male
a12.3 Holi Poster [..]
a12.4 BMI App [..]
a12.5 Data Analysis of Titanic Dataset [..]
a12.6 Data Analysis of mtcars [..]
a12.7 Check Palindrome
a12.8 Hangman
a12.9 Data analysis IPL dataset [..]
a12.10 How to convert a .csv
file from url into .xlsx
sheet
a12.1.a Functions used: LEFT(), RIGHT(), MID(), LEN(), VLOOKUP(), CONCAT() or “&” symbol
a12.6.a Hypothesis: Correlation between mpg vs wt (to explain CORREL()
and Scatter Plot)
a12.6.b Drawn box plot on wt vs mpg (used combo chart)
a12.6.c used VLOOKUP(), INDEX() and MATCH()
excel functions
a12.5 [🚢]
Hypothesis on Titanic:
a12.5.a Older and Younger Passengers have high chance of survival
a12.5.b High class Passengers have high chance of survival
a12.5.c Female Passengers have high chance of survival
a12.5.d Filling up blank values in Age Column
a12.5.cd Heatmap on correlation matrix (used conditional formatting) with Survival and other key features
a12.5.ui Histogram on age distribution (Pareto)
a12.5.e Worked on Plots like Piechart , Barplot, Donught Chart, 100% Stacked Bar Graph, Histogram and Box Plot
a12.5.e.i Introduced the Pivot Charts and made students understand the importance of summarized data.
a12.5.f Converted Text data to Numeric Data and done the Descriptive Statistics and found of the missing values in Age distribution.
a12.4.a [..BMI]
Used thick borders
a12.4.b Used basic math operators
a12.4.c Used IF statement
a12.4.d Used conditional formatting.
a12.4.e used Today()
function and Date()
function
a12.2.a Used LEFT()
and IF()
statements
Golden Rules on Tabular Data
Q41 What are the rules for Tabular Data ?
A41.a Rule #0: Make more mistakes and learn stuff during resolving them
A41.b Rule #1: Never ever disturb the source file
A41.c Rule #2: Duplicate source file and convert it into table
A41.d Rule#3: Insert Pivot Table for better understanding of data
A41.e Rule#4: Always start from A1
Rules of Chart Design
Q13 What are the rules for Chart design ?
A13.1 Rule#0: Always ensure to fill the values of Title, Axis Labels, Legends, data labels
A13.2 Rule#1: For pie charts, keep only percentages
A13.3 Rule#2: Use appropriate color coding
A13.4 Rule#3: Choose appropriate scale for the axis
Add-ins
Q77: What are the must have add-ins ?
A77.1 Data Analysis Toolpak (link)
A77.2 Powerbi plug-in (Plug-in happens when you install Power BI Desktop)
A77.3 Tableau plug-in (Plug-in happens when you install Tableau Desktop)
Social Media Handles
Q123: Whom to follow in Excel world ?
A123.1 Leila Gahrani Youtuber
A123.2 Chandoo.org
A123.3 Guy in cube for Power BI
Self-Study Track
Q97: What are self-study tracks ?
A97.a: Exceljet
A97.b: W3Schools
A97.c: Excel-easy
A97.d: [Tip]
Take a simple example and practice
A97.e: [Hack]
Use ChatGPT for quiz creation
A97.f: [Tip]
Use Templates to begin with and try to replicate the same on your own
A97.f: [Tip]
Take short text hallenges from Hackerrank or Hackerearth or edabits or leetcode and follow the same in Excel
A97.f.i [links]
- Hackerrank: Hackerrank
- Hackerearth: Hackerearth
- edabits: edabits
- Excel: Excel
Auditing Features
Q321: What are the auditing features that helps ?
A321.a: Use of precedents
A321.b: Evaluate formula feature
A321.c: Use conditional formatting
a12.3.a How to connect to the URL ? (Holi)
Limitations
Q94: What are the limitations of the classroom set up?
A94.a Different versions of the Excel
A94.b Learning is non-linear
A94.c Excel is getting updated for every 2 months with new functions and features.
What you learn few months back in Excel is more likely to get outdated, that’s the true challenge
Key Steps in Data Analysis
Q5: What are the five steps in Data Analysis ?
A5.a Data Collection
A5.b Data Exploration
A5.c Data Pre-processing
A5.d Data Transformation
A5.e.i Dashboard creation
+ A5.e.ii Data Insights Report
Try-outs
Q232: What are some interesting short challenges that can be tried out ?
A232.1 Twitter Sentiment Analysis
A232.2 Check if something is an email
A232.3 Find International Space Station LIVE location
Projects
A2.a Next Month Budget Sheet
A2.b Income Statement with scenario analysis
A2.c Stock Portfolio
A2.d Resume
A2.e Balance Sheet
A2.f Youtube Analytics
A2.g Social Media Content Creation
A2.h Job Dashboard
A2.i Financial Model of Tea Time 🫖
How to approach a new Challenge
Q124: What are the steps to be followed while facing a Challenge?
A124.a: Check for the Objective
A124.b: Write down a script in a flow chart format.
A124.c: Use code block for each step
┌───────────────────┐
│ │ # What is the objective of your task ?
│ objective ? │
└─────────┬─────────┘
│
│
│
▼
┌───────────────────┐
│ │ # Create a script on the flow
│ Scrpit │
└─────────┬─────────┘
│
│
│
▼
┌──────────────────┐
│ │
│ code block │ # Create a code block on each step
└──────────────────┘
A124.ci Provide an example of the above steps
A124.ci.1 Challenge is How to reverse a string ? (Cross ref a12.1)
Q782: I want to catch up on Generative apps, do we have any ?
A782: Yes, Excel formula bot (link)
[End]
N34 Meta Data:
N34.a Version of this post: 1.1.2403