- Excel Book Excerpt
Excel Interactive Chart to Show Next Customer
This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Interactive Chart to Show Next Customer
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.
Figure 1046 Chart for one customer.
Figure 1047 Choose another customer from dropdown in A15.
Figure 1048 The chart updates for all customers.
During the creation of this chart, the chart looks bad, then really bad. It finally makes sense in the last step.
1. Select your range of customers and sales.
2. Insert a clustered column chart.
3. Excel will assume you want customers in the legend and quarters along the axis. Click the Switch Row/Column icon.
Figure 1049 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.
4. 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.
5. 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.
6. 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.
For more resources for Microsoft Excel: