Save time with these new Excel features for working with text and lists.
Save time with these new Excel features for working with text and lists.. Microsoft Excel is renowned for its versatility beyond financial and arithmetic tasks. It offers numerous options for converting, transforming, cleaning up, enriching, and manipulating raw data into the desired format. Power Query, now known as Get & Transform, is an exceptional data transformation tool in Excel, forming the basis of the Power BI desktop app. Whether it’s preprocessing data for analytics, visualization, or even machine learning, Excel proves to be a valuable tool.
Save time with these new Excel features for working with text and lists.
Save time with these new Excel features for working with text and lists.
You don’t need external data sources to benefit from Excel’s data transformation tools. Whether you need to manipulate responses from online forms and questionnaires, clean up address lists, remove punctuation and HTML tags from data copied from online sources, or reformat credit card statements for expense claims, Excel is the perfect solution. It also allows you to format dates and currency amounts correctly and add supplementary data like exchange rates (the XLOOKUP function introduced in 2019 is ideal for this purpose).
Save time with these new Excel features for working with text and lists.
The Text-to-Columns feature, accessible under Data > Data Tools, offers more functionality than its name implies. Suppose you have a spreadsheet with dates in U.S. format and need to convert them to UK format without manually creating a second volume and complex formulas. In that case, you can use the Text-to-Columns wizard to swiftly switch the date formats. Simply select the cells with U.S. format dates, choose “Delimited” on the first screen of the wizard, clear all suggested delimiters on the second screen, and set the date format to MDY on the third screen. Despite the preview potentially appearing incorrect, the dates will be correctly formatted once you click “Finish.”
Text-to-Columns is not limited to date conversion; it can also split text into multiple cells. This feature is handy for discarding unnecessary information, such as the merchant number on a credit card transaction, or separating the postal code to enable sorting addresses by location. While formulas can be used for automated splitting across multiple spreadsheets, a better approach is using the new functions introduced in the latest Office Insider betas for Microsoft 365 subscribers. The TEXTSPLIT function breaks text into separate cells based on specified delimiters. Delimiters can be singular characters or arrays for multiple delimiters. Additionally, functions like TEXTBEFORE and TEXTAFTER allow you to extract specific parts of text from a cell, offering flexibility in copying and manipulating data.
When it comes to rearranging data from columns to rows (or vice versa), you can use the traditional copy-paste transpose method or leverage new functions for changing the shape of data. Functions like TOROW, TOCOL, WRAPROWS, and WRAPCOLS enable you to transform arrays into rows, columns, or arrays themselves, facilitating efficient data reorganization. Joining arrays is made easy with VSTACK and HSTACK, which stack arrays vertically or horizontally, respectively, eliminating any empty cells between them. Furthermore, if your data is already in multiple cells, you can use functions like CHOOSEROWS, CHOOSECOLS, TAKE, and DROP to extract specific columns or rows from the array based on your requirements.
In addition to data manipulation features, Excel offers auto lists to ensure consistency when entering text. If you have a list of possibilities for items like product names, accounting categories, or address abbreviations, you can create a dropdown list using the Data Validation feature. By selecting “List” in the Allow box on the Settings tab, you can specify the source of the list, which can be a table on a separate tab to allow for easy expansion. However, managing large lists can become cumbersome, requiring scrolling or precise typing. To address this, Microsoft has introduced AutoComplete for dropdown lists. By utilizing the Data Validation feature as usual and opening the dropdown list, you can begin typing to filter the list. As you type, Excel will automatically autocomplete the entry if there’s a single match or provide a reduced set of options if there are multiple matches. This streamlines the selection process and saves time.
It’s important to note that features in Office Insider betas, including dropdown AutoComplete, may undergo changes or be removed before their full release. Microsoft has already made adjustments to the AutoComplete functionality, such as requiring users to open the dropdown list before typing, which caused some frustration among users. Additionally, availability of features can vary across different builds and users due to the phased release approach adopted by Microsoft. This allows for thorough testing across diverse hardware, software configurations, and network environments.
To check if a specific feature is enabled for you, you can navigate to File > Account > What’s New, although the list of features may not be easily searchable. The appearance of the dropdown list creation dialog box does not change based on the availability of the new feature. If the variability of feature availability proves frustrating or confusing to your users, it’s recommended to ensure they are not using unsupported beta releases. The Current Channel (Preview) provides access to upcoming features while still offering support. However, even within the Office Insider program, it’s not guaranteed that all users will receive the new features simultaneously, so it’s important to communicate this unpredictability to users.
