Doing certain things on Microsoft Excel, such as – merging two spreadsheets having similar data, combining information from multiple cells or simply reorganizing the data for quick answers is almost hassle-free if you know these simple tricks. So before you spend your precious time on counting or coping-pasting data, look for a quick Excel trick and we assure you – you’ll likely find one.
In Part – I of our “How To Use Excel” articles, we are presenting few of the simplest Excel tricks to get you started.
1. Transpose – Flip rows to columns or vice-versa
Doing it manually would take a lot of time in copy and pasting the data but transpose function does it quickly.
• Select the rows that you want to transpose into columns and select “Copy” with a right click
• Now, select the cells on your spreadsheet where you want your first row or column to begin.
• When you select “Paste Special” a module will appear with an option to “Transpose” at its bottom. As soon as you check that box and select OK, it is done.
2. Text to Column – Splits information and moves it into two different cells
Examples where it is needed – when you have to pull out someone’s company name from their email address or separate first and last name.
• Highlight the column that you want to split.
• Go to the Data tab and select “Text to Columns”
• A module will appear with additional information from which you may select either “Delimited” (breaks up column based on characters such as semi-colon, commas, spaces, or tabs or @ sign) or “Fixed Width” (helps you select the exact location on all the columns that you want the split to occur)
• Let’s assume you select “delimited”. Then, choose the space where to break the column. Excel will then show you a preview of what your new columns will look like.
• If that’s exactly what you want, press “Next.” A page will open that will allow you to select Advanced Formats. When you’re done, click “Finish.”
3. Add multiple new rows/columns
One of the simplest things to do in Excel and though it is super tedious to do it one-by-one, there is a trick to do it in seconds.
• Highlight the same number of pre-existing rows or columns that you want to add (highlight 3 rows if you want to add 3 rows).
• Right-click and select “Insert.”
• And then you are able to add an additional three blank rows into the excel sheet quickly.
4. Remove duplicates – Helps greatly when data sets are large and when you just want to see the unique information
Useful especially when – you have a list of multiple contacts in a company but you only want to see the number of companies you have or you have to remove an entire row based on a duplicate column value.
• Highlight the row or column that you want to remove duplicates of. Then, go to the Data tab, and select “Remove Duplicates” (under Tools).
• A pop-up will appear and as soon as you confirm which data you want to work with, it’s done.
5. Filters – From a very large data set, it hides unwanted rows/columns and displays data that fit into a certain chosen criteria
• You can select “Filter” for each column in your data. You see that option in the Data Tab.
• Also, you can then choose which cells you want to view at once.
• Click the arrow next to the column headers and specify whether you want your data to be organized in descending or ascending order, and which rows you want to see.
Aequor 