The formula for the Value column is: End pillars The SankeyStartPillar table is reasonably easy to understand. It just needs each row category from the source data listed with a “Blank” item in between. The columns calculate the amount of space required below the shaded Sankey line. Each calculation is constructed the same way. It takes the SUM() then takes away the value from the equivalent Start, Mid 1, Mid 2 or End columns.īelow End: End]] SankeyStartPillars table Therefore, we can set the value of all 4 columns to equal the Value column.īelow Start, Below Mid 1, Below Mid 2, Below End Value Start, Value Mid 1, Value Mid 2, Value End height of the shaded Sankey line never changes. This calculates the amount of space required above the Sankey line at the end point. This is set to equal the Above End column (see below)Ībove End =SUM()-SUMIFS(,]) This is sent to equal the Above Start column. The formulas in each column are as follows:Ībove Start calculates the amount of space required above the Sankey line at the start point. Our SankeyLines table needs to expand with the following calculations. To create the data for the 100% stacked area chart, we need to calculate some additional data points: The End Position column determines the order of the lines at the end of the Sankey diagram. Where 1 is finishes at the top, 2 is the second item from the top, etc. Value of the Blank named range (where it is a row used for spacing between categories).The formula in the Value column is: / formula retrieves either: Our initial SankeyLines table needs to contain the following base information: SankeyLines tableįrom the source data, we create a table with a row for each possible combination of rows and columns. This table is called SankeyLines table in the example file. Each row category is separated by an additional line which creates the blank spaces we see in the charts. To create the Sankey effect from our initial data, we need to create interim calculations. Within the template, we also have a named range called Blank. The value in here represents the spacing to be applied between each category. The value at the intersection of these is the flow rate. Our initial data is a two-dimensional table. The rows are the start point for our Sankey diagram, while the columns are the endpoint. Now that I’ve revealed how simple this secret is, let’s look at each stage in a bit more detail. Then you will be able to create your own Sankey diagram templates. The following image shows the 21 individual charts that could make up a Sankey diagram.īy setting the backgrounds to be fully transparent, and the shaded lines as partially transparent overlaying the charts on top of each other creates the Sankey effect. The pillars at each end are 100% stacked column charts, which are also overlaid on top. Here is the secret: the Sankey diagram you see in the example file is not one chart, far from it. There are actually 21 charts all stacked on top of each other. While I should wait to reveal the secret until the end of the post, I think it will help to explain how it all fits together if I reveal it upfront. The secret to create a Sankey diagram in Excel
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |