Blog of Calc LLC

Suppose there are 5 academic subjects in a class which are English, math, science, music and art. You have the scores of Alice and Bob like below. You can use radar chart to compare their academic performance. The data is below. 1. Select the data. Click Insert > Charts . It automatically generates a bubble chart. Ignore it. Under Chart type , scroll down and select Radar chart. 2. Now you see a radar chart. However, the chart reading starts from 0, which makes the difference between Alice and Bob not big enough. Next, we make the chart reading starting from 50. We need to adjust the vertical axis.  3. Right click the chart, click Axis > Vertical axi s. 4. Set the Min of vertical axis to 50.  5. Now the difference between Alice and Bob looks bigger. Alice is blue and Bob is red. It can be easily seen that Alice is better in English and Music, while Bob is better in Math and Science. Their art scores are almost same.

Tree map chart uses rectangles to show the data of each component in a hierarchical way. For example, suppose a company sells its products to USA and Canada market. In USA, the clients are in New York City and Chicago etc. In Canada, the clients are in Vancouver and Montreal etc. Like below: To make tree map chart, you should have at least 3 columns of data. You can read the data this way: USA is part of World, New York City is part of World, Canada is part of World, Vancouver is part of Canada etc. In the first row, World is the top level, so cell B1 is blank. The third column is the number of items sold in each region, which is used as size of each rectangle in the tree map chart. When making tree map chart, the order of columns are very strict. However, the order of rows does not matter. You can rearrange the rows in a different way but the tree map chart does not change. 1. Select the data. Click Insert > Charts. Under Chart type, select Tree map chart. 2. Now you get a pretty t...

You can easily create an organizational chart in Google Sheets, as long as you set a correct data structure. Suppose a company has a reporting structure like below: 1. Select the data. Notice the first row of "Position" and "Supervisor" is excluded. Click Insert > Charts . 2. For Chart type , scroll down and select Organizational chart 3. Now you have a pretty organizational chart.

Gauge chart is a cool chart that resembles the speed gauge on your car. Suppose a company have the sales data for four fiscal years like below.  1. Select the data. Click Insert > Charts. Skip the recommended chart type. Scroll down and select Gauge.  2. A chart with 4 gauges pops up. There are 3 gauges on top and one gauge on bottom. Let's make all the 4 gauges lining up in a row. 3. Click the chart. A blue border appears. Click the right border and drag it. Now 4 gauges line up in a row. 4. Next, we want to add red, orange and green ranges to the gauges. Usually red color indicates bad results, green color indicates good results, and orange is in between. So we arbitrarily set 0 to 60 to be red, 60 to 80 to be orange, 80 to 100 are green.  5. Now you have a pretty gauge chart.

In Google Sheets you can easily make a map chart. Currently it is only able to plot high-level maps such as at country level. It does not do well in lower level geographic regions. Map at country level Suppose a company sell products to many countries in the world. The data is below: 1. Select the data. Click Insert > Charts . It automatically produce a bar chart. Let's skip the bar chart. Under Chart types , scroll down to Map and select it. 2. A pretty map chart is shown. In this chart, the smallest values are in red and largest values are in green. So for this company, China and USA are the largest markets, while Germany and Australia are the smallest markets. Map at state level or below You can also make map at state level or below in Google Sheets. However, it is not that pretty. For example, a study shows pollution level in 4 states of USA, like below. To make a map, do the same as above. However, you need to choose Geo chart with markers (the one with circles), not the ...

Scatter chart is used to show the relationship between two variables such as height vs weight of students, or ice cream sale vs temperature. Suppose a school nurse measures height and weight of some girl students and have the following result.  1. Select the height and weight. Notice that we do not need the student names. Click Insert > Charts .  2. A scatter chart pops up automatically.  3. If you activate the chart (by clicking on the chart and the chart border will turn into blue), it becomes an interactive chart. If you move mouse cursor over a dot, it will tell you the height and weight of a student, like the following figure. 4. It seems student height and weight are positively related from the scatter chart. We can add a trend line to display the relationship. Right click the chart, under Series , select Weight (kg), then check the Trendline box.  5. Now we have a trend line which goes upward. It indicates that when student height increases, weight also i...

Waterfall charts were added to Google Sheets recently. It used to take many steps to make a waterfall chart in Google Sheets. If the method of creating waterfall chart taught by a tutorial is too hard, it is a sign that the tutorial is outdated. Waterfall chart is used to show how the value changes over time or over steps of a process. Suppose a factory tracks its inventory of a part by the amount of changes, waterfall chart can be used. In the following table, suppose the factory has 52 parts in January. The number of parts decreases by 3 in February, increases by 5 in March etc. 1. Select the data. Click Insert > Charts . Immediately a line chart is shown. However, this is not what we want. We need to change it to waterfall chart. On the Chart editor on the right, under Setup , scroll down and select Other . We pick waterfall chart (in red circle in the following figure). 2. Now You have a pretty waterfall chart. Blue columns show positive change, and red columns show negative ...

This tutorial teaches you how to make stacked column chart. If you have multiple series of data, stacked column chart can be used. Suppose a company make two types of products, and the products are sold all around the world. The data is below. 1. Select the data. Click Insert > Chart . A stacked column chart is shown automatically. 2. We want to add data labels as well title to the vertical axis. Right click the chart, click Series > Apply to all series . 3. Check Data labels 4. Now you see the data labels. They make the chart more readable. 5. To add vertical axis, right click the chart, click Chart & axis titles > Vertical axis title . 6. Fill in the vertical axis title "Number of items sold". Now you get a pretty stacked column chart.

Suppose you own a grocery store and you recorded the number of customers visiting your store for the first 10 days of the year. The data is below. When data is ordered by time, line chart can be used to show the trend over time. 1. Select the data, click Insert > Chart . Immediately a line chart is shown. Google Sheets understands your data. It knows your data is a time-series of data so a line chart is chosen automatically. 2. The chart is already very pretty. If you want to do further editing, you can do it. For example, we want the number of customers are shown on the chart so that the chart reads easier. Right click the line chart, click Series , then "Number of customers", which is the only data series in the data. 3. Check the "Data labels" box. 4. Now the data labels are shown.

This tutorial teaches you how to make pie chart in Google Sheets. In Google Sheets, donut chart is regarded as a special type of pie chart (pie with a hole). So, this tutorial is also on how to make a donut chart. Suppose you run a grocery store and record the sales of items. The data is below: 1. Select the data. 2. On the menu, click Insert > Chart . A pie chart pops up automatically. Unlike Excel, Google Sheets are "smart". It tries to understand your data and select the most appropriate type of charts according to its understanding. 3. The percentages of sale volume for each item is already shown. Suppose we want it to display the dollar value as well. Right click the chart, then click Chart style . 4. Under Pie chart , we change Donut hole to 50%, and under Slice label , select value .  5. Now you get a pretty donut chart. If you set the donut hole to 0% in the last step, you will get a solid pie chart instead.