Trending February 2024 # How To Automatically Generate Charts And Graphs In Google Sheets # Suggested March 2024 # Top 3 Popular

You are reading the article How To Automatically Generate Charts And Graphs In Google Sheets updated in February 2024 on the website Hatcungthantuong.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested March 2024 How To Automatically Generate Charts And Graphs In Google Sheets

Charts and Graphs are useful tools used for conveying information to clients. Complex numerical data are often presented in graphical form so that they can be easily understood and remembered. The Graphical representation of data helps to present the information and its developing trends over a period of time in a quick way.

Visual images like charts and graphs also help to highlight the important facts which can be easily remembered and understood. Charts are most often used in business and for daily assignments allowing you to easily see trends and comparisons in a visually appealing way. Whether you want to deliver a presentation to frame a report, charts take an important role in visualizing a complex data analysis to your audience.

In this article, we explain how to automatically generate charts in Google Sheets.

Can you make graphs from data in Google Sheets?

Yes, Google Sheets comes with a native feature to automatically make graphs from the data present in a Google Sheets document. You need to select the graph or chart type (Line graph, Bar chart, etc.) in the Chart editor, set the data range, and other options, and a graph is generated based on the selected data range and graph type. You can also change the chart type later.

Generate Charts in Google Sheets

Fill the sheets with data into the cells and organize the numerical facts with a column header.

Navigate to Insert and select the option Chart from the drop-down menu. A chart editor window pops up.

In the Chart type field, select the type of chart you want to create from the drop-down menu. Google Sheets provides 30 variety of charts that you can choose to create for your data.

In the Data range field, select the range of cells that you want to be displayed on the table. You can either manually enter the range or use the pointer to select the data range.

Google Sheets will now create and display a chart with all the data selected from the range of cells in the sheet.

Now if you make any changes to the data in the sheets, the chart will be automatically updated with the new results without having to create a new chart for the updated value.

You can also change the type of chart for the same data easily in Google Sheets.

Go to Setup and in the Chart type field select a new type from the drop-down menu.

Customize charts in Google Sheets

Additionally, Google Sheets allow you to fully customize your charts based on the data. The customizing options allow you to configure the chart series, style, background color, axis labels, legend, and more.

The window displays options like Chart style, axis titles, series, axis labels, gridlines, and many more which you can choose based on the type of chart you want to create.

All the adjustments you make in data and charts editor are automatically updated without having to repeat the entire process. The changes are reflected without you having to even refresh the page.

How do I make Google Sheets automatically update charts?

You don’t have to do anything to make Google Sheets automatically update charts. As soon as you edit the data in a Google Sheets document, the chart is updated accordingly. For example, if you remove data from some cells and that data is a part of the data range for the chart, then the chart is changed and updated instantly.

Hope this helps.

You're reading How To Automatically Generate Charts And Graphs In Google Sheets

How To Search In Google Sheets

Last Updated on May 16, 2023

A lot of the functionality remains and Google Sheets can open Excel files too. Once you have used Microsoft Excel, you should be familiar with the search functions and they can prove exceedingly useful.

In this guide, we will look at the search functionality and how to use Google Sheets to find specific terms and words. You can even find them and replace them which can save you a lot of time.

1

Keyboard Shortcuts

You can simply use keyboard shortcuts to search for cells that contain the specific text that you want to find.

Step

1

Step

2

Keyboard Shortcut

Hold the Control key (Ctrl) and press F on a Windows keyboard. On a Mac, this is slightly different as you hold the Cmd key and then press F.

Step

3

Find Dialog Box

You should notice that a small Find dialog box opens in the top right corner of your sheet.

Step

4

Enter Search Word

Enter the term that you want to search for. In this case, ‘may’ is the name of the month you want to find to search the whole sheet and highlight any matches.

If there are multiple matches then you press the upward and downward-pointing arrows in the Find field.

Step

5

Result

As you can see, the cell containing the term ‘may’ has been highlighted as green proving that this is a really handy method to search for a specific term.

Only one result was found which was what we expected. In the next column to the highlighted field, you can see that May has 31 days.

2

Find And Replace

If you wanted to quickly find a specific term then replace it with another, there is a simple method of doing that.

Using the ‘Find and replace’ functionality can save a lot of time from having to find each matching term and then having to manually replace each one.

You can also be assured that every matching term has been found and replaced as you may miss a couple if you have hundreds to search through.

Step

1

Find And Replace Option

Step

2

Keyboard Shortcut

There is another way of opening the ‘Find and replace’ dialog box and that is by using another keyboard shortcut.

On a Windows keyboard, you can do that by holding the Ctrl button and pressing H. For a Mac, again this is slightly different and you will press the Cmd button with H.

Step

3

Find And Replace Dialog Box

Once you have opened the ‘Find and replace’ dialog box, you can easily find your desired term.

For example, let’s say it is a leap year and you want to find the 28 days in February and replace it with 29. Only one month has as few as 28 days so this should be straightforward.

Step

4

Replace All

Step

5

Found And Replaced

As expected, there was only one field to replace with our desired term and that has been completed.

Final Thoughts

If you realize that you have to replace one specific term with another then this can be easily done with the ‘Find and replace’ functionality too.

Using the ‘Find’ and ‘Find and replace’ functionality can make a tedious task seem very straightforward. Instead of having to trawl through an entire sheet (or sheets) of data, you can let the application do the hard work for you.

How To Create A Waterfall Chart In Google Sheets

Update December 2023: Google has added Waterfall Charts to the native charts in the Chart Tool of Google Sheets, obviating the need for you to manually create your waterfall charts (or use apps script) per my original post.

This original post that follows was first published in late 2024, and I’m leaving it here for anyone who wants to look under the hood at how waterfall chart data is constructed and how to do that using apps script.

Original article:

In this post, we’ll look at how to create a waterfall chart in Google Sheets.

Waterfall charts are real. And useful. They show the cumulative effect of a series of positive and/or negative values on an initial starting value.

The following waterfall chart shows the headcount changes for a department, visually depicting the cumulative effect of the additions and deletions to the start value:

It shows the number of staff in our department at the start of the year (left grey bar), the number of people added from other departments or as new hires (green bars), the number of people who left (red bars) and finally the balance which is the headcount at the end of the year (right grey bar).

The waterfall chart above is relatively easy to create in Google Sheets but does still require some data wrangling to set it up. Notice that all of the bars are above the x-axis (Case 1), which makes the data set up vastly simpler than the case when we have a mix of bars above and below the x-axis, or spanning the x-axis (see Case 2 below).

I’ll show you how to create both of these cases, starting with the easier, positive-bar case.

After creating the simple and complex versions manually with formulas, I’ll show you some Apps Script code to automate the majority of the process and massively speed up creating complex waterfall charts.

Templates are available for all three methods, with links at the end of each section and at the end of this post.

Case 1: Simple Waterfall Chart

In this case, all the bars are above the x-axis.

Step 1: What does our waterfall chart data look like?

The data for our simple waterfall chart looks like this initially:

What happens if we simply create a chart from the data table above?

Well, we end up with a standard column chart, which doesn’t show what’s happening as clearly as the waterfall chart. We can’t change the color of the bars, since they’re all part of the same series:

We can do better than that!

Step 2: get the data into the right shape

Create a new data table for the waterfall chart.

Directly adjacent to the original data, make a copy of the row labels, then add four new columns: Base; Endpoints; Positive; Negative, as shown below:

Step 3: Formulas for our waterfall chart

I’ve added color-coding to distinguish the different parts of the table.

In the first and last rows, rows 2 and 8 in this example, I’ve put 0 in the Base column and the count value in the Endpoints column. The Positive and Negative columns are left blank.

For the middle rows, rows 3 to 7 in this example, put this IF formula into the Base column:

Leave the Endpoints column blank.

Put this formula in the Positive column:

=max(B3,0)

Put this formula in the Negative column:

=-min(B3,0)

Drag these formulas down.

Step 4: Create a stacked column chart

Make sure you select the stacked column chart:

Step 5: Make the base transparent

Set the Base column color to none:

Step 6: Format the chart for presentation

Now it’s simply a matter of selecting suitable colors for the other series, formatting axes and titles.

You should also remove the legend as the series labels are essentially meaningless.

The final chart looks like this:

Case 2: Complex Waterfall Chart

In this scenario, let’s look at what happens if we have negative columns or columns that span the x-axis. For example:

It doesn’t look too different from the simple one above, so what’s the big deal?

Well, you’ll notice we have positive-value columns that cross the x-axis (e.g. Revenue in above chart), negative-value columns that cross the x-axis (e.g. Cost of Sales in above chart), as well as positive- and negative-value columns above and below the axes.

So there are more variations for our formulas to handle, which makes it more complex than the first example above.

Step 1: Complex waterfall data

This time our dataset is more complex:

Step 2: Use MIN and MAX formulas to shape the data

Naturally, so are the formulas.

I embarked on this project trying to create one single formula that I could drag across and down my columns and rows that would just fill in the values. I got bogged down in seriously wacky IF statements and decided the ROI wasn’t worth it (I still think it’s possible tho!). So I created an in-between version with special formulas for the first and last row, and then general formulas for the middle rows.

The formulas I created were hideous love-children of Array Formulas, IF formulas, SIGN formulas, ROW formulas, etc… Suffice to say, sub-optimal.

So I googled around to see how other people had solved this problem and found a far more succinct, understandable set of formulas from Excel visualization guru Jon Peltier. These are the formulas I’ve included in the waterfall chart template below.

Assuming the data from Step 1 above is in range A1:B8, add the following formulas to the following cells:

Header-row:

J1: Negative Cols, Below

End-rows:

F2 to J2: Leave blank

Middle-rows:

J3: =min(0,MAX(sum($B$2:$B3),B3))

Your final data table should look like this:

Step 3: Create a stacked column chart

This will add a waterfall chart to your page, from where you can follow steps 5 and 6 of the Simple example above to finish the chart.

How-to create waterfall charts automatically with Apps Script

As you saw in the example above, the formulas are pretty finicky for a generic, catch-all scenario when we have positive and negative bars, some of which may also cross the x-axis.

Arguably a better way to solve this problem is to write a small macro, using Google Apps Script, to do all the heavy lifting, wrangle the data and insert the chart directly into our Sheet.

For example, imagine we have the following data that we want to display as a waterfall chart:

It’ll take some time to manually figure out which bars cross the x-axis and what the base bars need to be for each one. However, once I’ve written a small macro, it takes only a few seconds each time to create the chart.

I’ve added the following code to my Script Editor:

function onOpen() { ui.createMenu('Waterfall Chart') .addItem('Insert chart...','waterfallChart') .addToUi(); }; function waterfallChart() { var range = sheet.getActiveRange(); var data = range.getValues(); var newData = [['Label','Endpoints','Base','Postive Cols above','Positive Cols below', 'Negative Cols above','Negative Cols below']]; var tempTotal = 0; var tempTotalPrior = 0; var lastRow = sheet.getLastRow(); var lastCol = sheet.getLastColumn(); for (i = 1; i < data.length; i++) { tempTotalPrior = tempTotal; tempTotal += data[i][1]; newData.push([data[i][0],data[i][1],0,'','','','']); } else { var baseVal = Math.max(0,Math.min(tempTotal,tempTotalPrior)) + Math.min(0,Math.max(tempTotal,tempTotalPrior)); var val1 = Math.min(tempTotal,data[i][1]); var val2 = Math.max(tempTotal,data[i][1]); var posValAbove = Math.max(0,val1); var posValBelow = Math.min(posValAbove - data[i][1],0); var negValBelow = Math.min(0,val2); var negValAbove = Math.max(negValBelow - data[i][1],0); newData.push([data[i][0],0,baseVal,posValAbove,posValBelow,negValAbove,negValBelow]); } } sheet.getRange(lastRow - data.length + 1, lastCol + 2, data.length, newData[0].length).setValues(newData); var chartData = sheet.getRange(lastRow - data.length + 1, lastCol + 2, data.length, newData[0].length); sheet.insertChart( sheet.newChart() .addRange(chartData) .setChartType(Charts.ChartType.COLUMN) .asColumnChart() .setStacked() .setColors(['grey','none','green','green','red','red']) .setOption('title','Waterfall Chart') .setLegendPosition(Charts.Position.NONE) .setPosition(lastRow - data.length + 4,lastCol + 4,0,0) .build() ); }

View this code on GitHub.

The onOpen() function adds a custom menu to the Sheet, so you can run the waterfallChart() function from the Sheet.

This runs the chart function, wrangles the data, pastes the new data into your Sheet and finally creates a draft chart for you:

Steps to setup and use this apps script waterfall chart template:

1. Open the view-only version of this file: Embedded Waterfall Chart with Apps Script

5. Go back to your Google Sheet and you should now have a new menu option, called Waterfall Chart.

This will create new table of data and waterfall chart.

Here’s the full process again:

Links to the Google Sheet templates

Complex Numbers In Google Sheets

Complex numbers are numbers in the form a + bi, where i is the square root of -1.

What on earth does that mean?

Imaginary And Complex Numbers

To start, consider an integer, say the number 4. The square root of 4 is 2. Easy peasy.

Now consider -4. What’s the square root of that?

It’s not -2, because -2 * -2 = 4 (a minus multiplied by a minus is a positive in mathematics).

No real number will equal the square root of – 4, so we need a new number.

This new number is called an imaginary number (no, I didn’t just make that up!) and is denoted by i and defined as the square root of -1.

i = √-1

or put another way:

i² = -1

A complex number is a number that has real and imaginary parts, and can be expressed in the form:

a + bi

where a and b are real numbers and i is the imaginary number defined above.

We can look at complex numbers visually in a 2-dimensional plane, with the x-axis representing the real numbers and the y-axis representing the imaginary numbers:

Complex numbers are usually represented by the letter z, and written:

z = a + bi

Let’s go and create one in Google Sheets!

Complex Numbers In Google Sheets

Let’s create a complex number in Google Sheets.

Starting in a blank Sheet, add the numbers 3 and 2 into cells A1 and B1 respectively.

In C1, add this formula with the COMPLEX function:

=COMPLEX(

A1

,

B1

,

"i"

)

This is what our complex number looks like in Google Sheets:

Size Of A Complex Number

We do this using the Pythagorean formula for the longest side of a right angle triangle, namely the square root of the squares of the other two sides.

Google Sheets has a handy function, called IMABS, that will calculate this value for us.

Put this formula into cell D1:

=IMABS(

C1

)

It gives the result 3.605551275.

This is the same as doing the square root of the squares of the other two sides:

=SQRT(3^2+2^2)

Answer 3.605551275.

Adding Complex Numbers

The next operation we need to know how to do with complex numbers is how to add them.

What’s 3 + 2i added to 5 + 7i?

To get the answer we add the real parts and imaginary parts separately, and combine into a new complex number.

So

= 5 + 9i

Back in our Google Sheet, I’ve added a new row for the new complex number and used the IMSUM function to add them together:

=IMSUM(

C1:C2

)

which looks like this in our Google Sheet:

Squaring Complex Numbers

Another operation we need to perform on our complex numbers is to square them.

The IMPOWER function does the trick. It returns a complex number raised to a power.

In our Google Sheet, use this formula in cell D1:

=IMPOWER(

C1

,2)

which gives the answer 5 + 12i, calculated as follows:

= 5 + 12i

Extracting Real And Imaginary Parts

You can use the IMREAL function to return the real coefficient of a complex number (the “a” part).

And IMAGINARY function returns the imaginary coefficient (the “b” part).

There are other, more specialized complex numbers functions in Google Sheets too, but they’re beyond the scope of this article.

Why Are Complex Numbers Useful?

For mathematicians, complex numbers are just as “real” as real numbers.

They have applications in many areas of mathematics, physics, and engineering.

One area where you may have seen them without realizing, is in fractal geometry and specifically pictures of a beautiful set of complex numbers called the Mandelbrot set:

Here’s How To Draw The Mandelbrot Set In Google Sheets.

How We Manage Our Family Finances With Google Sheets And Tiller

“If you can’t measure it, you can’t improve it.” – Peter Drucker

Disclosure: Some of the links in this post are affiliate links, meaning I’ll get a small commission (at no extra cost to you) if you signup.

Trying to have conversations about saving, spending and planning for retirement is infinitely more difficult and more stressful without accurate numbers in front of you.

You fall back on anecdotes and feelings because you have nothing else to go on.

Conversations start with phrases like: “it feels like we haven’t spent much on eating out this month” and they don’t get any better from there.

My wife and I have two beautiful boys, aged 2 and 4, and we’re both ambitious with our careers and work full-time. Life is crazy, crazy busy for us right now.

We’ve found it challenging to find time to manage our family finances, so we’ve been in this position of flying blind without a financial tracking plan in place. We’ve had those frustrating conversations, knowing that if we had better insights into our financial habits we could do a much better job at financial planning.

I want to show you how we changed that.

How we created a system in Google Sheets for tracking our spending habits.

It now only takes us about 10 or 15 minutes each week, so we can focus on understanding our financial situation better, and maximize our saving.

Enter Tiller

Tiller is an amazing tool that connects our bank accounts and credit cards securely to Google Sheets (or Excel), and automatically updates them on a daily basis.

It means we can see all of our financial transactions in one place and do our own custom analysis in Google Sheets.

It’s been transformative for our family’s sanity and helped us get on top of our spending and hit our saving goals.

Tiller has a suite of Google Sheet templates available too, covering spending, saving, budgeting and net worth tracking, so that you can visualize your financial data immediately.

Of course, you can also build your own solutions to answer whatever questions you have.

It costs $79/year, which is tremendous value since you’re getting a fully customizable, automated personal finance tool.

How to setup Tiller with Google Sheets

Tiller is a third-party tool so you have to create an account with them, which is done securely through your Google account credentials.

This is what your homepage looks like, and where you add accounts or create new Google Sheet templates:

Once you add the bank accounts and credit cards you want to track, you can go ahead and create a new Google Sheet:

How we use Google Sheets to track our spending habits

We’ve setup categories to group our transactions, so that we can see how much we’re spending on different things at a high-level. For example, we group all restaurant expenses together into an “Eating Out” category, which allows us to see how much we spend eating out.

You want enough categories to differentiate items in a meaningful way, but not too many that you end up with too much granularity. The whole idea is to summarize transactions into something more manageable.

Each week my wife or I will jump into the Tiller Sheet and categorize any new transactions. It’s as simple as selecting the category from the drop-down menu in Transactions tab in the blank cell next to the transaction name:

You can even use Tiller’s new Autocat tool to now automatically categorize transactions for you.

Creating custom reports with Tiller and Google Sheets

I’m going to share our solution for tracking our spending habits.

It allows us to understand how we’re spending our money and identify ways to reduce it.

I created a summary table in our Tiller Google Sheet, which shows our family spending by category. It takes the data we’ve categorized in the transactions tab, which is automatically updated by Tiller, and summarizes it.

The transactions are summarized by categories in the rows, and by months across the columns. Columns A and B contain checkboxes, which I use to control which spending categories to show in my charts.

This table alone gives us more insight into our spending habits than anything the bank gives us. Every single transaction is included and categorized, by us not the bank.

You can create a table like this with the Google Sheets QUERY function, or using Pivot Tables in Google Sheets.

In my case, I’ve used a QUERY function in cell C3 to retrieve, aggregate and pivot my transaction data:

=QUERY(QUERY(

Transactions!A1:O

,

"select C, sum(D)*-1 group by C pivot K"

),

"offset 1"

,

0

)

Visualizing our spending habits in Google Sheets

I added the checkboxes in columns A and B, so there’s a way for my wife and I to choose categories to focus on.

The checkboxes can be individually checked or unchecked and they feed into other data tables that only show the data for the checked items.

Our mortgage, car lease and utility payments remain largely the same month-to-month. We know we have to pay them every month, so we don’t necessarily need to see them every time. (That’s not to say they’re not important, but trying to visualize all your categories at once will just clutter your charts to the point of being useless. )

However, seeing our discretionary spending — things like travel and eating out for example — helps us understand our spending habits and find ways to save more money in a healthy way.

We’re currently using two charts to track our spending habits:

Chart 1: Current monthly spend vs. Average monthly spend

The first is a monthly breakdown by category, showing actual spend this month (blue) against the average amount we spend in this category each month (red):

(Chart shows fictional data.)

Chart 2: Discretionary Spend by Month

The second is a look at our discretionary spending over the past few months, so we can see how selected categories are trending (chart shows fictional data):

The combination of the monthly category breakdown table and these two simple charts gives us tremendous insight into our spending habits.

Knowing how we’re spending our money gives us tremendous peace of mind.

It’s helping us to minimize our unnecessary spending and maximize our saving.

See Also:

Tiller homepage

10 techniques to use when building budget templates in Google Sheets

A guide to the super useful QUERY function

Pivot Tables in Google Sheets: A Beginner’s Guide

How to use checkboxes in Google Sheets

How To Calculate Age In Google Sheets From Date Of Birth With Formulas

If you have a list of people with their date of birth mentioned on Google Sheets, and you need to find their age, then this article will be helpful. This discussion is explained under the assumption that the dates are written dd/mm/yyyy format or dd-mm-yyyy format. It has been tested on Google Sheets and not any other similar software.

How to calculate Age in Google Sheets from Date of Birth

We can calculate the age of people on a Google Sheet page as follows:

Using the DATEDIF formula

Using the Array formula.

1] Using the DATEDIF formula

The syntax for the DATEDIF formula is as follows:

Eg. If you are creating the list of ages of the individuals in column C and the cell number of the first cell in the column of cells mentioning the dates of birth is B3, the formula would become as follows:

=IF(B3,DATEDIF(B3,TODAY(),"Y"),"")

Interestingly, the DATEDIF formula has an option if you wish to get the exact number of years, months, and days corresponding to the dates of birth. The syntax would be:

Eg. In the example mentioned above, the formula would become as follows:

=DATEDIF(B2,TODAY(),"Y")&" Years, "&DATEDIF(B2,TODAY(),"YM")&" Months, "&DATEDIF(B2,TODAY(),"MD")&" Days"

Some users might find it difficult to pull the formula across cells.

2] Using the Array formula

Unlike the DATEDIF formula, the Array formula requires you to mention all the details in the formula itself, thus making it easier to use.

The syntax for the Array formula is as follows:

Eg. If the dates of birth are listed in column B from B3 to B8, the formula will become:

=ArrayFormula(int(yearfrac(B3:B8,today(),1)))

If you wish to create the formula for an infinite number of rows, the syntax would be:

Eg. In the case mentioned above, the formula would become as follows:

=ArrayFormula(if(len(B3:B),(int(yearfrac(B3:B,today(),1))),))

The procedures mentioned in this post would be especially helpful for those who are managing employees, students, sports teams, etc.

In case you want to calculate the age containing only year (and no months and days) for a single cell only, then you can use the basic YEARFRAC function with cell number and current date. So, let’s say you want to calculate the age from the date of birth present in cell A5, then the formula will be:

=int(YEARFRAC(A5, today()))

Add this formula in a cell and hit the Enter key to get the result.

That’s it!

How do you calculate years between two dates in Google Sheets?

If you want to calculate the number of whole years between two dates in Google Sheets, then use the DATEDIF function along with cell #1 and cell #2 with year unit (Y). So, let’s say you have a date in cell C4 and the second date in cell C5, then to calculate the years between those two dates, the formula will be:

=DATEDIF(C4, C5, "Y") What is the formula age from date of birth in Excel?

To calculate the whole age from the date of birth in Excel, you can use the YEAR and NOW functions. As an example, if the date of birth is added in cell A7, then to calculate the total age from that date of birth, use the following formula:

=(YEAR(NOW())-YEAR(A7))

Make sure the cell where the date of birth is present displays a number (or formatted as a number or General), otherwise, the output won’t come.

Hope it helps!

Update the detailed information about How To Automatically Generate Charts And Graphs In Google Sheets on the Hatcungthantuong.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!