Creating an SCCM Collection from an List of Computers in Excel

Share on:

I frequently use this trick to manage collections of computers in SCCM where the original list comes from Excel, or from a query of another system that I can dump into Excel.  Doing it this way, you don't have to rely on adding computers one at a time to a collection query or dumping systems into tools like the the ConfigMgr 2012 Right Click Tools (which by the way are awesome).  

One thing to note is that you're adding systems by name when you do it in a query, and not as direct adds.  This may or may not be your desired state, just something to keep in mind.

  1. Assuming your list of systems is in Column A, and Column B is blank, enter the following formula into B1 (adjust accordingly if you have headers):

=Char(34) & A1 & Char(34) & ","

Using the Char(34) function, you're adding in quotes, around your computer name, and then appending a comma afterward.  This is the format required in WQL formatted queries for use in Configuration Manager.  Drag this formula down the page and you should have something that looks like this:

Excel Computer List for SCCM

For those programatically inclined, I posted a VBA solution to automate Step #1 in 2009 in this post.

  1. Create a new query in SCCM.  You can do this under queries, or as part of a collection, whatever is needed.  Edit the WQL of the query and enter the following:
1select *  from  SMS_R_System where SMS_R_System.Name in ( )

What you now have is a query that returns no computers as there are no names in your list.

  1. Now just copy and paste in your list from Excel right inside the parenthesis, making sure to exclude the final comma after the last computer name.  You'll have something like this:
1select *  from  SMS_R_System where SMS_R_System.Name in (

That's all there is to it - you now have a query that returns a list of records based on a list populated from Excel.


Chris Kibble

Hi Cora, it depends on what the purpose of your collection is. All Systems is fine, unless you're looking to ensure that they're in a collection you've already setup, such as all laptops, all servers, etc.


This method is useful for when you have less than roughly 1092 systems, as the WQL query statement area has a maximum of approximately 19,656 characters.

Chris Kibble

You're right, Tuna - you'll need to break them up into multiple queries if it's too long, or use something like the Right Click Tools for Configuration Manager to add them as direct adds.


This is a really good trick. Thanks for finding and sharing it.


This trick is from exactly 4 years ago and still is relevant. Thank you so much! : )



remove all the extraneous spaces and it works


This does not work in Excel (Office365). With or without spaces. Just get #NAME? errors


Try typing it out manually. It wont work if you copy and paste.


Try this one, i had the same issue with #Name? and now in this case it works for me:

= """" & A1 & """" & ","


Year is 2020 and it still works! You just saved yet another inexperienced SCCM admin