Excel - InDesign Index Quality by Grouping Numbers in an Excel Pivot Table - Episode 2186a

2,123 views · Published 6 April 2018 · 3:58 · Indexed 14 November 2026

Channel: MrExcel.com · 2018 · Education

Watch on YouTube

Microsoft Excel Tutorial: Checking the quality of an index in a book.

Welcome to episode 2186.5 of the MrExcel podcast, where we dive into the world of Excel and explore its endless possibilities. In this episode, we will be discussing how to analyze InDesign data in Excel, a useful skill for those of us who work with books and publications.

As someone who publishes books here at MrExcel, I understand the importance of using Excel to check your work in InDesign. That's why I've been asked to do a 45-minute presentation on Excel for InDesign customers at Creative Pro-Week in New Orleans, June 4-8, 2018. This conference is a great opportunity for those who use InDesign as much as I use Excel to learn more about how to use these two powerful tools together.

One of the final steps in the book publishing process is indexing, which can be a tedious and boring task. But fear not, as Excel can come to the rescue! By copying and pasting the index entries from InDesign into Excel and using the Flash Fill feature, we can quickly and easily extract the page numbers. But what do we do with this information? That's where the pivot table comes in.

By inserting a pivot table and organizing the data, we can get a better understanding of the quality of our index. We can group the pages into sections and see how many index entries are on each page. This allows us to identify any sections of the book where we may have missed index entries or not paid enough attention. And with the help of InDesign, we can easily regenerate the index.

This technique is just one of the 54 tips featured in my book, MrExcel LIV The 54 Greatest Tips of All Time. But today, I want to reach out to those who use InDesign, Photoshop, or Illustrator. This conference is for you, and I encourage you to check out creativeproweek.com for more information. And for those of you who use Excel regularly, but have a spouse or colleague who uses one of these creative tools, be sure to share this episode with them.

Thank you for tuning in to today's episode, where we learned how to use Excel to analyze InDesign data and improve the quality of our index. Don't forget to join us for our next episode, where we will continue to explore the endless possibilities of Excel. And as always, happy Excelling!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/

#excel
#microsoft
#microsoftexcel
#exceltutorial
#walkthrough
#evergreen
#excelpivot
#adobeindesign 

This video answers these common search terms:
how to group numbers in excel pivot table
how do you group numbers on excel pivot table
how to create groups in pivot table in excel
how to group a pivot table in excel
how to group in pivot table excel
how to group in excel pivot table
how to group data in a pivot table in excel
how to group pivot excel

Table of Contents:
(00:00) Analyzing InDesign Index data in Excel.
(01:04) Using pivot tables to analyze index quality.
(02:00) Grouping numbers in a pivot table
(02:41) Using conditional formatting to identify missed index entries.
(03:38) Clicking Like really helps the algorithm

If you know someone who uses Photoshop, Illustrator or InDesign, learn more about the conference at CreativeProWeek.com.
In today's episode: I want to check the quality of an index that I created in InDesign. Were there any sections where I was not as attentive as I needed to be?
Copy the Index from InDesign and paste in Excel.
Use Flash Fill to separate the page numbers.
Insert, Pivot Table, OK.
Put Page in Rows area and Heading in Values area (this seems backwards, but in this situation, it is correct)
Select the first page number. On the Analyze tab, Group Field. Group into 10 unit buckets.
Select all numbers in the pivot table except the Grand Total. 
Home, Conditional Formatting, Data Bars

Join the MrExcel Message Board discussion about this video at  https://www.mrexcel.com/board/threads/learn-excel-indesign-index-quality-podcast-2186a.1154015/

 About MrExcel: Bill Jelen is an Excel MVP and the author of 70+ books on Excel. He has trained thousands of professionals worldwide.

More from this channel