Most valuable professional
  • 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.

LE10001080.jpg 

Figure 1046 Chart for one customer.

LE10001081.jpg 

Figure 1047 Choose another customer from dropdown in A15.

LE10001082.jpg 

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.

LE10001083.jpg 

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.

<-Previous Topic                    Next Topic->

For more resources for Microsoft Excel: