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.
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.
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).
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.
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.
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.
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)
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.
Right-click on the chart area, and click Select Data.
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.
Next, click Edit in Horizontal (Category) Axis Labels section, and enter “=’Dynamic Charts.xlsx’!FullName” reference for Axis label range. Press OK.
Press OK again to save the chart.
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.
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.