terra incognita A broad view of Microsoft Office

Excel Add-In for Manipulating Points on Charts (MPOC)

This post is syndicated from Microsoft Excel.

Overview

In Excel 2007, the ability to directly resize or reposition points on the chart was deprecated. This feature was sometimes referred to as "Graphical Goal Seek." For example, in Excel 2003 a user could click on a data point in a column chart twice which would surface handles that could be used to resize the columns. Over the last couple of years we have received a lot of feedback from customers indicating that this was a valuable feature for some scenarios. However, we were not able to react in time to roll this feature back into Excel 2010 but we are evaluating how to bring this back as a native feature in a future release. In an effort to restore this lost functionality, we have developed a sample Add-In that can be used in both Excel 2007 and Excel 2010.

In this blog post, I will provide the Sample Add-In for download and illustrate how to use this Add-In for manipulating points on your chart.

The sample add-in with RibbonX and VBA code is here.

Using the MPOC Add-In

Before trying out the following scenario, please install the Add-In as described in the provided documentation (attached at the bottom of this post).

In this section we will run through a fictional scenario about how you can use the Add-In to manipulate points on your chart. Tailspin Toys is in the process of developing a new toy airplane and are conducting various experiments before they settle on the final design. We will be looking at one such experiment where the company is studying the impact of different materials and wingspans on the maximum altitude that can be attained by this toy air plane.

The experiment has been largely successful and it has been observed that for all materials there is a general trend:

• Initially as the wingspan increases, the altitude begins to increase.
• However, larger wingspan results in a heavier plane. Hence, as we keep increasing the wingspan the heavier plane fails to reach the previous attained heights resulting in the benefits tapering off.

The scientists are elated at their find but these are some erroneous values in the data (show with red circles on the chart). The field scientist tells us that the erroneous readings were caused by some loose wiring to the altimeter used in the experiment. Since the trends are pretty obvious, the research department wants to clean up the charts before presenting their findings to the management.

In the next steps, we will see how MPOC Add-In can be used for this purpose:

IMPORTANT: Please back-up your file before using the MPOC Add-In. The undo functionality in Excel will NOT work since this Add-In is written entirely using VBA. Also, this is a sample Add-In, so please exercise caution before using it on important data.

1. We will start by selecting the point on the chart that we want to manipulate
    
2. A “Manipulate Points“ button will now appear whenever a chart object is selected under the “Chart Tools -> Layout” Tab of the Ribbon.
    
3. Click this Button to Launch the MPOC Dialog. The dialog will be automatically populated with information about the selected point.

   In this case, we have selected the 8th point in the “Aluminum” series. Since this is an X-Y scatter chart, we can change both the X and Y value associated with this point. Next, we will see how we can change the Y- value associated with this point to smoothen our curve.
   

   NOTE: Value controls are used to directly manipulate the value of the cell corresponding to the selected point on the chart. We dynamically add additional value controls depending on the type of the chart. For example: column or bar charts have one value controls, x-y scatter charts have two value controls and bubble charts have three value controls.

4. In this step, we will decrease the Y-Value associated with the point to smoothen the curve (as we have determined that the altitude is too high due to an error).

   NOTE: The MPOC Add-In provides 3 different ways to change the value of the point with varying level of precision:
   Scroll Bar – When you are far away from the value you want, use scroll bars quickly change values.
   Spin Button – When you are close to the value you want, use spin button for greater precision to zone in on the value you want.
   Text Box – if you know the value you want, why waste time zoning in on the value. Use the textbox to directly input it in the text box

   1. Drag the scroll bar associated with the Value Y to the left to quickly preview where we want the point on the chart. We let go when we are in the vicinity of where we want to be.
       
   2. Next, we will use the spin button to refine the position of the point (or else if we know the precise value, simply type it in the text box).
      
5. Next, we will change the next erroneous value. Since we have a modeless dialog, all we have to do is simply click to select the point on the chart (NO need to close the dialog). The dialog will automatically update with information about the selected point.
   

   MPOC provides a truly modeless experience - you can even click on a different chart or a different worksheet and the dialog will automatically update itself.

   
   Repeat step 4 to change the value associated with this point.
6. Next, let us explore alternate ways of selecting a different point on the chart by using the Series dropdown and Point dropdown on the dialog (or you may choose to select your point directly on the chart by using a mouse pointer as described in step 5). This method is quite useful when there are many points or series on the chart and selecting points can be difficult. Also, this opens up the possibility of manipulating points of charts where point selection is not supported – such as area charts.
   1. With just the chart selected (no points or series selected), launch the MPOC dialog as described in step 2 (if it is not up already).
   2. The MPOC dialog is automatically populated with the series information for the selected chart.
      
   3. Choose the series in the Series dropdown whose point you want to manipulate. Let us choose “Aluminum”. The entire series will be automatically selected on the chart and the point information will be populated in the Points dropdown.
      
   4. Next, let us choose the point that we want manipulate using the Points dropdown. We happen to know that we want to select the 28th point in the series. The point is automatically selected on the chart and the information about value is populated in the dialog.
      
   5. The next step is to change the value to smoothen the curve. We do this the same way as shown in step 4.
      

Thus using the MPOC Add-In, we can easily manipulate points of the chart to smoothen out the erroneous values.

MPOC Add-In Documentation

The section above covers the basics of using the Add-In. There are many more features that are beyond the scope of this blog article but these are covered in great detail in the provided documentation (attached at the bottom of this post). Please read the documentation before using this Add-In. Some of the topics covered in the documentation are:

1. Installation Instructions
2. Formula Support – The MPOC Add-In will not overwrite formulae in cells. We will instead use the Goal Seek feature in Excel to manipulate points that are based on formulas.
3. Dialog Forms – The MPOC Add-In supports two forms of dialogs controlled by registry keys
   1. Compact Form
       
   2. Expanded Form
      
4. Known Limitations – Unsupported scenarios include Cross Workbook charts and Pivot Charts.

Conclusion

The Manipulate Points on Charts (MPOC) Add-In is a sample solution that will help you manipulate points on charts. This Add-In can be used to visually smooth curves in Excel. If this Add-In is useful to you or if there are any chart interactivity features you would like to see, we would like to hear from you.