A floating column chart in Google Sheets is actually a stacked column chart in which the series color “Min” highlighted to white (none). At some extent, it resembles the Candlestick chart.

Candlestick charts are the best way to visualize low, high, open and close values of a security (stock) for a specific period. But when you want to simply show the min and max (low and high) values, it won’t help. Here comes the use of a floating column chart in Google Sheets.

You can use a floating column chart in the following and similar scenarios.

To visualize;

1. Minimum and maxim temperature for a given period.
2. The high and low price of a security for a given period.
3. Opening and closing price of a security for a given period.

Here are two examples. In that, I am going to depend on the stacked column chart to create the said min and max floating column chart.

Floating Column Chart Showing Minimum and Maxim Temperature for a Week

I have the following prediction for the min and maximum temperature for the coming week.

Here are the steps to create the floating column chart in Google Sheets based on the above sample.

Copy and paste the above data in the range A2:C8 in a blank Google Sheets file. Then in cell D2, enter the below formula which calculates the difference of max and min temperature for the period 20/05/2019 to 26/05/2019.

=ArrayFormula(C2:C8-B2:B8)

Then hide the column C as we don’t want that column as a series in our chart. The sample data must be looked now as follows.

We only need these three columns for the chart. You can now select the range A1:D8 and click on the menu Insert > Chart.

Chart Editor Necessary Settings for Floating Chart

1. Under “Setup” select “Stacked column chart”.
2. Then under the tab “Customise”, click “Series” and select “Min”. There change the format color to “None”.
3. Finally, remove the legends.

You are full set. Here is the finished chart. You can see that the columns are floating based on the min and max values.

Creating a Floating Column Chart to Show Low and High Values of a Security

You can follow the same above method to create this chart. But in this case, you can use the GoogleFinance function to get the required data to plot the chart.

In cell A1, enter the following GoogleFinance formula. You can change the ticker symbol “NSE:HINDALCO” used in this example to the one that you want.

=googlefinance( "NSE:HINDALCO" , "low" , "01/08/2018" , "31/08/2018" )

This formula would return the low price of the used security for that specified period. In cell C1, use the following formula to fetch the high price.

=googlefinance( "NSE:HINDALCO" , "high" , "01/08/2018" , "31/08/2018" )

Then in cell E1, use this formula to subtract low price from high.

={"Difference";ArrayFormula(if(len(A2:A),(D2:D-B2:B),))}

Hide the columns C and D. Then you can create the floating chart as per the chart I have created in the first example.

Floating Column Chart Vs Built-in Candlestick Chart

Check my Candlestick chart below and the above floating column chart. You can see that both the charts are almost similar in look.

In the floating column chart, I have only used the low and high values. But in Candlestick, other than low and high values, we need open and close values. If you don’t want the open and close values in your Candlestick chart, the only option left with you is to use the above floating chart.

To know how to create the above same Candlestick chart, please follow this guide – Google Sheets Charts: Built-in Charts, Dynamic Charts, and Custom Charts.