Spreadsheets can be extremely useful tools in themselves, but at a certain point, all that data can just be too much to process. Show
That's when a chart or graph can help clarify things. If you use Google Sheets, you can easily add a chart to your existing spreadsheet in just a few simple steps. Here's what you need to know to get it done. How to create a graph in Google SheetsCreating a graph in Google Sheets is fairly simple as long as you're logged into your Google account. 1. Open your Google Sheet, or create a new one by going to sheets.new and inputting your data into a sheet. 2. Select the cells you want to use in your chart by clicking the first cell and holding shift on your Mac or PC keyboard while selecting the other cells you want to include. 3. In the top toolbar, select "Insert" and then "Chart." Bernhard says: Thank you very much for this great blog. Thank you very much. ReplyCharles Zweifel says: I am using a pie chart, showing % of "tasks" completed, not started, in progress, or impeded. When I change a status, it updates chart immediately which is all good. The problem is, if I use a filter on another row, say to see how a particular person is doing on executing/completing their tasks and hide the other employees, it seems to break the formula for the overall graph. I then re-populate (select all) the team's names in the filter, and the graph that comes back is either only showing some of the data, or in some cases gone completely. Numbers can be boring. Visualization makes your data more interesting. You can add more power to your data by using charts: grab people’s attention, change their perspective, and make them act by showing facts in a more visual way. Fortunately, creating a chart in Google Sheets is pretty easy. (And fun!) In this article, you’ll learn how to insert different charts and graphs, from basic to custom types. You’ll also learn how to customize these charts using the options available in Google Sheets. Table of Contents hide Basic steps: how to create a chart in Google Sheets Step 1. Prepare your data Step 2. Insert a chart Step 3. Edit and customize your chart Chart vs. graph – what’s the difference? Different types of charts in Google Sheets and how to create them How to make a line graph in Google Sheets How to make a column chart in Google Sheets How to make a bar graph/chart in Google Sheets How to make an area chart in Google Sheets How to make a pie chart in Google Sheets How to create a scatter chart in Google Sheets How to create a bubble chart in Google Sheets How to create a waterfall chart in Google Sheets How to create a candlestick chart in Google Sheets How to make an organizational chart in Google Sheets How to create a timeline chart (for days and times) in Google Sheets How to make a Pareto chart in Google Sheets How to create a histogram graph in Google Sheets Charts and graphs in Google Sheets: real-life use cases How to create a chart with random data in Google Sheets How to use auto-fill to create chart data in Google Sheets How to create a chart from data across multiple sheets in Google Sheets How do I create a pivot table graph in Google Sheets? How to create a chart with 2 y-axis labels in Google Sheets How to create a vertical line in a Google Sheets chart How to create an interactive map chart in Google Sheets How to create a living graph in Google Sheets How to create a versus graph in Google Sheets Bonus stage #1: how to create dependency graph in Google Sheets Bonus stage #2: how to create a graph in Google Sheets with multiple separate data ranges Basic steps: how to create a chart in Google SheetsTo create a chart in Google Sheets, basically, you just need to follow the steps below:
Pretty simple, right? Now let’s dive into each step in more detail. Step 1. Prepare your dataAdd your data in a Google sheet. In case your data is in another source, such as Airtable, Shopify, WordPress, Xero, QuickBooks, and so on, pull them into Google Sheets first. You can use Coupler.io, the tool that provides ready-to-use Google Sheets integrations to retrieve data from the mentioned sources and more. Next, summarize your data if needed. For example, if you have daily data, you may need to group it by month if you’d like to visualize it monthly. Your data doesn’t need to have a grand total—here’s an example:  Step 2. Insert a chartBegin by selecting the data you want to show. After that, click Insert > Chart from the menu. Alternatively, you can also click the Insert chart icon in the toolbar. Just like that! Google will create a default chart for you based on your data. For example, the chart may be a pretty line chart, as the following screenshot shows. However, it may not be the chart you were expecting. Maybe you hate lines and think that they’re as boring as numbers. But don’t worry, you can edit your chart using the Chart editor that opens on the right side. Step 3. Edit and customize your chartIf you accidentally closed the chart editor, just double-click on the chart, and it will open again. The editor has two tabs: Setup and Customize. There are many options available to edit your chart—you can customize almost everything here. You can change your chart type from a line chart to a column or bar chart in the Setup tab. You can also modify your data range, add and remove series, and switch between rows and columns. Google’s idea may be different from yours. Thus, you may want to customize a generated chart’s default appearance — from a white background to light gray, from a red bar to green, etc. To do this, use the various options available in the Customize tab. If you look at the chart area, you’ll notice three vertical dots in the top right corner. This is the chart menu. Click on it, and you’ll see several options as follows: Notice that you can download your chart as an image (PNG or SVG) or PDF file. You can also copy and paste it to other apps, such as Google Slides or Google Docs. Alternatively, use Ctrl+C and Ctrl+V as keyboard shortcuts to copy and paste it. Chart vs. graph – what’s the difference?
A graph is basically a two-dimensional diagram. This means it illustrates the correlation between two or more sets of data using the horizontal (X-axis) and vertical (Y-axis) lines. Here is an example of a scatter plot graph of monthly expenses data:
At the same time, a pie chart is not a graph. Why? Pie charts use only one quantitative coordinate. A heat map is also not a graph, whereas bar charts and line charts can be named bar graphs and line graphs respectively. Nevertheless, there is no distinct rule on using these terms. Most people use them as synonyms to refer to the same thing: a visual representation of data. Here is how we suggest you differentiate between graphs and charts: GraphsChartsBar graphLine graph Stacked bar graph Histogram Dot graph or Plot Scatter plot Pictogram graphColumn chart Area chart Pareto chart Mekko chart Pie chart Bubble chart Waterfall chart Funnel chart Bullet chart Heat Map Radar chart Spline chart Not to be confused, bear in mind that Google Sheets treats all graphs as charts. 🙂 Different types of charts in Google Sheets and how to create themBasically, to create a chart, follow the three basic steps described above, and you’ll be fine. As of this writing, there are 17 chart types available in Google Sheets, and each type also has its own variation. We will cover most of them, but not all. How to make a line graph in Google SheetsLine graphs are best if you’re going to show trends over time and reveal the overall direction of the data via trendlines. This can help you understand your sales trend, see whether the number of support tickets is growing linearly or exponentially, and so on. A line chart is usually better than a bar chart or column chart when showing data over time. This is because trendlines are more straightforward, allowing you to see change over time far more easily. To create a line graph, make sure to select “Line chart” from the “Chart type” dropdown after you have inserted a chart. The data range for this example is A2:G3. How to create a multi-lined graph in Google SheetsFor a multi-lined graph, we need to select a larger data range to compare the rest of the items against each other. In our example, the data range will be A2:G6. Notice that the lines move from point to point over months. And almost always, in the X-axis of a line chart, you’ll see some kind of time frame. How to create a curved graph in Google SheetsIf you want to customize the lines, there are options that are worth considering. One of them is the option to make the lines smooth to give you a different look and feel. Simply double-click the chart, then select the Customize tab. Under Chart style, tick the Smooth option to make curved lines. How to make a comparison mode line chart in Google SheetsAnother option worth considering is making the chart show additional information when you hover over a data value. In the Chart style section, tick the Compare mode to enable this option. You’ll see useful comparison info as the following screenshot shows: You may need to change the axis scale when dealing with multiple series. We cover this issue in the section “How to make a Pareto chart in Google Sheets“. How to make a column chart in Google SheetsColumn charts (and bar charts) are often considered the best chart if you’re trying to accentuate the idea of volume – for example, if you’re going to show units of products sold, numbers of tickets sold, numbers of students per faculty, and so on. To create a column graph, make sure to select “Column chart” from the “Chart type” dropdown after you have inserted a chart. How to make a stacked column chart in Google SheetsThe advantage of a stacked column chart is that it’s simpler, slightly cleaner looking and, at a glimpse, you can tell the total. You can choose two types of stacked column charts from the Chart type dropdown: stacked column and 100% stacked column charts. A stacked column chart is best to compare category-to-category total value. If you look at the first chart (the top-left chart), you may ask which is bigger between Store 1 vs. Store 2. But in the second chart, it’s clear that Store 1 is bigger than Store 2. You won’t see the third chart (100% stacked column) very often. The attempt here is to meet some of the limitations of a pie chart. Each individual column here is showing the proportional breakout of the total. How to create a log scale graph in Google SheetsLogarithmic, or log, scales can be useful in graphs where a few points significantly exceed most of the data, or when you need to display percentage change or multiplicative factors. You can add a log scale to your column or bar graph in the Chart editor. Go to Customize => Vertical axis. Scroll down to the Log scale checkbox and select it. Note: if you want to add a log scale to your bar graph, you’ll find the Log scale checkbox in the Horizontal axis section. How to create a ranking chart in Google SheetsA column chart is one of the best charts you can use to show ranking. Before plotting the data, it’s best practice to sort your data from largest to smallest if you want to see a ranking trend. It will help you come to a conclusion quickly. How to make a bar graph/chart in Google SheetsA bar chart is a column chart rotated 90 degrees. Like a column chart, a bar chart is suitable if you want to visualize quantity or volume. Usually it is used if your X axis captions are too long or there is not enough space to represent all of the needed columns. To create a bar chart, make sure to select “Bar chart” from the “Chart type” dropdown after inserting a chart. How to create a double or triple bar graph in Google SheetsIn the screenshot above, the bar graph overlays four items: Orchid, Rose, Sunflower, and Daisy. If you want to compare only two or three of them, just remove the unnecessary item(s) using the Series section: How to create a legend in Google Sheets bar graphChart editor provides numerous customization options. For example, you can tune the legend for your graph. To do this, click the Customize tab and select Legend. Now you can customize the graph legend:
If you don’t need a legend on your bar graph at all, choose None in the position field and the legend will vanish. How to make a stacked bar chart in Google SheetsSimilar to column charts, there are also options in Bar chart to make it stacked or 100% stacked. This type of charts is the best option if you need to create a Gantt chart in Google Sheets. How to create a profit & loss bar chart in Google SheetsIf you want to look at profits, a bar chart (or column chart) can visualize that. It’s possible to have a negative profit, which is a loss. The bar chart shows that on a negative axis. See the following screenshot: You can make the color of the negative bars different. To do that, separate the Profit/loss column into two: Profit and Loss. Thus, you’ll have two data series and can customize each series’ color as you want. And here is what a bar graph made on the Profit and Loss report exported from Xero may look like: How to make an area chart in Google SheetsThe area chart is a variation of the line chart. It has shade below the lines to help show the magnitude of trends. The area chart has different stacked variations like in a bar chart and column chart. It also has stepped shape variation. To create an area chart, select “Area chart” from the “Chart type” dropdown after you have inserted a chart. See the following image for area chart variations: How to make a pie chart in Google SheetsA pie chart depicts a portion of a whole and is best for displaying pieces that add up to 100%. To create a pie chart, you basically need data with only two columns (or two rows). Here is an example: A pie chart has a limitation that if you select data with more than two columns and rows, you can only see a certain category’s values. To customize the appearance of your pie chart, there are options in the Chart editor that may interest you:
Note: We do not suggest using the 3D variant for slice distance and doughnut charts, as it is considered to be a bad practice. These types of charts are hard to read. How to create a scatter chart in Google SheetsA scatter chart is often referred to as an X-Y chart or a plotted graph. Use it to show the relationship between two variables. For example, when you want to find out how much one variable is affected by another. The closer the data points come to making a straight line, the stronger the correlation between the two variables. Here’s an example of a scatter chart that shows a relationship between the number of units sold and sales volume. How to create a slope graph in Google SheetsOn the scatter plot above, it would be great to have a slope line to see whether it is a positive or negative slope. To add the slope line to your plotted graph, go to the Customize tab and select Series. Scroll down to find a Trendline checkbox. Mark the checkbox to add a slope line to your graph. In our example, we have a progression graph with a positive slope. How to create a bubble chart in Google SheetsA bubble chart is similar to a scatter chart. The only difference is that, in bubble charts, you need to add a third dimension, which is the size of the bubble. Here’s an example: We added Store location and Number of employees columns to the same data set used in the previous scatter chart. Here, the numbers of employees are the bubbles’ sizes, and the store locations represent colors. How to create a waterfall chart in Google SheetsA waterfall chart is ideal for showing positive and negative data flow changes. For example, you can use it to show monthly (or quarterly, yearly, etc.) sales changes, cash flow changes, and so on. You can find the Waterfall chart in the “Other” chart types. Here’s an example of monthly revenue changes plotted using a waterfall chart: How to create a candlestick chart in Google SheetsA candlestick chart shows the movement of low, high, opening, and closing values of a currency, security, or derivative. You can use a candlestick chart to analyze the fluctuation of daily/monthly stock prices, currency rates, TV program ratings, rainfall, or temperature. Check out what it may look like in the next example. How to create a stock chart in Google SheetsGoogle Sheets currently does not have “Stock chart“ in its chart types. So, use a candlestick chart to create a stock chart. If you want to model daily stock movement, for example, put your data in the following order in a spreadsheet:
Then, highlight your data and insert a chart. After that, change the chart type to “Candlestick chart.” How to make an organizational chart in Google SheetsYou can use an organizational chart to show the relationship between employees and their managers, a hierarchical structure of job titles, a family tree, etc. To create an organizational chart for employee-manager relations, add two columns to your spreadsheet. In the first column, list all the employee names. Then, in the second column, list their manager names. Highlight your data and insert a chart. After that, change the chart type to “Organizational chart“, as the following screenshot shows: Organizational charts are also great at representing a project work breakdown structure. The data for the chart can be exported from a project management tool as tasks and parent tasks. For example, here is how you can export data from Jira to Google Sheets. How to create a timeline chart (for days and times) in Google SheetsA timeline chart shows important events in chronological order. You can use it, for example, to visualize your daily weight records, daily attendance counts, etc. The first column in your data must be in a date (or date and time) format to create a timeline chart. Then, insert a chart and change the type to “Timeline chart.” The timeline chart displays two time frame views for the data changes:
How to make a Pareto chart in Google SheetsA Pareto chart is a statistical chart that shows a combination of two charts: column and line charts. As of this writing, the Pareto chart is not available by default in Google Sheets, but you can create it using the Combo chart. Now, let’s see a Pareto chart below that shows sales and their cumulative percentages. Here are simple steps to create a Pareto chart as shown above: Step 1. Sort the sales data (B1-B7 cells) from highest to lowest. Step 2. Calculate the cumulative percentage. Here are easy formulas to calculate it manually:
If you don’t want to copy and adjust the formula for each line, you can simply place this ARRAYFORMULA instead of the column C header: ={“Cumulative %”; ARRAYFORMULA(IF(LEN(B2:B)=0,,MMULT(TRANSPOSE((ROW(B2:B)<=TRANSPOSE(ROW(B2:B)))*B2:B),SIGN(B2:B)))}Read our blog post to know more about the magic of Google Sheets ARRAYFORMULA Function. Step 3. Select the data (A1:C7), then insert a chart. Then change the chart type to Combo chart. Step 4. In the Customize tab, expand Series. Then, select the Cumulative % series, and change its axis to “Right axis.” How to create a histogram graph in Google SheetsAs you know, a histogram is a graph that distributes closely-related data into groups. Google Sheets allows you to create a distribution graph easily. Here is a data set we have: Select it, insert a chart, select Histogram chart, and there you go! Google Sheets histogram automatically distributed the data into five buckets:
You can customize the bucket size, as well as the outlier percentile in the Customize tab. The outlier percentile lets you group outliers with the closest relevant bucket size. For example, a 10% outlier percentile includes 10% of the top and bottom values when calculating buckets. Charts and graphs in Google Sheets: real-life use casesHow to create a chart with random data in Google SheetsYou may want to generate fake data for your chart (for example, random numbers). In Google Sheets, there are two functions you can use to do that: RANDBETWEEN and RAND. The RANDBETWEEN function creates random numbers within specified min and max values, while the RAND function generates random values between 0 and 0.9999999. How to use auto-fill to create chart data in Google SheetsUsing auto-fill when preparing data for your chart will save you time. To do that, enter numbers, dates, or text in at least two cells next to each other. Then, select the cells and drag the small blue dot down or across. Here’s an example of using auto-fill to generate month values: How to create a chart from data across multiple sheets in Google SheetsLet’s say you have a Google Sheets doc that contains raw data about your deals, as well as some calculations such as how many lost/won deals you had in previous years. You want to reference this data from another spreadsheet and create a graph to visualize the relationship between won and lost deals for the previous years. So, basically, you need to reference data ranges:
You can do this using one of the following options:
={
importrange("https://docs.google.com/spreadsheets/d/1o4M1evO7bcnU2vBspjFchF54Uq1z6CTPtdxVNK2l09g/edit#gid=1987922184","Dashboard!A76:A80"),
importrange("https://docs.google.com/spreadsheets/d/1o4M1evO7bcnU2vBspjFchF54Uq1z6CTPtdxVNK2l09g/edit#gid=1987922184","Dashboard!D76:D80"),
importrange("https://docs.google.com/spreadsheets/d/1o4M1evO7bcnU2vBspjFchF54Uq1z6CTPtdxVNK2l09g/edit#gid=1987922184","Dashboard!E76:E80")
}
Read more about using IMPORTRANGE in Google Sheets.
=query(importrange("https://docs.google.com/spreadsheets/d/1o4M1evO7bcnU2vBspjFchF54Uq1z6CTPtdxVNK2l09g/edit#gid=1987922184","Dashboard!A76:E80"),"select Col1, Col4, Col5")Read more about using QUERY and IMPORTRANGE in Google Sheets.
Note: with Google Sheets importer, you can only select an integral data range, so it’s A76:E80 in our case. Read more about Google Sheets importer. Now you can select the data set and make a graph by following the steps that we described before. For example, here is what the stacked bar graph will look like: How do I create a pivot table graph in Google Sheets?The same steps should be followed to create a graph of a pivot table. However, it’s very important to make sure a pivot table is properly structured. Let me explain in the example. We have a data set imported from Airtable. We need to make a pivot table to see the number of products by years. If we add two rows, Product and Year, we’ll get the following view: The pivot table provides what we needed, but this structure won’t let you build a bar or column graph. So, we’d better remove the Year row and add Year as a column to get the following pivot table: Now you can easily build a graph by selecting a data range (in our case, A2:C12) and inserting a chart. Mark the “Use row 2 as headers” checkbox to make it work. Read our guide to master pivot tables in Google Sheets. How to create a chart with 2 y-axis labels in Google SheetsSometimes, adding a second Y-axis to a chart can be useful. You can add it to a line, area, or column chart using a combo chart. Yes, it’s similar to the Pareto chart explained previously. Note: You can’t add a second X-axis to a graph. We’ll create a combo chart using the following data containing a list of states, total users, and new users. Select all three columns and insert a combo chart. After that, customize the “Total new users” series to use the right axis. In the Customize tab, expand Series. Then, select “Total new users.” and change the axis value to “Right axis.” How to create a vertical line in a Google Sheets chartIn Google Sheets, unfortunately, you can’t add a vertical line to a chart easily. Suppose you want to add a vertical line in a chart as in the following screenshot: Notice that the vertical line is on X = 26 (age = 26). Step 1. Insert three new rows at the top, and fill them as the below screenshot shows: To fill the new rows, see the following explanation:
Step 2. Click the Insert chart icon in the toolbar — a new blank chart will be added automatically. Step 3. In the Setup tab, change the chart options as follows:
And, done! Your chart will appear as in the expected result. How to create an interactive map chart in Google SheetsJust like it sounds, a map chart has the ability to plot data on a map. It’s pretty easy to create a map chart in Google Sheets. You can also share it and make it interactive. The following steps demonstrate the process to create a map chart that shows the cumulative COVID-19 cases as of Dec 1, 2020, around the world. Step 1. Prepare data in a spreadsheet. Tip: If you’d like to get data from the Internet, such as from a Wikipedia page, you can use the IMPORTHTML function. Step 2. Select your data and insert a chart. Then, change the chart type to Geo map. Step 3. Prepare to publish the chart by clicking the Chart menu, then select Publish chart. Step 4. Choose whether you want to link or embed your document, make it interactive or not, etc. See a few options as shown in the screenshot below: And here are explanations for the options above:
Done! Now you have your interactive map chart published. “Interactive” here means that when you hover your mouse over each country, you’ll see the number of its total cases. How to create a living graph in Google SheetsA living graph means that your data set will be constantly updated. For this, you need to automate data import from a third-party data source and create a graph of the data imported to Google Sheets. How to automate data importIn Google Sheets, there are a few functions that let you automate import of specific data formats:
If you need to automate data import from other sources or apps such as Xero, Airtable, Shopify, and others, you should use Coupler.io, a no-code solution that can integrate your spreadsheet with different sources. How to create a graph in Google Sheets with every hour valueHere is an example of a living graph. Using Coupler.io, we connected Google Sheets to the Exchange rates API, a free service for current and historical foreign exchange rates published by the European Central Bank. Coupler.io will update currency exchange rates every hour, so the graph will update accordingly. This example is pretty simple, but you can build more advanced living graphs without any coding. How to create a versus graph in Google SheetsThe example above is a distinct case when we use graphs to visualize the comparison of two or more things. It works pretty simply and doesn’t require you to somehow tweak the data. And it’s not necessary to compare X and Y on a time scale. For example, we have three columns:
In this case, we compare the appetite of John vs. Mary, and a graph is the best way to visualize this comparison. Select the range and insert a chart. Google Sheets will automatically fit a bar graph or a stacked bar graph: Bonus stage #1: how to create dependency graph in Google SheetsThere is no native functionality in Google Sheets for building dependency graphs. However, there is a workaround. We’ll show it in the example of this funny engineering flowchart: Let’s recreate it in Google Sheets as a dependency graph. First, specify the questions and answers, as well as their dependency in a spreadsheet: Then apply the following array formula in the C2 cell to create dependency bonds: =arrayformula(" """&A2:A13&""" -> """&B2:B13&"""")Go to webgraphviz.com, copy the values from the C column and paste them into the curly brackets as follows: Click the Generate Graph! button, and there you go! Bonus stage #2: how to create a graph in Google Sheets with multiple separate data rangesWe have two data ranges: The idea is to make a graph that represents two lines for each data range separately. However, you can’t just select the range A1:E5 and click Insert chart. What you should do is relocate the second data set below the first one, like this: Now you can select the range (A1:C9) and create a line graph: We attempted to cover as many use cases as possible to show you the power of building graphs in Google Sheets. However, we’re pretty confident that it’s just a drop in the bucket. So, if you have an interesting use case, share it with us. Good luck with your data! How do you make an XY line graph in Google Sheets?How to Create a Line Graph in Google Sheets in 4 Steps. Enter your data,. Highlight the data you want to be in the graph,. Click on “Insert Graph” from the top toolbar,. Choose your desired chart,. Customize and place your chart.. How do I create a chart in a cell in Google Sheets?In our example, we select the range A1:D5. Then choose in the menu: Insert - Chart. The Google Sheets graph is built, the chart editor is displayed. Your spreadsheet will offer you a chart type for your data at once.
How do I make a bar graph in Google Sheets?Where Is the Bar Graph in Google Sheets? To start creating bar graph, you need to go to Insert > Chart. Then, in the pop-up chart menu, click the dropdown under 'Chart Type' and choose 'Bar Graph'. Just make sure you highlight the data you want to convert beforehand.
How do I make Google Sheets look like graph paper?The Google Sheets template is linked near the top of the page. To use the Google Sheets template and print graph paper, follow these instructions: Click the link to the template, then click “Use Template” Click on the tab that has the graph paper that you want to print.
|
How do i make a graph on google sheets
Related Posts
Copyright © 2024 paraquee Inc.
