Paste special in Google Spreadsheet

They just keep adding features to Google Spreadsheet theese days - now we can paste data in a couple of new ways.

New features are "Paste all except bordes", "Paste formular only", "Paste data validation only" and "Paste conditional formatting only".

Source

Google spreadsheet can now merge cells vertically

Google Docs blog reports that it's now possible to merge cells vertically in a Google spreadsheet.

All you need to do is to select a set of cells and click on the down arrow next to the merge icon, and select Merge vertically.

Source

Getting started with custom functions in Google spreadsheet

Google spreadsheet has a very large collection of functions for you to use when you are developing a spreadsheet. There's functions for manipulation text, arrays, and dates, doing financial and statistical calculations and much more - you can see the whole list in the documentation. Although there's a lot of functions available you might run into a situation where you need to make your own function, and to do that you need to write a bit of JavaScript (about JavaScript in WikiPedia).

Lets look at a simple example: In a spreadsheet you need to convert calories (kcal in EU) to kilojoules (kJ), and instead of doing the calculation in cells (calories * 4.2) you like to use a function called CaloriesToKilojoules. Here's how to do that:

In a spreadsheet click Script editor... on the Tools menu

Create a function called CaloriesToKilojoules

function CaloriesToKilojoules(calories)

{

  return calories * 4.2;

}

Click Save on the File-menu, call the script MyFunctions and close the Script editor.

Back in the spreadsheet type =CaloriesToKilojoules(1200) in a cell and after thinking a bit the cell should have the value 5040

You could also try adding the value 1200 to a cell and in another cell type =CaloriesToKilojoules([ref]) where [ref] is a reference to a cell

As you can see its very simple to add user functions to a Google spreadsheet. This is a very basic example but there really no limits on what you can do. You need to know how to write code in JavaScript though. If you need to learn this I can recommend JavaScript, A Beginner's Guide or why not a online course (JavaScript Fundamentals from Pluralsight perhaps?).

Hope you find this useful.

Getting started on Pivot tables in Google Spreadsheet

A pivot table is a way to summarize data in either another table or a graph. Let's look at a simple example where this budget need to be summarized:

Note that there are several columns that would be nice to summarize like Category, User and Month. A pivot table could solve this problem by summarizing two or columns - here Category and User:

or by Month and Category:

In these examples the expenses are summarized but you can also choose to do a count instead. Here the same pivot table using count instead of a sum:

This was a very short introduction but it should show the power and potential in pivot tables. You can read more detailed about pivot tables at WikiPedia. Now lets see how to make a pivot table in a Google Spreadsheet.

Start of by creating a spreadsheet with some data - just like the one shown at the beginning of this post. If you need some testdata then copy them from this spreadsheet. Now select a cell within the range of data and use "Pivot table report..." on the Data-menu. This will create a new sheet and open the Report Editor.

To create a pivot table showing categories as rows, months as columns and summerizing expenses click "Add field" to the right of Rows and choose Category:

Click "Add field" beside Columns and choose Month and click "Add field" beside Values and choose Expense. Now choose the type of summerize you want (sum, count, average ect) and you are done.

Here's a short video showing you the process:

That's it - introduction to pivot tables in Google Spreadsheet - nothing to it.

Hope you find this useful.

Comment only access in documents

In a few days it should be possible to share a document with a "comment only" access - both in public documents

and in documents shared among specific users

Note that this feature is only available in documents (not spreadsheet, drawing etc) at present time.

Source

Page numbers in documents

Its now possible to add page numbers and page count to pages. You can use Page numbers and Page Count on the Insert-menu.

Source