In the past months I dedicated several post about Google Spreadsheets about how to use basic formulas, how to design a project plan structure and how to implement a gantt charts using Widgets. Yesterday my friend Ivan ask to me to dedicate a post about how to add custom charts and, in particular, dynamic charts which update themselves when an user update a table with input data.
This tutorial explains how to add custom charts on Google Spreadsheets using Widgets. If you are an Excel user you'll find this very simple!
Take a look at this spreadsheet or copy this spreadsheet in your Google Apps Account.1. From table to chart
Image to have this table with the following data about a list of products:
For each product I want to display, in the same chart, price, cost and the difference between price and cost. Ho can I do? A good way to do it is using an Area Chart.
2. Select Chart
To add an Area Chart on Google Spreadsheets click on Insert gadget and from the Gadget window select Charts > Area Chart. The chart will be added on you active sheet. Now you have to set all required parameters (Range is necessary!):
If you take a look at the table at the step 1, cost and price values are in the column D and E, so the range is:
Sheet1!D3:E10
...where Sheet1 is the name of the sheet where is the table. Now click on Apply and Close to add your chart on the current sheet. Simple, no?
3. Dynamic Charts
Now, image to have the following table which simulate the selling trend of a set of products (products details at step 1). When an user change quantity (column K) total values in the table will be updated with the new data. Price is taken from the table at the step 1 (column E), using Vertical Lookup function (Vlookup) using the ID Product (column I) as search criteria.
Total (column J) is calculated as Quantity * Price. I want to add a chart which display how revenues are allocate (for each product) on the total revenue. You can do it easy using a Pie Chart. Click on Insert gadget link and from the Gadget window select Charts > Pie Chart. The chart will be added on you active sheet. Now you have to set all required parameters (Range is necessary!):
If you take a look at the simulation table, total values are in the column J and products names in the column I, so the range is:
Sheet1!I4:J6
...where Sheet1 is the name of the sheet where is the table. Now click on Apply and Close to add your chart on the current sheet. At this point every times you change quantity values (column K) your chart will be updated with the new data.
Take a look at this spreadsheet or copy this spreadsheet in your Google Apps Account.Related content
Google Spreadsheets Tips: Gantt Chart (Microsoft Project-like) using Widget
Use Google Spreadsheets to get financial informations about companies
Gantt Chart using Google Spreadsheets and conditional formatting
Google Spreadsheets: formulas tutorial
No comments:
Post a Comment