Dynamic Ranges & Charts using Excel Tables

We frequently deal with data that is supposed to grow with time (product catalog, sales records etc), how do we write auto correcting formulas for such data? One very good solution lies in using excel tables to do this.

Suppose a stand-alone store owner decides to keep a record of all the items sold for the day in excel. The data looks like following:

Basic data that is likely to expand as we go

Excel Tables: Manage your data like a pro

Excel is all about tables (the two dimensional sheets), and we deal with tabular form data all the time. Due to this, a specific table format was introduced from excel 2007 onwards. Excel table feature help us in several ways, but the crucial ones are:

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.

Basic formula writing in Excel

In your daily routines, you may come across situations where you need to perform certain basic calculations on your numeric data like Adding, Averaging or Counting and so on.

To perform these basic operations, Excel provides in-built functions that can be used to speed up your calculations.

Quick easy steps to get familiarise with basic functions are explained below-

Writing SUM Function

As name suggests this function is used to ADD up any list of numbers in excel sheets.

Formula writing using Excel Function

Lets understand how to write a complete formula in Excel. Let’s start with one of the basic formula of adding numbers in Excel by using simple Sum function. Formula writing is a simple 3 step process as explained below-

  1. Selecting Cell and function to be used in Formula

As explained above, whenever we type an equal to (=) sign Excel understands this as a formula.

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