libreoffice calc / oocalc pie charts

Count occurrences of words in a column and create a pie chart from it

The column should have the title in its first cell e.g Animals:

Animals
Cat
Dog
Cat
  1. Select the column.
  2. Data -> Pivot Table -> Create… (in ye olde OpenOffice days this used to be Data -> DataPilot -> Start).
  3. Select Source: Current selection. OK.
  4. A ‘Pivot table’ window will pop up to define the data fields. There will be a button at the top with the name of the first cell in the column we selected earlier, called ‘Animals’ in our example.
    1. Drag it into the ‘Row fields’.
    2. Drag it into the ‘Data fields’, click the ‘Options’ button and select count.
    3. Click the ‘More’ button and set the results to go into a new sheet.
    4. OK.

You now have a data table in the new sheet which counts occurrences of the words in the source selection:

Animals Count - Animals
Cat 2
Dog 1
Total result 3

When the data in the source selection changes, right click the title cell of the data table and refresh.

Pie chart from count

  1. Select what data you’d like to chart.
    1. Either: Select the part of the pivot table that you’d like to chart.
    2. Or: Select the pivot data table’s title cell. This will include the total in the chart, assuming the pivot table was configured to include the total (as it does by default).
  2. click the ‘Chart’ button to create a chart (or Insert -> Object -> Chart…).

Creat pie chart of timesheet in the form Date | Time | Category

The following example assumes your data in following format:

Date Time Category
15/02/2015 6 Development
16/02/2015 2 Documentation
16/02/2015 5 Development

The category can obviously be anything - work type, client code, project, etc.

Create totals for each category

  1. Select Time and Category columns. Note: It seems that you have to select contiguous columns, but that doesn’t matter - just make sure you include all columns you need and you can ignore the others later.
  2. Data -> Pivot Table -> Create…
  3. Select Source: Current selection.
  4. Pivot table:
    1. Drag ‘Category’ button to ‘Row Fields’ box.
    2. Drag ‘Time’ button to ‘Data Fields’ box.
    3. Expand the options with the ‘More’ button.
      • ‘Ignore empty rows’ (you don’t need any other option).
      • ‘Results to new sheet’ if required e.g. $E$1.

Create pie chart

  1. Insert -> Object -> Chart…
  2. Chart Type: Pie.
  3. Data Range:
    • Data range: $E$1:$F$100 (choose a range that covers the pivot table you created).
    • ‘Data series on columns’.
    • ‘First row as label’.
    • ‘First column as label’.
  4. Finish.

This should be enough to create the chart. It will have auto-created the correct data series. If it’s not looking correct then change the data range values via right-click on the chart -> Edit, right-click -> Data Ranges…

Data series, Sum-Hours spent -> Data ranges, Name, Range for Name: $E$1 (the first cell of the pivot table).

Data series, Sum-Hours spent -> Data ranges, y-Values, Range for y-Values: $E$2:$E$10 (a range covering the ‘Sum - Time’ column - second column - of the pivot table).

For both the Name and y-Values the Categories is: $E$2:$E$10 (a range covering the ‘Category’ column - first column - of the pivot table).

Show numbers, percentages or categories in the chart

  1. Right-click chart -> Edit.
  2. Right-click chart -> Insert Data Labels.
  3. Right-click chart -> Format Data Labels…
  4. Data Labels for Data Series ‘Sum - Time’:
    • Select ‘Show value as number’, ‘Show value as percentage’ or ‘Show category’.

Last modified: 16/02/2015 Tags: , ,

This website is a personal resource. Nothing here is guaranteed correct or complete, so use at your own risk and try not to delete the Internet. -Stephan

Site Info

Privacy policy

Go to top