Use Placeholder Values when Building Charts in Solver (and a couple other chart tips)
I always found it challenging to build charts in Solver because they are built in Design mode and the charts are empty:
I’d temporarily replace the Solver formulas with placeholder values, build the chart then put the Solver formulas back. This is a tedious process and takes care – especially if the formulas are complex or varied. Recently I began adding cells for place holder values. The cells where the chart is linked include an IF formula that refers to the placeholder values in design mode and actual values after the report runs. Even after completing a report, leave the placeholder values in the report to enable easy future maintenance. As an extra precaution, use placeholder values that are easily identified as false.
A couple of other chart tips:
Tip 1: Give your chart title meaning by linking it to a cell with a formula that clearly communicates results:
Result – Informative title!
Tip 2: As shown in the chart above, keep the chart clean by only charting key categories and group all others into a single category. I prefer this to a top X chart because it also provides context of the top X to the entire population.
Tip 3: A chart can be shown or hidden with an Excel grouping by setting Properties of the Chart Area to Move and size with cells and including the entire chart within the rows of the Excel group.
