5. Data entry

5.1 Internal datasheet

Every chart created with think-cell has an associated datasheet. The datasheet is opened by double-clicking the chart or by clicking the image.  Open Datasheet button that appears when the chart is selected. The datasheet also opens immediately when a new chart is inserted.

think-cell uses a customized Microsoft Excel sheet for data input, which you can use in the same way as regular Excel. You can use all the same shortcut keys, you can enter formulas instead of numbers, and so forth. But of course you can also use an Excel file as a data source (see 21. Excel data links).

To insert or delete a row (or column) you can use the respective buttons in the toolbar of the datasheet. The standard buttons for undo and redo and cut, copy and paste are available as well.

Note: If you have Microsoft’s Chinese Conversion feature installed, you will find the options Simplified Chinese and Traditional Chinese in the datasheet’s toolbar.

5.2 Absolute and relative values

The think-cell datasheet alternatively supports entry of absolute or relative values. The distinction between the two types of data is made by the Excel cell formatting. You can always toggle the interpretation of a column’s data with the image. button.

Keep in mind that for the display in the chart, it does not matter if you enter percentages or absolute values. If you enter absolute values but want to label the chart with percentages (or vice versa), think-cell performs the necessary conversion (see 6.5.5 Label content). A simple datasheet with only absolute values looks like this:

think-cell data-sheet absolute values.

For simple charts based on absolute values only, the 100%= row on top of the chart data can be left empty. If you choose to label the chart with percentages, the percentages are calculated from the absolute values, assuming the sum of each column to be 100%. You can enter explicit values in the 100%= row to override this assumption. The following datasheet calculates percentages based on 100% being equal to a value of 50:

think-cell data-sheet absolute values with totals.

Alternatively, you can fill in the datasheet with percentages. Again, you can choose to label the chart with absolute or relative values. In order to have think-cell calculate absolute values from the percentages you entered, you should fill in the absolute values that represent 100% in the 100%= row. The following datasheet uses percentages to specify the same data values:

think-cell data-sheet relative values with totals.

The default behavior of the datasheet depends on the chart type: 100%-charts and area or Mekko charts with %-axis as well as pie charts default to percentages, while all other charts default to absolute values.

5.3 Transposing the datasheet

The layout of a think-cell datasheet depends on the chart type. In bar charts, for example, columns contain the data for a single series, while in column charts, rows contain the data for a single series. Here is a typical datasheet for a column chart:

think-cell typical data-sheet for column charts.

If you would instead like to work with a datasheet where columns contain the data for a single series, with rows representing categories, you can simply click the image.  Transpose Sheet button to transpose the datasheet, swapping the row and column data together with any category/series interpretation. Here is the transposed version of the previous column chart datasheet:

think-cell data-sheet transposed.

Note that the columns have also been flipped to preserve the relationship between the visual order of data in the datasheet and the order of the data in the chart.

5.4 Reverse order in datasheet

In think-cell, the visual order of data in the datasheet always corresponds to the order of the data in the chart. This is also true for flipped charts (see 3.3 Rotating and flipping elements). If you want to flip the data in the datasheet, you can use the image.  Flip Rows (or image.  Flip Columns) button in the datasheet’s toolbar.

think-cell datasheet flipped with Flip button highlighted.

5.5 Edit data layout

You can decide whether to include any of the special rows and columns in the datasheet, such as the Series label column, the 100%= row, or the Category label row.

To do so, click Edit Data Layout icon.  Edit Data Layout in the datasheet's toolbar, and add or remove them via the menu that appears on top or to left of the special columns and rows, respectively.

think-cell datasheet with Edit Datasheet Layout button highlighted and menu showing.

Note: The top-left-most cell that is not a special cell is always interpreted as the first data cell. This means for example that when the 100%= row has been disabled in a non-transposed datasheet, where rows represent series, the data for the first series would start in cell B2, and its series label would be in cell A2. Special rows and columns that have been removed from the datasheet are effectively treated as empty; for example, in a datasheet without a 100%= row, the sum of the absolute values will be used as the reference for calculating percentages displayed in the chart as explained in 5.2 Absolute and relative values.

5.6 Extracting numerical data from images

Let’s assume you have an image of a bar or column chart like the following somewhere on your screen, e.g., a chart on a website, in a PDF document or a reporting software window:

think-cell extract numbers tool sample input.

Using think-cell’s capture tool, you can extract the numerical data of the image and use it in a think-cell chart.

  1. Create a think-cell chart of the type you wish to use to present the extracted data, e.g., a stacked column chart. The chart type does not have to be same as the chart type in the source image.
  2. In the chart’s datasheet, click the image.  Extract and Paste Numerical Data button in the toolbar. A capture window titled Chart Scanner appears on the screen.
  3. Drag and resize the window so that it covers the chart image. It is best if you restrict the gray detection area to the chart data area, i.e., without any chart title, but including axes and labels.

    think-cell extract numbers tool during detection.

  4. Once the algorithm has finished analyzing the image, the Fill Datasheet button becomes active. In the capture window, the detected baseline is highlighted in green and detected chart segment outlines are highlighted in red.

    think-cell extract numbers tool after detection.

  5. Click the Fill Datasheet button. The extracted numbers are inserted into the datasheet window. To close the capture window, click the button in the top-right corner.

    think-cell datasheet with extracted numbers inserted.

In the chart, you can now use think-cell’s formatting to highlight aspects of the data or use difference arrows and other tools for analysis.

think-cell chart generated from extracted numbers.

Instead of filling the data into a think-cell element's datasheet in PowerPoint, you can also insert the data into an Excel sheet. To do so:

  1. Select a cell range on an Excel worksheet where you want the extracted numbers to be inserted.
  2. Click the Chart Scanner button in think-cell’s ribbon group in Excel.
  3. Follow the procedure described above to position the capture window, check the highlighted detection result and click the Paste Into Excel button.
  4. The image’s numerical data is inserted into the previously selected cell range.

You can use the data range as the source for a linked think-cell element in PowerPoint (see 21. Excel data links) or for further data analysis in Excel.

Share