VOOZH

URL: https://www.analyticsvidhya.com/blog/2020/06/3-advanced-excel-charts-every-analytics-professional-should-try/

⇱ 3 Advanced Excel Charts Every Analytics Professional Should Try - Analytics Vidhya

India's Most Futuristic AI Conference Is Back – Bigger, Sharper, Bolder

d

h

m

s

Machine Learning

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

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?

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 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

Advanced Excel Charts #1 – Sparklines
Advanced Excel Charts #2 – Gantt Charts
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.

👁 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.

Beginner Business Analytics Data Visualization Excel Listicle

Login to continue reading and enjoy expert-curated content.

Free Courses

👁 Generative AI
4.6

Exploratory Data Analysis with Python & GenAI

Learn EDA with Python: Transform data into insights using PandasAI & more.

👁 Generative AI
4.7

Introduction to CrewAI: Building a Researcher Assistant Agent

Build smart AI agents with CrewAI to automate tasks and solve problems.

👁 Generative AI
4.7

Understanding the working of Neural Networks

Learn the neural network basics, concepts, layers, and activation functions.

👁 Generative AI
4.5

No Code Predictive Analytics with Orange

No-code AI course for business pros with real-world ML use cases.

👁 Generative AI
4.6

GenAI Landscape: Foundations & Hands On

Learn Generative AI basics: prompting, RAG, fine-tuning & agents.

Recommended Articles

Responses From Readers

👁 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

👌

123

Become an Author

Share insights, grow your voice, and inspire the data community.

Reach a Global Audience
Share Your Expertise with the World
Build Your Brand & Audience

Join a Thriving AI Community
Level Up Your AI Game
Expand Your Influence in Genrative AI

Flagship Programs

GenAI Pinnacle Program| GenAI Pinnacle Plus Program| AI/ML BlackBelt Program| Agentic AI Pioneer Program

Free Courses

Generative AI| DeepSeek| OpenAI Agent SDK| LLM Applications using Prompt Engineering| DeepSeek from Scratch| Stability.AI| SSM & MAMBA| RAG Systems using LlamaIndex| Building LLMs for Code| Python| Microsoft Excel| Machine Learning| Deep Learning| Mastering Multimodal RAG| Introduction to Transformer Model| Bagging & Boosting| Loan Prediction| Time Series Forecasting| Tableau| Business Analytics| Vibe Coding in Windsurf| Model Deployment using FastAPI| Building Data Analyst AI Agent| Getting started with OpenAI o3-mini| Introduction to Transformers and Attention Mechanisms

Popular Categories

AI Agents| Generative AI| Prompt Engineering| Generative AI Application| News| Technical Guides| AI Tools| Interview Preparation| Research Papers| Success Stories| Quiz| Use Cases| Listicles

Generative AI Tools and Techniques

GANs| VAEs| Transformers| StyleGAN| Pix2Pix| Autoencoders| GPT| BERT| Word2Vec| LSTM| Attention Mechanisms| Diffusion Models| LLMs| SLMs| Encoder Decoder Models| Prompt Engineering| LangChain| LlamaIndex| RAG| Fine-tuning| LangChain AI Agent| Multimodal Models| RNNs| DCGAN| ProGAN| Text-to-Image Models| DDPM| Document Question Answering| Imagen| T5 (Text-to-Text Transfer Transformer)| Seq2seq Models| WaveNet| Attention Is All You Need (Transformer Architecture) | WindSurf| Cursor

Popular GenAI Models

Llama 4| Llama 3.1| GPT 4.5| GPT 4.1| GPT 4o| o3-mini| Sora| DeepSeek R1| DeepSeek V3| Janus Pro| Veo 2| Gemini 2.5 Pro| Gemini 2.0| Gemma 3| Claude Sonnet 3.7| Claude 3.5 Sonnet| Phi 4| Phi 3.5| Mistral Small 3.1| Mistral NeMo| Mistral-7b| Bedrock| Vertex AI| Qwen QwQ 32B| Qwen 2| Qwen 2.5 VL| Qwen Chat| Grok 3

AI Development Frameworks

n8n| LangChain| Agent SDK| A2A by Google| SmolAgents| LangGraph| CrewAI| Agno| LangFlow| AutoGen| LlamaIndex| Swarm| AutoGPT

Data Science Tools and Techniques

Python| R| SQL| Jupyter Notebooks| TensorFlow| Scikit-learn| PyTorch| Tableau| Apache Spark| Matplotlib| Seaborn| Pandas| Hadoop| Docker| Git| Keras| Apache Kafka| AWS| NLP| Random Forest| Computer Vision| Data Visualization| Data Exploration| Big Data| Common Machine Learning Algorithms| Machine Learning| Google Data Science Agent

👁 Av Logo White

Continue your learning for FREE

👁 Av Logo White

Enter OTP sent to

Edit

Wrong OTP.

Enter the OTP

Resend OTP

Resend OTP in 45s

👁 Popup Banner

👁 AI Popup Banner