Despite several spreadsheet tools and other similar applications, we still prefer to work with the Microsoft Excel himself. Indeed, this spreadsheet makes your work quite practical if you know how to use it. Whether you are a small businessman or you work in a corporate office, the Excel spreadsheet has become a very useful and essential resource for everyday users.
This application is mainly used to record and manipulate digital data for various purposes. Because of its simple functions and easy-to-use features, it has always been the primary medium for every business user. In this guide, there are the top 10 useful Excel tips and tricks you should know.
Microsoft Excel Tips and Tricks
In this article, we will cover the following Excel tips and tricks, which we think are most useful:
- Select all cells at once
- Automatic adjustment column width
- Transpose columns and rows
- Use the filter command to hide unwanted data
- Remove duplicate entry
- Delete blank records
- Insert blank lines in the record
- Automatically copy data to another sheet
- Take a screenshot with Excel Camera
- Link cells to your email address
Let’s start and get to know each one in detail.
1]Select all cells at once
Sometimes you may need to make massive changes to your Excel spreadsheet. In this case, you can select all the cells in your worksheet using the square box available in the upper left corner of the worksheet.
When you select the square box, it also highlights all cells in the worksheet, even empty cells. You can also use the keyboard shortcut Ctrl + A to select all cells in the worksheet.
2]Automatic adjustment column width
While working on MS Excel with a large amount of data records, it often becomes complicated to resize each column cell according to its text. To easily resize columns, you can use the Auto Adjust Column Width feature which is a feature built into Excel.
Everything you need to move your mouse pointer over the limit of the column you want to adjust automatically, then double-click it. And if you need to adjust all the columns, select them all and double-click on the limit of any column.
You can also resize the column width using Automatic adjustment column width option. To do this, select the column you want to adjust, then go to the menu bar> Home tongue. in the Cells section click on the Format then select Width of automatic adjustment columns from the drop-down menu.
3]Transpose columns and rows
Some people create Excel spreadsheets in which the main fields cross horizontally in a row. Others put the data vertically. Sometimes these preferences lead to a scenario in which you want to transpose Excel data. Transpose is an Excel function that allows you to switch Excel columns to rows without retyping the data.
The transpose function is a great feature that you will get in MS Excel spreadsheets. This becomes very useful when you need to flip the data columns from horizontal to vertical without retyping the data.
In the image above, the data is arranged in the form of a table where the figures of the subjects are displayed vertically. If we had to rearrange the data horizontally manually, that would be quite difficult. But with the help of the transpose function, you can do it in seconds.
So you must first select the data from the table and copy it. Now place your mouse pointer where you need to transpose the data, right click and select Special bonding in the context menu. When the Special Paste window opens, check the Transpose function and click the OK button.
4]Use the filter command to hide unwanted data
The Excel filter function allows you to hide unwanted data and display only certain important data in the spreadsheet. Using this feature, you can easily filter the names, dates, and numbers from the dataset or huge table. However, it does not delete or modify the original data. This becomes very useful when you need to focus only on a particular type of information.
In the dataset above, you can see that there is a list of students whose grades are different, their address is different. It can be even bigger. So if you have to filter specified data, it can be quite difficult.
To do this easily, you can use the filter command. So everything you need to select the table header and then go to Editing section. In the corresponding section, select the Sort and filter then choose it Filtered tool.
Click the down arrow option that appears next to the header of each column. It allows you to sort your data alphabetically or organize the numbers from smallest to largest. In addition, you can use the text filter to filter the data as needed.
5]Remove duplicate entry
It is quite easy to locate and remove duplicate entries for a worksheet containing a small set of data. But if it has to be done with a large dataset where the columns and rows are in the hundreds, this would indeed be a difficult task.
In the table above, there are ten records in which two entries are completely similar and a common name was used on three lines. So to find duplicate entries, you need to select the table. After that go to the Data menu, select the Data tools category and then click Remove duplicates.
In the dialog box, check the box next to My data have headers. And, under the column section, remove the serial number mark as it is different for each entry. Finally, click on the Okay and you will see that the duplicate entry is removed from the table.
6]Delete blank records
Sometimes you may have noticed blank cells or lines in your spreadsheet that are mostly unwanted. Users usually right click on the specified line and delete it one by one. But what will you do if their number is hundreds? In this situation, deleting them one by one would be really tedious work.
Therefore, in order to delete them all at once, press the shortcut key Ctrl + G and when Go to dialog box appears, select the Special button. On the next page, mark the Whites radio button and click the OK button. You will notice that all the empty cells are selected.
Now press the Ctrl + minus button. When a context menu appears, select Move cells up and click the OK button. You will see, all the empty cells are deleted and the dataset is also organized appropriately.
7]Insert blank lines in the record
As I mentioned above, blank lines are generally unwanted, but it sometimes works too. So if you need to insert them, you can right click on the specified line and add them one by one as I said while removing the empty lines.
So, to insert blank lines in each separate record, do a series of the mentioned serial number. After that, select all the datasets, then click the sorting tool available on the Data tab.
In the next dialog, click the Sort by drop-down menu and organize them by serial number.
By clicking on the OK button, it will insert a blank line between each separate line.
8]Automatically copy data to another sheet
Sometimes you need to use data from another sheet as you enter the first sheet. To do this, you usually use the traditional copy and paste method. But, when you have to modify certain data in one sheet, he must also change it on the other sheet as well. It gets a little complicated when you have to change the same data on both sheets one by one.
In Excel, there is an option that automatically copies the data from the first sheet to other sheets in a real-time process. However, to automatically copy the data to other sheets, you must first establish a connection between them.
To do this, go to the status bar of the first sheet. After that, keep pressing the Ctrl key and click on the sheets to which you should copy the data automatically. That’s it.
Now, whatever data you enter on the first sheet, it is automatically copied to the other sheets.
9]Take a screenshot with Excel Camera
We have always said: “A picture is worth a thousand words”. Therefore, you will sometimes need to add a screenshot to your Excel spreadsheet in order to explain things better.
However, you don’t need to use a third-party app to capture a screenshot. Excel has a built-in camera control that helps you take the screenshot with one click.
To use this function, you must first activate the camera control. So everything you need to access the quick access toolbar and click on the Customize the quick access toolbar. After that click on the drop down menu of Choose commands > All commands> Camera. Now select the Add button and press OK.
After following the above procedure, you will notice that a camera icon is now added to the quick access toolbar. Now select the cells or data for which you need an image. After that click on the Camera icon and then click anywhere on the spreadsheet. You can also drag and resize the image to adjust it as needed.
ten]Link cells to your email address
In the Excel spreadsheet, you can easily create a hyperlink to your Gmail address.
To do this, you must first select the cell in which you want to insert the link. After that, press the shortcut Ctrl + K. When the dialog box appears on the screen, select the email address in the left pane. Add your email address, then press the OK button.
That’s it. I hope these tips and tricks can be helpful when using Excel spreadsheets on your computer.