1) Select your data (or table) and navigate to Data tab in Excel.
2) Under "Data" tab, select Get Data -> From Other Sources -> From Table/Range option. This will open the Power Query editor with the selected data.
💡 Do you know you can use a simple VBA Macro to count total words in a cell? Check this out.
3) In the Power Query editor, go to Add Column and click Custom Column option. It will open the "Custom Column" window.
4) In the Custom Column window, enter the (below) custom formula to count words in each cell. Click the OK button to apply the changes.
Text.Length([Column1]) - Text.Length(Text.Remove([Column1], {" "}))+1
5) Finally, go back to the Home tab and click Close & Load option.
Your Excel sheet will now display the word count for each cell or range.
💡 If you prefer not to use VBA or Power Query, you can use a formula to count total words in a cell.