#NA Trick and Flexible x-Scale

The file below demonstrates how to make flexible graphs using the NA() method. This is a very simple way to make the x-scale flexible when you have a time series in the x-scale. To apply this method you substitute the #N/A for the date on the x-scale and you make sure that the x-scale is classified as a date axis. This can be created with a template that is saved in the file and is very good for presenting various statistical analyses. The file demonstrates how to gather data for the graphs with the INDEX, MATCH and INDIRECT function along with using TRUE/FALSE switches to limit the graph to selected items. It also includes use of the OFFSET function in order to change the number of items that appear on the x-axis. The bubble graphs are created using the macros that are included in the bubble file example above and in a video.



Dynamic graphs with #NA.xlsm

Spinner Box to Change the Scale on Graphs

The simple video below shows how to put a spinner box on a graph to allow you change the scale on a graph. All you do is to start recording a macro and then go to the graph and change the scale. After you record the macro you can enter the minimum scale somewhere in the file and name the range. Then just go back to your macro and replace the number you entered for the minimum amount with the range name using RANGE(“min”). Of course, you need to name the range.