For quick, ad hoc analysis on small datasets or tasks requiring a flexible, Excel-like tool to organize, calculate, and visualize data, traditional SQL queries can be cumbersome, while professional BI tools often have a steep learning curve. DataWorks Data Analysis provides the spreadsheet feature, an online Excel-like tool that lets you enter and edit data directly—much like a desktop spreadsheet—and import local files or export analysis results.
Version limits
Chart type limit: The Basic Edition supports only seven chart types. To access more chart types, upgrade to DataWorks Standard Edition or a higher edition.
Spreadsheet sharing limits: The maximum number of editors and viewers varies by edition.
Feature
Basic Edition
Standard Edition
Professional Edition
Enterprise Edition
Maximum number of editors
0
3
5
10
Maximum number of viewers
0
10
20
30
Go to the spreadsheet page
On the Data Analysis page, click Go To DataAnalysis. In the left menu bar, click the 
or 
icon to go to the Spreadsheet list page.
Create a spreadsheet
Before you perform data analysis, create a spreadsheet to hold the target data.
New Data Analysis
On the Spreadsheet page, click
On the spreadsheet editing page, after you complete the data analysis, click Save in the upper-right corner.
In the Save File dialog, enter a File Name and click OK.
Legacy Data Analysis
On the Spreadsheet page, click the
icon next to New Spreadsheet to go to the spreadsheet editing page.On the spreadsheet editing page, after you complete the data analysis, click Save in the upper-right corner.
In the Save File dialog, enter a File Name and click OK.
Import data to a spreadsheet
You can directly enter data in a spreadsheet or import data from a local file to prepare for subsequent data analysis.
On the spreadsheet editing page, click the Import button in the upper-right corner. You can import three types of files: Spreadsheet, Local CSV File, and Local Excel File.
Import a spreadsheet
In the Import dialog, click Spreadsheet and configure the parameters.
Parameter | Description |
Spreadsheet | From the Spreadsheet drop-down list, select the name of the spreadsheet to import. |
Sheet | From the Sheet drop-down list, select the sheet to import from the spreadsheet. |
Data Preview | Preview the data to import. |
Import Start Row | Import data starting from the specified row. The default value is 1. |
Placement Location | Valid values: Current Worksheet and New Worksheet. |
Placement Method | Valid values: Append, Overwrite, and Active Cell. |
Import a local CSV file
In the Import dialog, click Local CSV File and configure the parameters.
Parameter | Description |
File | Click Choose File, select the local CSV file to import, and click Open. |
Original Character Set | Valid values: UTF-8 and GBK. If garbled characters appear, switch the character set. |
Separator | Includes row and column separators:
If cell data cannot be properly separated, switch the separator. |
Data Preview | Preview the data to import. |
Import Start Row | Import data starting from the specified row. The default value is 1. |
Placement Location | Current Worksheet: The worksheet in the current visible area. New Worksheet: A new worksheet. |
Placement Method | Valid values: Append, Overwrite, and Active Cell. |
Import a local Excel file
In the Import dialog, click Local Excel File and configure the parameters.
Parameter | Description |
File | Click Choose File, select the local Excel file to import, and click Open. |
Sheet | From the Sheet drop-down list, select the sheet to import. |
Data Preview | Preview the data to import. |
Import Start Row | Import data starting from the specified row. The default value is 1. |
Placement Location | Valid values: Current Worksheet and New Worksheet. |
Placement Method | Valid values: Append, Overwrite, and Active Cell. |
Analyze data
The spreadsheet provides rich and convenient data analysis operations that are highly consistent with Microsoft Excel. On the spreadsheet editing page, you can configure font, alignment, number format, rows and columns, conditional formatting, and styles, and explore the data in the spreadsheet.
For detailed operations of each button in the spreadsheet, see Appendix: Detailed description of each button.
Formatting and styles
In the top toolbar, adjust the font, alignment, number format (such as currency and percentage), and conditional formatting of cells in the spreadsheet to make data more readable.
Use formulas and functions
As in Excel, enter = in a cell to start writing a formula. Common functions such as SUM and AVERAGE are supported.
Create charts
Select the data range to analyze.
In the top menu bar, choose Chart and select a chart type such as Bar Chart, Line Chart, or Pie Chart.
The system automatically identifies the data type and generates the chart.
ImportantIf the chart does not meet your expectations, try right-clicking the target column and selecting Convert to Numeric.
Use data profiling
The data profiling feature analyzes the quality, structure, distribution, and statistics of the current data to help you preview, explore, process, analyze, and visualize data.
Simple data profiling mode: Select the data and click Data Profiling in the top toolbar. The system automatically analyzes the type, distribution, null values, and duplicate values of each column, allowing you to quickly understand the data quality.
Detailed data profiling mode: In the simple profiling mode, click Detailed Mode in the upper-right corner to view the Field Name, Field Type, Field Chinese Name, Field Description, and Security Level profiling results for each column.
Data type | Simple mode description | Detailed mode description |
String / Date | Displayed in rich text format: | Displays detailed information across multiple dimensions: |
Integer / Float | Displayed as a binned histogram to intuitively show the range and distribution of data. | Displays detailed information across multiple dimensions: |
Boolean | Displayed as a pie chart to clearly show the proportion of true/false values. | Displays detailed information across multiple dimensions: String values of true and false, and numeric values of 0 and 1 are identified as BOOLEAN type. |
Mixed type | A pie chart displays the proportion of each data type in the column, with a prompt indicating dirty data exists. If the dirty data has been cleansed, the value distribution is displayed according to the three types described above. | Not applicable (in detailed mode, the analysis is performed based on the predefined type of each field). |
Null | The proportion of | Displayed as the null rate metric in the basic information of each type. |
View and manage spreadsheets
On the spreadsheet editing page, click Spreadsheet in the upper-left corner or the
icon in the left menu bar to go to the spreadsheet list page.On the Spreadsheet page, in the All Spreadsheets section, view the I created and Share it with me spreadsheet lists.
On the list page, you can perform the following management operations on spreadsheets:
Rename: Click the
icon next to the target file. In the Rename dialog, enter a new File Name and click OK.Change owner: Click the
icon next to the target file. In the Change Owner dialog, enter and select the target user, and click OK.Clone: Click the
icon next to the target file. A new file with the suffix _copy is created.Delete: Click the
icon next to the target file. In the Delete dialog, click OK.
Click File Name to re-enter the spreadsheet editing page.
Export, share, and download spreadsheets
After you complete the processing and analysis of the online spreadsheet, export, download, or share the data with specified users.
Export data to a MaxCompute table
The spreadsheet supports quickly generating MaxCompute CREATE TABLE statements based on the processed data. Copy the statement and use it in Data Studio to export data to a MaxCompute table. A maximum of 100 rows of data can be exported.
On the spreadsheet editing page, click in the upper-right corner.
In the Export as MaxCompute Table dialog, configure the parameters.
Insert mode
Parameter
Description
Insert data into a MaxCompute table (insert overwrite)
Workspace
Select the target workspace.
Table
Enter the name of the table to insert data into and select it.
Create MaxCompute Table and Insert Data (INSERT OVERWRITE)
Workspace
Select the target workspace.
Table Name
Enter the name of the new table. Make sure the table name is unique and click Check Duplicate Names to verify.
Click Copy SQL Statement and then Close the dialog.
ImportantOnly non-partitioned tables are supported. A maximum of 100 rows of code can be copied.
Click the
icon in the upper-left corner of the page and choose All Products > Data Development and O&M > Data Studio.Use a MaxCompute SQL node to insert data into an existing table or create a new MaxCompute table and insert data.
For operations in the new Data Studio, see Develop a MaxCompute SQL node.
For operations in the legacy Data Studio, see Develop an ODPS SQL task.
Click Submit to Development Environment and Submit to Production Environment respectively.
If you are using a workspace in basic mode, you only need to click Submit to Production Environment.
Share a spreadsheet
Scenarios for sharing a spreadsheet include:
Collaboration: Share the spreadsheet and grant edit permissions to others. For example, collect personal information of team members or activity registrations.
Read-only sharing: Share the spreadsheet and grant read permissions to others.
If a permission issue is prompted, contact the tenant administrator to go to Security Center > Data Query and Analysis Control > Query Result Control > > Query Result Control > > Query Result Control and enable Allow Sharing and Allow Download for Spreadsheet. For more information, see Data Query and Control.
On the spreadsheet editing page, click Share in the upper-right corner and configure the sharing method.
Link: After specifying editable or viewable members, or setting the spreadsheet to be visible to all, click Copy Link and send the link to the target users.
If you enable Access Code, a link that requires an access code to view is generated.
Specify editable members: To grant specific users the permission to edit the spreadsheet, click Specify Editable Members > Add. In the dialog, enter and select the members to share with, and click Confirm.
Visible to All: If the spreadsheet can be made available to all users, enable the Visible to All toggle.
The following members can view: To grant specific users the permission to view the spreadsheet, disable Visible to All and click The Following Members Can View > Add. In the dialog, enter and select the members to share with, and click Confirm.
After sharing is successful, send the link to the target users so they can access the spreadsheet through the link.
In the View Records section in the upper-right corner of the spreadsheet editing page, view which users have viewed the spreadsheet after it was shared.
On the Spreadsheet list, view the Share it with me spreadsheets.
Download a spreadsheet
On the spreadsheet editing page, click the Download button in the upper-right corner to download the current spreadsheet to your local device.
Appendix: Button details
On the spreadsheet editing page, configure the following settings:
Font
No.
Feature
Description
①
Font
Select the desired font type.
②
Font Size
Select the desired font size.
③
Bold
Bold the text.
④
Italic
Italicize the text.
⑤
Underline
Add an underline to the text.
⑥
Strikethrough
Add a strikethrough to the text.
⑦
Borders
Add a border to the text.
⑧
Background Fill
Fill the background color of the text.
⑨
Font Color
Change the color of the text.
Text Alignment
No.
Feature
Description
①
Top Align
Align text to the top.
②
Middle Align
Align text to the middle of the cell vertically.
③
Bottom Align
Align text to the bottom.
④
Wrap Text
Wrap long text across multiple lines for easier viewing.
⑤
Align Left
Align text to the left.
⑥
Center
Align text horizontally to the center.
⑦
Align Right
Align text to the right.
⑧
Merge and Center
Merge selected cells into a larger cell and center the content.
Number
No.
Feature
Description
①
Format
Select the cell format, such as number, currency, short date, long date, time, percentage, fraction, scientific notation, or text.
②
Percentage
Set the data type of the cell to percentage.
③
Two Decimal Places
Format the cell data to retain two decimal places.
④
1000 Separator
Separate thousands in cell data with commas, for example, 1,005.
⑤
Currency
Set the data type of the cell to a currency type, such as CNY, USD, GBP, EUR, or CHF.
Rows and Columns
No.
Feature
Description
①
Insert Row
Add a new row to the spreadsheet.
②
Insert column
Add a new column to the spreadsheet.
③
Delete Row
Delete the selected row from the spreadsheet.
④
Delete column
Delete the selected column from the spreadsheet.
⑤
Lock Row
Freeze all rows above the selected row in the spreadsheet.
⑥
Lock Column
Freeze all columns to the left of the selected column in the spreadsheet.
⑦
Hide Row
Hide the selected row in the spreadsheet.
⑧
Hide Column
Hide the selected column in the spreadsheet.
Conditional Formatting
No.
Feature
Description
①
Filter-based Conditional Formatting
Includes rules from Highlight Cells Rules and Top/Bottom Rules.
②
Color Fill Conditional Formatting
Includes styles from Gradient Fill, Solid Fill, and Color Scales.
③
Icon Set Conditional Formatting
Includes Directional, Shapes, Indicators, and Ratings icons.
④
Clear Conditional Formatting
Includes Clear Rules from Selected Cells and Clear Rules from Entire Sheet.
Style
No.
Feature
Description
①
Apply Table Style
Select and apply a table style.
②
Delete
Remove the applied table style.
③
Cell Style
Set the cell style.
④
Clear
Includes Clear All, Clear Content, and Clear Style.
Edit
No.
Feature
Description
①
AutoSum
Supports five types: Sum, Average, Count Numbers, Max, and Min.
②
Search
Click Search directly or use the shortcut Ctrl+F to open the search box.
③
Sort and Filter
Filter data and sort in ascending or descending order.
④
Clear
Directly delete the selected content.
Charts
No.
Feature
Description
①
Column Chart
For details, see Column chart.
②
Line Chart
For details, see Line chart.
③
Pie chart
For details, see Pie charts.
④
More
Click More and select one of the following charts:
Plug-in: Currently supports Type Conversion. Click the
icon to convert selected data to Convert to Numeric or Convert to String.List of Shortcut Keys: Click the
icon to view the shortcut keys for each feature.