Count occurrences of words in a column and create a pie chart from it
- The column should have a title in its first cell.
- Select the column.
- Data -> DataPilot -> Start.
- Use current selection.
- 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.
- 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.
- You now have a data table in the new sheet which counts the words in the source selection.
- When the data in the source selection changes, right click the title cell of the data table and refresh.
- Select the part of the data table that you’d like to count and click the ‘Chart wizard’ button to create a chart.
Or, select the data table’s title cell and click the ‘Chart wizard’ button to create a chart. This will include the total in the chart.
Creat pie chart of timesheet in the form client|hours
Insert -> Chart.
Chart Type: Pie chart.
Data Range:
Data range is $SheetName.$A1:$AX,$Sheet1.$B1:$BX, where X is the number of rows.
Data series in columns.
First column as label.
Data Series:
Data series is Column B
Data ranges is Name blank, Y-Values $Sheet1.$B1:$BX.
Rande for Name blank.
Categories $SheetName.$A1:$AX.
Done.
To show hours in the chart itself…
Right click -> edit.
Note: This next step didn’t f**king work when I tried it a few days later - ‘object properties’ just wasn’t showing in the menu. I HATE openoffice almost as much as I hate Microsoft Office!! Could have been a change introduced by an upgrade.
Right click -> object properties.
So, instead do the next three steps…
Insert data labels.
Right click on data label.
Format data labels.
Tick show value as number.
Placement: Best fit.
Other nice options are Show category, Show legend key, Separator: New Line.
Exclude rows with zero hours
Link to the client|hours column in a new sheet (see ‘Source cell range from different sheet’ above).
Select the cells.
Data -> Filter -> Standard Filter.
Column B > 0.
Use these filtered cells as the pie chart’s data source.
NOTE: The filter doesn’t automatically update when you change the source data.
