Posts tagged Excel Hints & Tips

On of the best techniques to reference data in excel is to use named ranges. This can be extended if you want dynamic named ranges which adjust with new data.

Normally we hard code the source range for a chart or pivottable, especially if the full range is fixed for the year. However, if the final range is not fixed, then we have to manually update the range for the charts or pivottables every time the new data is added to the database or excel list. To update a pivottable manually, you need to select the table and then pivottable wizard, select back and then reselect the range. The source range also needs to be open.

You can use VBA code that opens up the source and updates the relevant pivottables automatically instead of having to do the above. If all the updating was left to the end, you could easily call all macros and update with one click!

However, in excel version 2003. A new data List feature provide dynamic lists. Another benefit is that there is no need to load the source file first as when using the OFFSET formula. The list range is usually given a named range. If you are using an older version then use the offset formula outlined below.

Once the formula or list range has been completed, go into the relevant pivottable and add the name of the dynamic range after the file name e.g. yourworkbook.xls’!yournamedrange

This means that when new data is added to the database, the named ranged should automatically expand to include the new data. When you refresh the pivot tables manually or by using the code, the pivot tables should now show the new data. This is a great way to start the development of an excel dashboard. Dynamic pivottables are a useful data model.

Go to: Insert>Name>Define and in the Names in workbook box type any one word name (I will use MyRange) the only part that will change is the formula we place in the Refers to box depending on if you are using offset or are using the list feature.

For the offset formula only use this:Expand Down as Many Rows as There are Numeric Entries.In the Refers to box type: =OFFSET($A$1,0,0,COUNT($A:$A),1)

If it is the data list you are using, then Go to: Insert>Name>Define and in the Names in workbook box type any one word name. Use this formula in the refers to box. Adjust the sheet name and range accordingly: =’yourworksheet’!$A$5:$G$339

Gary is an internet writer and expert in excel. View our latest excel dashboard guide at http://www.stewarttraining.co.uk Click here to view a sample excel dashboards