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 |
- Select the column.
- Data -> Pivot Table -> Create… (in ye olde OpenOffice days this used to be Data -> DataPilot -> Start).
- Select Source: Current selection. OK.
- 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.
- Drag it into the ‘Row fields’.
- Drag it into the ‘Data fields’, click the ‘Options’ button and select count.
- Click the ‘More’ button and set the results to go into a new sheet.
- 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
- Select what data you’d like to chart.
- Either: Select the part of the pivot table that you’d like to chart.
- 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).
- 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
- 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.
- Data -> Pivot Table -> Create…
- Select Source: Current selection.
- Pivot table:
- Drag ‘Category’ button to ‘Row Fields’ box.
- Drag ‘Time’ button to ‘Data Fields’ box.
- 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
- Insert -> Object -> Chart…
- Chart Type: Pie.
- 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’.
- 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
- Right-click chart -> Edit.
- Right-click chart -> Insert Data Labels.
- Right-click chart -> Format Data Labels…
- Data Labels for Data Series ‘Sum - Time’:
- Select ‘Show value as number’, ‘Show value as percentage’ or ‘Show category’.