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
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
Transcript of the video:
Learn Excel from MrExcel podcast, episode 2186.5.
Analyze InDesign Data in Excel.
InDesign is the world's leading software for designing books and we publish books here at MrExcel.
That means at every season we are working on books and I have a great use of how to use Excel to check your work InDesign.
Now, this is coming up as I've been asked to do a 45 minute presentation on Excel.
Excel for InDesign customers at Creative Pro-Week in New Orleans, June 4-8, 2018.
There'll all people use InDesign as much as I use Excel.
So, I'm gonna try demystify some of Excel and...
The process of getting a book ready to go to print, is one of the last things you do is indexing.
Indexing, I've to tell you is boring, it is boring, boring work.
So, at the end of the process, I've gone through and you marked all the pages and you know, have these little index entries here in A.
Copy those from InDesign and paste here in the list column in Excel and use Flash fill, we'll talk about the Flash fill at the conference, to get just the page numbers.
What I'm looking for here, is I'm looking for sections of book where essentially index are (that's me) fall asleep.
Alright!
It's hard to stay focus, it's hard to keep coming up listing stuff.
So, I wanna look for index quality, I'm gonna do this using a pivot table in Excel.
So, insert...
Choose one cell in your data, insert pivot table, and I'm gonna put the date, the report here in existing worksheet, in this location, click [ OK ].
Alright! What we have, is we have the columns area, the rows area, the values area.
The values area are the things we wanna add up and the rows area is the stuff down the side.
Now, this gonna be really backwards.
I'm gonna take the numeric field which almost always goes in the values area.
I'm gonna put it here in rows like that.
So, these all the page numbers in the book that have index entries.
And now I wanna know, how many index entries?
I need each one of those pages so, I take this text over here.
This is a heading and drag it to the values area, which will force them to count.
Alright! Now, one, two, three, four, wow!
That 7 on that page, alright!
This doesn't tell me a lot, it doesn't tell me sections of the book, where I fall asleep.
So, I'm gonna take this first-page number right here.
Choose that cells really important, you choose one of these cells, on the analyze tab come to group field and say, I wanna group this.
Yeah! ten page pockets, that makes sense.
So, from page 1 to 210 and 10 page pockets, click [ OK ].
Now, we have groups of pages and you'll see here Under column this is heading.
It seems like you know, doing about 1.4 entries per page.
Every 10 pages, I'm getting 1.4.
But let's see if there's any part of the book, where it looks like I fell asleep.
So, we come here, home, conditional formatting, data bars and we'll choose one of these fills here like this, alright!
You see this some sections like that one have 39 entries.
Alright! But, then there are other sections like this one, right here from page 141-150 only 10 entries or 181-190, only 4 entries.
I need to go back to the book and re-read those pages and see there are sections, that I missed in index or they just not paying attention.
Didn't call on enough things in the index and then of course, InDesign makes it really easy to regenerate the index.
These are kind of things are in my book MrExcel LIV The 54 Greatest Tips of All Time.
But, today I really reaching out to those people, who use InDesign, or Photo Shop, or Illustrator.
This conference is for you and may be you use Excel all the time, but your spouse uses one of the creative tools, have them check out, creativeproweek.com Alright! Today's episode we have created a stratification, using a pivot table, tried to figure out.
How many index entries per every 10 pages, use GroupBy feature on analyze tab and then finally, home, conditional formatting, data bars.
I wanna thank you for stopping by, we'll see you next time for another netcast from MrExcel.
Analyze InDesign Data in Excel.
InDesign is the world's leading software for designing books and we publish books here at MrExcel.
That means at every season we are working on books and I have a great use of how to use Excel to check your work InDesign.
Now, this is coming up as I've been asked to do a 45 minute presentation on Excel.
Excel for InDesign customers at Creative Pro-Week in New Orleans, June 4-8, 2018.
There'll all people use InDesign as much as I use Excel.
So, I'm gonna try demystify some of Excel and...
The process of getting a book ready to go to print, is one of the last things you do is indexing.
Indexing, I've to tell you is boring, it is boring, boring work.
So, at the end of the process, I've gone through and you marked all the pages and you know, have these little index entries here in A.
Copy those from InDesign and paste here in the list column in Excel and use Flash fill, we'll talk about the Flash fill at the conference, to get just the page numbers.
What I'm looking for here, is I'm looking for sections of book where essentially index are (that's me) fall asleep.
Alright!
It's hard to stay focus, it's hard to keep coming up listing stuff.
So, I wanna look for index quality, I'm gonna do this using a pivot table in Excel.
So, insert...
Choose one cell in your data, insert pivot table, and I'm gonna put the date, the report here in existing worksheet, in this location, click [ OK ].
Alright! What we have, is we have the columns area, the rows area, the values area.
The values area are the things we wanna add up and the rows area is the stuff down the side.
Now, this gonna be really backwards.
I'm gonna take the numeric field which almost always goes in the values area.
I'm gonna put it here in rows like that.
So, these all the page numbers in the book that have index entries.
And now I wanna know, how many index entries?
I need each one of those pages so, I take this text over here.
This is a heading and drag it to the values area, which will force them to count.
Alright! Now, one, two, three, four, wow!
That 7 on that page, alright!
This doesn't tell me a lot, it doesn't tell me sections of the book, where I fall asleep.
So, I'm gonna take this first-page number right here.
Choose that cells really important, you choose one of these cells, on the analyze tab come to group field and say, I wanna group this.
Yeah! ten page pockets, that makes sense.
So, from page 1 to 210 and 10 page pockets, click [ OK ].
Now, we have groups of pages and you'll see here Under column this is heading.
It seems like you know, doing about 1.4 entries per page.
Every 10 pages, I'm getting 1.4.
But let's see if there's any part of the book, where it looks like I fell asleep.
So, we come here, home, conditional formatting, data bars and we'll choose one of these fills here like this, alright!
You see this some sections like that one have 39 entries.
Alright! But, then there are other sections like this one, right here from page 141-150 only 10 entries or 181-190, only 4 entries.
I need to go back to the book and re-read those pages and see there are sections, that I missed in index or they just not paying attention.
Didn't call on enough things in the index and then of course, InDesign makes it really easy to regenerate the index.
These are kind of things are in my book MrExcel LIV The 54 Greatest Tips of All Time.
But, today I really reaching out to those people, who use InDesign, or Photo Shop, or Illustrator.
This conference is for you and may be you use Excel all the time, but your spouse uses one of the creative tools, have them check out, creativeproweek.com Alright! Today's episode we have created a stratification, using a pivot table, tried to figure out.
How many index entries per every 10 pages, use GroupBy feature on analyze tab and then finally, home, conditional formatting, data bars.
I wanna thank you for stopping by, we'll see you next time for another netcast from MrExcel.
