Working with Data

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

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 you want that column sorted 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:

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(s) within the selected range.
  3. Filter a column in the following ways:

Note:

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:

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/values, enter a value/values in the Value or formula field(s). A value may be one of the following types:
  6. Configure the formatting style you want to apply to the cells that meet the above criteria.
  7. Click OK.

Note:

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:

Limitations on defined ranges:

Inserting Charts

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

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.