Office hacks: Excel table list ranks deftly converted

Table within the same department’s names are crammed into a cell, how can such data into a list of one person a cell it (Figure 1)? In fact, the use of splitting and reverse pivot columns can be easily achieved.


First, select any cell in the data area, select the “Data” tab, click the “From the table / region” button, in the pop-up window will automatically select the source of data in the table area, that is, A1: B7, check the “Table contains the title”, OK, Excel automatically open the “Query Editor” interface. Click the column label where the “responsible” column, select the “Convert” tab, click the “Split → By Separator”, in the split dialog box, select the separator “Custom”, enter “,” (stop), OK, the “responsible” column will be split into several columns by name (Figure 2).


Next, hold down the Ctrl key, select the “responsible” in turn where the corresponding columns, in the “conversion” tab, click “reverse perspective columns”. In this way, all “responsible for” a number of columns corresponding to the merging of 1 column (Figure 3).


Finally, right-click the “Properties” column label, delete, click “Start → Close and Upload”. This will generate a new worksheet, the list of columns transposed is complete.

Application extensions.

So, in turn, how to use Power Pivot (query editor) to merge similar items? First, place the mouse cursor anywhere in the data area, select the “Power Pivot” tab, click “Add to Data Model”, it will pop up “Power Pivot for Excel” window, you will see the name of the new table, such as “Table 2_25”.

Next, the “Power Pivot for Excel” window is minimized, in the data source worksheet window click “Metric → New Metric”, in the pop-up window formula to enter “= CONCATENATEX (‘Table 2_25’, ‘Table 2_25’ [responsible],”, “)” (Figure 4).


Open the minimized Table Tools window, select the “Home” tab, create a new pivot table, and expand “Table 2_25” in the “Pivot Table” window on the right. Check the “Point of Sale” and the metric value. In this way, the merging of similar items is achieved (Figure 5).


Leave a Comment