Data Visualization

Making Histogram chart in excel

Histogram looks similar to a column charts, but rather than showing all data points individually, they group data points into ranges. They work well when we want to see the distribution of numbers in a range.

To explain the concept of Histogram, let’s consider a class of 30 students and their marks in a subject.

If we simply plot these marks using a column chart, it will make little sense as shown in the left side image below:

Stacked Column Charts

We already understand the clustered column charts in excel as we have used this option in all the previous column chart tutorials. This is the first option under column charts and is called so as all the columns for a particular category value are clustered.

Another requirement could be to have all the columns for a category label to be clubbed vertically (making a single column) rather than standing beside each other. This very option is called a stacked column chart.

This difference is shown in the image below:

Basic Formatting options to dress up your charts

Notice that in excel 2013, when you click on the chart, relevant formatting options are automatically shown in the right panel. In previous versions (& also in excel 2013), the same can be accessed through right clicking on the elements and selecting format options from the right click menu.

Chart formatting option

Understanding Column Charts

We have used column charts as example in all of the previous tutorials (to cover basics of charting),

Still for sake of completeness, let’s recap the steps required to insert a column chart.

As an example, let’s say we have the following average occupancy data for a hotel chain, and we want to represent this in a chart:

Column Chart data

Select the whole data & the headers (A2:F3) -> Go to Insert-> Column->Click on Clustered Column

Understanding Excel charts (Series data and Category axis labels)

Before we dig into different chart types in excel, it will be good to understand the way excel handles chart data. We’ll straight away dive into an example to explain this:

Suppose we have the following data (Gender-wise numbers of graduating students) and we want to represent this in a chart.

Excel Chart Basics and adding Axis Titles to excel chart

Following is an excel chart where we have marked all the components:

Data Series: The most important of all the elements, this represents the base data on the chart. We see 2 data series in the above graph: Toys (represented by data points 321, 305 & 345) and Office Supplies (165, 195 and 258).

Data Labels: This is the text for corresponding data points is shown on the graph

Making and understanding your first excel chart

Making a basic chart in excel is super easy. Suppose we have the following data of a retail store selling the following two product lines and their month-wise sales value in dollar terms.

We explain the simplest way of inserting the chart here, you will get more and more confident with charting as we move forward and may discover your own style of working with charts.

Introduction to charts & their usefulness

Excel is meant to handle a lot of data and it is extremely good at it. But decision makers need information (that come from such data) and this usually mean summarizing the data in visual forms (such as charts/graphs) so that the basic underlying story behind all this data stands out. The good thing is that Excel is reasonably good at visually presenting the data in the form of various charts (graphs).

Let’s understand this through an example:

Here is a 12 months data on 4 product groups that a company sells. If you see the table alone, you’ll find that it is difficult to make sense of this in a quick time.

lot of data

Some of the questions that are very basic but not straightforward to answer are: Which product group is showing most resilient growth? Which one is suffering? Which one could be the next big thing? Any month where all products suffered?

Answering this through the data table is not so easy.

These are the situations where visualization are very useful.

Color Scales

Excel provides extremely powerful & immensely useful data analysis tools under the Conditional Formatting tools. One of the best thing about these tools is that they are easy to comprehend and are at best 2-click away from being applied to your data.

We have already seen Data-Bars in a previous tutorial.

Color Scales reveal the relative position of each cell in a range (Colors are used to convey if the number is big or small with respect to all the numbers in the selected range)

Data Bars

Data bars provide a quick way of visualizing the data at hand. In essence it provides an in-cell bar graph to represent the data of that cell. Length of the bar depends on the number contained in the cell and is relative to other cell values for which this visualization is selected.

We take you through a quick overview of this functionality through an example.