Although we use Excel regularly in our work, it is almost impossible for us to know each and every one of the functions 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 work organization 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 box 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 we had done and not previously saved. However, all may not be lost and there is a chance to save the job and avoid having to start over.
To do this we are going to follow the path «File»> «Information»> «Manage book». Here we will find the option of “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
In our work, we may 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. Fortunately, Excel allows us create pivot tables that will serve 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 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 option of “Immobilize” that we find inside 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.