Recently a project at work required that I do the following:

• Create two charts - one showing the top "X" percent of total revenue and the other showing the remaining "Y" percent of revenue. "X" and "Y" needed to be variable depending on the variability of the source data as well as how the user decided to present the data.
• In addition to the first chart showing the top "X" percent of revenue, it had to contain a bar at the end that showed the total "Y" percent of revenue that was to be detailed on the second chart.
• The data in both charts had to be sorted high-to-low with the exception "Y" bar of the first chart that needed to appear at the end.
• The data had to be set up in an static input format so that someone with little knowledge of Excel could update the charts.

Before explaining how to build, the final charts looked similar to these:

Chart 1

Chart 2

The first step in building the worksheet was to create a static range for data input. This range is reflected in cells C6:C25. This static data input range allows someone with very little spreadsheet knowledge could walk down each of the departments, which are in alphabetical order, and enter the data for that department.

The second step in building the worksheet was to create three helper columns. The first two helper columns covering the range D6:E25 contained formulas that sorted the data in the input range C6:C25. The screenshot below shows the worksheet.

The formula in cell E6 is below. This formula sorts the data in the input range C6:C25 from high-to-low. It is copied through the range E6:E25.

=LARGE($C$6:$C$25,ROWS($C$6:C6))

The formula in cell D6 is below. This formula looks up the department values based on the sorted data in the range E6:E25. It is copied through the range D6:D25.

=INDEX($B$6:$B$25,MATCH($E6,$C$6:$C$25,0))

The reminder of the revenue that makes up the last bar in the first chart is reflected in cell E27. The formula in that cell is:

=SUMPRODUCT(($F$6:$F$25=2)*($E$6:$E$25))

The third helper column covers the range F6:F25. The purpose of this range is to assign the data to charts one or two depending on where each department falls as a percentage of total revenue. The formula in cell F6 is below. It is copied through the range F6:F25. It works by building a cummulative sum of the data and comparing that sum to the total sum of the data. If the percent of revenue is less than the value in cell D2, the revenue is assigned to chart one. If it is more than the value in cell D2, it is assigned to the second chart.

=IF(SUM($E$6:E6)/SUM($E$6:$E$25)<$D$2,1,2)

Once the worksheet was built, the key to building the charts is utilizing a series of dynamic range names as the chart source. Because the first chart had to reflect the top "X" percent of revenue and a summary bar showing the remaining revenue, the chart had to be based on two non-contiguous data sources.

To create the first chart four named ranges needed to be created. The first named range, called "Cht1Data", makes up the first non-contiguous range. It is a dynamic formula (the range changes depending on what is entered into cell D2, and consists of the formula:

=OFFSET(Sheet1!$E$6,0,0,COUNTIF(Sheet1!$F$6:$F$25,1),1)

The formula appears in the named range dialog box as:

The second named range, called "Cht1Remainder", makes up the second non-contiguous range. It picks up the remainder of the revenue shown in cell E27. It consists of the formula:

=Sheet1!$E$27

The formula appears in the named range dialog box as:

Both of these named ranges will need to have corresponding X-Axis values assigned. To do so, two more named ranges are created. The first named range assigns the X-Axis values to the "Cht1Data" range. It is called "Cht1XAxis" and consists of the formula:

=OFFSET(Sheet1!$E$6,0,-1,COUNTIF(Sheet1!$F$6:$F$25,1),1)

The formula appears in the named range dialog box as:

The second named range assigns the X-Axis values to the "Cht1Remainder" range. It is called "Cht1XRemainder" and consists of the formula:

=Sheet1!$B$27

The formula appears in the named range dialog box as:

Now that the range names are created for the first chart, it's time to create the chart. To do so, the range names need to be added to the series and X-Axis in a non-contiguous format. Below are the source values for the first chart.

Assuming the Excel file is called "NCR.xls", the source for the first series is:

=(NCR.xls!Cht1Data,NCR.xls!Cht1Remainder)

Note that a comma separates the two range names and that the source is surrounded by parenthesis.

The source for the X-Axis is:

=(NCR.xls!Cht1XAxis,NCR.xls!Cht1XRemainder)

The process of building the second chart is exactly the same as the first chart - just a little easier. This chart only requires two named ranges. The first named range, called "Cht2Data", consists of the formula (enter the formula into the named range dialog box as shown in the first examples above):

=OFFSET(Sheet1!$E$6,COUNTIF(Sheet1!$F$6:$F$25,1),0,COUNTIF(Sheet1!$F$6:$F$25,2),1)

The second named range, called "Cht2XAxis", consists of the formula (enter the formula into the named range dialog box as shown in the first examples above):

=OFFSET(Sheet1!$E$6,COUNTIF(Sheet1!$F$6:$F$25,1),-1,COUNTIF(Sheet1!$F$6:$F$25,2),1)

Below are the source values for the second chart.