Have you ever wanted to visually represent the minimum and maximum values of a dataset? While candlestick charts are great for displaying low, high, open, and close values of a security, they can be overwhelming when you just want to focus on the min and max values. That’s where a floating column chart in Google Sheets comes in handy.
A floating column chart is essentially a stacked column chart where the series color for “Min” is highlighted to white (none), giving it a clean and focused appearance. It’s a simple yet effective way to showcase the range of values for a given period.
When to Use a Floating Column Chart?
You can use a floating column chart in various scenarios, such as:
- Visualizing the minimum and maximum temperature for a given period.
- Showing the high and low prices of a security over time.
- Highlighting the opening and closing prices of a security.
To demonstrate the versatility of a floating column chart, let’s look at a couple of examples.
Floating Column Chart Showing Minimum and Maximum Temperature for a Week
Suppose you have the following predictions for the min and max temperature for the coming week:
| Date | Min Temp. (°C) | Max Temp. (°C) |
|---|---|---|
| 20/05/2019 | 27 | 33 |
| 21/05/2019 | 27 | 34 |
| 22/05/2019 | 27 | 35 |
| 23/05/2019 | 27 | 36 |
| 24/05/2019 | 27 | 32 |
| 25/02/2019 | 27 | 33 |
| 26/05/2019 | 27 | 32 |
Here’s how you can create a floating column chart in Google Sheets based on the above sample:
- Copy and paste the data into a blank Google Sheets file, starting from cell A2 to C8.
- In cell D2, enter the formula
=ArrayFormula(C2:C8-B2:B8)to calculate the difference between the max and min temperature for each day. - Hide column C as it won’t be used in the chart.
- Select the range A1:D8 and click on “Insert” in the menu, then choose “Chart”.
Chart Editor: Necessary Settings for Floating Chart
- Under the “Setup” section, select “Stacked column chart”.
- Switch to the “Customize” tab, click on “Series”, and select “Min”. Change the format color to “None”.
- Finally, remove the legends.
And voila! You have created a floating column chart that beautifully displays the range of temperatures throughout the week.
Creating a Floating Column Chart to Show Low and High Values of a Security
The same method can be applied to create a floating column chart depicting the low and high values of a security. However, in this case, we can leverage the handy GOOGLEFINANCE function to retrieve the required data.
In cell A1, enter the following GoogleFinance formula, modifying the ticker symbol “NSE:HINDALCO” to match your desired security:
=googlefinance("NSE:HINDALCO", "low", "01/08/2018", "31/08/2018")
This formula will fetch the low price of the specified security for the given period. Similarly, in cell C1, use the formula:
=googlefinance("NSE:HINDALCO", "high", "01/08/2018", "31/08/2018")
To calculate the difference between the low and high prices, enter this formula in cell E1:
={"Difference";ArrayFormula(if(len(A2:A),(D2:D-B2:B),))}
Now, hide columns C and D to streamline the visualization. You can then create the floating chart as shown in the previous example.
Floating Column Chart Vs Built-in Candlestick Chart
Take a moment to compare the floating column chart above with a typical candlestick chart. You’ll notice that both charts have a similar appearance.
The key difference lies in the additional data points captured by the candlestick chart, including the open and close values. However, if you prefer a cleaner presentation without these extra details, the floating column chart is the perfect choice.
To learn how to create a candlestick chart in Google Sheets, check out our comprehensive guide on Crawlan.com titled “Google Sheets Charts: Built-in Charts, Dynamic Charts, and Custom Charts.”
So, the next time you want to showcase the range of values in a clear and concise manner, try creating a floating column chart in Google Sheets. It’s a simple yet powerful visualization tool that will impress your audience and effectively convey your data.
