translated from Spanish: 10 Excel functions “that everyone should learn,” according to Harvard University

Populate an Excel may seem the most tedious and boring world. However, the system of spreadsheets that launched Microsoft at the end of the 80s can be much more interesting and useful than many imagine.
According to the prestigious Harvard University (in Massachusetts, USA), 10 of the 500 functions offered by the program are basic and necessary, and “everyone should learn them”.
The educational institution said on its web site Harvard Business Review that 750 million people work daily to maintain updated Excel, and that the product of Microsoft stands out from other spreadsheet applications.
The “time Bank” created by an adviser of J.F. Kennedy, who became an international phenomenon, “We took a decade teaching and testing Microsoft Excel”, is read in the magazine. “A survey that we produce to several employees of office suggests we had more than 10% of our working life doing spreadsheets.”
“For those who work in research and development or finance, this figure rises to 30%, about 2.5 hours a day”.
The problem is that not all of them are just as good when it comes to handling spreadsheets or know the basic formulas.
We then show you a Decalogue of Excel functions that Harvard University considers “especially easy to learn” (you need two hours in total) and who “can make a difference” when it comes to “save time and improve productivity”.
Harvard University says that well use certain Excel features can be key to win time and productividad.1. Stuck (10 minutes of learning) special Excel allows you to copy all the information from a selection of cells and paste the data in other cells specified, but you can use an option called “Paste Special” to paste the information in another way.
The Paste Special serves so you can choose which elements copies and which are not. That way you avoid copy formats or unwanted formulas.
How to run it?: when you copy what’s in your cell (Ctr+C) press Ctrl+Alt+V to make a paste special and select what you want to copy. ALT+E+S+V is the command to paste these elements. You can also use the drop-down menu and choose the option in the dialog box.
2. Insert several rows (2 minutes) sometimes, you need to add several rows of time to a table, and one not always is the more practical. This feature speeds up the task.
How to run it?: using a keyboard shortcut (Ctrl, shift, +) select the number of rows that you want to add and click on the right button for inserted. You can also use the command “Insert Sheet rows”, provided you can select rows that you are going to add before.
3 filler (30 minutes) “fast filling (Flash Fill in English) is like magic and can be used in different situations”, Harvard Business Review is read in the . “It’s a great timesaver when you need to enter or change data quickly and accurately; It is an amazing method.”
It is a system that allows you to Excel to learn certain things from the work you do and identify patterns to complete some cells, filling them automatically.
For example, a full name suggests, if you put names and last names in two different columns, or allows you to separate them if the put together.
The filler automatically completes data when it detects a pattern. how to run it?: you must press Ctrl + E, or go to the “Data” tab and enable “Fast fill”. If you do not see this option, test in “Advanced” and “Automatic fast fill”, says Microsoft Office on their help page.
4. index and match (45 minutes) “Index” and “Match” are widely used in Excel to perform complex searches. Separately, they are not too interesting, but when the combine is appreciated his power, say the Harvard specialists.
Both allow you to extract data and specific values that large spreadsheets, you need to make”a routine and tedious task quick and easy”, explained from the University.
This method requires a bit more time (and patience) to learn it, but will allow you to be much faster when looking for certain data.
How to run it: “Index” has two syntaxes: array and reference. The first (array, row_num; [núm_columna]) is used to perform searches. “Match”(valor_buscado,matriz_de_busqueda;)[tipo_de_coincidencia]) is used to find the position of a value in a list.
5. quick sum (2 minutes) according to Harvard, this is one of the first functions you probably learned in Excel: how to add a row or column. But know that you can select the cell at the end of a row or column in just a few seconds?
How to run it: select the cell and click on Ctrl + Shift + = to activate the function. Equals “AutoSum”.
When you manage large amounts of data, it is useful to use the formula adecuada.6. CTRL Z / CTRL and (1 minute) probably already know that Ctrl + Z is used to reverse errors. As well, there is another option to recover that deleted: the command Ctrl + and.
It is like a free pass that saves you from any problems, says the American University.
How to run it?: very easy: press Ctrl + and to reverse any “error” that you removed with Ctrl + z. 7. Remove duplicates (10 minutes) this formula is simple and quick to use. Obviously, serves to what one would expect: remove values that are repeated in any set of data in Excel.
Harvard University recommends placing on another sheet of paper what you want to delete. System leaves the first value listed in the table by default.
How to run it: select the table, go to the “Data” tab and click on “Remove duplicates”. The program will ask to which column apply you that change.
Grouping the data can be simple, but how to find them in Excel effectively? 8. Freeze panels (15 minutes) freeze rows and columns can be used to contrast data you have away from others in the worksheet. In doing so, you will see them on the screen when you browse through it.
How to run it: choose the part of the worksheet that you want to freeze, and click on the menu “view” or “display”. Then select “freeze top row” and “freeze column”, click on what you want to freeze.
9 F4 (10 minutes) F4 has two specific functions in Excel: create an absolute reference and repeat your action or most recent command.
How to run it: simply press F4 when you want to do one of those two things. Most of the times will repeat the last action. If you press the key while you have highlighted a cell with a formula, it will give you options of possible references.
10 Ctrl + Arrows (5 minutes) this option is especially practical for large databases, because it allows access to the last digit in a matter of seconds, without stroll across the table manually.
How to run it: run the command with Ctrl + down to get to the end of document, or move it with the arrow keys. Also will help you to select data in seconds and maximize your productivity.

Original source in Spanish