Chart Adjustment = Data Adjustment
Did you know that with some MS Excel 2D charts, you can actually adjust the chart to look as you want it and Excel will then adjust the corresponding data to match?
This could be a good tool for the “what if” situations. You know, what data value do we need to make things “look right”?
If you’ve ever asked yourself this type of question, you might want to take a look at this simple trick.
Once you’ve made your 2D chart (it doesn’t seem to work with all of them, but it certainly does with a common bar or line graph), you need to select just the data element to change.
(Click once to select the entire data range, then click again on the individual data element to select it alone).
At this point, you can use the grab handles on the chart element to adjust its value. Some grab handles will adjust and some won’t. It’s a bit of a trial and error process, but you’ll catch on with just a quick try.
Once you’ve got the chart as it needs to be, simply release the grab handle. Instantly, Excel will adjust the correct data value to match your movement.
I’m sure some of you are wondering how all this works with formulas. As in, what data is adjusted when the data is dependent on several sources?
In these cases, when you release the grab handle, the Goal Seek window will open.
Here you can tell Excel which part of the data used in the formula should be adjusted to create your new value. Give the cell location of the data in the “By changing cell” field and click OK.
There you have it. It’s super quick and easy to make adjustments to your chart and let Excel find the correct data value to match. Yes!