Interview Prep
Career
GenAI
Prompt Engg
ChatGPT
LLM
Langchain
RAG
AI Agents
Machine Learning
Deep Learning
GenAI Tools
LLMOps
Python
NLP
SQL
AIML Projects

Reading list

Machine Learning Basics for a Newbie
6 Steps of Machine learning Lifecycle Introduction to Predictive Modeling
Introduction to Exploratory Data Analysis & Data Insights Descriptive Statistics Inferential Statistics How to Understand Population Distributions?
Reading Data Files into Python Different Variable Datatypes
Probability for Data Science Basic Concepts of Probability Axioms of Probability Conditional Probability
Central Tendencies for Continuous Variables Spread of Data KDE plots for Continuous Variable Overview of Distribution for Continuous variables Normal Distribution Skewed Distribution Skeweness and Kurtosis Distribution for Continuous Variable
Central Tendencies for Categorical Variables Understanding Discrete Distributions Performing EDA on Categorical Variables
Dealing with Missing Values Understanding Outliers Identifying Outliers in Data Outlier Detection in Python Outliers Detection Using IQR, Z-score, LOF and DBSCAN
Sample and Population Central Limit Theorem Confidence Interval and Margin of Error
Bivariate Analysis Introduction
Covariance Pearson Correlation Spearman's Correlation & Kendall's Tau Correlation versus Causation Tabular and Graphical methods for Bivariate Analysis Performing Bivariate Analysis on Continuous-Continuous Variables
Tabular and Graphical methods for Continuous-Categorical Variables Introduction to Hypothesis Testing P-value Two sample Z-test T-test T-test vs Z-test Performing Bivariate Analysis on Continuous-Catagorical variables
Chi-Squares Test Bivariate Analysis on Categorical Categorical Variables
Multivariate Analysis A Comprehensive Guide to Data Exploration The Data Science behind IPL
Supervised Learning vs Unsupervised Learning Reinforcement Learning Generative and Descriminative Models Parametric and Non Parametric model
Machine Learning Pipeline Preparing Dataset Build a Benchmark Model: Regression Build a Benchmark Model: Classification
Evaluation Metrics for Machine Learning Everyone should know Confusion Matrix Accuracy Precision and Recall AUC-ROC Log Loss R2 and Adjusted R2
Dealing with Missing Values Replacing Missing Values Imputing Missing Values in Data Working with Categorical Variables Working with Outliers Preprocessing Data for Model Building
Understanding Cost Function Understanding Gradient Descent Math Behind Gradient Descent Assumptions of Linear Regression Implement Linear Regression from Scratch Train Linear Regression in Python Implementing Linear Regression in R Diagnosing Residual Plots in Linear Regression Models Generalized Linear Models Introduction to Logistic Regression Odds Ratio Implementing Logistic Regression from Scratch Introduction to Scikit-learn in Python Train Logistic Regression in python Multiclass using Logistic Regression How to use Multinomial and Ordinal Logistic Regression in R ? Challenges with Linear Regression Introduction to Regularisation Implementing Regularisation Ridge Regression Lasso Regression
Introduction to K Nearest Neighbours Determining the Right Value of K in KNN Implement KNN from Scratch Implement KNN in Python
Bias Variance Tradeoff Introduction to Overfitting and Underfitting Visualizing Overfitting and Underfitting Selecting the Right Model What is Validation? Hold-Out Validation Understanding K Fold Cross Validation
Introduction to Feature Selection Feature Selection Algorithms Missing Value Ratio Low Variance Filter High Correlation Filter Backward Feature Elimination Forward Feature Selection Implement Feature Selection in Python Implement Feature Selection in R
Introduction to Decision Tree Purity in Decision Tree Terminologies Related to Decision Tree How to Select Best Split Point in Decision Tree? Chi-Squares Information Gain Reduction in Variance Optimizing Performance of Decision Tree Train Decision Tree using Scikit Learn Pruning of Decision Trees
Introduction to Feature Engineering Feature Transformation Feature Scaling Feature Engineering Frequency Encoding Automated Feature Engineering: Feature Tools
Introduction to Naive Bayes Conditional Probability and Bayes Theorem Introduction to Bayesian Adjustment Rating: The Incredible Concept Behind Online Ratings! Working of Naive Bayes Math behind Naive Bayes Types of Naive Bayes Implementation of Naive Bayes
Understanding how to solve Multiclass and Multilabled Classification Problem Evaluation Metrics: Multi Class Classification
Introduction to Ensemble Techniques Basic Ensemble Techniques Implementing Basic Ensemble Techniques Finding Optimal Weights of Ensemble Learner using Neural Network Why Ensemble Models Work well?
Introduction to Stacking Implementing Stacking Variants of Stacking Implementing Variants of Stacking Introduction to Blending Bootstrap Sampling Introduction to Random Sampling Hyper-parameters of Random Forest Implementing Random Forest Out-of-Bag (OOB) Score in the Random Forest IPL Team Win Prediction Project Using Machine Learning Introduction to Boosting Gradient Boosting Algorithm Math behind GBM Implementing GBM in python Regularized Greedy Forests Extreme Gradient Boosting Implementing XGBM in python Tuning Hyperparameters of XGBoost in Python Implement XGBM in R/H2O Adaptive Boosting Implementing Adaptive Boosing LightGBM Implementing LightGBM in Python Catboost Implementing Catboost in Python
Different Hyperparameter Tuning methods Implementing Different Hyperparameter Tuning methods GridsearchCV RandomizedsearchCV Bayesian Optimization for Hyperparameter Tuning Hyperopt
Understanding SVM Algorithm SVM Kernels In-depth Intuition and Practical Implementation SVM Kernel Tricks Kernels and Hyperparameters in SVM Implementing SVM from Scratch in Python and R
Introduction to Principal Component Analysis Steps to Perform Principal Compound Analysis Computation of Covariance Matrix Finding Eigenvectors and Eigenvalues Implementing PCA in python Visualizing PCA A Brief Introduction to Linear Discriminant Analysis Introduction to Factor Analysis
Introduction to Clustering Applications of Clustering Evaluation Metrics for Clustering Understanding K-Means Implementation of K-Means in Python Implementation of K-Means in R Choosing Right Value for K Profiling Market Segments using K-Means Clustering Hierarchical Clustering Implementation of Hierarchial Clustering DBSCAN Defining Similarity between clusters Build Better and Accurate Clusters with Gaussian Mixture Models
Understand Basics of Recommendation Engine with Case Study
8 Ways to Improve Accuracy of Machine Learning Models
Introduction to Dask Working with CuML
Introduction to Machine Learning Interpretability Framework and Interpretable Models model Agnostic Methods for Interpretability Implementing Interpretable Model Understanding SHAP Out-of-Core ML Introduction to Interpretable Machine Learning Models Model Agnostic Methods for Interpretability Game Theory & Shapley Values
Introduction to Machine Learning Interpretability Framework and Interpretable Models model Agnostic Methods for Interpretability Implementing Interpretable Model Understanding SHAP Out-of-Core ML Introduction to Interpretable Machine Learning Models Model Agnostic Methods for Interpretability Game Theory & Shapley Values
Introduction to AutoML Implementation of MLBox Introduction to PyCaret TPOT Auto-Sklearn EvalML
Pickle and Joblib Introduction to Model Deployment
Deploying Machine Learning Model using Streamlit Deploying ML Models in Docker Deploy Using Streamlit Deploy on Heroku Deploy Using Netlify Introduction to Amazon Sagemaker Setting up Amazon SageMaker Using SageMaker Endpoint to Generate Inference Deploy on Microsoft Azure Cloud Introduction to Flask for Model Deploying ML model using Flask
Model Deployment in Android Model Deployment in Iphone

3 Advanced Excel Charts Every Analytics Professional Should Try

Ram Dewani
Ram Dewani Last Updated : 26 Aug, 2021
8 min read

Overview

  • Advanced Excel charts are a great way to create effective and impactful stories for our audience
  • Learn 3 advanced Excel charts here to impress your manager and build rapport with your stakeholders

 

Introduction

I was completely focused on analyzing data and using statistical methods to build complex data science models when I first started my analytics journey. In fact, I know a lot of newcomers are fixated on this method of approaching things. I have some news for you – this is actually not the core quality of a good analyst.

The stakeholders weren’t able to understand what I was trying to communicate to them. There was a missing link in the whole scenario – storytelling.

Advanced charts in excel

I was able to level-up by improving my storytelling skills. To convey the story to our management team, a key skill to learn is understanding different types of charts. Usually, we can explain most things with a simple bar chart or a scatter plot but they don’t always fulfill the need.

There is no one size fits all chart and that’s why to create intuitive visualizations, it is imperative that we understand different types of charts and their usage. And Excel is the perfect tool to build advanced yet impactful charts for our analytics audience.

In this article, I am going to discuss 3 powerful and important advanced Excel charts that are going to make you a pro in front of your audience (and even your manager).

This is the sixth article in my Excel for Analysts series. I highly recommend going through the previous articles to become a more efficient analyst:

I encourage you to check out the below resources if you’re a beginner in Excel and Business Analytics:

 

Table of Contents

  1. Advanced Excel Charts #1 – Sparklines
  2. Advanced Excel Charts #2 – Gantt Charts
  3. Advanced Excel Charts #3 – Thermometer Charts

 

Advanced Excel Charts #1 – Sparklines

I will start off with one of my favorite chart types – Sparkline charts. These charts really help me out in making amazing dashboards! So what are sparkline charts?

Sparkline charts are typically small in size and fit into a single cell. These charts are wonderful to visualize trends in your data such as seasonal increase or decrease in value.

In Microsoft Excel, we have 3 different types of sparkline charts – Line, column, and Win/Loss. Let us see how can we make a sparkline chart with this problem statement:

You are an analyst in a product-based company and you want to understand the performance of the salespersons during the first six months of the year. You decide to use the sparkline charts for this.

Follow the steps or refer to the video for more understanding of sparkline charts:

Step 1 – Choose your Type of Sparkline Chart

You will first need to select the type of sparkline chart you want to plot. In our case, we will be using a line chart.

Go to Insert -> Line:

Step 2 – Create  Sparklines

Select the first row of the data in the Data Range and the corresponding Location Range:

We have successfully made our Sparkline chart! Now we just need to drag it for all our rows:

Step 3 – Desired Formatting

We have the basic sparklines ready. We can format it so that the chart becomes more intuitive to understand.  In this case, we have selected High Point and Low Point:

Finally, our table looks like this:

Try using sparklines in your reports next time. You will absolutely love it!

 

Advanced Excel Charts #2 – Gantt Charts

If you have previously worked in the field of Project Management, then you must be familiar with Gantt charts. These charts are one of the most popular and useful ways to track activities of a project against time.

Gantt charts are essentially bar charts. Each activity represents a bar. The length of the bar represents the duration of the activity or a task.

To understand Gantt Charts, let us take up a problem statement. There are two roommates – Joey and Chandler. Due to the recent pandemic, they usually stay at home and they decided to build themselves a home entertainment unit. Chandler being interested in analytics and visualization turns this small project into a Gantt chart representation. Let us see how.

You may refer to the video or the steps given below according to your convenience:

Step 1 – Select the Stacked Bar Chart

Since Gantt charts are basically bar charts, let us first select one:

Go to Insert -> 2D Bar -> Stacked Clustered Bar

Step 2 – Put Categories in Reverse Order

You may notice that the sequence of events is actually in a manner opposite to the required sequence. So let’s reverse it.

Select the categories on the axis and right-click -> Go to Format Axis:

Now simply select the tickbox  – “Categories in reverse order”:

 

Step 3 – Make Date Bar chart invisible

If you observe the chart properly, you’ll notice that the blue chart represents the start date. We will remove the color of this chart to attain the desired Gantt chart.

Select the blue chart -> right click ->Format Data Series:

Now go to the fill section and select – No fill:

Now the chart looks something like this:

Step 4 – Select the minimum and maximum bounds

The date in our chart starts from 29 April which we clearly don’t want as it is utilizing unnecessary space. So let us select the bounds for our Gantt Chart dates.

Select the date (Horizontal Axis) -> Right-click -> Format Axis:

Now we simply need to provide a minimum and maximum bound date:

We have made the following chart:

Step 5 – Format your Chart

We have made our Gantt chart but we won’t be showing it to someone unless it looks appealing to the eyes so we’ll do a bit of formatting to make our chart look cool.

Here we have done a few things:

  • Removed the axis
  • Changed the title and
  • Added Tick Marks

Congratulations! You have made your first Gantt Chart! Joey and Chandler must be enjoying their new entertainment unit.

 

Advanced Excel Charts #3 – Thermometer Charts

Thermometer charts are really interesting and they disburse crucial information in a very crisp manner. These charts are great in visualizing the actual value and the target value. It is useful in visualizing sales, the footfall on the website, etc. Let us understand the charts better with a problem statement.

Every New Year’s, people tend to make resolution. Jake had taken a resolution to complete 48 books during the year. That is around 1 book every week. Let us understand how did Jake perform.

You can follow along with the steps and refer to the video for better understanding:

 

Step 1 – Select Clustered Charts

Select the Percentage data as shown below:

Go to Insert  -> 2D Column -> Clustered Column:

Here is our chart:

Step 2 – Combine the Column

Go to Chart Design -> Select Switch Row / Column and click OK:

Now, right-click the Achieved % column -> Go to Format Series ->Select Secondary Axis:

 

This will overlap both the columns into one column.

Step 3 – Select minimum and maximum

We have our overlapping columns but in different axes so let us give it a uniform bounding.

Select the percentages (Vertical axis values) and right-click -> Format Axis:

Now select the minimum bound as 0.0 and maximum bound as 1.0:

Step 3 – Format the chart

Select the Goal % chart and right-click ->  Format Data Series:

Go to Fill options -> Select No Fill and Solid line border:

Now we have a chart that finally resembles a thermometer chart. All we need is some additional formatting.

Here, we have followed the below steps for formatting:

  • Remove the gridlines
  • Rearrange the size of the chart
  • Give the chart title
  • Adding tick marks

Perfect!

 

End Notes

In this article, we covered three beautiful Excel charts of different kinds to help you become an efficient analyst. I hope these charts will help you in building amazing visualizations, save you a lot of time. and impress your boss. 🙂

Let me know your favorite Excel Charts which you feel make a visualization great.

Excelexcel advanced chartsexcel chartsexcel visualizationvisualization
Ram Dewani
Ram Dewani

Product Growth Analyst at Analytics Vidhya. I'm always curious to deep dive into data, process it, polish it so as to create value. My interest lies in the field of marketing analytics.

Free Courses

Generative AI 4.7

Generative AI - A Way of Life

Explore Generative AI for beginners: create text and images, use top AI tools, learn practical skills, and ethics.
Large Language Models 4.5

Getting Started with Large Language Models

Master Large Language Models (LLMs) with this course, offering clear guidance in NLP and model training made simple.
Prompt Engineering 4.6

Building LLM Applications using Prompt Engineering

This free course guides you on building LLM apps, mastering prompt engineering, and developing chatbots with enterprise data.
LlamaIndex 4.8

Improving Real World RAG Systems: Key Challenges & Practical Solutions

Explore practical solutions, advanced retrieval strategies, and agentic RAG systems to improve context, relevance, and accuracy in AI-driven applications.
RAG systems using LlamaIndex 4.7

Microsoft Excel: Formulas & Functions

Master MS Excel for data analysis with key formulas, functions, and LookUp tools in this comprehensive course.

Recommended Articles

Responses From Readers

Clear

Harish Nagpal
Harish Nagpal

Nice explanation Ram

123

Mantej Singh H Dhanjal
Mantej Singh H Dhanjal

I liked how well you have explained everything

123

Susan
Susan

👌

123

Write for us

Write, captivate, and earn accolades and rewards for your work
  • Reach a Global Audience
  • Get Expert Feedback
  • Build Your Brand & Audience
  • Cash In on Your Knowledge
  • Join a Thriving Community
  • Level Up Your Data Science Game
imag

We use cookies essential for this site to function well. Please click to help us improve its usefulness with additional cookies. Learn about our use of cookies in our Privacy Policy & Cookies Policy.

Show details