Data Visualization using Microsoft Excel

In the previous Blog, we talked about The Importance of Data Visualization for your Business and How your Business is profitable from it.

As you must have read that tools like Tableau, QlikView, Raw are some of the tools used in Data Visualization. But, Microsoft Excel is also not that far behind.

Excel has an excellent range of tools that are used to analyze business data and present them visually using Graphs, Charts and Plots.

Here are some commonly used Data Visualizations in Microsoft Excel:

  1. Charts

  2. Spark lines

  3. Conditional Formatting

  4. Pivot Charts & Pivot Tables

In this article, we ’ll explain about each of the above data visualization techniques and the appropriate situations to use them.

Charts in Microsoft Excel:

A Chart is a visual representation of data in rows and columns. Charts are great additions to your dashboard. They offer the simple understanding of data.

Normally they are used to analyse trends and patterns in data. For Example Analyse sales trends to find out your most and least successful products. Charts are also used to compare Targets, Analyse Competitors, Analyse Performance over Years and more.

Excel provides you with a plethora of charts that you can choose to suit your business needs.

Column Chart - In this type of Chart the categories are displayed along the Horizontal axis and values along the vertical axis.

A Column chart is used to compare several items for a specific value. For Example, Revenues generated by different regions.

Bar Chart - In bar Chart the categories are organized along the vertical axis and values along the horizontal axis

These types of charts are used to compare sales performance of several persons for a single time period.

Pie Chart - Pie Chart shows the size of one data, proportional to the sum of items. The data points in a Pie Chart are shown as a percentage of the whole pie.

A pie chart is favorable when plotting distribution or proportion of a total value. For example the cost distribution of Marketing, Operations, Consumer Relations etc.

Doughnut Chart - It is similar to a Pie chart. But a Doughnut chart can contain more than one data series and are represented as rings.

Doughnut Charts can be used when you are plotting multiple distribution or proportion of a total value. For Example Source of Cost and Revenue as a percentage of total cost and revenue.

Line Chart - These can show continuous data over time on an evenly scaled axis.

A line chart is ideal for showing trends in data over months, quarters or years. So, if you need a chart that changes over time consider a line chart.

Area Chart - Area Charts are used to plot the change over time and draw attention to the total value of a trend.

By showing the sum of the plotted values, an area chart also shows the relationship of parts to a whole. For example the degree of change in stock price over the months.

Scatter Plot - Scatter Plots are usually used for showing and comparing numeric values. IT combines x and y values into a single data point and displays them in irregular clusters.

A Scatter plot is used to determine the relationship between variables like Relationship between discounts offered and consumer purchase basket.

Bubble Chart - A Bubble chart is like a scatter chart with an additional third column to specify the size of the bubble that shows the data points in the series.

For Example, you can plot Competency score vs performance score of employees and the size of the bubble can represent the years of service.

Stock Chart - Stock charts can show fluctuations in stock prices.

However, a Stock chart can also be used to show fluctuations in other data, such as daily rainfall or annual temperatures.

Surface Chart - A Surface chart is useful when you want to find the optimum combinations of two sets of data.

Surface Charts are used in a topographic map, colors and patterns indicate areas that are in the same range of values.

Radar Chart - Radar charts compare the aggregate values of data series. To create a Radar chart, arrange the data in columns or rows on the worksheet.

For Example, you can use a radar chart to show the rate of sales per product. In this chart the farther the point is from the center the greater their value is.

Combo Chart - Combo charts combine two or more chart types to make the data easy to understand, especially when the data is widely varied.

It is shown with a secondary axis and is even easier to read. For Example A chart to show the difference between targeted value and actual value of sales achieved over the year.

Sparklines in Microsoft Excel:

Sparklines are tiny charts that are plotted in single cells and each representing the data in the corresponding row.

Sparklines are used to quickly analyse the trends in data. For Example to see if the company the revenue of your company is increasing YOY ( Year on Year) or decreasing. Similar to the costs.

Conditional Formatting in Excel:

Conditional Formatting as the name suggests formats the range of cells on the basis of a condition. We can highlight the top performing salesperson in your Sales data, you can format/highlight duplicate values in your data.

To add conditional formatting to your data/reports, you need to first select the range where you would be applying Conditional formatting. Then go to the Home tab Conditional Formatting and apply the rule you wish to apply as per your data.

There are many predefined rules like, greater than, less than, duplicate values, top 10, bottom 10 etc. but you can create your own conditions also by going into a New rule and then select Use a formula to determine which cells to format.

Conditional Formatting is another powerful feature of Excel that can highlight data and can be used for data visualization.

Conditional formatting combined with Data Bars, Color Scales and Icon Sets is capable of building some of the complex Dashboards.

Conditional Formatting can be used to find the profitable and non-profitable products, Top 10 salesperson of the month, Salespersons who have achieved the target or not and more.

Conditional formatting uses several formatting options to explain the visualizations like

Data Bars:

Data bars are used to compare the values in adjacent cells. A longer bar represents a higher value and vice versa.

You can choose different colors and gradient options to play with. You can also format the data to show positive and negative

It can be used to compare numerical variables in between themselves like sales, Revenue, Cost etc

Color Scales:

Color Scales are used for the similar purpose as that of Data Bars. But in this case, the cells are highlighted.

In this case, instead of bars color gradient is applied to display the difference in values.

Icon Sets:

Icon sets are used to visualize difference or to group values into an icon. In this case, instead of colors or bars, Icons are displayed beside the numbers.

These icons follow the rules that are defined by you.

Pivot Charts and Pivot Tables in Excel:

Another Important and most powerful Data Visualization tool is Pivot Tables and Pivot Charts.

They provide tremendous flexibility when dealing with a large data. With the features like rows, columns, filters and slicers you can create advanced dashboards.

Pivot Charts and Normal charts (As explainedbove) are the same but with a major difference that Pivot Charts is interactive and gives you filtering controls. In short, It’s a Dynamic Chart.

Pivot Charts are useful when you have a large amount of data and need to make sense of it.

A pivot chart doesn’t need a Pivot Table you can also make your standalone pivot chart.

Excel is the most commonly used Data Analytics tool in the market. And the It’s true potential is still not explored by many companies. In experienced hands, Microsoft Excel is a tool that can do complex Data Analysis and create attractive dashboards.

  • Linkedin
  • Facebook
  • excel