Knowledge Center

Working with Data

This section deals with the main features that Office provides for you to work with your data in your spreadsheets.

Data Summary

When you select a range of cells that contain data, you can quickly see the sum, average, min, max, count, count numbers, and range at the lower-right corner.

To switch between the different data summaries:

Click the scroll arrow icon and select the desired data type.

To copy the data summary value:

Click on the data type to copy the value.

Sorting a Spreadsheet

To sort a spreadsheet:

  1. Open a spreadsheet.
  2. Highlight the range of cells you want to sort.
  3. Click the Data menu > Sort, or simply right-click the selected cells and choose Sort.
  4. Select the column you want to be sorted first and whether to sort that column in ascending or descending order.
  5. Specify more columns in the Then by drop-down menu if you want to add more sorting rules. Sorting will be prioritized according to the order of your rules.

Note:

  • You cannot sort a range containing merged cells.

Filtering Data

To filter data:

  1. After selecting a range in a spreadsheet, click the Data menu > Filter or simply click the Filter icon in the toolbar to create a filter.
  2. Click the filter selector (small upside down triangle) in the column header within the selected range.
  3. Filter a column in the following ways:
    • Sort data: Click Sort A to Z or Sort Z to A.
    • Filter by color: Click Fill color or Text color, and select the color to filter by.
    • Filter by condition: Click the upside down triangle to select a condition from the drop-down menu, and fill the Value field, if applicable.
    • Filter by value: Check/uncheck values on the list, or type a value into the search field to find the value you to keep or hide. You can also check/uncheck all checkboxes by checking/unchecking Select all.

Note:

  • If you filter a range by condition and by value at the same time, Spreadsheet will filter the range by condition only.
  • When using filter by condition, you cannot enter a formula in the Value field.
  • A filter does not apply to cells whose value is changed after the application of the filter. For example, if you apply a filter with the condition "Greater than 3", and then you change a cell's value from 4 to 2, this cell will still be displayed.
  • Filter view is also supported, allowing you to filter data without influencing the way other users view the same spreadsheet. After selecting a filter in Filter view, you can use Filter view options to make changes to the filters, such as renaming, updating range, making duplicates, deleting filters, or obtaining a link to the filter view to share with others.

To filter data only for yourself:

You can temporarily filter data without saving your filter. The filtered results will only be visible to you and will not be saved.

  1. After you have created a filter, click the filter selector (small upside down triangle) in the column header within the selected range.
  2. Toggle to turn on Filter only for me and click OK.

Using Data Validation

To apply data validation:

  1. After selecting a range in a spreadsheet, click the Data menu > Data validation, or simply right-click the range and choose Data validation.
  2. Enter a range into the Cell range field. Alternatively, click the grid icon to select a range. Press and hold Ctrl to select multiple ranges.
  3. Select a rule type from the Rule drop-down menu.
  4. Select whether you want to show a help message. Each rule type has a corresponding default help message, which you can edit.

    Note:

    • Help message will be displayed as a tooltip message when you move the mouse over an empty cell.
    • If a cell already contains an invalid value, help message will be displayed as a warning instead (see below).
  5. Select whether you want Spreadsheet to Show warning or Reject input when you enter data that contains an invalid value.
  6. Click Save.

    7. Note:

    • If you select Show warning, there will be a red triangle on the upper-right corner of the cell. Move the mouse over the red triangle to view the warning message.
    • If you select Reject input, a dialogue will pop up to reject the data that you just entered.

Applying Conditional Formatting Rules

To apply conditional formatting:

  1. Click the Format menu > Conditional formatting to open the settings window.
  2. Click Create to create a new conditional format rule, or double-click an existing rule to edit it.
  3. Enter a range into the Cell range field. Alternatively, click the grid icon to select a range. Press and hold Ctrl to select multiple ranges.
  4. Select a rule type from the Rule drop-down menu.
  5. If the selected rule type contains a value, enter a value in the Value or formula field. A value may be one of the following types:
    • Number
    • Text
    • Formula (must start with the equals sign = )
  6. Configure the formatting style you want to apply to the cells that meet the above criteria.
  7. Click OK.

Note:

  • If different rules have overlapping ranges, Office gives priority to the rule that occurs highest on the rule list. You can drag and drop rules to change the order.
  • Conditional formatting style overrides the style originally applied to a cell. For example, the original background color of a cell is blue, but since this cell meets the criteria of a conditional format rule which applies green background color to cells, the background color of this cell will become green.

Defining Range Name

To define a range:

  1. Open the Data menu > Defined range name.
  2. Click Create and enter a name you prefer.
  3. Select or enter the cell reference of the range you want to define in the Range field.
  4. Click OK.

Limitations on rule names:

  • The first character of a rule name has to be a letter, a backslash, or an underline, while the other characters can be letters, numbers, periods, and underlines.
  • Rule names shall not be cell references, pure numbers, or Boolean constants, for example, A5, R2C3, 123, or TRUE.
  • Upper and lower case letters are not differentiated. Therefore, “NamedRange” and “namedrange” will be considered the same.

Limitations on defined ranges:

  • The cell reference of a defined range must include the name of the corresponding spreadsheet, such as sheet!A1:B2.
  • Formulas are not supported as range values.

Remove Duplicates

To remove duplicates:

  1. Select the range that you want to check for duplicated data.
  2. Click the Data menu > Remove duplicates.
  3. If the selected range includes a header row, select Data has header row.
  4. Select the column that you want to check for duplicates. The rows with duplicated values in the selected column will be removed.
  5. Click Remove.

Split Text into Columns

To split text into columns:

  1. Select the cell or column that contains data you want to split.
  2. Click the Data menu > Split text into columns.
  3. In the Delimiter field, select a delimiter from the drop-down menu or manually enter one. A delimiter can be any alphabet, number, or symbol. You can use multiple delimiters by pressing Enter after entering each delimiter.
  4. Select Treat consecutive delimiters as one if you want to do so.
  5. Click OK.

Note:

  • To prevent any text from being split, wrap the text with quotation marks. For example, "text".
  • If there are consecutive delimiters in your data, you can select Treat consecutive delimiters as one to avoid splitting text into extra blank columns. For example, selecting this option for 2,,,,,3 will return the same result as 2,3.

Inserting Charts

Office supports 11 types of charts to help you present numeric data visually and thus enrich your spreadsheet.

  • Column chart
  • Stacked column chart
  • Bar chart
  • Stacked bar chart
  • Area chart
  • Stacked area chart
  • Line chart
  • Stacked line chart
  • Pie chart
  • Scatter chart
  • Combo chart

To insert a chart:

  1. Select the cells with data that you want to create a chart for.
  2. Click the Insert menu > Chart, or simply click on the Chart icon from the toolbar. This will open the chart editor.
  3. In the Chart tab, choose a chart type. From here, you can also edit the data range and decide whether you want to use the leftmost column or the top row as headers.
  4. Switch to the Advance tab to edit the chart title, the x-axis title, and the y-axis title. If you have chosen a combo chart, you can also adjust the two chart types within the combo chart.
  5. Preview the chart on the right side of the window.
  6. Click Apply.
  7. If you want to edit the chart again, click on the chart and then click Edit to open the chart editor.

Import Data from another Synology Spreadsheet

You can import data from another Synology Spreadsheet with the IMPORTRANGE function.

To import data from another Synology Spreadsheet:

  1. Open the spreadsheet you are working on.
  2. Enter =IMPORTRANGE into an empty cell.
  3. Add the following parameters in quotation marks and separated by a comma. Wrap all parameters in parentheses.
    • The URL of the spreadsheet from which to import data (e.g., https://example.com/oo/r/abc123abc123).
    • The name of the reference sheet and the range of cells to import (e.g., Sheet1!A1:C3).
    With the above examples, the entire function looks like =IMPORTRANGE("https://example.com/oo/r/abc123abc123","Sheet1!A1:C3")
  4. Press Enter on your keyboard.
  5. Click Connect Content to import the data.

To refresh connections to external Synology Spreadsheet:

  • Click the Data menu > Manage Links. There are three ways to refresh connections.
    • If you want all external data to be automatically updated on a regular basis, select Auto refresh.
    • To manually update all external data right away, click the Refresh all icon.
    • To update a specific linked spreadsheet right away, click the ⋮ icon next to the spreadsheet and click Refresh.

To break connections to external Synology Spreadsheet:

  • Click the Data menu > Manage Links. There are two ways to break connections.
    • To disconnect all the external data right away, click the Break all links icon. The data will be pasted as array values.
    • To break connection to a specific linked spreadsheet right away, click the ⋮ icon next to the spreadsheet and click Break link. The data will be pasted as array values.

Group Data in a Synology Spreadsheet

Spreadsheet supports up to eight levels of grouping to help you manage your data.

To create a group, follow these steps:

  1. Select the the rows or columns that you want to group.
  2. Right-click on the selected rows or columns and choose Group.

To remove a group, follow these steps:

  1. Select the the rows or columns that you want to ungroup.
  2. Right-click on the selected rows or columns and choose Ungroup.
Data Summary
Sorting a Spreadsheet
Filtering Data
Using Data Validation
Applying Conditional Formatting Rules
Defining Range Name
Remove Duplicates
Split Text into Columns
Inserting Charts
Import Data from another Synology Spreadsheet
Group Data in a Synology Spreadsheet