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.

A recent post to the Microsoft Excel Charting Discussion Group asks "how can I create a pie chart that references a fixed data source and excludes all zero values?" Below is an example of how to do so . . .

Step 1 - Creating the Worksheet

First, this example assumes that the workbook is called "PNZ.xls". Assume your original data set is in the range B4:C13.

The purpose of the formulas in column D is to slightly change the numbers in column C to allow them to continue to sort high-to-low if there are duplicates. The formula below is in cell C4. It is copied down the column to cover the range D4:D13.

=IF(C4=0,0,C4-ROW()/10^10)

A new range containing formulas that exclude zero values is created in cells E4:F13.

The formula in cell F4 is below. It is copied down the range F4:F13.

=IF(LARGE($D$4:$D$13,ROWS($B$4:B4))=0," ",LARGE($D$4:$D$13,ROWS($B$4:B4)))

The formula in cell E4 utilizes the Index and Match functions to perform a left-sided lookup on the data values returned in column F. It is copied down the range E4:E13.

=IF(ISERROR(INDEX($B$4:$B$13,MATCH($F4,$D$4:$D$13,0)))," ",INDEX($B$4:$B$13,MATCH($F4,$D$4:$D$13,0)))

Step 2 - Building the Chart

The chart uses defined names based on the data and data label ranges to automatically update. Two range names need to be created. In this example the first named range is called "Data". The formula that makes up the "Data" range appears in the screenshot below:

The second named range is called "DataLabels". The formula that makes up the "DataLabels" range appears in the screenshot below:

The source of the pie chart is based on the Data and DataLabels defined names. The chart source data dialog box containing series references to the two defined names (Data and DataLabels) is shown below.

Finally, the completed pie chart appears below.

In the screenshot below, the value 425 has been added to cell C6. Note how the chart source (columns E and F) has automatically updated to include the new entry in row 7:

The pie chart has automatically updated to include the new value:

A recent post to the Microsoft Excel Charting Discussion Group asks "how can I sort charts on a worksheet by name?" The code below loops through all of the charts in the active sheet and loads their names into an array called "arrChartNames". The array is then sorted via the Array_Sort function. The sorted results are loaded into the Buffer variable and the charts are finally placed in alphabetical order via the For-Next loop.

Sub SortChartNames()

    Dim arrChartNames()
    Dim Cht As ChartObject
    Dim Buffer As Variant
    Dim Rng As Range

    X = 0

    For Each Cht In ActiveSheet.ChartObjects
        ReDim Preserve arrChartNames(X)
        arrChartNames(X) = Cht.Name
        X = X + 1
    Next Cht

    Buffer = Array_Sort(arrChartNames)

    Z = 2
    
    For Each X In Buffer
        ActiveSheet.Shapes(X).Top = Z
        ActiveSheet.Shapes(X).Left = 10
        Z = Z + 90
    Next X

End Sub

Private Function Array_Sort(ByVal arry As Variant) As Variant

    Dim i As Long
    Dim j As Long
    Dim k As Variant

    For i = LBound(arry) To UBound(arry)
        For j = i + 1 To UBound(arry)
            If arry(i) > arry(j) Then
                k = arry(j)
                arry(j) = arry(i)
                arry(i) = k
            End If
        Next
    Next
    Array_Sort = arry

End Function

From Microsoft, this Knowledge Base article provides a macro that allows the user to extract data from a chart.

In Microsoft Excel, you can retrieve data from a chart even when the data is in an external worksheet or workbook. This is useful in situations where the chart was created from, or linked to, another file that is unavailable or has been damaged in some way. When the source data to a chart is lost, the data can still be retrieved from the chart itself, by using a Microsoft Visual Basic for Applications macro.

First add a worksheet into your workbook and call it "ChartData". Next, add the following macro to a module within your workbook. Finally, run the macro to extract the chart data.

Sub GetChartValues()
   Dim NumberOfRows As Integer
   Dim X As Object
   Counter = 2

   ' Calculate the number of rows of data.
   NumberOfRows = UBound(ActiveChart.SeriesCollection(1).Values)

   Worksheets("ChartData").Cells(1, 1) = "X Values"

   ' Write x-axis values to worksheet.
   With Worksheets("ChartData")
      .Range(.Cells(2, 1), _
      .Cells(NumberOfRows + 1, 1)) = _
      Application.Transpose(ActiveChart.SeriesCollection(1).XValues)
   End With

   ' Loop through all series in the chart and write their values to
   ' the worksheet.
   For Each X In ActiveChart.SeriesCollection
      Worksheets("ChartData").Cells(1, Counter) = X.Name

      With Worksheets("ChartData")
         .Range(.Cells(2, Counter), _
         .Cells(NumberOfRows + 1, Counter)) = _
         Application.Transpose(X.Values)
      End With

      Counter = Counter + 1
   Next

End Sub

This example allows a fixed range for inputs, automatically sorts the results, and charts those results. The input area and chart source look like this:

The formulas in column D calculate a very small number that is subtracted from the original. The purpose is to prevent possible duplication of entries. Below is the formula in cell D4.

=C4-ROW()/10^10

The formulas in column F sort the data in column D from highest to lowest value. Below is the formula in cell F4.

=LARGE($D$4:$D$13,ROWS($B$4:B4))

Finally, the formulas in column E utilize the INDEX and MATCH worksheet functions to perfrom a left-sided look-up on the data in column F. Below is the formula in cell E4.

=INDEX($B$4:$B$13,MATCH($F4,$D$4:$D$13,0))

The chart below provides the end-result.

This next variation of the color-bars macro allows you to assign colors to the chart bars based on the background colors of selected cells. For example, assume the screenshot below:

The macro below loops through the department column and assigns a color to the bars based on that department.

Sub ColorBars()

    Application.ScreenUpdating = False

    Dim Rng As Range
    Dim Cnt As Integer
    Dim Color As Integer

    Cnt = 1

    For Each Rng In Range("B3:B10")
        Color = Rng.Interior.ColorIndex
        Set Pts = ActiveChart.SeriesCollection(1).Points(Cnt)
        Pts.Interior.ColorIndex = Color
        Cnt = Cnt + 1
    Next Rng

End Sub

The final product looks like the chart below.