Get a list of posts with word counts into a spreadsheet

You have a few options here:

Option 1: Use a word count WordPress plugin and copy/paste the posts list into a spreadsheet

You can use a WordPress plugin to add word counts column to your posts list, then copy and paste the html table contents into a spreadsheet.

To do this, first download and activate a WP word count plugin. I use a lightweight one called CH Word Count for showing counts in WP admin area, but WP Word Count is more popular and robust.

Then you’ll need to copy your post list into a spreadsheet:

1. Go to Posts > All Posts in WP Admin to get a list of your recent posts, you should now also see a column for word counts.
2. Select “Screen Options” tab from the top right of screen and change the “Number of items per page” under “Pagination” to 999 and hit “Apply” blue button.
3. Hover and drag down from top left of the table of posts starting with just before the checkbox.
4. Release the mouse once you’ve dragged to the first 2 or 3 rows
5. Click “Command + Shift + Down arrow” on a Mac or Ctrl + Shit + Down Arrow on a PC. This should highlight all text from the top of the table to the bottom of the page.
6. Release the keys and then hold down on the “Shift” key. While the “Shift” key is pressed, start pressing the “Up” key until the last item highlighted is the “Bulk Edit” dropdown placeholder text.
7. Release all keys. You should have the entire table highlighted now. Copy the contents by pressing Command + C on a Mac or Ctrl + C on a PC. You can also go to Edit > Copy in your browser menu bar in Chrome.
8. Open a new Google Sheet, click on the first cell A1 and paste the contents by pressing Command + V on a Mac or Ctrl + V on a PC. You can also go to Edit > Paste in your browser menu or Sheets menu.
9. Bask in the glory of your keyboard shortcut efforts. You should now have a full list of post titles, their categories, tags, and word counts!

Option 2: Crawl a list of your posts with Screaming Frog and export to a spreadsheet

You can easily pull a list of your posts into Screaming Frog free version if you have an SEO plugin and under 500 posts (the crawl limit on the free version).

You can see my post sitemap generated by Yoast SEO Plugin for an example.

Option 3: Run a SQL query in PHPMyAdmin to generate a list of posts with word counts and export to CSV

Here is a short tutorial to do that with screenshots:

How to generate a list of your published WordPress posts by word count in SQL (PHPMyAdmin)

So to get started, you will just need to drag-n-drop the How-to Schema block in the Gutenberg editor. The How-to Schema block can be used on pages that contain a How-to in their title and describe steps to achieve certain requirements.

Required Tools:

– WordPress PHPMyAdmin access

Steps to configure the How-to Schema:

Step 1

Go to PHPMyAdmin and click on your database in the sidebar (it will likely start with wp_) “SQL” tab

Step 2

Click on the “SQL” tab and paste the following SQL query:
SELECT `ID`, `post_date`, `post_type`, `post_status`, `guid`, SUM( LENGTH(`post_content`) - LENGTH(REPLACE(`post_content`, ' ', '')) + 1 ) AS 'Wordcount' FROM `wp_posts` GROUP BY `ID` HAVING `post_type` = 'post' AND `post_status` = 'publish' ORDER BY `Wordcount` DESC LIMIT 0, 10000
You can change the final number to any integer that is large enough to capture all your posts, if you have 5000 posts, you can set it to 5001 (don’t use commas!)

Step 3

Click “Go” and watch the columns for ID, post date, and URL with Worrdcount listed descending in the last column

Step 5

To save the results, scroll to the bottom and click the middle link, titled “Export”

Step 6

Select “CSV” from the “Format:” dropdown item and hit “Go”