Watblog – How to create a waterfall chart in Excel 2007 – Creating a waterfall chart in Excel 2007 might require a bit more manual work compared to newer versions of Excel that have built-in waterfall chart functionalities. In Excel 2007, you’ll need to use a stacked bar chart and modify the data series to create the waterfall effect. Here’s a step-by-step guide:
Here’s a step on how to create a waterfall chart in Excel 2007
Step 1: Prepare Your Data:
- Create a table with two columns: “Categories” and “Values.”
Categories Values Starting 100 Category 1 -20 Category 2 -30 Category 3 50 Ending 100
Note: The first row (“Starting”) represents the initial value, the intermediate rows represent positive and negative changes, and the last row (“Ending”) represents the final value.
Step 2: Create a Stacked Bar Chart:
- Select your data table.
- Go to the “Insert” tab on the Excel ribbon.
- Click on the “Bar Chart” dropdown and choose “Stacked Bar.”
Step 3: Modify the Chart:
- Right-click on any of the data bars in the chart and choose “Format Data Series.”
- In the “Format Data Series” pane, adjust the settings to make the bars transparent:
- Set the “Fill” option to “No fill.”
- Set the “Border Color” to “No line.”
- Right-click on the X-axis (bottom axis) and select “Format Axis.”
- In the “Format Axis” pane, set the “Axis Labels” option to “None.” This hides the x-axis labels.
Step 4: Add Data Labels:
- Click on any of the data bars to select them.
- Go to the “Layout” tab on the Excel ribbon.
- Click on “Data Labels” and choose “Inside End.”
Step 5: Adjust Data Labels:
- Right-click on any of the data labels and choose “Format Data Labels.”
- In the “Format Data Labels” pane, customize the labels:
- Select “Label Options” on the left.
- Check “Value.”
- Uncheck “Y Value.”
Step 6: Change Data Series Fill Color:
- Click on one of the data series (not the transparent one) to select it.
- Right-click and choose “Format Data Series.”
- In the “Format Data Series” pane, go to the “Fill” section and choose a color for the positive or negative values.
Step 7: Adjust Axis Labels:
- Right-click on the Y-axis (side axis) and choose “Format Axis.”
- In the “Format Axis” pane, set the “Major tick mark type” to “None.” This hides the y-axis labels.
Step 8: Final Touches:
You might need to adjust spacing, alignment, and other visual elements to make the chart look like a proper waterfall chart.
Remember, creating a waterfall chart in Excel 2007 involves manual adjustments and formatting. If you frequently need to create waterfall charts, consider upgrading to a newer version of Excel that offers built-in waterfall chart functionality for a smoother and more automated experience.