Excel can be your database, it can be your calculator, and it can even be your time card. This handy software is also the first choice of many business professionals when it comes to data visualization. Dynamic charts in Excel allow for creating dashboards that automatically update when new fields are added into your tables.

There are 2 ways to create dynamic charts in Excel. The first is by creating tables, and the second is by creating formulas and named ranges. Let’s start with the table method. You can download the sample workbook here.

 

By Table

This approach is relatively easier than the second one and also provides some additional filtering capabilities that come with the tables layout of Excel.

Excel table feature (previously known as an Excel list) was introduced in Excel 2007. Tables in Excel make managing, querying, and referencing data much easier. The table will also auto-resize as you add more data. These features make tables very easy to work to create dynamic charts.

Start by selecting your table. You can either select the entire area or a single cell within that area, as Excel can detect the extent of your table automatically. Then go to Insert, and select Table to transform this range into an Excel style table. The selected range will be displayed on the Create Table window. If your table doesn’t have headers, be sure uncheck My Table Has Headers checkbox. Otherwise, press OK to create the table.

dynamictable

Now that the table is ready, we can start creating our dynamic charts. Select the specific columns you want to be graphed using the CTRL key.

dyn2

Then go to the Insert ribbon on the top menu. Under the Charts section, choose from the chart types and select the specific chart from the dropdown menus. Here, we used a 2-D Column Chart (the first option under 2-D Column).

dyn3

The dynamic chart will automatically update when new data is added to the table. Here, we added a new employee (HAILEY BATES) to the end, and the chart updated to reflect this change.

dyn4

 

By Formula and Named Range

Although not as straightforward as the table method, formula and named range approach offers more flexibility, and is also supported by most Excel versions, including those before 2007.

We’re going to need to add a named range for each label (column) to be used in the chart. Furthermore, each named range will need to be tied to an OFFSET function. Let’s start with how function works. When you start typing “=OFFSET(“, you will see the parameters that need to go into the formula.

dyn5

Here is an example for its use,

=OFFSET(ByFormula!$A$2,0,0,COUNTA(ByFormula!$A:$A)-1)

The first parameter in the formula is reference, which base the offset. Typically, this number should be “2”, because the first row often contains the headers for each column.

Following 2 parameters are rows and cols. These must be set to “0” so that the range of results will start from our base cell.

Finally; last argument represents height of the results range. The COUNTA function searches the entire row, and returns number of cells with values in them. We accommodate the header cell (the first row) by subtracting “1” to get the correct count.

Please note that data must be entered in consecutive rows. If you skip a row, COUNTA function will return the wrong number of cells, and this will result in a mismatch in dynamic row numbers.

To identify cells as a named range, click an empty cell, and go to the Formula ribbon. Click Define Name. Enter a Name for your cell and copy the formula,

=OFFSET(ByFormula!$A$2,0,0,COUNTA(ByFormula!$A:$A)-1)

Into the Refers to section. Once you’re done, press OK.

dynamicform

Do the same for the Base Salary column, entering a unique name. Repeat this step until you cover all fields.
=OFFSET(ByFormula!$G$2,0,0,COUNTA(ByFormula!$G:$G)-1)

dyn7

Once you’re done with the named ranges, we can now insert a chart. Here, we’re using a column chart. Insert an empty chart by going to Insert ribbon, and select a chart from the Chart menu, when an empty cell is selected.

dyn8

Right-click on the chart area, and click Select Data.

dyn9

In the Select Data Source window, click Add in the Legend Entries (Series) section. Select G1 cell as name, and enter “=’Dynamic Charts.xlsx’!BaseSalary” reference for value. Press OK.

dyn10

Next, click Edit in Horizontal (Category) Axis Labels section, and enter “=’Dynamic Charts.xlsx’!FullName” reference for Axis label range. Press OK.

dyn11

Press OK again to save the chart.

dyn12

Add a new row of data to check the chart is updating automatically. Here, we added a new employee (HAILEY BATES) and Excel added one more column to our chart to reflect the changes.

dyn13

All data applications, databases, or data collection tools need to be updated sooner or later, because data keeps growing. The importance of analyzing it is known to all business professionals and Excel is the one tool that can handle all such tasks. Thanks to Excel’s dynamic charts, you can ‘build-and-forget’ a dashboard, because it’s going to keep updating automatically, and save you time.