![]() ![]() If you have a column made up from more than one segment, you can enter an e for at most one of them. You may enter two or more values into a single column. Thus, the following datasheet results in the same chart as shown above: In this case, think-cell starts from the right-most column and calculates backward to find the value for the e column. You can even begin the calculation with an e in the first column. ![]() All e segments are calculated by think-cell and automatically kept up-to-date when data changes. In fact, you can use e in any segment that you want to be stretched to fit the rest of the chart. segments that go all the way down to the baseline of the chart – are easily created with an e (for “equals”). Positive values result in segments going upwards, negative values create segments going downwards. To create a waterfall chart, simply type the calculation into the datasheet: (ok, I cheated in the last one – the smaller arrows are just manually added lines, and won’t change if your data changes.9.1 Creating a waterfall chart 9.2 “Percent of datasheet 100%=” as label content 9.1 Creating a waterfall chart Icon in Elements menu:Ī waterfall chart (sometimes called bridge chart) visualizes an additive calculation with subtotals. Why not use the pasting shapes technique to add some further direction and clarity to the data? Then drag up to above the column, and repeat for the closing value. In 2013, CHART TOOLS àDESIGN àAdd Chart Element àData Labels àData Callout Select the first value, twice, so only it is selected. I also like to add a callout label to the start & end values. Select it, then select again so only ‘base’ it selected, then DELETE If you want to keep it, you’ll want to remove the ‘Base’ item. Now we can tidy up the titles and legend (I find a legend isn’t always needed for this type of chart. Then colour it in the same way as before, and repeat for the end value Now select the first value, and then select again, so it becomes the only selected value… Again select the ‘Rise’ series, and fill. If you have more than 2 ‘increases’, you’ll probably want to set your blue first, so we’ll do that here as well. Which way round you do this will depend on how many values you have. Select the ‘Fall’ series, ensuring all values get selected, and ‘Fill’ accordingly.īecause our increases and start/end value are part of the same series, we’ll need to first set a base colour for the series, and then colour exceptions individually. For me, I want RED drops, BLUE increases and GREEN for start/finish values Now change the FILL colour to NO FILL (This will vary by version, but CHARTS TOOLS àFORMAT àShape Fill is 2013, and similar for others)įrom here, the rest is really down to how you want to format your data. Left click ones on one of the blue ‘Base’ value – this should select all the base values as below… Now all we need to do is apply formatting to make it looks like the segments are ‘floating’ You should have something similar to this… Select your new data range, and insert a Stacked Column chart. The start & end values should only have a ‘Rise’ valueĪlso, all values should be Positive – if you’re linking your values back to your source data, use the function ABS(Value) to make a negative positive. So for each of your values, move them into either Rise or Fall – with Base being a formula of Previous Base + Previous Rise – Current Fall To start, you’ll have some simple data, with a starting value, some positives & negatives, and a finishing totalīecause what we’re actually going to do is create a stacked column chart, we need to split the values into ‘Rise’ and ‘Fall’ values, and then the ‘base’ value for each – that is, the lower part of the stacked column on which our waterfall segments will sit. I’m using excel 2013, but the approach is almost identical for previous versions of Excel We’re going to go through the process to create the below chart – a simple income & expenditure chart for a shop. As with most of the chart techniques I’ve demonstrated, it’s really about tricking excel to show the bits of data you want it to show. That being said, they’re really not as bad as to put together as they’re sometime perceived to be. Waterfall Charts are another one of those charts that’s much harder to put together than it should be, particularly as they’re a great way to understand the sequential impact of positive & negative values to a total. ![]() Update: Feb 2016 – If you’re using Excel 2016, be sure to check out the new Waterfall Chart Type, which makes waterfall charts a breeze. Ma3 comments Article Advanced, Excel, Microsoft Office ![]()
0 Comments
Leave a Reply. |