If you have some experience with Excel, then you’re probably familiar with the Text to Columns tool that helps you analyze data from certain types of unstructured text, like news articles and posts from blogs or magazines. But have you heard about the new features in Excel 2016 that make working with text and lists even easier? This article describes these new features and explains how to use them to save yourself time when working on your spreadsheets.
Paste Link
With the Paste Link feature in Excel, you can now link data between cells in different worksheets or even different workbooks. This is great for when you want to keep track of changes or make sure data is consistent across multiple files.
Pivot Tables: Pivot tables are a great way to analyze your data, especially if it’s too difficult to read using traditional charts and graphs.
Data Validation Lists: Data validation lists are handy because they allow you to create an input list so that your user only has a certain number of options available. For example, maybe you need a list of state abbreviations that will populate as the user types them into an input field in Excel.
Table Name Autofill
In the latest version of Excel, you can now autofill table names. Simply click on a cell in your table, then go to the Formulas tab and click the Table Name Autofill button. A drop-down menu will appear with all of the available table names. Just select the one you want and it will be automatically filled in!
Cross Out Words: Do you have a lot of data in cells that needs to be edited? Now, you can cross out words or phrases by highlighting them and selecting Format > Font > Highlight Color. To delete an entire row or column, highlight the text for that row or column and use the Delete key.
Align Tables: When formatting tables, we often need to align columns or rows within the table according to our needs. Here are some common alignment scenarios.
Horizontal alignment (left, center, right): We can do this by going to the Home tab and clicking Cell Alignment > Horizontal (Left).
Vertical alignment (top, middle, bottom): Again from the Home tab and clicking Cell Alignment > Vertical (Top).
One value aligned against another: Let’s say we want each row of data to start at the same place but not necessarily align against another.
AutoFill Formula Names
One of the great things about formulas in Excel is that you can give them names. This helps make your formulas more readable and easier to work with. And now, with the new AutoFill feature, you can quickly give your formulas names without having to manually type them out. For example, if you want a formula for calculating taxes for a list of items, you could do something like this: =Tax(B4) * B3*B2*B1 .
Once it’s set up, AutoFill will take care of all the hard work for you. It’ll start by typing out Tax(, which creates an empty cell with the name Tax. It then fills in the other cells automatically based on what you have selected. In our case, it would calculate each item individually then add those totals together!
#4: Data Validation Lists: If you’re constantly creating drop-down lists for data validation purposes, there’s good news for you. Now when using Data Validation Lists, Excel automatically fills down any remaining entries as soon as one is selected.
Freeze Panes
If you’ve ever tried to scroll through a large spreadsheet only to lose your place, then you know how frustrating it can be. Luckily, there’s a simple solution: freeze panes. This feature lets you lock a row or column in place so that it’s always visible, no matter how far down or to the side you scroll. To use it, just select the cell where you want your frozen pane to start, then go to the View tab and click Freeze Panes > Freeze Panes.
Drag Fill Handle
If you have a list of items that you need to enter into Excel, the drag fill handle can save you a lot of time. Simply enter the first few items in your list, then click and drag the fill handle (the small square in the bottom-right corner of the cell) to complete the rest of the list. Excel will automatically fill in the correct information for you. #4: Convert Text to Table: Creating a table from text is easy with this new feature. To convert text to table, highlight all the cells that contain text data and select Convert to Table from the Home tab on the ribbon. The data will be organized into rows and columns like a spreadsheet so it’s easier to edit and understand.
