Charts and pivot tables

DataMind Oda builds native Excel charts and pivot tables from your data, changes and deletes them, and refreshes pivot tables. They are ordinary Excel objects: they stay in the workbook and work without Oda. This page covers what Oda can set, where it puts them, what each permission mode asks and what you can undo.

Charts

Oda creates, changes, lists and deletes charts. The step row reads Working on a chart and the chart, then, for example, Chart Revenue on Sales, Changed the chart Revenue, Deleted the chart Revenue or 3 charts.

• Types: Excel's 33 chart types: column, bar, line, area, pie, doughnut, scatter, radar, bubble and stock charts, with their stacked, 100% stacked, 3-D, exploded, marker and line variants.
• Data: one block of cells whose header row names the series and whose first column holds the categories; Oda can also take the series from rows, or let Excel choose. A pie or doughnut chart shows one series; a scatter chart takes the x values in the first column; a bubble chart takes x, y and size columns; a stock chart takes its columns in Excel's order, such as date, open, high, low and close.
• Place: the chart goes on the sheet of its data unless you name another, over the cells you name, or by default over 8 columns and 15 rows to the right of the cells in use. Oda puts charts in empty cells, never over data, and names each one, so it can change it later by its name.
• Changes: the type, the data, the title, the position and size, the axes (titles, number formats, reversed categories, minimum, maximum, the step between gridlines and a logarithmic scale), the legend, data labels, the chart style, and single series: a new name, a type of their own for a combo chart, a secondary value axis or a colour.
• A chart's data must be cells of this workbook; a reference to another workbook is refused like a formula that reaches outside it.
• A legend that Excel placed in a corner or a custom place is listed as such, and Oda cannot move it there.

The card reads, for example, Add a column chart of Sales!A1:D13 to the sheet “Sales”, Change the chart Revenue on “Sales”: title, legend or Delete the chart Revenue on “Sales”. Creating and changing a chart run without a card in Accept edits; deleting one asks first in every mode except Skip all permissions.

Pivot tables

Oda creates, changes, refreshes, lists and deletes pivot tables. The step row reads Working on a pivot table and the pivot table or its source, then, for example, Pivot table RegionTotals on Summary!B3:E12, Changed the pivot table RegionTotals, Refreshed the pivot table RegionTotals or Refreshed 4 pivot tables.

• Source: a range whose first row names its columns, such as Data!A1:F500, or a table by its name, so the pivot table grows with the table.
• Destination: a cell, such as Summary!B3, or a sheet; a sheet the workbook does not have is added. Without a destination the pivot table goes on a new sheet, named after the pivot table or Pivot 1, Pivot 2 and so on, starting at A3.
• Fields: up to 16 source columns each as rows, columns and report filters, and up to 32 value fields. A value field sums its column by default, or counts, averages, takes the largest or smallest value, multiplies, counts numbers, or gives a standard deviation or variance. It can show a share of the grand total, the row total or the column total, a running total, or the difference from an item, with a number format and a caption of its own.
• Layout: compact, outline or tabular, grand totals for rows and for columns, subtotals, and a sort of a row or column field by its items or by a value field.
• Dates: to group dates, such as by month, Oda adds a helper column to the source, such as =EOMONTH(A2,-1)+1 for the first day of each month, and uses it as a field.
• A pivot table never covers cells that hold data: Oda refuses to create it, or to change it, where it could. To change its source or its destination, Oda deletes it and creates it again.
• A refresh reads the source again, for one pivot table, or for every pivot table on a sheet or in the workbook, up to 200 at once, leaving snapshot sheets out.

The card reads, for example, Add the pivot table RegionTotals at Summary!B3, summarizing Data!A1:F500; with a new sheet, it adds a line such as Add the sheet “Summary”. Creating, changing and refreshing run without a card in Accept edits, except a refresh of a pivot table with data below or right of it, which it covers if it grows: that asks first, as in Refresh the pivot table RegionTotals from its source data; if it grows, it can cover cells that hold data: Summary!G4, Summary!G5. Deleting asks first in every mode except Skip all permissions, as in Delete the pivot table RegionTotals and what it shows in Summary!B3:E12.

Listing charts and pivot tables

Oda lists the charts and pivot tables of one sheet or of the whole workbook, or one by its name, with their settings, so it can change them in place. A list of the whole workbook leaves out snapshot sheets. In Office 2021, Excel does not report which data a pivot table summarises, so the list leaves its source out. To see every kind of object at once, see Listing the workbook's objects.

Questions, answered

Where does Oda put a new chart?

On the sheet of its data, over 8 columns and 15 rows to the right of the cells in use, unless you name a place. Oda never puts a chart over data.

Can Oda make a combo chart with a second axis?

Yes. Ask, for example, Show Margin as a line on a second axis. Oda gives that series a type of its own and plots it on a secondary value axis.

Why did Oda put the pivot table on a new sheet?

You named no place for it, and a new sheet is where it cannot cover data. Name a cell, such as Summary!B3, if you want it somewhere else.

Why did Oda refuse to create a pivot table there?

The pivot table could cover cells that hold data. Name an empty place, or leave the destination out so Oda adds a new sheet.

How do I change the data a pivot table summarises?

Ask Oda to change its source. Oda changes the fields and the layout of a pivot table in place, but not its source or its place: for those it deletes the pivot table and creates it again, and the delete asks first unless you chose Skip all permissions.

Why does refreshing a pivot table ask first in Accept edits?

Data sits below or right of the pivot table, and a refresh that makes it grow would cover those cells. The card names them; move them, or allow the refresh.

Can Oda group dates by month in a pivot table?

Yes. Oda adds a helper column to the source that turns each date into its month, and uses that column as a field.

Does Revert turn remove a chart Oda added?

No. Revert turn refuses a turn that added, changed or deleted a chart. Ask Oda to delete the chart, or use Ctrl+Z in an up-to-date Excel in Microsoft 365 or in Excel on the web.