# User Guide This guide is intended for the end user of the Spreadsheet. The document provides information about the functionalities of the component for data entry and management. ## Conventions This guide uses the following conventions: | Item | Convention | | :---------------------------------------- | :----------------------------- | | Buttons, menus, tabs, dialog titles | **boldface** font | | Keyboard buttons, names of functions | `screen` font | | Selection sequences of buttons or options | **Data** > **Data Validation** | ## Overview The Kendo UI Spreadsheet is used for the implementation of related data and its visualization in a tabular format (rows and columns). This kind of data structuring provides for an easier management and analysis. The component renders the touch and feel of a [Microsoft Excel table](https://support.office.com/en-ca/article/Overview-of-Excel-tables-7ab0bb7d-3a9e-4b56-a3c9-6c94334e492c) by offering many cell-formatting options, styles, and themes while utilizing simpler and easy-to-master user interface. ![Kendo UI for jQuery Spreadsheet UI components](https://www.telerik.com/kendo-jquery-ui/documentation/assets/c83685d883fef6e7bd3feeef1d0f7f80/spreadsheet-components.png) ## Actions * [Sheets](https://www.telerik.com/kendo-jquery-ui/documentation/controls/spreadsheet/end-user/user-guide#sheets) * [Cells](https://www.telerik.com/kendo-jquery-ui/documentation/controls/spreadsheet/end-user/user-guide#cells) * [Rows](https://www.telerik.com/kendo-jquery-ui/documentation/controls/spreadsheet/end-user/user-guide#rows) * [Columns](https://www.telerik.com/kendo-jquery-ui/documentation/controls/spreadsheet/end-user/user-guide#columns) * [Fonts](https://www.telerik.com/kendo-jquery-ui/documentation/controls/spreadsheet/end-user/user-guide#fonts) * [Filters](https://www.telerik.com/kendo-jquery-ui/documentation/controls/spreadsheet/end-user/user-guide#filters) * [File import and export](https://www.telerik.com/kendo-jquery-ui/documentation/controls/spreadsheet/end-user/user-guide#file-import-and-export) * [Other](https://www.telerik.com/kendo-jquery-ui/documentation/controls/spreadsheet/end-user/user-guide#other) ### Sheets When you open the Spreadsheet, the component automatically generates a worksheet and applies to it a collection of default settings. | What you want | How to get there | | :------------ | :--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | Select | Click **Select All** \[ ] located to the left of column header **A** and above row header **1** on the active sheet. | | Create | Click **+** on the Sheet Tab Bar to create a new sheet. | | Rename | Use one of the following options:
  1. Double-click the tab of the sheet you want to rename and modify it.
  2. Press `Enter` to exit, or click anywhere on the sheet.
Or:
  1. Click the `down-arrow` icon \[] on the right side of the tab for the sheet you want to rename.
  2. Select the `Rename` command.
  3. In the `Rename Sheet` popup, enter the new name of the sheet and click the `Rename` button.
| | Reorder | Use one of the following options: Or:
  1. Click the `down-arrow` icon \[] on the right side of the tab of the sheet you want to reorder.
  2. Select the `Move Right` or `Move Left` command to change the sheets' order.
| | Delete | Click the `down-arrow` icon \[] on the right side of the tab for the sheet you want to delete. Select the `Delete` command. | | Hide | Click the `down-arrow` icon \[] on the right side of the tab for the sheet you want to hide and select the `Hide` command. | | Unhide | To find the list of all sheets, including the hidden ones, click the \[] icon at the beginning of the Sheets Bar. You can select a sheet from the All Sheets menu list to unhide it. | ### Cells | What you want | How to get there | | :------------------------------ | :------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | Select/activate | Click a cell. | | Select a range of cells |
  1. Click a cell.
  2. Extend the selection by holding down the mouse while marking the selection you want.
| | Edit | Double-click a cell. | | Copy content |
  1. Select a cell.
  2. Click **Copy** \[ ] on the **Toolbar**, or right-click the desired cell to display the context menu and choose **Copy** from it, or use the [`Ctrl` + `C`](https://www.telerik.com/kendo-jquery-ui/documentation/controls/spreadsheet/end-user/list-of-shortcuts) keyboard shortcut.
| | Cut content |
  1. Select a cell.
  2. Click **Cut** \[ ] on the **Toolbar**, or right-click the desired cell to display the context menu and choose **Cut** from it, or use the [`Ctrl` + `X`](https://www.telerik.com/kendo-jquery-ui/documentation/controls/spreadsheet/end-user/list-of-shortcuts) keyboard shortcut.
| | Paste content |
  1. Click the cell where you want to place the copied or cut content.
  2. Click **Paste** \[ ] on the **Toolbar**, or right-click the desired cell to display the context menu and choose **Paste** from it, or use the [`Ctrl` + `V`](https://www.telerik.com/kendo-jquery-ui/documentation/controls/spreadsheet/end-user/list-of-shortcuts) keyboard shortcut.
| | Delete content |
  1. Select a cell.
  2. Press **Delete** on your keyboard to remove its content.
| | Align content |
  1. Select a cell.
  2. Click **Alignment** \[ ] on the **Toolbar**.
  3. Choose a style form the drop-down list.
| | Wrap content |
  1. Select a cell that contains the content you want to wrap.
  2. Click **Wrap Text** \[ ] on the **Toolbar**.
| | Merge cells |
  1. Select the cells you want to merge.
  2. Right-click the selection> **Merge**.
  3. Select the option from the **Merge Cells** pop-up window.
| | Merge cells (via **Toolbar**) |
  1. Select the cells you want to merge.
  2. Click **Merge Cells** \[ ] on the **Toolbar**.
  3. Choose an option from the drop-down list.
| | Add a background color |
  1. Select a cell.
  2. Click **Background** \[ ] on the **Toolbar**.
  3. Choose a color from the drop-down list to apply to the cell background.
| | Choose border styles and colors |
  1. Select a cell.
  2. Click **Borders** \[ ] on the **Toolbar**.
  3. Select the border style and/or the color from the drop-down list.
  4. Click anywhere on the sheet to exit.
| ### Rows | What you want | How to get there | | :------------------------------ | :-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | Select/activate | Click the row header against of the row you want to select. | | Insert |
  1. Select a row by clicking on the respective row header.
  2. Click the **Insert** tab from the **Menu**.
  3. Click **Add Row Below** \[ ] or **Add Row Above** \[ ] on the **Toolbar**.
| | Delete |
  1. Right-click the row header.
  2. Choose **Delete**.
| | Delete (via **Toolbar**) |
  1. Select a row by clicking on its row header.
  2. In the **Menu** click **Insert**.
  3. On the **Toolbar** click **Delete Row** \[ ].
| | Hide |
  1. Select the rows by clicking on the respective row header.
  2. Right-click the row header.
  3. Choose **Hide**.
| | Wrap content |
  1. Select a row.
  2. Click **Wrap Text** \[ ] on the **Toolbar**.
| | Resize the height of a row |
  1. Select a row or a cell from a row.
  2. Hover the pointer over the row header field until either the upper or bottom field border is highlighted.
  3. Click the highlighted area and hold while dragging it to the desired height. Release the hold.
| | Add a background color |
  1. Select a row.
  2. On the **Toolbar** click **Background** \[ ].
  3. Select the color from the drop-down list.
| | Choose border styles and colors |
  1. Select a row.
  2. Click **Borders** \[ ] on the **Toolbar**.
  3. Select the border style and/or the color from the drop-down list.
  4. Click anywhere on the worksheet to exit.
| ### Columns | What you want | How to get there | | :------------------------------ | :------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | Select/activate | Click the column header against the column you want to select. | | Insert |
  1. Select a column by clicking on the respective column header.
  2. Click the **Insert** tab from the **Menu**.
  3. On the **Toolbar** click **Add Column Left** \[ ] or **Add Column Right** \[ ].
| | Delete |
  1. Select the columns by clicking on the respective column header.
  2. In the **Menu** click **Insert**.
  3. On the **Toolbar** click **Delete Column** \[ ].
| | Hide |
  1. Select a column by clicking on the respective column header.
  2. Right-click the column header.
  3. Choose **Hide**.
| | Wrap content |
  1. Select the column whose content you want to wrap.
  2. Click **Wrap Text** \[ ] on the **Toolbar**.
| | Resize the width of a column |
  1. Select a column or a cell within a column.
  2. Hover the pointer over the column header field until either the left or right border is highlighted.
  3. Click the highlighted area and hold while dragging it to the desired width. Release the hold.
| | Add a background color |
  1. Select a column.
  2. On the **Toolbar** click **Background** \[ ].
  3. Select the color from the drop-down list.
| | Choose border styles and colors |
  1. Select a column.
  2. Click **Borders** \[ ] on the **Toolbar**.
  3. Select the border style and/or the color from the drop-down list.
  4. Click anywhere on the sheet to exit.
| ### Fonts | What you want | How to get there | | :--------------- | :----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | Choose fonts |
  1. Select the cells to which you want to apply the font.
  2. On the **Toolbar** click **Font** \[ ].
  3. Choose a font type from the drop-down list.
| | Change font size |
  1. Select the cells to which you want to apply the font size.
  2. Use one of the following options:
| | Format fonts |
  1. Select the cells where you want to format the font.
  2. Use any of the following commands:
| ### Filters | What you want | How to get there | | :----------------- | :----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | Sort fields |
  1. Click **Data** on the **Tab Bar**.
  2. Select a column.
  3. Click **Sort** \[ ] on the **Toolbar** and choose any of the two options from the drop-down list:
| | Unsort fields | | | Filter information |
  1. Click **Data** on the **Tab Bar**.
  2. Select the columns to which you want to apply the filtering criteria.
  3. Click **Filter** \[ ] on the **Toolbar**.
  4. Choose from the drop-down list of options:
    5. | ### File Import and Export You are able to export the content of your Spreadsheet to Portable Document Format (PDF) or Excel format (.xlsx) files, as well as import Excel Workbook content to the Spreadsheet. | What you want | How to get there | | :------------ | :------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | Import files |
    1. Click **Open** \[ ] on the **Toolbar**.
    2. From the dialog box select the Excel Workbook you want to import by clicking on it.
    3. Click **Open** to load its content.
    | | Export files |
    1. Click **Export** \[ ].
    | ### Other | What you want | How to get there | | :----------------------------------------- | :---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | Auto fill |
    1. Insert textual, numeric, or textual and numeric characters of one and the same pattern in at least two subsequent column cells. For example, 1, 2.
    2. Select these cells.
    3. To copy the data series automatically, drag the fill handle across, down, or up the cells you want to fill.

    Note that the **Auto Fill** command copies the format of the cells with the initial data series and will apply it to the target ones.

    If you select just one cell and drag the fill handle to auto-complete adjacent cells, the command copies, but does not change in the existing pattern, the content from the initial cell to each of the target ones.

    | | Freeze panes |
    1. Select the row or column which you want to freeze.
    2. From the **Toolbar** click **Freeze Panes**.
    3. Choose the option from the drop-down list.
    | | Apply the `Undo` function | Click **Undo** \[ ] on the **Menu** to reverse the actions you made during your current session. The number of actions you can reverse using `Undo` is unlimited. | | Apply the `Redo` function | Click **Redo** \[ ] on the **Menu** to repeat the actions you made during your current session. The number of actions you can repeat using `Redo` is unlimited. | | Increase decimal values | Click **Increase Decimal** \[ ] on the **Toolbar** to increase the number of digits after the decimal point. | | Decrease decimal values | Click **Decrease Decimal** \[ ] on the **Toolbar** to decrease the number of digits after the decimal point. | | Customize the format of numbers |
    1. Select the cells where you want to format the font.
    2. Click **Custom Format** \[ ] on the **Toolbar**.
    3. Select an option from the drop-down list. The style of formatting is hinted to the right of each option.
    | | Further customize the format of numbers |
    1. Select the cells whose content you want to format.
    2. Click **Custom Format** \[ ] > **More Formats**. Choose any of the following options:
    | | Apply formulas and functions |
    1. Select a cell.
    2. Go to the **Formula Bar** and type an equal sign \[ `=` ].
    3. Type your formula/function and press `Enter` once done to see the outcome of your input in the cell. Note that when you start writing a function, the `AutoComplete` prompts you with the syntax.
    | | Edit formulas and functions |
    1. Double-click a cell to display the formula/function in the **Formula Bar**.
    2. Change the formula/function and press `Enter`.
    | | Validate data | Data validation allows you to predetermine the format and constrain the value of the content of a single cell or cells within a range, column, or row:
    1. Select a cell or a range of cells.
    2. In the **Menu** click **Data** > **Data Validation** to open the **Data Validation** window.
    3. Choose the criterion from the drop-down list of options.
    4. A new window with criteria-specific options appears. Use the prompts and drop-down options to set the rules for modifying the content.
    | | Choose data from predefined values |

    The Spreadsheet supports the implementation of custom editors. They facilitate your input of data in a cell by providing you with predefined date and list values that are already placed in the necessary format.

    For example, if you are asked to fill in a date in a cell, clicking the icon of the custom editor of that cell provides you with a calendar. Choose the desired date without bothering about the syntax you are supposed to use to fill it in.

    1. Select a cell.
    2. An icon appears right next to it. Click on it to see the options that are predefined for you to choose from. | | Define names for cells and ranges of cells |

      It is possible to define a custom name for a cell or a range of cells in the Spreadsheet. As a result, instead of typing the range in formulas later on, you can directly refer to the range by its name.

      1. Select a cell or a range of cells by using the mouse. For example, select the `A1:C3` range.
      2. Click on the **Name Box** of the Spreadsheet.
      3. Type in the name of the range that is currently selected. For example, name the range **MyRange**.
      4. Press `Enter`.
      5. Go to a cell outside the range and type in the **Formula Bar** a formula which refers to the range by its name. For example, `=SUM(MyRange)+2`.
      | | Delete names of cells and ranges of cells |
      1. Select the name of the cell or the range of cells from the **Name Box**.
      2. Click **X**.
      |
      > * To close an active drop-down list or menu or exit an open window and then return to the worksheet, click **Cancel** whenever displayed as a command. > * To apply an options you have already selected, click **Apply** whenever displayed as a command. ## Formulas and Functions The Spreadsheet supports many of the Excel formulas and functions as seen in the [List of Formulas and Functions](https://www.telerik.com/kendo-jquery-ui/documentation/controls/spreadsheet/end-user/list-of-formulas). ## Keyboard Navigation The Spreadsheet supports many of the Excel keyboard shortcuts as seen in the [List of Keyboard Shortcuts](https://www.telerik.com/kendo-jquery-ui/documentation/controls/spreadsheet/end-user/list-of-shortcuts). ## Known Limitations * Because of specific requirements, browsers might not support the **Paste** command when it is initiated from the context menu or from the **Toolbar**. If you try to apply the command in either of these ways, a popup message is displayed informing you about the limitation. Use the keyboard navigation to achieve the desired result. * The Spreadsheet uses a JavaScript `Number` object which has a certain precision limitation (restriction). In general, the `Number` object persists its precision up to 16 digits. Numbers longer than 16 digits get converted to exponential numbers and lose their precision. Because the widget relies on a `Number` object, it gets the same precision limitation. This limitation comes from JavaScript and cannot be worked around in a feasible way. * Formulas that are very deeply nested, such as `=sin(cos(sin(cos(...))))` or `=A1+A2+A3+...+A200`, might produce a stack overflow error. Even though the latter does not seem nested, it is internally treated as `=((((...(A1+A2)+A3)+A4)+...+)+A200)`). **Solution** Use `SUM` when your case is similar to the second example of the nested formulas above. If to use `SUM` is not possible, avoid nesting values more than 100 levels deep. Note that the maximum depth depends on the browser, but one hundred is considered to be a safe limit. ## See Also * [List of Keyboard Shortcuts](https://www.telerik.com/kendo-jquery-ui/documentation/controls/spreadsheet/end-user/list-of-shortcuts) * [List of Formulas and Functions](https://www.telerik.com/kendo-jquery-ui/documentation/controls/spreadsheet/end-user/list-of-formulas)