Analyzing data ofttimes ways spending more time getting and cleaning upward data than analyzing it. If that describes you, definitely review Excel 2016's Power Query (or Get & Transform). Using Power Query, you can load data from several different sources, including the active workbook. Once data is in Ability Query, y'all can clarify and manipulate that information quickly using specialized tools. If you think the simply style to get the results y'all need is to use complex formulas or even VBA code, have a pace back and think, Tin can I exercise this in Power Query instead? Often, the reply will be yep.

In this short introduction to Power Query, I'll evidence you a problem that requires some fancy hoop-jumping to manage, unless you lot employ Power Query. Specifically, I'll show yous how to create a database-fashion structure past separating multiple values stored in the same cavalcade into individual rows–i row for each value. It'due south a common problem and not easily solved in Excel without specialized knowledge.

I'm using Role 365's Excel 2016 (desktop) on a Windows 10 64-bit system. Ability Query, otherwise known equally Get & Transform, is available in earlier ribbon versions, but you need to install it every bit an add together-in. There's no comparable tool in earlier menu versions. You tin use your own data or download the demonstration .xlsx file.

What's a multi-value field

If you're familiar with database engineering science, yous know that one of the nigh important normalization rules is that each field should store the smallest possible autonomous value. Many databases and peculiarly spreadsheets break this rule, much to their users' sorrow. Figure A shows a simple information set that breaks this rule.

See: Software Usage Policy (Tech Pro Enquiry)

Figure A

A multi-value field stores one or more values.

Every bit y'all can meet, three records (rows iii, 6, and 7) take more than one value in the Items column. It makes sense, but but at commencement. It won't take long to realize that yous tin't easily analyze or manipulate by the subsequent values. For instance, how would you count the number of items represented in this data set? (There'south sixteen).

If you stored this data in a relational database, you lot'd have at least two tables: One for each social club with a primary key that uniquely identified each record (order) and a second that stored each product equally a unmarried record. And so, you'd use a query to pull those records together–one record for each production using a primary/foreign key to identify, which products go with which social club. Using this construction, you lot can easily sort, group, and even analyze your data past the Items values without jumping through any hoops.

That'southward what we're after with the simple sheet shown in Figure A. We want a record for each value in the Items column. Specifically, instead of 12 records, we want 16, and nosotros want a manner to group the products for each order. Can you see the trouble? Trying to do this manually would be worse than dull. Serious developers would plow to VBA, merely what if you don't have that skill? Thanks to Power Query, even casual users tin create sixteen records from 12 in simply a few minutes:

  • First, we'll load the data into Ability Query.
  • Then, nosotros'll add together an index cavalcade, then you lot tin visually run into which products belong to the aforementioned society.
  • Next, we'll create the new rows using a delimiter query. A delimiter is a abiding character that separates values.

Information technology's quick, and information technology's easy.

Load data into Ability Query

I mentioned before that you can load data from lots of sources; I recommend that you explore the many options (Figure B) on your own. For now, we're going to load data from a Table object already in Excel. If your data is a normal range, you must convert it to a Table object; if you don't, Excel forces you to do and so when you appoint the characteristic.

Figure B

Power Query can retrieve data from many sources.

To convert a data set into a Table object, click anywhere inside the data gear up, click the Insert tab, choose Table from the Tables grouping, specify whether the data ready has a header row, and click OK.

To load the Table object into Ability Query, do the post-obit:

  1. Click anywhere within the Table.
  2. Click the Data tab.
  3. In the Get & Transform Data group, click From Table/Range. (If the data isn't a Table, Excel will run you through the steps necessary to convert the information into one.)
  4. Excel will launch the Query Editor (Figure C). Each column header identifies its data type for piece of cake visual confirmation.

Figure C

The Query Editor offers many tools for manipulating and analyzing data.

Add an index column

This adjacent footstep isn't necessary, but it adds a powerful visual "Ohhhhh!" moment that I think you'll appreciate. We'll add together a unique value to each row–remember, right now, each row represents a single order, just some of the orders comprise multiple products.

To add an index column:

  1. Click the query carte (the sail icon at the intersection of the header rows).
  2. From this menu, choose Add Index Cavalcade, and and so select From i from the resulting submenu (Figure D).

Figure D

Select a seed for the index column.

Equally y'all tin come across in Figure Due east, each tape has a unique value. The serial starts with one and is consecutive. We take 12 order records.

Effigy E

Add together an index column to the data gear up.

A footling magic

Now we're ready to work a petty magic–or rather, let Excel work some magic. Remember, we want a single row for each Items value, not each record. The solution is like shooting fish in a barrel:

  1. Select the Items row (click the header cell).
  2. Click the Carve up Column pick in the Transform group, and choose By Delimiter from the resulting dropdown listing.
  3. The resulting dialog prompts y'all to identify the delimiter; in this case, it's the semi-colon (;) (Figure F), so select Semicolon from the dropdown list. Retain the default option, Each occurrence of the delimiter, simply don't click OK nevertheless.
  4. Click Advance options.
  5. In the Dissever into section, select Rows (Figure G) and click OK.

Effigy F

The query returns a unmarried row for each value in the Items column.

Figure Thou

Specify a row carve up.

As you lot can meet in Figure H, each item moved to a row of its own, along with the society information: date, Value, and and then on. The alphabetize column helps you lot discern, which products belong to the same order. The index values nevertheless identify the original 12 orders, merely there's more than 1 row for gild i, 4, and 5, which indicates those orders have multiple items. You can put your hoop away–no jumping required. This is where you lot say, "Ohhhhhhh!"

Figure H

Ability Query separates each particular value into a row of its own.

When you close Power Query, Excel prompts yous to keep or discard the query. If you want to work with the data, choose Keep. Excel copies the results of the query into a new sail.

Transport me your question about Office

I answer readers' questions when I can, but there's no guarantee. Don't ship files unless requested; initial requests for help that arrive with fastened files will be deleted unread. You tin send screenshots of your data to assistance clarify your question. When contacting me, exist as specific as possible. For example, "Please troubleshoot my workbook and fix what's wrong" probably won't go a response, but "Tin can you tell me why this formula isn't returning the expected results?" might. Please mention the app and version that you're using. I'm not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I inquire for a fee from readers I assist. You can contact me at susansalesharkins@gmail.com.

Come across also

  • Three running total expressions for Excel (TechRepublic)
  • Five tips for using Outlook 2016'due south AutoComplete list efficiently (TechRepublic)
  • How to use VBA to select an Excel range (TechRepublic)
  • Excel errors: How Microsoft'due south spreadsheet may be hazardous to your health (ZDNet)
  • Office Q&A: How to handle end-of-judgement spacing in Microsoft Word (TechRepublic)
  • AI helps Google Sheets grok your plain-English commands (CNET)