Excel Tips and Tricks to Know
Microsoft Excel is a tool used by professionals and individuals for decades now. At this point, there are hundreds of features that you can find to manage your spreadsheets easily.
While there are simpler alternatives to Microsoft Excel, you will be missing out on many powerful functionalities and tricks that you can do with Excel. Of course, you need to invest a reasonable amount of time in utilizing every aspect of Excel’s capability, but it isn’t necessarily tough.
In this article, we shall focus on highlighting some of the most valuable and time-saving Excel tips and tricks.
VLOOKUP Function
When dealing with a large set of data, it can be time-consuming to find something based on the rows for a specific column.
However, with the VLOOKUP function, you can easily find a value by using specific rows and columns to filter. For instance, if you want the email address of every name that you input, you can use this function and tweak the formula to get the result.
You can also combine the VLOOKUP function with other functions like IF logic. In addition to that, you can use VLOOKUP to combine data from multiple tables given that they share a similar field.
Transpose Rows into Columns
If you want to copy a selected number of row cells (vertically) and order it in a single row (converting them to columns), you can simply use the transpose paste option.
Select the range of cells you wish to transpose.
Copy them by doing control + C
Click the cell where you wish to start the transpose
Right-click and select the paste – transpose option.
Column Sorting Tips
For basic single-column sorting, you can click on any row of the column that you want to sort and then right-click to find the sort option or click on the “Sort & Filter” button.
You will find a couple of options to choose from as per your requirements. When you click on any of them, the entire table gets sorted accordingly.
When working with multiple columns, you can utilize the shortcut ALT + A + S + S to open the dialogue box where you can specify multiple columns to sort.
Make sure to enable the option “My data has headers” available if your data has headers.
Quick Analysis
You might have noticed the lightning icon when you select a range. Click on that to access quick functions that help you identify duplicates, add a chart, count the totals, and more.
Wrap Text to Limit it in a Cell
No matter what, we always re-sizing rows/columns more often than we would want to. When it comes to dealing with numbers, that is doable.
However, when it comes to writing a text longer than the cell length, it could look confusing. Even though the text stays in the same cell, it seems to merge with other rows.
So, in this case, all you need to do is hit “Wrap Text” to contain the information in a single cell.
Add Multiple Rows/Columns Quickly
To add a single row, you need to right-click and hit “Insert” to add a row/column.
And, if you want to add multiple rows or columns, you can select the number of rows or columns and then hit “Insert“.
Ctrl + Shift to Select Multiple Rows/Columns
When you start using Microsoft Excel, you can’t escape using the mouse to navigate everything unless you are already comfortable using keyboard shortcuts.
So, to help you save time from dragging endless rows/columns till you hit the required selection, press Ctrl + Shift and then use the arrow buttons on the keyboard to continue your selection.
Add a Drop-Down List
You need to first give the list of values you want to see in the drop-down list in some part of the spreadsheet as the ‘source’.
After this, all you need to do is select the cell where you want the drop-down list and choose the source cells. Once done, you need to navigate your way to “Data Validation” under “Data” menu and change it to “List” while specifying the sources.
Pivot Tables
When you are dealing with more data in hand, it is essential to summarize it. Not just as an overview, but the ability to get the total tally of all the figures is a time-saver feature.
Pivot tables will help you with that. You just need to insert a pivot table and choose a data source.
Once done, it will let you navigate through different datasets and let you quickly find out the sum or average while filtering through them.
Remove duplicate entries
While you should always aim to avoid duplicate information in your sheet, Excel provides you with a quick way to get rid of them.
You have to select the row or column you want to clean and click on “Remove Duplicates“. It will prompt you to confirm the selection or change it and delete it when you proceed.
