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:
- Open a spreadsheet.
- Highlight the range of cells you want to sort.
- Click the Data menu > Sort, or simply right-click the selected cells and choose Sort.
- Select the column you want to be sorted first and whether you want that column sorted in ascending or descending order.
- 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:
- 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.
- Click the filter selector (small upside down triangle) in the column header(s) within the selected range.
- Filter a column in the following ways:
- Sort the column: Click Sort A to Z or Sort Z to A.
- 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 viewing the same spreadsheet.
Using Data Validation
To apply data validation:
- After selecting a range in a spreadsheet, click the Data menu > Data validation, or simply right-click the range and choose Data validation.
- 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.
- Select a rule type from the Rule drop-down menu.
- 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).
- Select whether you want SpreadSheet to Show warning or Reject input when you enter data that contains an invalid value.
- Click Save.
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:
- Click the Format menu > Conditional formatting to open the settings window.
- Click Create to create a new conditional format rule, or double-click an existing rule to edit it.
- 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.
- Select a rule type from the Rule drop-down menu.
- 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:
- Number
- Text
- Formula (must start with the equals sign = )
- Configure the formatting style you want to apply to the cells that meet the above criteria.
- 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:
- Open the Data menu > Defined range name.
- Click Create and enter a name you prefer.
- Select or enter the cell reference of the range you want to define in the Range field.
- 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.
Inserting Charts
Office supports 11 types of charts to help you present numeric data visually and thus enrich your spreadsheet.
- Colum 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:
- Select the cells with data that you want to create a chart for.
- Click the Insert menu > Chart, or simply click on the Chart
icon from the toolbar. This will open the chart editor.
- 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.
- 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.
- Preview the chart on the right side of the window.
- Click Apply.
- If you want to edit the chart again, click on the chart and then click Edit to open the chart editor.