Creating a Sankey Chart in Excel is an excellent way to visualize data flow and energy transfer flows efficiently. These charts are unique because they display the flow between nodes using a two-dimensional flow map. Here’s a step-by-step guide to help you create a Sankey Chart in Excel:
**1. Organize Your Data**
Before diving into creating a Sankey Chart, you need to organize your data into a table. The table should have the following structure:
– **Leftmost Column (Node A)**: This should be the origin of the flow.
– **Last Column (Node B)**: This should be the destination of the flow.
– **All Other Columns**: Represent the intermediate steps or processes in the flow.
All numerical data in your table should reflect the magnitude of the flow between nodes.
**2. Open Excel and Input Your Data**
– Launch Microsoft Excel and input your data into a worksheet.
– Be sure to label your columns clearly to indicate the direction and nature of the flow.
– Save this worksheet for future reference.
**3. Prepare the Data for sankey creation**
– You may need to reformat your data, especially when dealing with hierarchical flows. For example, if you have processes nested within other processes, you might want to collapse or expand them to create a more manageable dataset.
– Ensure that the value ranges (amounts or percentages of flow) are correct for each segment in your chart. This information is crucial to accurately represent the flows.
**4. Insert a Chart**
– Click on the cell where you want the Sankey Chart to appear.
– Go to the “Insert” tab on the ribbon.
– Look for the “Chart” group, and click on it.
– In the dropdown menu, choose “Sankey” to insert a new Sankey Chart.
**5. Configure the Sankey Chart**
– Once the chart design appears, click on the “Chart Elements” button to open the “Chart Tools” contextual tab.
– In the “Chart Elements” panel, select the options for legend, title, and gridlines depending on what you want to show in your chart.
– You will see icons for the chart elements or a dropdown menu; click or select the dropdown menu, then choose the “Design” tab for more customization options.
**6. Modify the Data”
Now you need to link your Sankey Chart to the organized data created in your worksheet:
– Click on the Sankey Chart and go to the “Chart Tools” contextual tab.
– Click on “Design” in the top menu bar.
– Choose “Select Data” from the “Data” group on the ribbon.
– Select the range that includes your flow data.
– This link should be established for each segment of the Sankey Chart (i.e., between flows).
– Save and close the “Edit Data” dialog box.
**7. Format the Sankey Chart**
Excel provides various settings to format and customize the Sankey Chart:
– Double-click on a segment, and you can customize the color, label, and other formatting options by selecting the “Format Data Series” option from the “Format” contextual tab.
– Adjust the thickness and direction of the Sankey arrows to represent different values.
– Click on the “Chart Title” within the chart, click on “Format” to customize its properties, such as font size, color, and placement.
**8. Save and Finalize**
– Save your worksheet after making the necessary edits.
– You can print the chart or use it in slideshows, presentations, or any digital medium to convey your flow information effectively.
Creating a Sankey Chart in Excel can seem daunting at first, but with this step-by-step guide, you should be able to visualize your flow data effectively. Happy charting!
