17 Life-Saver 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.

Note: The online version (Office 365) or the mobile version may not display all the features available in the native desktop app on your Windows/Mac system. If you are specifically looking for an online-only option, there are other online spreadsheet services for teams.

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.

This is how the formula looks like:

=VLOOKUP(A2,A1:E5,3,FALSE)

In this case, the function looks for the email ID (in the third column) for Bryan in the second row (A2).

Here, I added the entire table range for search except for the first row. But, you can limit your range to C5, or D5 if you wish.

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.

To explore more about VLOOKUP, you can refer to the official resource by Microsoft. Also, you may want to explore all the other functions available in Excel to make the most of it.

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.

Here’s how it looks:

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 as shown in the image below.

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.

It can be also accessed using the shortcut CTRL Q.

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, as shown in the image above.

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

It is easy to select a value when you add a drop-down list to the sheet. But, how do you do that?

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 (from existing sheet or external sources).

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.

Auto Fill & Flash Fill to fill in values automatically

When you have a specific pattern of data to work with, the auto-fill and flash fill features can help you get things done faster.

Suppose you have to enter roll numbers of students that follow a pattern/gap. After entering two-three values in the row or column, you have to extend the selection to fill the rest automatically.

Similarly, let’s say you have two fields, “First Name” and “Last Name” but you want another column for the full name, Flash Fill will come in handy here.

You will have to select the number of rows you want to fill with existing values and click on the “Flash Fill” option. The rest of the blank fields in your selection will be automatically populated.

Split text into columns

Suppose you have a column of text that you want to split into two or more columns; you can do that using the “Text to Columns” option.

As instructed in the GIF above, you need to select the value you want to convert and choose the destination cells. You get to set the rules to split the text (by spaces, tab, comma, and more).

Instead of manually creating multiple entries from a single column, you get to move them using this neat little feature automatically.

Hide a worksheet

Considering an Excel workbook can include several worksheets, you may need to hide some of them just to limit what you’re viewing or for privacy.

Most of the users just leave all the worksheets in the view cluttering all the space. You can simplify your work by hiding what’s not immediately necessary. All you have to do is perform a right-click on the worksheet and hit “Hide” as shown in the screenshot.

Use a logical IF function to fill in values

Suppose you add a payment status column in a sheet, and you need it to be “PAID” when someone deposits $100 and “DUE” if someone paid less than $100. Of course, this is just an example. You can add a variety of logical functions and make things easier.

All you need to do is add logic to the cell where you want the status like:

=IF(D2=100,"PAID","DUE")

Here, D2 refers to the single cell of a column.

You can explore Microsoft’s help resources to know more about available logical functions and how they are used.

Take a screenshot and add it to Excel

If you want to add a screenshot of an active program/windows to your excel sheet, you do not have to separately use a screenshot tool (or snipping tool) and then manually add the picture.

Head to the “Insert” menu section and navigate your way through “Illustrations” to “Screenshot“. When you click on it, you can notice thumbnails of active windows launched on your computer. Just select the one you want, and it will be added seamlessly.

Conditional formatting to automatically set colors of a cell

Instead of individually formatting the cells to make sense of your data, you can save time by using conditional formatting.

With this feature, you get to use pre-existing rules or create new rules, and when the condition is met, the cell will be colored as per your preference. It is one of the most popular tricks to make the spreadsheet visually appealing and improve your data clarity.

Insert a Hyperlink using Ctrl K

If you have text in the excel sheet to insert a hyperlink, select the text/cell, press Ctrl K, and enter the URL of the web page you want to link.

Unlock The Full Potential With Microsoft’s Excel 😎

Combining some of the useful tips mentioned with other features, become an expert at using Excel.

For most users, Microsoft’s Excel might be a program only when a spreadsheet is in question, but for professionals who deal with excel every day, knowing these tips will save a lot of trouble and time.

Once you get to know some of the popular tips and tricks, you can explore more yourself. Also, it can be of great help if you utilize some of the useful Excel add-ins available.