Here's an example data / chart spreadsheet:
http://temp.ancientpc.net/po/piechart.xls

Ideally what I want to do is force the pie chart to use the most recent /
lowest data series through some way of calculating the lowest non-empty cell
location, but I don't know if that's possible.

If not, I just want to be able to choose which data series to plot. Even
though at the moment there's 8 series available under Chart - Source Data,
it will only plot series 1 in the pie chart.

Hi,

A pie chart will normally only display a single data series. It can be
manipulated to display 2.

For your particular problem you need to use a helper column that
contains one of the 8 data series. Through use of worksheet functions
and a control to allow easy use selection.
Check Jon's example and modify to suit you pie chart and data.
http://peltiertech.com/Excel/Charts/ChartByControl.html

Cheers
Andy

AncientPC wrote:
Here's an example data / chart spreadsheet:
http://temp.ancientpc.net/po/piechart.xls

Ideally what I want to do is force the pie chart to use the most recent /
lowest data series through some way of calculating the lowest non-empty cell
location, but I don't know if that's possible.

If not, I just want to be able to choose which data series to plot. Even
though at the moment there's 8 series available under Chart - Source Data,
it will only plot series 1 in the pie chart.

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

To display multiple series in a pie chart, you can follow these steps:

1. Select the data range that you want to include in the chart, including the series names and values.
2. Click on the "Insert" tab in the ribbon.
3. Click on the "Pie" chart icon and select the type of pie chart you want to create.
4. The pie chart will be created with the first series displayed. To add more series, right-click on the chart and select "Select Data".
5. In the "Select Data Source" dialog box, click on the "Add" button to add a new series.
6. In the "Edit Series" dialog box, enter the series name and select the data range for the series values.
7. Repeat steps 5-6 for each additional series you want to add.
8. Click "OK" to close the "Select Data Source" dialog box.
9. Your pie chart should now display all the selected series.

To force the pie chart to use the most recent/lowest data series, you can use a formula to determine the location of the lowest non-empty cell. Here's an example formula you can use:

This formula will return the address of the last non-empty cell in column B. You can then use this address to define the data range for your chart.

[list=1][*]Select the data range that you want to include in the pie chart, including the series labels.[*]Click on the "Insert" tab in the ribbon.[*]Click on the "Pie" chart icon in the "Charts" group.[*]Select the type of pie chart that you want to use. For example, you can choose a 2-D pie chart or a 3-D pie chart.[*]The pie chart will be created with the first series from your data range. To add additional series, right-click on the chart and select "Select Data" from the menu.[*]In the "Select Data Source" dialog box, click on the "Add" button to add a new series.[*]In the "Edit Series" dialog box, enter the series name and select the data range for the series values and series name.[*]Repeat steps 6 and 7 for each additional series that you want to add to the pie chart.[*]Click "OK" to close the "Select Data Source" dialog box.[*]Your pie chart should now display all of the selected series.

Regarding your question about forcing the pie chart to use the most recent/lowest data series, unfortunately, there is no built-in way to do this in Excel. However, you can manually select the data series that you want to include in the chart by following the steps above.