4 Hidden Excel Tricks for Pro Users
1. Text to Columns
If you have a string of data in a single cell but you want to sort the data by something other than the cell’s first character, it can be quite frustrating. Rather than retyping everything, you can use Text to Columns to break that data down into a more usable format.
Text to Columns allows you to take the data in one column and break it apart into multiple columns. The tool looks at the characters inside the data to split it up. For example, with Bible verses, space and a colon can be identified to consistently split up data.
To use this tool, first select the column with the data you want to break up. Then go to Data > Text to Columns, select Delimited and click Next. Identify the delimiters you want to use (comma, colon, space, etc.) and click Finish. Now you can use these separate columns to sort your data however you need to.
2. Custom Lists for Sorting
Sometimes you need to sort text not alphabetically. Maybe it’s for days of the week, months, or books of the Bible. This feature takes a bit more time to set up, but once you do it for a set of data, it’s really easy to use that data in the future.
To set this up, first go to File > Options > Advanced. Under General, click the button Edit Custom Lists. Enter your list, then click OK twice to get back to your data. Next, select the data you want to sort, and go to Data > Sort. Under Order, select Custom List. Select the custom list you want to use, and click OK twice to get back to your (now custom sorted) data.
3. Sort By Color
Many people know how to use the Sort tool to sort alphabetically or numerically, but did you know you could sort by formatting as well? For example, if you use Conditional Formatting to highlight duplicates, you can sort by color to put all unique values at the top of the spreadsheet and duplicate values at the bottom.
To use this tool, go to Data > Sort. Then choose which column to sort, which formatting feature to sort by, and what order to sort the data in, and click OK.
4. Remove Duplicates
After you use Conditional Formatting to identify all the duplicate values, you could manually delete all duplicate entries. But there’s actually a tool that can automatically do this for you!
To use this tool, go to Data > Remove Duplicates and select the column(s) you want to remove duplicates from. Then click OK.
0 Comments