Problem: We have a monthly meeting to review accounts. They want one chart per customer that we can review at the meeting.
Strategy: Build one chart showing all customers, but then hide all customers but one using a filter. By default, when you hide rows in the worksheet, they get hidden in the chart.
- Chart for one customer.
- Choose another customer from dropdown in A15.
During the creation of this chart, the chart looks bad, then really bad. It finally makes sense in the last step.
- Select your range of customers and sales.
- Insert a clustered column chart.
- Excel will assume you want customers in the legend and quarters along the axis. Click the Switch Row/Column icon.
- Use Switch Row/Column to move customers to the legend.
At this point, your chart will be unreadable. You are one step away from having a good chart.
- If you've added Filter by Selection to the Quick Access Toolbar, you can select one customer in the worksheet and click Filter by Selection. Otherwise, select a customer, use Data, Filter, then open the customer dropdown and choose one customer.
- Because the chart now has one visible series, you get the name of that series in both the title and the legend. Click the legend and press the Delete key to remove the redundant information.
- You can now choose a customer from the dropdown and the chart will update to show the new customer.
Again, this trick takes advantage of the default behavior that the point in the chart will be hidden when the row or column in the worksheet is hidden.