The Easy Way to Create Sheets from a List of Values in Excel

[@TheNYAMS]

Thanks for this video. What I was also looking for now was to transfer any other info to the respective tab along with the name of the tab. So, if for example I have a table listing the states with towns in the next column then population for each town and size of each town, I want to use the list to create a new tab per state, then have the relevant town info for each state transfer automatically. How can I do that?

[@joannpaules3642]

That is really slick! I've used an Excel add-in to do that in the past but I'm the only one here at work who uses it. Time to spread the word! Thank you!

[@HowToExcelBlog]

Add-in be gone (though I'm sure it does other useful stuff).

[@houstonvanhoy2198]

John, Thank you for this tip. My opinion is that the Microsoft Excel team should add this functionality and make it available on the ribbon with just a couple of clicks, and a prompt to select the needed values - or even substrings of values - from a column list. Excel MVPs, please promote the idea. Thank you.

[@MawlitiaARTEEST]

This is EXACTLY what I was looking for! 😭😭 AND you showed me a easy way to copy a feature into all my tabs too!! ♥

[@tonynichols2872]

Thank you!! This was great! I had to do a dump from 50 AD Groups! This worked like a charm!

[@wayneedmondson1065]

Hi John.. yes.. cool trick. I was aware of the ability to generate a separate sheet for each item from the Filter definition in a Pivot Table. So, very clever use of this PT feature, although you have to do the clean up after to clear the unwanted PT on each sheet, as you demonstrated. If the user is familiar with VBA, here is a sequence you can type directly into the Immediate window to generate the same with no subsequent clean up required. The way I wrote the code, you must first highlight your list (i.e. the Selection): For Each cl in Selection: Worksheets.Add: ActiveSheet.Name = cl.Value: Next cl You could also put this into a formal Sub and run it from a button, the QAT or a custom ribbon entry. I find the Immediate window to be very handy to write quick code procedures to run a series of commands to get some work done that would normally required a series of repetitive manual steps. It has the added advantage of forcing me to practice and use my VBA skills. Anyway, to each his own. Thanks for your insights. Thumbs up!

[@jacquelinechavez3933]

Good morning! I would love some help on creating adfditional sheets for my work sheet if you have some time

[@donmedina2998]

I knew about this feature but its been a while, and I've forgotten how it's done. I got a task to create measures on a pivot table for each item in a list of 50 and thought this would be the fastest way to get all of them done. Thank you for creating this video. It's certainly a big help.

[@HowToExcelBlog]

Glad it helped you Don!

[@TheTravalgarLaw]

Briliant !no use of macro anymore for this ! thanks !

[@davidanilvs6293]

Sure it helps a lot, Nice and good tip, Thank you

[@mohammedateef6732]

Thanks, superb.....task made easy....keep it up for more interest tricks in excel....

[@CeliaAlvesSolveExcel]

😲 amazing! I never knew that feature was there! Thanks a lot, John! If I had to automate that I would have gone with VBA.

[@HowToExcelBlog]

Yes, I avoid VBA when I can 😉

[@CeliaAlvesSolveExcel]

@@HowToExcelBlog That's a good strategy.

[@c17nav]

@@CeliaAlvesSolveExcel Avoiding VBA and exotic functions/formulas/features is a good strategy when a spreadsheet must be used cross-platform from Windows to another OS (iOS, Android, etc.) or vice versa.

[@518sjohnson]

Awesome time saver. I'm very grateful.

[@abuishaq5914]

You Video saved me a lot of time, thanks

[@ArielGuego]

Thank You, this was very helpful.

[@m.raedallulu4166]

You are genius, sir!

[@samuelthomas5494]

Excellent! thank you for sharing this. It worked for me :-)

[@HowToExcelBlog]

Great!

[@AbuTalha-eo7pr]

impeccable et c'est très facile à utiliser; Merci bien

[@user-gm4lo6pq6x]

Awesome. Thanks a alot.

[@huipipia]

awesome video! thanks!

[@pannylewis9768]

Thank you so much!

[@fostercaretrainingtoday]

This was incredibly helpful!!!

[@robertjohnbell6053]

Outstanding, thanks.

[@yasserarafa88]

that exactly what i was looking for , thanks for it😍

[@subinelias674]

Incredibly helpful. Thank you🙌🏻

[@qrsimon]

thanks

[@dafnejara7479]

So Great! thank you so much! I didn´t want to make a macro

[@HowToExcelBlog]

Yep, I'm not a fan of VBA either 👍

[@rasoulkhoshravan5912]

@@HowToExcelBlog Me too. But you know, VBA is very paramount. Its time to go for it, if you want to excel in excel

[@HowToExcelBlog]

I find there are better tools these days. VBA is old tech.

[@thomasavinash72]

Working with excel 2010 Unable to delete the pivot as you explained in the video Msg box warning reads cannot delete part of pivot table

[@yonnab1]

Same for me!

[@jimlea7935]

@@yonnab1 and me!!!

[@bhole770]

Thanks a lot sir

[@HowToExcelBlog]

No problem 👍

[@jonaschannel1635]

thank you sir i love you

[@chriswebb4797]

Excellent trick!

[@jcarmi1947]

Thanks, you just saved me a lot of work... why don't you have a notification option so I can receive notices on your videos as you produce them? I would certainly like to receive them.

[@mlunghu2515]

Thanx for this, great help

[@Mcconnellsmagic]

Super helpful. What if you have data you want to show for each of those "States", like their population and that is in Column B. Is there an easy way without having to go to each new sheet and click on "Population" as a filter to show?

[@alializadeh8195]

Thanx

[@SiyamandRashid]

thank you for the video, how if we want an excel table in each sheet as well.

[@bilalemamy1563]

thanks alot

[@duif1995]

bro, thanks :)

[@gavinburt3750]

Awesome video - saved me faffing with VB. Note: How do people find out how to do this stuff !!!

[@C4WebConsulting]

It's crazy that you have to go around the house to get through the door.. Thank you for sharing this

[@mahamohan1]

Nice tutorial. The only drawback in this technique is that the sheets are created in an order say from A to Z . if I have a name Vietnam below Alabama and California at the end in a list. the sheet for California will be the second one and not Vietnam. of course we can drag and drop the sheet in the desired place, if the number of sheets are less.

[@pedrofebres699]

Is the same problem i have, how to keep the order in MY list, not alphabetical order that the program did automatically

[@grichadulabdas4158]

thanksss, very usefull

[@HJay]

This is a cool way of creating sheets! Is there an equally cool way of renaming sheets? Say if I wanted to set up a workbook that contained a table that I would enter data into every day and each month I wanted to copy the sheet and rename the sheets to match the dates and days of the week?

[@user-py3ku2wn3p]

Genial! :)

[@kevinbush4300]

Cool addition would be to use =HYPERLINK() on the Index sheet, to jump to the State sheet rather than scroll through the tabs

[@c17nav]

Superfluous. You don't need to use a sheet to list/link to the sheets in the workbook, or devote space in other sheets for such a list. Excel automatically builds a list of worksheets in a workbook file. Use your mouse to hover over the extreme bottom left of your worksheet tabs and the left/right angle brackets. Right click and the list pops up so you can highlight and select the desired sheet. IMO, its more efficient to create hyperlinks to open other files on my system or web pages.

[@allabout1135]

This was really cool trick. I am wondering where You get all this?

[@HowToExcelBlog]

15 years of using Excel for 8 or more hours a day 😂

[@HowToExcelBlog]

I don't know it all though, still learning.

[@allabout1135]

Hi mate. I am quiet new in exceling, but have already done some useful project for data analysis automation. I with pleasure doing things whats makes my life easy in the end. Some of Your tricks are chopping the data in my projects.as well ;) Thanks for input. @@HowToExcelBlog

[@miguelcalejo1770]

Clever!

[@striperseeker]

I followed your steps perfectly, but now when I add info to one of the created sheets, it appears on all sheets. and it won't let me delete the original pivot table that made the sheets. Is there a fix? Thanks

[@V0lk]

Could you duplicate a sheet with data in it using this same method?

[@simonbriffa7450]

Out of curiosity, this is great, but let's say you have duplicate values within the column, how would you work around this so you don't create duplicate sheets ?

[@HowToExcelBlog]

Try it out. You'll find it doesn't matter if there are duplicate values in the list.

[@rasoulkhoshravan5912]

Sheets can't have same name. Like same file names inside one folder. As author says, try and see what happens.

[@lavenderisviolet7182]

Does it work with work book?

[@keishabarnes6497]

Hi there. When I select the columns that the pivot table is in and try to clear content I get a message saying that I can't. Any idea what I'm doing wrong?

[@veyselgonel2668]

How can we hyperlink the states in that list to those sheets? What I am trying to say is, I want to click on the state name and then it will take me to that state's sheet. Is it possible to do that automatically other than doing one by one?

[@izzatkiswani]

Hi every step is working except the last one to clear contents from the two columns , note my excel is 2007

[@HowToExcelBlog]

Probably time to upgrade.

[@xeniaorozco5557]

What should I do if the tab names are being cut off when I create them?

[@mamamtl]

Hello I have a question. Can this be made dynamic with a macro? For example if you add a line to the list a new sheet will be created but the old ones are not overwritten.

[@c17nav]

IMO, after first creating the multiple sheets in an .xlsx file, this doesn't need a dynamic macro in an .xlsm file - especially for inserting a few sheets. The quickest and most reliable way to create a new sheet is either: 1) Insert a new blank sheet where needed. Rename it and start from scratch on structure, data, and formulas - which can be tedious and/or efficient. 2) Copy an existing sheet and move the copy to the desired tab position. It will inherit the structure, formulas, data, etc. of the original. Rename the new sheet and modify it as required.

[@jamesgreen7082]

Why does the "We can't change this part of the PivotTable" prompt appear when attempting to Clear Contents (remove the PT) in Excel 2016?

[@8998jjbpf]

I had a similar problem using Office 2013. I got around it by selecting all sheets, as per John's video, but instead of clearing contents of the two PT columns (which also gave me the error message), go to the "Editing" section of the ribbon, on the far right, in there go to the drop down menu for "Clear" and select "Clear All".

[@HowToExcelBlog]

Great tip!

[@rasoulkhoshravan5912]

@@8998jjbpf I also used this solution to overcome the problem. Thanks for sharing.

[@sredharbalaraman1387]

@@HowToExcelBlog thanks

[@88omarz]

Not allowing me to clear content, keeps saying there is a pivot table present....

[@essamhelmy5776]

👍👍👍

[@Elmnopen]

why doesn't this work with dates?

[@nevillecreativitymentor]

SOOOOPURRR

[@sredharbalaraman1387]

I couldn't clear content why?