I. Overview
1) Data sources for a chart include data columns in a dataset and cell data. This article shows how to turn cell data into chart data.
2) Please refer to: Insert Charts and Insert Formulas.
II. Reference cell data
1. Design a report
1) Input text in B2~D2 by reference to the figure below. Column B lists salespersons, Column C lists products and Column D lists sales.
2) B2~D10: set center alignment and add borders. B2~D2: fill the background with grey.
2. Insert a floating chart
1) Click [Insert] > [Floating Element] > [Insert Chart].
2) Select Column Chart as the chart type.
3. Set chart data type
Select cell data as the data source.
4. Set chart data
1) Click on the formula icon after [Category] and input B3:B10 in the Formula Definition box.
2) Click [+] to add a series.
3) Click on the formula icon below [Series] and input C3:C10 in the Formula Definition box.
4) Click on the formula icon below [Value] and input D3:D10 in the Formula Definition box.
5. Preview the effect
1) Series: it represents the meaning of the data in a chart, and each series and its corresponding color are displayed in the legend. Here, products (Milk, Bread, Salt and Pepper) are series that intuitively indicate the meaning of each column, that is, these columns represent a product indicator (i.e., sales).
2) Category: it is used to group data. Each category name exhibits a label on the X axis. Here, salespersons (Tom and Jerry) are categories, and columns falling into the same category form a cluster, allowing intuitive comparison of performance between the two salespersons.
3) Value: it shows data itself and is embodied as the height of a column in a column chart.
III. Reference expanded cell data
1. Create a new dataset
1) Create a new DB Query dataset [ds1].
2) Drag the table [Sales_Volume] into the dataset.
2. Design a report body
1) Drag [Region], [Product] and [Sales_Volume] in [ds1] into B3~D3.
2) B2~D3: add borders and set center alignment. Fill the background of B2~D2 with grey.
3. Summarize a data column
1) Click D3, click [Cell Element] in the right-hand pane, define data setting as [Summary] and the method of summary as [Sum] which is the default value.
2) After summarizing the data column, the sum of sales of each product is displayed in the preview.
4. Add a chart
1) Insert a floating chart and select Column Chart as the type.
2) Set chart data:
Data Source: Cell Data
Category: B3
Series: C3
Value: D3
5. Preview
Note: The following two sections build on the template in this section, please save the template first.
IV. Reference cell data in another sheet
1. Insert a sheet
Click on the plus icon at the bottom to insert a sheet. Then, a sheet named sheet2 will be added.
2. Insert a chart
1) Insert a floating chart into sheet2 and select Column Chart as the type.
2) Set chart data:
Data Source: Cell Data
Category: 'sheet1'!B3
Series: 'sheet1'!C3
Value: 'sheet1'!D3
Note：When referencing data in another sheet, you have to add the sheet name before the cell name, wrap the sheet name with single quotes and put an exclamation mark between the sheet name and the cell name.
3. Select Data Analysis Preview
4. Preview the effect
sheet1 displays data while sheet2 displays a chart.
V. Expand a chart with a cell
1. Modify the report body
Delete the text in B2 and modify the text in C2 to Category.
2. Insert a cell chart
1) Click [Insert] > [Cell Element] > [Insert Chart].
2) Select Column Chart as the type.
3. Set chart data
Data Source: Cell Data
Category: C3
Series: "Sales_Volume"
Value: D3
4. Set the parent cell for the cell where the chart is located
Click E3, click [Expand] in the right-hand pane and customize Left Parent Cell as B3.
5. Set chart style
1) Cancel the title.
2) Change the legend position to above the chart.
3) Add a rotation angle for labels on the X axis.
6. Preview the effect
1) As the parent cell of the cell where the chart is located is Region, each Region will have a corresponding chart after expansion.
2) There is only a series Sales_Volume in the chart, so all columns are in the same color.
3) Product names are categories, so each column represents a separate group. Category names are displayed as labels on the X axis and by setting a rotation angle for these labels, category names containing more characters can be displayed completely.
Previous：Dataset as Chart Data Source
