Google Sheets Questions & Answers

Free Google Sheets Video Tutorials!

What formulas can you use for the financial year beginning 1st April?

A lot of businesses financial year starts on the 1st of April, so when you need to create calculations in Google Sheets you might need some functions to take that into account.

Try the following:

Open a blank worksheet in Google Sheets and in cell A1 Enter the Title Today's Date and in the cell beneath put in today's date.
(Tip: Hold down Ctrl & ; to automatically enter today's date.

First thing is to work out the Fiscal month, enter that as the next heading and beneath add in the following formula in cell B2.

=IF(MONTH(A2)<4,9+MONTH(A2),MONTH(A2)-3)

As you can see if the month is August in the new fiscal year the month is May or 5. You can use this as the basis for the Quarter and the Year. In Cell C1 enter the following:

=ROUNDUP(B2/3,0)

And finally the fiscal year in the next column.

=IF(MONTH(A2)<4,YEAR(A2)-1,YEAR(A2))

So as you can see with the above we at first check to see if the year is less than 4 then we minus one from the year. Otherwise we leave the year as it is.

Finally you can use the Date function to produce a date:

=DATE(D2,B2,DAY(A2))

You can then AutoFill (Copy down) the formula so that the result looks like:

There you have it, various fiscal calculations that might be handy please let us know if you feel there's any mistakes or things I could have done a little better.

How to adjust this formula if your financial year starts on April 6th.

---

The UNIQUE function in Google Sheets

The unique function is a handy function should you want to list unique values. Pretty straightforward to use and create a result as follows:

=UNIQUE(A:A)

Where A:A is a list of values. The above could be use to produce:

This is fantastic when you want to use this in conjunction with COUNTIF.

The above can be created by adding into cell D2 the formula

=COUNTIF(A:A,C2)

---

How do I link different files in Google Sheets?

You can link data from 2 completely different Google Sheet files by using the IMPORTRANGE function.

Try the following:

Open two Google Sheets files in different tabs in Chrome.

To make things clear, name one sheet data and the other display.

On the Data sheet on Sheet1 write the days of the week.

On the Display sheet in cell A1 write the following:

=IMPORTRANGE("

Remember to type the opening double quote.

Now swap back to the data sheet and copy the address of the data sheet as indicated in the picture below:

Copy from the https to the number but not including the forward slash after the number.

Now swap back to display and paste into your formula and complete the formula so that your formulas reads:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1r3WoZUBwTcqoWKV-y93syhhCs3e4iD_iiRFCkILGig4","Sheet1!A1:A7")

Note that you have to add the sheet name and the range you want to link at the end after a comma.

Should you get a #REF error click in the cell and click the Allow Access button. This should get the formula working.

You can change the range at the end so if you change the data sheet by adding numbers to the right of the days of the week.

You can swap back to the display sheet and adjust the formula to pick up the numbers column as well.

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1r3WoZUBwTcqoWKV-y93syhhCs3e4iD_iiRFCkILGig4","Sheet1!A1:B7").

---

Using the Filter Function in Google Sheets

You know, there are some functions in Google Sheets that don't exists in Excel. The Filter Function is one of them.

What does the Filter Function do?

Say you have a load of data on one sheet like this:

And you want to filter all of the orders of John Wilson like this:

First you have to type in the column headers then type the following in A2.

=FILTER(Data!A2:J1000,Data!B2:B1000="John Wilson")

Note the two ranges in Bold, these need to be the same rows so the following wouldn't work.

=FILTER(Data!A2:J1000,Data!B2:B999="John Wilson")

The Filter function is a way of extracting data from another sheet or even from another Google Sheet.

Below is the full function used in the from the Filter Function video tutorial.

=IF(NOT(ISBLANK(L2)),
FILTER(Data!$A$2:$D$1000,Data!$B$2:$B$1000=L2,Data!$D$2:$D$1000>L5),
FILTER(Data!$A$2:$D$1000,Data!$D$2:$D$1000>$L$5)
)