How do I create a pie chart / circle graph from a more complex table with multiple columns? (categories in expense report)

I suggest adding a second table that summarizes the data by category. This does two things:

1) arranges the data and categories into a single table already "set up" for use in a chart

2) allows you to continue adding entries in the original data table while being able to plot as a pie chart

Assuming the data you enter is in a table named "Table 1", create a second table (I named it "Data Summary").

Make the first row and the first column Headers.

Copy the categories to column A (remember to add new categories if you add an additional ones)

B2=SUMIF(Table 1::C, A2, Table 1::B)

this is shorthand for... select cell B2, then type (or copy and paste from here) the formula:

=SUMIF(Table 1::C, A2, Table 1::B)

to fill down:

select cell B2, copy

select cells B2 thru the end of column B, or as needed, paste

now select columns A and B and add the pie chart from the charts menu

Perhaps you can carefully read my post and try it.

Notice I posted two reasons. (2) addresses having other entries.

You should make sure that the tables have the first row as a header row and that the summary table has the first row and first column set as headers

Hi B'

Some Numbers vocabulary:

Spreadsheet: Also called 'Document' or 'Spreadsheet Document': The entire document contained in a Numbers file.

Sheet: a large 'canvas' which can contain one or more Tables, plus other objects such as text boxes, Charts or Images.

Every Document contains at least one Sheet.

Each Sheet has a name, which is displayed on a Tab above the workspace.

The Tab contains nothing except the name of the Sheet to which it is attached (plus a pop-up menu marked v).

The default name of a Sheet is "Sheet n" where "n" is a serial number starting with 1 for the first Sheet.
The names can be changed to give a better indication of what work the Tables on that sheet do.

Table: A set of one or more Cells, arranged in columns and rows.

Each Sheet, when created, contains at least one Table.
Each Table has a Name. The default name of the first table on a Sheet is Table 1. It is recommended that each

Table in a document be given a distinct name, as this makes formulas shorter by removing the need to include

the Sheet name when addressing cells on another Sheet.

Data is placed in Cells in Tables. The Tables are placed on Sheets.

The full address of a cell has three parts, separated by double colons: sheetname::tablename::column and row

Numbers needs only enough of the full address to locate the cell, and will drop the unneeded parts automatically.

Here is an example using the data table you provided, and a second small table to summarize the data and feed it to the pie chart. All three objects, the two tables and the chart, are on the same table. Because the tables have distinct names, the formula used on the summary table will work without change if that table is placed on a separate sheet.

The formula in B2 of Expense Summary is:

SUMIF(Expense log::C,A2,Expense log::B)

"Expense log" is the name of the data table.

The formula is filled down to B3 an B4.

Category names in column A of Expense Summary must exactly match the category names in column C of Expense log.

The chart was created by selecting cells B2-B4 of Expense summary, then clicking the Charts button and choosing the Pie Chart. I made no changes to the chart except to reduce the size to make it fit in the space under the Expense summary table.

Regards,

Barry

bananaforsale wrote:

I will check this out, thanks!

edit: It appears to be the same issue; the categories are already totaled in that template, but they are not in my table. Is there a way to have the individual entries totaled by category instead of doing it by hand?

The categories are not already totaled in the template.

Here's the pie chart, showing the data source in column C of the Summary table.

Here's how the Summary table gets its values (from the Transactions table on the second sheet (tab):

And here's the Transactions table on the second sheet:

Notice how the Summary table used the SUMIF formula to automatically total the two items under the category Food.

In that template you already have a working document and all that needs to be done is change the Category names in the Pop-up Menu in column C of the Transactions table and enter matching category names in the Summary table. No need to reinvent the wheel, unless you like doing that.🙂

SG

If you are using Numbers 3 or higher, recommend having a look at the 'Personal Budget' template at File > New in your menu. It does almost exactly what you describe, after you change the categories to match what you want.

SG

Barry wrote:

All three objects, the two tables and the chart, are on the same table.

I think you have a typo there and meant to write:

All three objects, the two tables and the chart, are on the same sheet.

SG

"I think you have a typo there and meant to write:

All three objects, the two tables and the chart, are on the same sheet.

Correct. Thanks for the edit.

Barry