Spreadsheet functions are case sensitive.
They must be either lower or upper:
(ABS(), abs(); AVERAGE(), average();)
Excel Function Arguments Example Result Additional Information Sample # Sample Text
CHART *Values
*Axis Labels
*Height as integer
*Width as integer
(For more info see the link "Charts" in the sheet title)
"=CHART.BAR(F3:F13)" (For more info see the link "Charts" in the sheet title)
ABS Perfect "=ABS(F3)" 23 Hello World
AVERAGE number "=AVERAGE(F3:F13)" 153.54545454545453 Synonym: AVG 45 True
CEILING Perfect "=CEILING(F3:F13)" 2.345621082003618e+23 62 False
COUNT numbers_as_array "=COUNT(F2:F13)" 12 108 To High
DAYSFROM values_as_array "=DAYSFROM(2009,4,15)" 117 200 To Low
DOLLAR Perfect "=DOLLAR(F13)" $999.00 36 Perfect
FALSE "=IF(F3 < 100, TRUE(), FALSE())" true 17 number
FIXED number, decimals, noCommas? "=FIXED(F3+F13)" 999.00 Two decimal places 99 numbers_as_array
FLOOR Perfect "=FLOOR(F3-F4)" -23 Synonym: INT 100 values_as_array
HTML date "=HTML('
Hello World!!!
Hello World!!!
The value sent to the HTML function must
start and end with double quotes (""). All
element values must have single quotes
999 date
HYPERLINK html_as_string "=HYPERLINK("http://www.jquery.com", "jQuery's website")" jQuery's website -100 html_as_string
IF IF(logical_test, value_if_true, value_if_false) "=IF(F12 < 100, TRUE(), FALSE())" Can have nested IF functions. -14 url_as_string
IMG html_as_string "=IMG("http://ui.jquery.com/images/logo.gif")" The url can be sensitive to numbers. Also, on initial
load, because the image doesn't really have a size, the
outerheight can be distorted. An easy way to offset this
is to have some text in front of it that's taller than the
image :).
MAX number "=MAX(F3:F13)" 999 -21
MIN number "=MIN(F3:F13)"
N Perfect "=N(F3)"
PI "=PI()" 3.141592653589793 If you use "=PI" it will return the actual function as text, which is incorrect. Use "=PI()".
TODAY "=TODAY()" Mon Aug 10 2009 14:11:35 GMT-0400 (Eastern Daylight Time)
TRUE "=TRUE() || FALSE()" true
SUM number "=SUM(F2:F13)" 1689
ROUND Perfect "=ROUND(1.6)" 2
RAND "=RAND()" 0.7583332616753238 Synonym: RND
LIST numbers_as_array "=LIST(b3:b10)" I really didn't want to much lag on this, so to get the
values to update, just click the refresh button in the top
left of the cell bars.
LISTVAL url_as_string "=LISTVAL(D25)"
Advanced Functionality Example Result Additional Information
Direct Javascript "=jQuery.sheet.version" 0.51 The character '=' simply starts a
reference to javascript. The example here
interacts with the jS (or jQuery.sheet)
object and calls a function that
returns it's current version.
Math.PI "=Math.PI" 3.141592653589793 The Math function here is actually the
javascript Math function. So in a since, you
are writing javascript from the sheet.
Self as Function "=function(value, output) {
if (isNaN(value)) { return value; } else { return output; }
function (value, output) { if (isNaN(value)) { return value; } else { return output; } } function () { return this; }
Call a Cell as Function As many as you like. "=D31(F3, F4)" 23 The example here is calling the above cell
as a function. This makes working with
sheets extremely flexible.
jQuery.sheet() SettingsDescriptionDefaultExample
urlGetThe url of the table that is loaded into jQuery.sheet()documentation.html table:first$(obj).sheet({
urlGet: "mySheet.html"
urlSaveThe url for the sheet info to be saved at.save.html
editableMakes the sheet editable. Boolean value.true
urlBaseCssLocation of the base css file used to configure the sheet.jquery.sheet.css
urlThemejQuery UI Themeroller css theme location used to style the sheet.theme/jquery-ui-1.7.2.custom.css
urlMenuLoads the menu bar found at the top
that uses jQuery.clickMenu(). If left blank,
will not load menu.
urlMenuJsLocation of the jQuery.clickMenu plugin for use with the
menu control.
urlMenuCssLocation of the jQuery.clickMenu's style file.plugins/menu.css
urlScrollToLoads the plugin jQuery.scrollTo. Makes editing the
sheet an absolute pleasure :).
loadingThe loading html message when
jQuery.sheet() first starts.
Loading Spreadsheet...
newColumnWidthSets the width of newly added columns.120
ajaxSaveTypeSets the type of forums/ajax submit type.POST
buildSheetLets you create a new sheet. Example: '10x30' (Column
count by Row count). If you use this option it cancels out
urlGet. Must be separated by an "x" ("10x30") or be set
to boolean false. Note: Large sheets will take some
time to load.

buildSheet: "10x30"

calcOffEnables or disables the sheet calculations; on loadup
only. Boolean value. This usually isn't needed because
the values are saved when the sheet is saved.
logEnables or disables sheet log, used for
debugging. Boolean value.
titleOverrides the sheet's sheettitle attribute.""
lockFormulasTurns function edit off. This protects
your spreadsheet's supplied functions.
Note: If you create a function, after the
first edit, it will no longer be editable.
parentThe sheet's parent, needed for internal
usage, doesn't need to change.
colMarginThe width and height of standard toolbars.18
fnBeforeFunction ran just before the sheet is initialized.function () { }
fnAfterFunction ran after the sheet is initialized and styled.function () { jS.obj.formula().focus().select(); }
fnSaveFunction used for saving the spreadsheet, default is jS.saveSheet().function () { jS.saveSheet(); }
fnOpenFunction used to open a spreadsheet.function () { }
fnCloseFunction used to close the current spreadsheet.function () { }
Regular InitializationInitialization with Options
$(document).ready(function() {
$(document).ready(function() {
urlGet: "documentation.html table:first",
urlSave: "save.html",
title: '',
editable: true,
urlBaseCss: 'jquery.sheet.css',
urlTheme: "theme/jquery-ui-1.7.2.custom.css",
urlMenu: "menu.html",
urlMenuJs: "plugins/mbMenu.min.js",
urlMenuCss: "plugins/menu.css",
urlMetaData: "plugins/jquery.metadata.js",
urlScrollTo: "plugins/jquery.scrollTo-min.js",
urlScrollsync: 'plugins/scrollsync.js',
urlJGCharts: 'plugins/jgcharts.pack.js',
loading: 'Loading Spreadsheet...',
newColumnWidth: 120,
ajaxSaveType: 'POST',
buildSheet: false,//'10x30', this can be slow
calcOff: false,
log: false,
lockFormulas: false,
parent: this,
colMargin: 18, //If text size make cell bigger than this number the bars will be off on loadtime
fnBefore: function() {},
fnAfter: function() {
fnSave: function() { jS.saveSheet(); },
fnOpen: function() {},
fnClose: function() {},
joinedResizing: false //this joins the column/row with the resize bar
jQuery.sheet DependenciesPlugin File NameSize (kb)Compressed
jQuery.sheet Style Sheetjquery.sheet.css5no
jQuery UI - ThemeRoller styleMake your theme using jQuery UI
Themeroller. This is a pack of files.
(MB)Menu (optional plugin)plugins/mbMenu.min.js14yes
(MB)Menu Style Sheet (optional plugin)plugins/menu.css4yes
jQuery.metaData (A dependency of (MB)Menu)plugins/jquery.metadata.js4yes
Menu Htmlmenu.html8yes
jQuery.scrollTo (optional plugin)plugins/jquery.scrollTo-min.js3yes
jQCharts - jQuery Google Charts Plugin (optional plugin)plugins/jgcharts.pack.js6yes
scrollsync (optional plugin)plugins/scrollsync.js3yes
Total App Size:207KB<--Note:this field is dynamic :)
The below area is mainly for those that would like to
really build around the sheet. I tried to be as detailed
as possible. I will try to keep this as updates as
jQuery.sheet Object/FunctionDescription
jQuery.sheet / jSObject that holds all the functions used for
jQuery.sheet (A jQuery extension, different from jQuery.fn.sheet(), a jQuery extension).
jQuery.calculationsEngine / cEObject that holds the functions used to
calculate the table as a spreadsheet.
EMPTY_VALUEEmpty object - {}
cellIsEditIs true on the first keydown for a cell edit. Makes cell navigation very speedy!
clClass list shorthand
getResizeControlreturns the resize function needed for a Id bar resizer.
idId list shorthand
objObject list shorthand
sjQuery.sheet.settings shorthand
themeRollerThemeroller object. Used for styling.
toggleHideNot yet used, too buggy. Used for hiding and showing sheets.
versionReturns jQuery.sheet's current verison.
ERRORThe value returned when a cell fails a function.
HTMLtoCompactSourceParses the sheet's HTML to compact source. Somewhat buggy, was
included from trimpath. You should use jS.obj.pane().html() if you want the
sheet as html.
HTMLtoPrettySourceParses the sheet's HTML to pretty source. Somewhat buddy, see above...
addColumnAdds a single column. Accepts atColumn (int) & insertBefore (bool)
addColumnMultiCalls jQuery.sheet.addColumn a specified number of times.
addRowAdds a single row. Accepts atRow (int) & insertBefore (bool)
addRowMultiCalls jQuery.sheet.addRow a specified number of times.
barAdjustorRuns in memory to re-adjust the position of the cell Id bars. Still in code
but will only run if plugin scrollsync isn't used.
barResizerObject that holds the functions for bar resizing.
buildSheetReturns a jQuery object of a table.
Accepts size (string) example: "10x100" - or 10 columns by 100 rows.
calcFunction for compiling against the jQuery.calculationsEngine.
cellClickFnDecides which cellClick Function to use depending on
jQuery.sheet.settings.lockFormulas - between
jQuery.sheet.cellOnClickLocked() or jQuery.sheet.cellOnClickReg()
cellClickEnforces a cell click on a specified cell. Accepts keyCode (int). This is really for inplace arrow navigation and to make editing easier when a user presses enter. Fires on keydown.
cellEditCauses a cell to enter into an edit mode. accepts td (jQuery 'td' object)
cellEditAbandonTakes the focus away from cells, escapes the edit process. Sets the current cell to A1.
cellEditDoneOccurs when cell focus is lost. Decides if the sheet should recompile. Accepts bsheetClearActive (bool).
cellOnClickReg /cellOnClickLockedStep 1 of the cell click function. Accepts evt (jQuery click event).
cellOnClickManageStep 2 of the cell click function. On first click, sets the cell to editable. On
second click it grabs the html in the cell or formula and throws it into a
textarea and the textarea within the cell. Accepts td (jQuery 'td' Object).
cellOnMouseDownMakes multi selectable cells possible. When occurs, sets
cellSetActiveMulti to run through it's mousemove and mouseup on
document. Accepts evt (jQuery mousedown event).
cellSetActiveSets the cell Id bars and cell visually active. Accepts td (jQuery 'td' object),
loc (Array [row, col]).
cellSetActiveMultiWorks in accord with cellOnMouseDown. For use with styling the cells.
Accepts evt (jQuery mousedown event).
cellSetActiveMultiColumnFrom a column's double click event, it selects all cells within that column.
Accepts i (int) of selected column.
cellSetActiveMultiRowFrom a row's double click event, it selects all cells within that row.
Accepts i (int) of selected row.
cellStyleToggleManages all styles interactions.
Accepts setClass (string) & removeClass (string).
cellTextAreaManages leaving, and entering a textarea. Returning the most current
value. Also makes the formula disabled if textarea is active.
Accepts td (jQuery 'td' Object), returnVal (bool), makeEdit (bool), setVal (var).
deleteColumnDeletes currently selected column.
deleteRowDeletes currently selected row.
followMeUsed with jQuery.scrollTo plugin. Makes the pane follow the currently
selected cell.
formulaKeyDownThe centralized location for most keydown events. Takes advantage of
select rather than if statements to make it almost seam like nothing is
capturing these event.
Accepts e (jQuery keydown event).
getCssSimple plugin that writes a css link for those that are needed. Interacts
with any url setting of jQuery.sheet.
Accepts url (string).
getIndexTdMostly used with jQuery.calculationsEngine.
getIndexTrMostly used with jQuery.calculationsEngine.
getTdReturns the currently selected cell from a tableBody var that's sent to it.
Returns a cell very quickly.
Accepts tableBody (DOM sheet object), row (int), col (int).
getTdLocationReturns an array of 2 numbers - [row, column].
Accepts td (jQuery 'td' Object).
importColumnCreates a new column, then sets their html to a value that's sent in the
form of an array.
Accepts columnArray (Array) of values for each cell.
importRowCreates a new row, then sets their html to a value that's sent in the form of
an array.
Accepts rowArray (Array) of values for each row.
logUsed for debugging. Needs to have some timing values in the futures.
Accepts msg (var).
makeBarItemLeftCreates the functional Id bar to the left of the spreadsheet.
Accepts url (string) to detect how the sheet was imported.
makeBarItemTopCreates the functional Id bar to the top of the spreadsheet.
Accepts url (string) to detect how the sheet was imported.
makeControlsIf a cell is editable, it create the formula textarea, FX, Menu, sheetTitle.
Accepts parent (jQuery Object).
manageHtmlToTextConverts different characters for use with formula textarea/in cell edit
manageTextToHtmlConverts from different values from the formula textarea/in cell edit textarea
to HTML.
newSheetThis prompts the user for a value of sheet size.
openSheetCentralized location for all sheet traffic.
refreshLabelsColumnsResets all column labels. Used when adding new columns, deleting columns.
refreshLabelsRowsResets all row labels. Used when adding new rows, deleting rows.
saveSheetStandard function used for saving sheets. Called from jQuery.sheet.settings.fnSave(). If you have a different way that you'd like to save sheets, use the fnSave setting.
sheetClearActiveUsed to clear the themeRoller classes from the cell Id bars and to set the formula textare to ''.
sheetDecorateSet's the sheet up & syncronizing sheet size in accord with that of it's
sheetDecorateRemoveReturns a sheet back to normal html.
sheetSyncSizeToCols / sheetSyncSizeToDivsSyncs the actual sheet size from it's columns or from the resizing bars
sheetTitleGets/Sets the sheet's title if it's enabled.
tableCellA prototype object for interacting with jQuery.calculationsEngine.
tableCellProviderA prototype object for interacting with jQuery.calculationsEngine.
viewSourceA nice way of viewing source. Can be compact or pretty.
If you are not a programmer, just using jQuery.sheet for
it's spreadsheet purposes, just ignore the "Dependency"
Cell Navigation Result Dependancy
Left Arrow Active cell moves left if possible. jQuery.sheet.cellClick()
Right Arrow Active cell moves right if possible. jQuery.sheet.cellClick()
Up Arrow Active cell moves up if possible. jQuery.sheet.cellClick()
Down Arrow Active cell moves down if possible. jQuery.sheet.cellClick()
Escape Active cell is removed from focus. jQuery.sheet.cellEditAbandon()
Enter Starts in-place edit / Active cell moves
down if possible.
jQuery.sheet.formulaKeyDown() private function enter()
Ctrl + Enter Ends in-place edit / Active cell moves
down if possible.
Tab Active cell moves right if possible. jQuery.sheet.cellClick()
Feature InfoDependancy
jQuery.sheet is Re-sizable! Click and drag on the cell Id bars, and it will
resize the row.
Charts Click the "Charts" above for more info.jGCharts plugin
Auto Scroll When you navigate to a cell the
spreadsheet pane automatically scrolls to
jQuery.scrollTo plugin
Multi cell select If you drag your mouse over a range of
cells, it will select them for you. You can
use this to change their style.
Multi cell select from cell Id bars Double click on the mouse Id bars and it
will select the range of cells associated
with it.
jQuery.sheet.cellSetActiveMultiColumn() or
Add Row Adds a row to the bottom of your spreadsheet.jQuery.sheet.addRow()
Insert Row Inserts a row just below the currently selected row.jQuery.sheet.addRow()
Add Multi Row Adds multiple rows to the end of the spreadsheet.jQuery.sheet.addRowMulti()
Delete Row Deletes the currently selected row.jQuery.sheet.deleteRow()
Add Column Adds a column to the last column of the spreadsheet.jQuery.sheet.addColumn()
Insert Column Insert column just after the currently selected column.jQuery.sheet.addColumn()
Add Multi Column Adds multiple columns to the last column in the spreadsheet.jQuery.sheet.addColumnMulti()
Delete Column Delete the currently selected spreadsheet.jQuery.sheet.deleteColumn()
Style cells Cells are styleable.jQuery.sheet.cellStyleToggle()
Support of jQuery UI theme You make the spreadsheet look like you want.
Pick your theme here: http://ui.jquery.com/themeroller
jQuery UI Theme / jQuery.sheet.themeRoller
Sheet Title You can change the sheet's title.jQuery.sheet.sheetTitle()
jQuery.sheet A jQuery Spreadsheet with Calculations jQuery.sheet on jQuery's Website
Version 0.4
Written By Robert Plummer My jQuery Project Website
Written Using Notepad++
Compressed UsingYUI Compressor
CompatibilityFirefox 3, IE 7, Chrome, Safari
About MeProfessional Services
Bugs?Bugs Page
Want to see a feature added?Features Page
Need support?Support Page
Chart TypeExampleChartNotesSample DataSample Legends
Simple Vertical Bar Chart"=CHART.BAR(G2:G17)"Each of the char types accepts the following variables in this order:

*Values - as array (ie "a1:a2") or jagged/multidimensional arrays (ie "[a1:a2, b1:b2]")
*Legend - as array from string (ie "['First Legend Label', 'Second Legend Label']") or from cell values (ie "a1:a2")
*Axis Labels - as array, similar to legend
*Height as integer
*Width as integer

EXAMPLE: =CHART.BAR(a1:a2, "Sales", "2009", 100, 100)
Simple Horizontal Bar Chart"=CHART.BARH(G2:G17)"One thing to note about dynamic images it that column sizing can get a bit messed up.2Dec2001
Simple Stacked Vertical Bar Chart"=CHART.SBAR(G2:G17)"3Jan2002
Simple Stacked Horizontal Bar Chart"=CHART.SBARH(G2:G17)"4Feb2003
Simple Line Chart"=CHART.LINE(G2:G17)"5Mar2004
Simple Pie Chart"=CHART.PIE(G2:G17)"7May2005
Advanced Simple Vertical Bar Chart"=CHART.BAR([G2:G7], ["Dec 2008 Sales" , "Jan 2009 Sales", "Feb 2009 Sales", "Mar 2009 Sales", "May 2009 Sales", "June 2009 Sales"])"7June2006
Advanced Horizontal Bar Chart"=CHART.SBARH([G2:G7, G2:G7, G2:G7, G2:G7, G2:G7], i2:i7, H2:H7, 850,250)"7.5
Advanced Vertical Bar Chart"=CHART.BARH([G2:G7, G2:G7, G2:G7, G2:G7, G2:G7], i2:i7, H2:H7, 350,500)"8
Advanced Line Chart"=CHART.LINE([[105.7,97.9],[108.1,101.6],[110.7,102.9],[111.0,93.7],[110.0,89.8],[109.0,90.7],












[164.7,166.3],[168.5,165.8]], ["Dec 2008 Sales" , "Jan 2009 Sales"])"
A bit more complicated :)8.2
Advanced Pie Chart"=CHART.PIE([G2:G5, g6:g8, g9:g11, g12:g15], '', ["Dec 2008 Sales" , "Jan 2009 Sales", "Feb 2009 Sales", "Mar 2009 Sales", "May 2009 Sales", "June 2009 Sales"], 400,200)"8.5