DataWorks workbooks allow you to perform multiple data analysis operations with ease. These operations are highly consistent with those in Excel. This greatly reduces learning costs.

Prerequisites

A workbook is created, and data is imported to the workbook. For more information, see Create 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 a workbook. You can also perform data pivoting and profiling on the workbook. For more information, see Data pivoting and 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 your workspace resides, find the workspace, and then 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. Choose Web Excel > All Spreadsheets and click the name of the required workbook to go to the workbook editing page.
    If you create a workbook, the workbook editing page appears after the workbook is created. For more information, see Create 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 as needed.
    2 Font Size Select a font size 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 text.
    8 Fill Color Specify the background color of text.
    9 Font Color Specify the text color.
  • Text AlignmentText Alignment
    No. Feature Description
    1 Top Align Align text to the top.
    2 Middle Align Align text vertically to the center.
    3 Bottom Align Align text to the bottom.
    4 Wrap Text Display long text in multiple lines to make it easy to view all the text.
    5 Align Left Align text to the left.
    6 Center Align text to the center.
    7 Align Right Align text to the right.
    8 Merge and Center Merge multiple cells to one cell and center the text 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, such as 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 selected rows from the workbook.
    4 Delete Column Delete one or more selected columns from the workbook.
    5 Lock Row Lock one or more selected rows in the workbook.
    6 Lock Column Lock one or more selected columns in the workbook.
    7 Hide Row Hide one or more selected rows in the workbook.
    8 Hide Column Hide one or more selected columns in the workbook.
  • Conditional FormattingConditional Formatting
    No. Feature Description
    1 Highlight cell rules Includes Highlight Cells Rules and Top/Bottom Rules.
    2 Data Bar/Color Scale Includes Gradient Fill, Solid Fill, and Color Scales.
    3 Icon Set Includes icons in the Directional, Shapes, Indicators, and Ratings categories.
    4 Clear Rule Includes Clear Rules from Selected Cells and Clear Rules from Entire Sheet.
  • StyleStyle
    No. Feature Description
    1 Apply table style Apply a table style.
    2 Delete Delete the table.
    3 Cell Style Set the cell style.
    4 Clear Includes Clear All, Clear Content, and Clear Style.
  • EditEdit
    No. Feature Description
    1 AutoSum Supports Sum, Average, Count Numbers, Max, and Min.
    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 selected content.
  • ChartsCharts
    No. Feature Description
    1 Column Chart For more information, see Column charts.
    2 Line Chart For more information, see Line charts.
    3 Pie Chart For more information, see Pie charts.
    4 More Click the More icon and select one of the following chart types:
  • Plug-in
    Type Conversion and Intelligent Chart Recommendation plug-ins are supported.
    • Type Conversion: Click the Type Conversion icon and select Convert to Numeric or Convert to String.
    • Intelligent Chart Recommendation: Click the Intelligent Chart Recommendation icon and select Remove the Title or Retain the Title.
      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 from A and increase alphabetically from left to right. The row headings are labeled with numbers, which start from 1 and increase from top to bottom.

You can right-click a row or column heading and select an option to delete, hide, or show the row or column.Row
You can right-click one or more selected cells and select an option to cut, copy, or paste data.Actions

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 data to be analyzed.
    You can select Select Range or Use External Data Source.
    • Select Range
      When you select the cells for which you want to create a pivot table, the value of the Range parameter changes accordingly.Range
    • Use External Data Source

      You can select a data store of the MaxCompute, MySQL, Data Services, or PostgreSQL type. If you use an external data store, prepare the required connection or API in advance and select the connection or API based on the business requirements. For more information about connections, see Connection configuration.

  3. Click OK. The pivot table editing page appears.
    OK
    In this example, select Select Range.
    • Data Source: the range of the selected data in the Excel file.
    • Pivot Table Fields: the names of the fields in the selected data.
    • Row: Drag a field to the Row section. Each value of the field added to the Row section occupies a row in the pivot table.
    • Column: Drag a field to the Column section. Each value of the field added to the Column section occupies a column in the pivot table.
    • Indicator: To modify the settings of an indicator, move the pointer over the indicator, click the Setup icon, and then 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: To filter data, click Set Filter. In the Set Filter dialog box, click Add Condition, specify the filter conditions, and then click OK.Filters
    • Sort: To sort a field specified in the Row section, click Set Sort. In the Set Sort dialog box, specify the sorting rule and click OK.Sort

Data profiling

The Data Profile feature allows you to analyze the quality, structure, distribution, and statistics of data. This feature 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 in the menu bar. Then, you can view the data types and value distribution of each column at the top of the workbook in the form of charts and rich text.Overview
The simple mode of data profiling has the following features:
  • For the STRING or DATE data type: displays the values ranking top 2 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 number of unique values is displayed.
  • For the INTEGER or FLOAT data type: displays the value distribution in the form of a histogram.
  • For the BOOLEAN data type: displays the proportions of different values in the form of pie charts.
  • For mixed data types that involve two or more data types: displays the proportions of different data types in the form of pie charts. 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 null values: 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.

The detailed mode of data profiling has the following features:
  • For the STRING or DATE data type: displays basic information and the values ranking top 5 based on frequency. The basic information includes the percentage of null values and the numbers of fields, unique values, and valid values.
  • For the INTEGER or FLOAT data type: displays basic information, the values ranking top 5 based on frequency, statistics, and a histogram. The basic information includes the percentage of null values and the numbers of fields, unique values, and zeros.
  • For the BOOLEAN data type: displays basic information, the values ranking top 5 based on frequency, and a pie chart. The basic information includes the percentage of null values and the numbers of fields, unique values, and zeros.
    Note The system considers the true and false strings and the 0 and 1 integers as values of the BOOLEAN type.