This tutorial is a step-by-step guide which illustrates how to use some useful formulas in order to simplify the way you work with spreadsheet tools. I also added some interesting links to my previous posts about the same topic.

In this tutorial I used Zoho Spreadsheet, a free and powerful on-line spreadsheets tool, but you can reuse all concepts in the most common spreadsheet tools (Google Spreadsheets, EditGrid, Microsoft Excel, OpenOffice Spreadsheets, etc.)

You can download this tutorial for Microsoft Excel and OpenOffice Spreadsheets or take a look at the on-line spreadsheet.

1. vlookup()

Vertical LookUp - vlookup() - is a powerful formula to check if a specific value is contained in the first column of an array. The function then returns the value to the same line of a specific array column named by index. For example take a look at the following image.


Add this formula into the cell F2:


"Translated" is: find the value contained in the cell D2 looking in the interval A1:B4 and return the value to the same line in the column with index 2 (in this case, A:index =1, B:index=2 so the formula return the value in the column B).

The formula returns #N/A if the name in input doesn't exist in the table:


Take a mind: You can use exactly the same formula with Microsoft Excel. But if you use Google Spreadsheets you have to change the syntax of all formulas in this tutorial changing the char which separates formula parameters (;) with (,). For example using Google Spreadsheets you may to use this syntax:

2. type()

Type() returns the type of value. For example, if an error occurs, the function returns a logical or numerical value. In the VLOOKUP example, if you don't like to see #N/A you can use type() to create a custom message if the value doesn't exist in the table:



Add this formula in the cell F4:


...and the result in the cell F4 is 16 if F2 is equal to #N/A. Then in the cell F6 add this formula:


You can also embed into a single formula directly into the cell F2:

3. hlookup()

Horizontal look up - hlookup() - is the horizontal version of VLOOKUP. The formula searches for a value and reference to the cells below the selected area. The function returns then the value in a row of the array, named in the Index, in the same column. For example take a look at the following table:



Add the following formula in the cell B6:


If the name you are looking for in the cell A6 doesn't exist in the table the result will be #N/A.

4. Subtotal()

Subtotal() calculates subtotals in a specific range of cells. If a range already contains subtotals, these are not used for further calculations. Take a look at this table:



Add this formula into the cell B2:


The first parameter (9) is a number that stands for the function SUM. You can change it with the following values, depending on the operation you have to do:

- 1 AVERAGE
- 2 COUNT
- 3 COUNTA
- 4 MAX
- 5 MIN
- 6 PRODUCT
- 7 STDEV
- 8 STDEVP
- 9 SUM
- 10 VAR
- 11 VARP

The result will be 12+23 = 35

Then, copy and paste the previous formula into the cells B5 and B8. Now to obtain the total of all subtotals add this formula in the cell B11:

5. Choose()

Choose() returns a value from a list of up to 30 values. The value to return is identified with an index (an integer from 1 to 3o). For example, in this example I want to choose the value contained in the range A2:A9 in the position 4 (index=4):


Copy and paste this formula in the cell E2 (the index of the value you have to choose is contained in the cell C2):


The result is "Micheal".

Take also a look at the following links to find other resources on Woork about the topic "spreadsheets":

Google Spreadsheets Tips: Add custom charts
Google Spreadsheets Tips: Gantt Chart (Microsoft Project-like) using Widget
Use Google Spreadsheets to get financial informations about companies
Google Spreadsheets Tips: invert word position using formulas
Google Spreadsheets: formulas tutorial
Gantt Chart with EditGrid online spreadsheets
Implement a Project Plan and manage activities with Google Spreadsheets
Project Management: a project plan with Excel (template)
Gantt Chart using Google Spreadsheets and conditional formatting
Project Management: Excel Gantt Chart Template

Longtime I was looking for an ultra portable alternative to my iMac to use mostly when I am not at home. My first intention was to buy a MacBook Air: great design, great quality... but also "great" price. Some week ago a friend of mine suggested to me to take a look at some Netbooks, in particular at the Compaq Mini, and I remained really impressed by this litte jewel. So, yesterday I decided to buy it.

First impressions: I can say the Compaq Mini is not a simple low-cost pc (399 EUR versus 1.699 EUR of a MacBook Air!) to use only to connect you to the internet. Compaq Mini is a powerful netbook you can use to do everything you do with your primary desktop PC or notebook. These are some of main characteristics:

- Processor 1.6GHz Atom N270
- 1GB RAM
- 60GB drive
- 1,024 x 600 display
- Bluetooth
- WiFi
- Integrated webcam
- Built-in microphone
- Windows XP

The first impression is to have a "micro" PC with all features of a normal notebook or desktop PC. You can use all your preferred applications such as Dreamweaver, Photoshop, Excel, Word, Outlook... without problems. The display is very clean and luminous, perfectly readable. This is a screenshot of my desktop:



Let me do this little clarification: price difference between Compaq Mini and MacBook Air is obviously motivated by a lot of factors. But if you are just looking for a portable alternative to your primary computer... I think a MacBook Air is very too expensive, considering you can have "same" functionalities with a considerably lower price in a low-cost pc.

Applications: In these hours I'm stressing my new netbook working with some applications I often use in my work and to update my blog with new posts and tutorials. I installed old versions of Photoshop and Dreamweaver (Photoshop CS and Dreamweaver 8) I used some year ago to design websites, and Microsoft Office 2007 which I bought in order to use it on my iMac with a version of Windows XP I never installed :). System response is awesome. You can work with Dreamweaver 8 and Photoshop CS at the same time without problems.


I'm writing this post on Blogger using Internet Explorer 8 and at the same time using Photoshop to modify images and Media Player 11 to listen music from a web radio. I am not noticing any slowdown or delay.


Microsoft Office 2007: It's a pleasure to use Office applications in this little netbook. Word, Excel and Outlook work perfectly. If you prefer to use open source alternatives try OpenOffice, but if you have Microsoft Office I suggest you to install and use it. It's without doubt the best office suite in circulation and is not so "heavy" to compromise system performances.


Mobile Phone integration: integration with Mobile Phone is very simple. I'm using ActiveSync 4.5 to syncronize contacts, messages, activities and everything with my Samsung SGH-i780 mobile phone which I also use to connect me to internet (if there is not a Wi-Fi hotspot available), and browsing the Net with the same speed of an ADSL connection. In this way I can work to my blog, talk with my friends using Skype, take a look at my banking account and make a lot of other things in every place where I am.

After all, Compaq Mini is an ultra portable and convenient alternative with a killer price to carrying your primary notebook PC everywhere you go. If you want to know more info about this little jewel I suggest you to take a look at the HP official page.

Today I received some requests about The Woork Handbook template I published yesterday. Some readers asked to me to distribute that template for their eBooks. So in this post I want to illustrate some guidelines to create an awesome eBook template, quickly and in few steps.

I prepared the template which you can download (for Apple Pages and Microsoft Word) and reuse it for your eBooks (I used Apple pages to write the eBook!).

Download Apple Pages template Download Microsoft Word template

eBook Format

You can print the eBook on A4 format page (2 pages for each facade). This is the screenshot:

Document structure

The document is divided in sections. Each section is a chapter. Use "section break" to separate each chapter in this way:


Adding a section break:

- Apple Pages: Insert > Section Break
- Microsoft Word: Insert > Break > Section break (next page)

Page header

This is a not necessary element but i like have a short description in the header of the my pages. It's a simple text on top of your page with above a line which separete the header from the rest of the page:


In general "page header" isn't placed on the first page of the chapter:

- Apple Pages: select layout button on the inspector and flag the option: First Page is different.


- Microsoft Word: double click on the page header and select different first page from the header/footer toolbar.

Chapter structure using document styles

When you work with a text document (using Word, Apple Pages, OpenOffice...), a very good practice is to define your custom styles for the elements of your pages. In this way, every time you decide to modify the look of an element (for example "chapter title"), it will be update automatically in all elements of your document which use that style. For example take a look at the following picture which represents a generic chapter structure:

Table of contents

Adding a TOC it's very simple:

- Apple Pages: Insert > Table of content.
- Microsoft Word: Insert > Document element > Table of content

Your TOC will be updated automatically every time you add a new content in your document but (this is really important!) you have to use style elements created for the chapter title and article title (Heading 1 and Heading 2).

It's all! I hope this template can help you to prepare quickly your eBook.

Every suggestion is appreciated :)

Note: I used Apple Pages to create my eBook, and I simply converted Pages template for Word. I experienced there are some litte problems with formatting which you convert a document in Word from Apple Pages... tell me if you have them!