DataWorks workbooks enable you to perform multiple data analysis operations that are highly consistent with those in Excel. This reduces learning costs.

Prerequisites

A workbook is created, and data is imported to the workbook. For more information, see Create and manage a workbook and Import data to a workbook.

Background information

On the workbook editing page, you can specify the font, text alignment, number format, rows and columns, conditional formatting, and style of the workbook. You can also perform data pivoting and profiling on the workbook. For more information, see Perform data pivoting and Perform data profiling.

Go to the workbook editing page

  1. Go to the DataStudio page.
    1. Log on to the DataWorks console.
    2. In the left-side navigation pane, click Workspaces.
    3. In the top navigation bar, select the region where the target workspace resides. Find the target workspace and click Data Analytics in the Actions column.
  2. On the DataStudio page, click the Icon icon in the upper-left corner and choose All Products > Data Development > DataAnalysis.
  3. On the DataAnalysis homepage, click Experience Now. The Web Excel page appears.
  4. In the All Spreadsheets section of the Web Excel page, click the name of the workbook that you want to edit in the File Name column to go to the workbook editing page.
    If you create a workbook in this step, the workbook editing page appears after the workbook is created. For more information, see Create and manage a workbook.

Edit the workbook

On the workbook editing page, you can specify the following settings:
  • FontFont
    No. Feature Description
    1 Font Select a font from the drop-down list as needed.
    2 Font Size Select a font size from the drop-down list as needed.
    3 Bold Set text in bold.
    4 Italic Set text in italic.
    5 Underline Underline text.
    6 Strikethrough Add a strikethrough to text.
    7 Borders Add borders to selected cells.
    8 Fill Color Specify the background color of selected cells.
    9 Font Color Specify the text color.
  • Text AlignmentText Alignment
    No. Feature Description
    1 Top Align Align text vertically to the top.
    2 Middle Align Align text vertically to the center.
    3 Bottom Align Align text vertically to the bottom.
    4 Wrap Text Display long text in multiple lines in a cell.
    5 Align Left Align text horizontally to the left.
    6 Center Align text horizontally to the center.
    7 Align Right Align text horizontally to the right.
    8 Merge and Center Merge multiple cells to one cell and center the content in the cell.
  • NumberNumber
    No. Feature Description
    1 Number Format Specify the number format for selected cells. You can select General, Number, Currency, Short Date, Long Date, Time, Percentage, Fraction, Scientific, or Text from the drop-down list.
    2 Percentage Apply the percentage format to numbers.
    3 Two Decimal Places Round numbers to two decimal places.
    4 1000 Separator Display numbers with thousands separators, for example, 1,005.
    5 Currency Add a currency sign to numbers. The following currency signs are supported: yuan sign (￥), dollar sign ($), pound sign (£), euro sign (€), and franc sign (Fr).
  • Rows and ColumnsRows and Columns
    No. Feature Description
    1 Insert Row Insert one or more rows to the workbook.
    2 Insert Column Insert one or more columns to the workbook.
    3 Delete Row Delete one or more rows from the workbook.
    4 Delete Column Delete one or more columns from the workbook.
    5 Lock Row Lock the rows before the selected row in the workbook.
    6 Lock Column Lock the columns before the selected column in the workbook.
    7 Hide Row Hide one or more rows in the workbook.
    8 Hide Column Hide one or more columns in the workbook.
  • Conditional FormattingConditional Formatting
    No. Feature Description
    1 Highlight cell rules Specify the rules for highlighting selected cells.
    2 Data Bar/Color Scale Format selected cells by using data bars and color scales.
    3 Icon Set Format selected cells by using icon sets. The icon sets include directional icons, shapes, indicators, and rating icons.
    4 Clear Rule Clear the formatting. You can select Clear Rules from Selected Cells or Clear Rules from Entire Sheet from the drop-down list.
  • StyleStyle
    No. Feature Description
    1 Apply table style Apply a predefined table style to selected cells.
    2 Delete Remove the applied table style.
    3 Cell Style Apply a cell style to selected cells.
    4 Clear Clear the content or style for selected cells. You can select Clear All, Clear Content, or Clear Style from the drop-down list.
  • EditEdit
    No. Feature Description
    1 AutoSum Select an aggregation method. You can select Sum, Average, Count Numbers, Max, or Min from the drop-down list.
    2 Search Click Search or press Ctrl+F to open the search box.
    3 Sort and Filter Filter data and sort data in ascending or descending order.
    4 Clear Clear the content in selected cells.
  • ChartsCharts
    No. Feature Description
    1 Column Chart Generate a column chart based on selected cells. For more information, see Column charts.
    2 Line Chart Generate a line chart based on selected cells. For more information, see Line charts.
    3 Pie Chart Generate a pie chart based on selected cells. For more information, see Pie charts.
    4 More Click the More icon to view more chart types, including area charts, bar charts, scatter charts, and stock charts.
  • Plug-in
    The following plug-ins are provided:
    • Type Conversion: Convert the selected data to numbers or strings.
    • Intelligent Chart Recommendation: Generate a recommended chart with or without a title based on selected cells.
      Note The Intelligent Chart Recommendation plug-in is supported in the following regions: China (Shanghai), China (Beijing), China (Hangzhou), China (Shenzhen), China (Chengdu), China (Hong Kong), China (Zhangjiakou), China North 2 Ali Gov, Singapore, Indonesia (Jakarta), and Japan (Tokyo).
  • List of Shortcut Keys

    Click the List of Shortcut Keys icon to view the shortcut keys for different features.

In a cell on the workbook editing page, you can enter content or enter a formula that references values in other cells. The column headings are labeled with letters, which start with A and increase alphabetically from left to right. The row headings are labeled with numbers, which start with 1 and increase from top to bottom.

To delete, hide, or unhide specific rows or columns, select the rows or columns, right-click the row or column headings, and then select Delete, Hide, or Unhide.Row
To manage specific cells, select the cells, right-click the cells, and then select an option from the shortcut menu, such as Shear, Copy, and Paste.Menu item

Perform data pivoting

Note The Pivot feature is supported in the following regions: China (Shanghai), China (Beijing), China (Hangzhou), China (Shenzhen), China (Chengdu), China (Hong Kong), China (Zhangjiakou), and China North 2 Ali Gov.
  1. On the workbook editing page, select the data for which you want to create a pivot table and click Pivot in the upper-right corner.
  2. In the Create Pivot Table dialog box, specify the range of the data to be analyzed.
    You can set Choose Data to Select Range or Use External Data Source as needed.
    • Select Range
      Select the cells in the workbook for which you want to create a pivot table. The value of the Range parameter changes based on the selected cells.Range
    • Use External Data Source

      Select a data store of the MaxCompute, Mysql, Data Services, or PostgreSQL type as needed. If you set Choose Data to Use External Data Source, make sure that the connection to the data store or the API for obtaining the source data has been configured. For more information, see Connection configuration.

  3. Click OK. The pivot table editing page appears.
    OK
    In this example, set Choose Data to Select Range.
    • Data Source: the range that you specified in the previous step.
    • Pivot Table Fields: the names of the fields that you selected in the previous step.
    • Row: the fields whose values are used as the row headings of the pivot table. Drag a field from the Pivot Table Fields section to the Row section. Each value of the field added to the Row section occupies a row in the pivot table.
    • Column: the fields whose values are used as the column headings of the pivot table. Drag a field from the Pivot Table Fields section to the Column section. Each value of the field added to the Column section occupies a column in the pivot table.
    • Indicator: the fields whose data is summarized and displayed in the pivot table. To change the type of calculation that you want to use to summarize data from a field, move the pointer over the field in the Indicator section, click the Settings icon, and select Edit.Edit
      In the Property settings dialog box, set the Summary method parameter and click OK. By default, the name indicated by the Field Display Name parameter is in the format of Summary method:Source field name and cannot be changed.Property settings
    • Filters: the filters used to limit the data to be displayed. To create a filter, perform the following steps: Click Set Filter. In the Set Filter dialog box, click Add Condition, specify the filter conditions, and then click OK.Filters
    • Sort: the rules used to sort data. To sort data based on a field specified in the Row section, perform the following steps: Click Set Sort. In the Set Sort dialog box, specify the sorting rule for the field in the Row section and click OK.Sort

Perform data profiling

The Data Profile feature allows you to analyze the quality, structure, distribution, and statistics of data. It also allows you to preview, profile, process, analyze, and visualize data. The Data Profile feature analyzes data based on columns and allows you to view the data types and value distribution of each column.

Select the data to be analyzed and click Data Profile. The Data Profile feature displays the data types and value distribution of each column above the editing section in the form of charts and rich text.Overview
Simple mode:
  • For a column whose values are of the STRING or DATE type: The simple mode displays the values ranking top 2 based on frequency and their respective percentages, and the percentage of other values in the form of rich text. If the number of unique values exceeds 50% of the total number of values, the simple mode displays the number of unique values.
  • For a column whose values are of the INTEGER or FLOAT type: The simple mode displays the value distribution in the form of a histogram.
  • For a column whose values are of the BOOLEAN type: The simple mode displays the proportions of different values in the form of pie charts.
  • For a column whose values are of two or more data types: The simple mode displays the proportions of different data types in the form of pie charts. In addition, the system reminds you that the current column has dirty data. After the dirty data is cleared, the simple mode displays value distribution in one of the preceding forms based on the data type.
  • For a column whose values are null values: The simple mode displays the percentage of null values in red.

Click Detailed Mode in the upper-right corner. In the Data Profile dialog box, you can view the profiling result including the field name, field data type, field description, and security level of each column.

Detailed mode:
  • For a column whose values are of the STRING or DATE type: The detailed mode displays the number of fields, the numbers and percentages of unique values, valid values, and null values, and the numbers of occurrences of the values ranking top 5 based on frequency.
  • For a column whose values are of the INTEGER or FLOAT type: The detailed mode displays the number of fields, the numbers and percentages of unique values, valid values, zeros, and null values, the numbers of occurrences of the values ranking top 5 based on frequency, the statistics, and a histogram.
  • For a column whose values are of the BOOLEAN type: The detailed mode displays the number of fields, the numbers and percentages of unique values, zeros, and null values, the numbers of occurrences of the values ranking top 5 based on frequency, and a pie chart.
    Note The system considers the true and false strings and the 0 and 1 integers as values of the BOOLEAN type.