There are times when the bar chart series values vary drastically and the smaller series values are not clear to the user. To facilitate user control over the
maximum limit, we can use an Input Text box near the chart. The chart will take the maximum limit from the user input provided during run time, else it will
take the maximum value from the calculated maximum value in the series.
Please see the steps that can be followed to achieve this.
- Place an Input text box on the top right corner of a bar chart and provide the properties as below.
- The destination would be linked to the cell J21 in the worksheet Dashboard_BL.
- The maximum and minimum limits of the bar chart would be linked to the spreadsheet.
- The mapping formulae would be as below. Minimum value =0 (As this is a bar chart)
- Maximum value rounded to a number higher than the maximum value
MAX(G18:G62) gives the maximum in the series and K18 is the length of the maximum value.
Logic:- If the user input is provided, then the maximum limit is the user input otherwise it is the maximum obtained as per the formula.
- The formula for the length
- The characters that should be allowed in the input box is as shown.
This way you get a bar chart with a User input box to the right that gives you the liberty to select the maximum value.