How to assign same axis values to a group of spark-lines in Excel?


Spark-line is a very handy data visualization technique! It’s great when you are space constrained to show trends among multiple data points.

Here’s an example:

Spark Line Trend Excel Data Visualization

But there’s an issue with above chart! Axis values for these group of spark-lines do not seem match – it could throw someone off if they didn’t pay close attention. So a good practice – when you know users are going to compare segments based on the spark-lines – is to assign them same axis values so it’s easier to compare. Here’s the modified version:

Excel Sparkline data visualization same axis

And…here are the steps:

1. Make sure that spark-lines are grouped.

Select the spark-lines > go to toolbar > Sparkline Tools > Design > Group

Excel Sparkline Group

2. On the “group” section, you’ll also find the “Axis” option – select that and make sure that “same for all axis” is selected for Vertical axis minimum and maximum values:

Excel Spark Line Data Viz same min max value


That’s about it. Just a quick formatting option that makes your spark-lines much more effective!

Author: Paras Doshi

Cohort Analysis: What is it and why use it?


In this post, you’ll learn definition and benefits of Cohort Analysis. Let’s get started!

Cohort Analysis: What is it?

Cohort analysis is a data analysis technique used to compare similar groups over time.

Cohort Analysis: Why use it?

Here’s the basic idea: Businesses are dynamic and thus are continuously evolving. A customer who joined previous year might get a different experience compared to customer who joined this year. This is especially true if it’s a startup or tech company where the business models change (or Pivot!) often. You might miss crucial insights if you ignore the dynamic nature of businesses in your data analysis. To see if the business models are evolving in right direction, you need to to use cohort analysis to analyze similar groups over time – Let’s see an example to make it a little bit more clear for you.

You decide to analyze “Average Revenue per Customer” by Fiscal Year and came up with following report:

Simple Data Analysis Averages Hide Interesting Trends

It seems that your “Average revenue per customer” is dropping and you worry that your investors might freak out and you won’t secure new investments. That’s sad! But hold on – Let’s put some cohort analysis technique to use and look at the same data-set from a different angle.

In this case, you decide to create cohorts of customers based on their joining year and then plot “Average Revenue Per Customer” by their year from joining date. Same data-set but it might give you different view. See here:

Cohort Analysis Customer Revenue and Year Joined

It seems you’re doing a good job! your latest cohort is performing better than previous cohorts since it has a higher average revenue per customer. This is a great sign – and you don’t need to worry about your investors pulling out either and well, start preparations to attract new investors – all because of cohort analysis! :) WIN-WIN!


As you saw, cohort analysis is a very powerful technique which can help you uncover trends that you wouldn’t otherwise find by traditional data analysis techniques.

You might also like: Top 2 techniques to analyze data

Author: Paras Doshi

Top two key techniques to analyze data:


There are many techniques to analyze data. In this post, we’re going to talk about two techniques that are critical for good data analysis! They are called “Benchmarking” and “Segmentation” techniques – Let’s talk a bit more about them:

1. Benchmarking

It means that when you analyze your numbers, you compare it against some point of reference. This would help you quickly add context to your analysis and help you assess if the number if good or bad. This is super important! it adds meaning to you data!

Let’s look at an example. CEO wants to see Revenue numbers for 2014 and an analyst is tasked to create this report. If you were the analyst, which report would you think resonated more w/ the CEO? Left or Right?

Benchmarking data providing context in analysis

I hope the above example helped you understand the importance of providing context w/ your data.

Now, let’s briefly talk about where do you get the data for benchmark?

There are two main sources: 1) Internal & 2) External

The example that you saw above was using an Internal source as a benchmark.

An example of an external benchmark could be subscribing to Industry news/data so that you understand how your business is running compared to similar other businesses. If your business sees a huge spike in sales, you need to know if it’s just your business or if it’s an Industry wide phenomenon. For instance, in Q4 most e-commerce sites would see spike in their sales – they would be able to understand what’s driving it only if they analyze by looking at Industry data and realizing that it’s shopping season!

Now, let’s shift gears and talk about technique #2: Segmentation.

2. Segmentation

Segmentation means that you break your data into categories (a.k.a segments) for analysis. So why do want to do that? Looking at the data at aggregated level is certainly helpful and helps you figure out the direction for your analysis. The real magic & powerful insights are usually derived by analyzing the segments (or sub sets of data)

Let’s a look at an example.

Let’s say CEO of a company looks at profitability numbers. He sees $6.5M and it’s $1M greater than last years – so that’s great news, right? But does that mean everything is fine and there’s no scope of optimization? Well – that could only be found out if you segment your data. So he asks his analyst to look at the data for him. So analyst goes back and after some experimentation & interviews w/ business leaders, he find an interesting insight by segmenting data by customers & sales channel! He finds that even though the company is profitable – there is a huge opportunity to optimize profitability for customer segment #1 across all sales channel (especially channel #1 where there’s a $2M+ loss!) Here’s a visual:

segmentation data Improve profitability low margin service offerings customers

I hope that helps to show that segmentation is a very important technique in data analysis!


In this post, we saw segmentation & benchmark techniques that you can apply in your daily data analysis tasks!

Answering a question using data: Are marketers around the globe shifting their dollars to digital ads?

YoY growth - Digital Ad Spends vs Traditional Ad Spend

According to the data shared by emarketer, we can clearly see that the Traditional Ad market is reaching a saturation state in 5 major economies (US, China, UK, Japan, Germany) while the digital ad market will see steady growth in some economies & explosive growth in US & China…but the market size of traditional ads will still certainly remain much bigger in US while market size of digital ads in china will overtake the traditional ads in 2017.

So to answer the question: Marketers are not decreasing their existing budgets for traditional ad channels but the increased marketing budget dollars seems to be directed to digital ads market.

Very interesting data-set, I encourage you to play with it!

Thanks Avinash Kaushik for sharing this interesting tool.

I was playing with the data using Excel & Tableau, here’s a public workbook if you’re interested:!/vizhome/WorldWideAdSpend/Dashboard-DigitalAdSpendvsTraditionalAdSpend

YoY growth - Digital Ad Spends vs Traditional Ad Spend

Now, it’s your turn! What insights do you get from this data?

Dashboard – Asset management & planning for a global crisis response team:

Asset Management Global crisis response


Asset (Volunteers, Field offices & Equipments) management & planning for a global crisis response team.


Working in a team, we created statistical surveys for field works to collect data about current state & estimated future needs. We also helped them with data gathering & cleaning tasks. After that, we helped them analyze & visualize the data to find actions for executives leading the global crisis response team.

Here’s a mockup of one of the ten data visualization created for them:

Asset Management Global crisis response

News from PASS Summit’14 for Business Analytics Professionals: #sqlpass #summit14


This post is a quick summary for all Business Analytics related updates that I saw at PASS Summit’14:

1. Theme of the Keynote(s)/Session(s) seemed to be around educating the community about the benefits of the NEW(er) tools. I saw demos/material for cloud-based tools like SQL databases, Azure stream analytics, Azure DocumentDB, AzureHDInsight & Azure Machine learning. The core message was pretty clear: A data professional does two things – 1) Guards data OR 2) helps to generate Insights from Data – And they will need to keep up-to-date on the new tools to future-proof their career.

Read more about this here:

2. Coming soon: Power BI will be able to connect to on-premise SSAS data sources (multi-dim & tabular).

3. Coming soon: A better experience to create Power BI dashboards.

Read more about Power BI updates here:

4. Azure Machine Learning adds a free-tier! You won’t need a credit-card/subscription to sign up for this.

5. I also saw sessions proposing new way of thinking about an architecture for “Self Service BI” and “Big Data” which might be worth following because since these are newer tools, it’s definitely worth considering an architecture that’s designed to make the most of the investments in these new tools. That’s it & I’ll leave you with a quote from James Phillips from Day 1’s keynote:

Sales Bookings vs Quota Dashboard for a B2B company:

Sales Team bookings vs quota dashboard

Business Goal:

Need a daily report delivered in sales team’s inbox that shows Sales Team’s Bookings vs Quota for current & next month.

Brief Description:

Ability to see Bookings vs Quota in near real-time is a key to effectively manage performance for any sales team. Before the project, analyst(s) would have to manually put together this report and since the report took more than a day to put together they couldn’t afford to run it daily and so they delivered this report bi-weekly/monthly basis to the sales team. After the project, the process was automated and the sales team received an email with a report on a daily basis and this helped them see Bookings vs Quota in near real-time. As a famous saying goes “if you can’t measure it, you can’t improve it” (by Peter Drucker) – in this case, the report helped them measure their actual numbers against their goals and helping them improve their sales numbers which directly hits their top-line!

Tools used: SharePoint report subscription, SQL server analysis services, SQL Server Integration services, SQL server reporting services & Excel.


Note: Drill down reports are not shown and the numbers are made up.

Sales Team bookings vs quota dashboard

How does Internet of Things (#IoT) impact data professionals?


Internet enabled computers to be connected with each other.

Internet enabled Mobile Devices to be connected with each other.

Now, Internet will be used to enable physical things to be connected with each other. This is what is called “Internet of things” (IoT).

So what happens?

since more devices are connected with internet – we will able to generate more data! This is usually good if there’s a business vision around how to make sense of data to increase efficiency of all these things.

Here’s a nice case study from Microsoft (focus on the business case – the things in this case is “elevator” to drive reliability)


This is all good news for data professionals! There will be increased demand for professionals who can help businesses make sense of data generated via IoT.

Also beware of the “hype” around this technology. It’s important to take incremental steps to achieve the vision – Instead of trying to analyze data from ALL devices in your organization, start with one physical thing that matter the most for your organization or start with data that you have and take incremental steps to spread data culture in your organization!

Now that Big Data has become a mainstream word in IT and business, we have a new buzzword to learn/talk about IoT – but remember it’s all about making sense of data and your skills would be more valuable than ever!

Business Intelligene Dashboard for Quality Managers

Quality Test Results Dashboard

Business Goal:

Need to understand the patterns in Quality test results data across all plants.


– The solution involved creating a Business Intelligence system that gathered data from multiple plants. I was involved in mentoring IT team, development and end-user training of a Business Intelligence Dashboard that used SQL server analysis services as it’s data source.

– Dashboard development involved multiple checkpoint meetings with business leaders since this was the first time they had a chance to visualize quality test results data consolidated from multiple plants. Since they were new to data visualization, I used to prepare in advance and create 3-4 relevant visualization templates to kick off meetings.


(it is intended to look generic since I can’t discuss details. Also, drill down capabilities had been added to the dashboard to go down to the lowest granularity if needed)

Quality Test Results Dashboard