Split an Excel File in to Many using VBA [Videos]

Share

Facebook
Twitter
LinkedIn

Splitting an Excel file in to many is easier than splitting bill in a restaurant among friends. All you need is advanced filters, a few lines of VBA code and some data. You can go splitting in no time.

Context:

Lets say you have lots of data like this in a file. And you want to split this in to multiple files, one per salesperson.

Splitting An Excel File in to Many using VBA - Example Data

Solution – Split Data in to Multiple Files using Advanced Filters & VBA

The process of splitting data can be broken down to 4 steps.

  1. Identify the split criteria and list down all values in a small range. In our case, we list all the salespersons names in a named range lstSalesman.
    List of all salespersons - Splitting data in to multiple files using VBA
  2. Set up advanced filters so that we can filter the data by one salesman at a time.
    Range where advanced filter criteria will be specified - Splitting data in to multiple files
  3. Now, for each salesman, apply advanced filters and set it to copy the filtered values elsewhere.
    1. Copy the filtered values
    2. Add a new workbook and paste the copied values there.
    3. Save the new workbook with a unique name
    4. Repeat the above 3 steps for each salesman
  4. That is all! You are done splitting.

Video Lesson on Splitting Data using Filters & VBA

Since splitting data in to multiple files requires a bit of macro code & advanced filter knowledge, I have created a short lesson explaining how this works. Watch it below.

[If you are not able to see the video, watch it on our Youtube Channel]

If you are new to VBA, take our crash course.

Download Split Data Example Workbook

Click here to download the split data example workbook.To use this,

  1. Save the downloaded file to any folder.
  2. Open the file and enable macros.
  3. Examine everything and when ready, click on “Extract” button.
  4. Check the folder where you saved the file and you will fine 4 new Excel workbooks named after the salespersons with the data extracted for them.

You can find the macro code in Module 1.

How do you Handle Splitting Situations?

In my work, I rarely had to split data. And whenever I had to split data, I usually copy paste the data after filtering what I want. But I can imagine many real life scenarios where you need to automate the splitting part.

How do you split data? What techniques and ideas you use to speed up the splitting process? Please share using comments.

More on Splitting & Consolidation

If you are in to splitting or combining things, we have a selection of tips & examples to help you. Check out these articles.

PS: Heck, we have even have an Excel tip to tell you how to split expenses among friends 😛
PPS: You can use Pivot Table Report Filters if you want to split data in to multiple sheets.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst
Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

26 Responses to “FIFA Worldcup Excel Spreadsheets [Roundup]”

  1. J. J. says:

    Nice roundup! Do you know of any one-page spreadsheets which will be updated by an administrator after each game? Would be nice to be able to print out the latest results whenever I feel like checking them as I probably won't be following closely every day.

    I actually haven't tried any of the above ones yet, but I thought I'd mention this one that I found which makes a nice one-page form you can fill in dynamically. http://exceltemplate.net/sports/world-cup-2010-schedule-and-scoresheet/

  2. Diego Garcia says:

    I would like to recommend you these one: http://www.anotagol.com/
    You can choose your interface language (english, spanish, italian, portuguese, german or french) and your country for the timezone of match. I like it very much.

  3. [...] Passend zu gerade laufenden Fußball-WM gibt es auf Chandoo.org alles wissenswerte über Excel-Anwendungen für den Fußball-Fan. [...]

  4. Rohit1409 says:

    Great!!!

    I strongly recommend this :

    http://www.en.excel-soccer-2010.de/downloads

    Chandoo how you found this ...

  5. Chandoo says:

    @Rohit.. really beautiful file. I missed it during my research. Now, I recommend it. 🙂

  6. Mourad Louha says:

    Hi Chandoo - thanks for the recommandation 🙂 - Regards

  7. [...] Excel, then print it on the other side of your Match Schedule from step 2 above. There are several other Excel spreadsheet templates you can download, but this is probably the only one-page version you can find; plus, it [...]

  8. Robert says:

    Does anybody know how to re-create this(?): http://www.marca.com/deporte/futbol/mundial/sudafrica-2010/calendario-english.html
    ...or do you know where a template can be found? I am DYING to have something like this on my site. When I found it, I had been looking for the longest time for a circular calendar. I found a couple that weren't adequate. Then I stumbled upon this one and my eyes nearly popped out of my head. If anyone can lead me in the right direction, I would be eternally grateful!

    Thanks in advance!
    Robert

  9. Chandoo says:

    @Robert...

    Doing something like that is a lot of work. You can probably get it done with some hired help from a flash developer.

  10. Pedro Wave says:

    @Robert, the World Cup flash in the Spanish Marca newspaper is impresive, but not much as my own animated spreadsheet with the Goals of 2010 World Cup South Africa in Excel that I just published into my blog:
    http://pedrowave.blogspot.com/2010/06/goals-of-2010-world-cup-south-africa-in.html

    Download from here:
    http://cid-6b219f16da7128e3.office.live.com/view.aspx/.Public/Goals%20South%20Africa%20Animated.xlsx

    And start to enter the goals of the rest of matches.

  11. Neil says:

    Has anyone seen, or made, a Spreadsheet where you can record the scorers and see a 'top scorers' chart. Would be a nice enhancement

  12. Chandoo says:

    @Neil... checkout this one http://www.inflexionary.com/sports/world-cup-2010-excel

    it uses macros to fetch scores from web (and provides very comprehensive analysis too)

    @All.. Thanks for the comments. I have updated the post with few more links now.

  13. Sergio Mathias says:

    Hi,
    Check this dashboards too:
    http://dashboards.org/world-cup-dashboards-and-visualizations/
    😉

  14. [...] Here is a collection of FIFA World Cup Spreadsheets if you are more in to that sort of thing. | [...]

  15. [...] Cup fever is here!In FIFA Worldcup Excel Spreadsheets Roundup, Chandoo has some links to useful World Cup tracking workbooks. Only one of them (the first one) [...]

  16. [...] World Cup fever is here!In FIFA Worldcup Excel Spreadsheets Roundup, Chandoo has some links to useful World Cup tracking workbooks. Only one of them (the first one) [...]

  17. Hey, you missed ours! It has everything you need and more, but not a whole pile of silly extras (National Anthems, etc).  I'll be making another one for the 2014 world cup.  We had over 4000 hits on it!

  18. Neil says:

    @Michael Harwood.

    Where is it then? You should have posted a link  

  19. phillip says:

    Sie sollten an einem Wettbewerb teil zu nehmen für einen der besten Blogs im Web. Ich werde empfehlen Sie diese Seite!
    Google translation: You should take part in a contest for one of the best blogs on the web. I will recommend this site!

  20. [...] and welcome to the forum, Maybe these similar spreadsheets might give you a few initial ideas: FIFA Worldcup Excel Spreadsheets [Roundup] | Chandoo.org - Learn Microsoft Excel Online If you have specific areas / formulae / layout choices for parts of your spreadsheet that you are [...]

  21. Petros says:

    Calling all football fans around the globe! The biggest football festival will kick off on the 12th June 2014 and everyone is placing their bets of who will have the honour of lifting the golden trophy.

    Use our free interactive Excel templatel to predict the World cup finalists ! No macros !

    http://www.spreadsheet1.com/world-cup-2014-free-excel-prediction-template.html

  22. marten says:

    I also made a Worldcup-tracker, with MS Access, which can also generate reports in Excel
    e.g. a match-schedule with locations on y-axis and dates on x-axis, see:
    http://worktimesheet2014.blogspot.com.es/2014/05/excel-with-match-schedule-for-2014-fifa.html
    and:
    http://worktimesheet2014.blogspot.com.es/2014/05/match-access-app-to-track-world-cup.html

  23. Vivek Ranjan says:

    where can i find raw data in excel file format of fifa world cups (1930-2014)

Leave a Reply