Search code examples
google-sheetspie-chart

How To Make Pie Graph become case insensitive

I am adding pie charts into a Google Sheets page, but they are just messy.

This is because the data it is pulling from has (distinct) but minor differences between each other. To put it more easily:

  • Roofer and roofer are showing as 2 separate slices in the chart.

I want the processing to be case-insensitive, considering Roofer and roofer as the same category.

Can this be done in Google Sheets? How about excel?

Same data and sheet: https://docs.google.com/spreadsheets/d/1YlNXVs6yljh339b84YVsjiMSaYxt76n13bWGbuM-iBk/edit?usp=sharing

Solution

  • The only easy way I see to do this is to insert helper columns to clean the data. Then graph the clean data.

    Using this technique produces this result - based on graphing colC.

    Chart Based on clean data in Col C

    The formula in cell B2 - to remove spaces - is:

    =arrayformula(TRIM(SUBSTITUTE(A2:A20, " ", "")))

    The formula in cell C2 - to convert case - is:

    =iferror(arrayformula(upper(left(B2:B20, 1)) & lower(right(B2:B20, len(B2:B20)-1))),"")

    The data range on the chart is C1:C1000

    You can hide Cols A and B for display purposes. Alternatively move the helper columns to out of view columns.

    The idea with this is you type the raw data into col A as shown, and the clean data appears in ColC.