The CHOOSECOLS function helps you select and rearrange columns from a table without altering the original data. While it may seem advanced, it’s surprisingly easy to use, especially when combined with the FILTER and GROUPBY functions.
How to use the CHOOSERCOLS function?
Using CHOOSECOLS is super simple!
- Start by selecting your source table with all its columns
- Then, pick the first column you want by entering its position in the table.
- Next, choose the second column.
- .... Keep adding more columns as needed!
For example, we want a new table with Clothing, Size, Color, and Total. With CHOOSECOLS, we can grab just those columns and arrange them in any order we like—there is no need to modify the original table! 😀
CHOOSECOLS has limited functionality on its own. However, integrating with other functions enhances data manipulation capabilities, enabling more efficient and dynamic workflows.
Using CHOOSERCOLS with FILTER function
The FILTER function has made working with data in Excel much easier. It helps you:
- Filter data dynamically, such as showing only absent employees.
- Create dynamic dropdown menus that adjust as you type.
- Extract specific values, like pulling only numbers from a text string.
- Even work with images!
However, FILTER maintains the original column order, which may not always be ideal for reporting. This is where CHOOSECOLS proves useful. For example, if we need a dataset showing Size, Color, and Total for Jeans but in a custom order. Here’s the process:
- Apply FILTER to extract the relevant dataset.
- Use CHOOSECOLS to redefine column order.
- Set the filtering criteria.
⚠️ Note: Column A was intentionally omitted from CHOOSECOLS. Observe how this alters the virtual table structure.
CHOOSERCOLS with GROUPBY or PIVOTBY
The GROUPBY and PIVOTBY functions in Excel 365 make it easier than ever to create summary reports. You can generate structured dashboards with just one formula without complex calculations. CHOOSECOLS makes it easy to reorganize data. It helps us create a virtual table where we can present results in any order without being limited by the structure of the original dataset.
For example, we want to see total sales by Genre and Clothing. Here’s how we do it:
Conclusion
With CHOOSECOLS, you no longer have to worry about how your data is arranged! Just pick the columns you need in whatever order you want—no more wasting time manually swapping things around to fit into complicated formulas.
