THE SPREADSHEET KID

In this post, we explore how you can share data betwen Google Sheets, first within tabs in a spreadsheet and then between two or more Google spreadsheets. Accessing the value of a cell on sheet #1 from sheet #2 Sharing data between sheets is very easy and usually consistent whichever software you use:  if you have a sheet called "facts" and you want to insert the value of cell C2 from "facts" into "Sheet2", select a cell in Sheet2, type = and then  =facts!C2 . Most of the time, you won't even need to type anything beyond = as Excel or Google Spreadsheet let you select the cell in the other sheet with your mouse. However knowing the syntax can help you more creative things. syntax :  the name of the sheet ("facts"), an exclamation mark (!) as separator and then the cell address (column+row). Now that you know how to work between between sheets within the same spreadsheet, you are ready to do the same across spreadsheets. Click H

Importing data from one spreadsheet to another is very similar to importing data between sheets within the same spreadsheet . STEP 1 : Accessing the value of one cell which is in another spreadsheet  Google Spreadsheets allow you to create an unlimited amount of spreadsheets, share and work on them with friends and colleagues in real time. Provided you have view permission to a spreadsheet, you can import a range, a whole sheet from one spreadsheet to another without affecting the source sheet. Addressing is very similar to Step 1, but this time it uses a function called importrange(). Example Here is the  spreadsheet and sheet  we are going to pull data from. Note the value of the key= parameter which is the address of the spreadsheet (do not include the #) : https://docs.google.com/spreadsheet/ccc? key= 0Akd1gJoxWTi1dFFwUDAySGp0VVpYLXM0RHZablVjR3c #gid=0 In a new spreadsheet, we want to import the value of cell C2 from sheet "facts". First, let's create a new

In Part 1 , we saw that Pivot table was the classic spreadsheet way of aggregating and displaying numbers from many rows and columns of data. We also saw that sorting the way we wanted is not always possible. Using the query() function is one way of doing that and it opens up many other possibilities. 2. the Query() function The Query() function can easily fix our sorting problem, but it requires some basic knowledge of SQL, a special language used to extract and order data from databases. Fortunately, the syntax used in Spreadsheets is simplified : The Query() is composed of 3 parameters : =query( source of data ,  the query itself ,  the column headers if required ). Here is an example and its result below : =query(facts!A1:D50,"SELECT D, sum(C) group by D order by sum(C) desc",1) This query gets two columns, Region (column B) and the sum of Population (column C) from the sheet called "facts". It then orders the table by decreasing order of Population

Quarterly reporting is important for many businesses. While most spreadsheets can extract the day, date, month or year from a date with dedicated functions, none of them have  a built-in quarter() function .  In this article we will show you how you can easily get the quarter of the year from a date. 1. The month() function The month() function in Google Sheets gives you the month number from a date: if your cell A2 contains Sep-30 2017 or 9/30/2017, month(A2) will show "9". 2. A little math There are four quarters in a year with three months each. Month 1 is in Q1, month 5 is in Q2, month 6 is also in Q2, while month 7 is in Q3. The logic is simple enough that we want to automate this with some math. Here is the trick : if we know the month and divide the month by 3, we are almost there : We can see that March, June and December are in the right quarter number but for other months, the floating numbers are cause for concern. Well, not for long. 3. Rounding nu

Although daily events like sales or customer interactions happen on a daily basis, it's nice to get a monthly graphical perspective .  In order to build those nice graphs and monthly trends, you will need to get the month from your daily dates in a nice automated way.  In this article, we are exploring various ways in which you can transform your dates in month and more with Google Sheets. 1. The month() and year() functions The month() function displays the month number from a given date, while the year() function returns the year. You can see below how this works for months (dates are in dd/mm/yyyy). 2. Combining content with the ampersand (&) character The formula to get the year is =year(A2). If we want to combine in one single cell the year and the month, we can do this easily with the ampersand character. Combining year(A2) and month(A2) alone works, but the result is 20121 (which is not very intuitive).  To make our date clearer, we add (&) a space,

In this post, we explore how you can share data betwen Google Sheets, first within tabs in a spreadsheet and then across several spreadsheets. Accessing the value of a cell on sheet #1 from sheet #2 Sharing data between sheets is very easy and usually consistent whichever software you use:  if you have a sheet called "facts" and you want to insert the value of cell C2 from "facts" into "Sheet2", select a cell in Sheet2, type = and then  =facts!C2 . Most of the time, you won't even need to type anything beyond = as Excel or Google Spreadsheet let you select the cell in the other sheet with your mouse. However knowing the syntax can help you more creative things. syntax :  the name of the sheet ("facts"), an exclamation mark (!) as separator and then the cell address (column+row). Now that you know how to work between between sheets within the same spreadsheet, you are ready to do the same across spreadsheets.  >> Click He