Excel, the spreadsheet program in Microsoft‘s Office suite, is undoubtedly the most widely used tool in its field in any company or organization. That is why if we work with it on a regular basis it is important that we know how to dominate. It is always important to let our superiors know that they can count on our help in case of a problem with Excel, making us a valuable employee.
Although we use Excel regularly in our work, it is almost impossible for us to know each and every one of the functions that it incorporates, much less know how they are used. But from basic to advanced level knowledge will always give us a small advantage in our work. Therefore, these are some of the functions that we should learn to master to work.
Find and remove duplicate data
One function that we can find very useful to learn is to find and eliminate duplicate data. If we work in a company with a strong database, it is possible that there are spreadsheets that are a mess with duplicate rows. If we know how to find and eliminate duplicate data, it will help us to better organize our work and give the appearance of being a well-ordered worker.
To solve this we can use the conditional format to find and highlight duplicates. In this way, we can review duplicates and decide if we want to remove them. This is something we can easily do from “Home”> “Conditional Formatting”> “Highlight Cell Rules”> “Duplicate Values”. Next, in the table we choose the format that we want to apply to the duplicate values.
Recover deleted or lost Excel files
There are few worse things that can happen to us while we work for some reason our computer or Excel crashes causing us to lose all the work that we had done and had not been previously saved . However, all may not be lost and there is a chance to save the job and avoid having to start over.
For this we are going to follow the path “File”> “Information”> “Manage book”. Here we will find the option “Recover unsaved books” , which will allow us to examine recent files that have not been saved in order to recover them. This will open a window with the folder where the unsaved files were left and that we can select to recover them again.
Use pivot tables to summarize data
We may, in our work, have to work with spreadsheets that are full of items and each one is part of a different category. This can cause a major mess that makes our work difficult. Luckily, Excel allows us to create dynamic tables that will help us to show us a summary of the first line of the data by category.
To do this, we only have to highlight all the data cells and click on the “Pivot Table” button that we will find within the “Insert” tab. After clicking on the new window, a blank pivot table will appear in a new tab. We only have to mark the names of the columns that we want to summarize and the table will be completed.
Freeze panels to lock rows and columns in Excel
If we have to work with very large spreadsheets, with a large number of rows and columns, it may be that when we scroll to certain columns and go down to the cell very far from the beginning, we can no longer see the name and the header cells. This is something that we can avoid because Excel allows you to freeze some columns and rows so that they remain visible on the screen at all times.
Therefore, if we want to keep a visible area of our spreadsheet while we move to another area of it, we can use the “Freeze” option that we find within the “View” tab. From here we can lock specific rows and columns in place, as well as Split panels to be able to create independent windows within the same spreadsheet.