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: 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 + CClick the cell where you wish to start the transposeRight-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: 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.
How to add a comment to a specific Cell?
You can add a comment to a specific cell if you wish to leave a message or offer feedback. A tiny indicator or sign displays in the cell’s corner when a comment is added. The comment can be modified or removed as necessary. Shift+F2 is the shortcut for adding comments, and you can also right-click and choose Insert Comment, like in the example below. You have the option to comment on a single cell, a column, or a row.
How to customize color for excel sheets?
To change the Color combination and customize each theme of your Excel sheet. Go to Page Layout -> Colors and select Customize Colors, as shown below. The new box will display various themes, as shown in the picture below.
How to identify and delete duplicate values?
The technique to eliminate duplicate numbers or data from an Excel sheet is highlighting it with a specific color before deleting it. To highlight the duplicate number, click Highlight Cells Rules from the Conditional Formatting tab on the Home tab, then go to Duplicate Values, as seen in the screenshot below. You can use a variety of color combinations to indicate duplicate values in the Excel sheet after choosing the Duplicate Values option, as illustrated in the image below. The duplicate values on the Excel sheet will be highlighted after choosing a color, as seen in the image below. The next task is to delete the duplicate values from the sheet. Go to the Data area in the Excel menu and select Remove Duplicates, like in the picture below. You’ve completed the task of eliminating all duplicate content from your Excel workbook.
Formatting
If you want to change the appearance of the cell without changing its content, then select the cell and right-click and go to Format Cells and you will get numerous options to format the particular cell as shown below. The formatting can be applied to an entire column, an entire row, or a specific cell.
Line break
If you wish to begin a new line of text or provide spacing between lines or paragraphs of text in an Excel cell, it is pretty easy to insert a line break. As demonstrated in the image below, select the worksheet cell, position the cursor where you want to create the line break, and then press Alt+Enter.
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. Try these blazing fast tools for quick Excel password recovery without corrupting your files.
