10 MS Excel functions “that we should all learn”, according to Harvard University

10 MS Excel functions “that we should all learn”, according to Harvard University

2 views

Filling an Excel may seem like the most tedious and boring thing in the world. However, the spreadsheet system launched by Microsoft in the late 1980s 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 “we should all learn them”.

The educational institution highlighted on its Harvard Business Review website that 750 million people work daily to keep Excel updated, and that Microsoft’s product stands out from other calculation applications.

“We have been teaching and testing Microsoft Excel for a decade,” reads the digital magazine. “A survey we made to several office employees suggests that we spend more than 10% of our working lives doing spreadsheets.”

“For those who work in research and development or finance, that figure rises to 30% , about 2.5 hours a day.”

The problem is that not all of them are equally good at handling spreadsheets or they know the basic formulas.

Below, we show you a decalogue of Excel functions that Harvard University considers “especially easy to learn” (you need about two hours in total) and that “can make a difference” when it comes to “saving time and improving productivity”.

Harvard University
Copyright of the GETTY IMAGES image
Image caption Harvard University says that using Excel functions well can be key to gain time and productivity.

1. Special stuck (10 minutes of learning)

Excel allows you to copy all the information from a selection of cells and paste the data into other specified cells, but you can use an option called “special paste” to paste the information in another way.

The special paste serves so that you can choose which elements copies and which do not. That way you avoid copying unwanted formats or formulas.

How to execute it? : when you copy what is in your cell (Ctr + C) press Ctrl + Alt + V to make a special paste and select what you want to copy. Alt + E + S + V is the command to paste those 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 doing it one by one is not always the most practical. This function streamlines the task.

How to execute it? : using a keyboard shortcut (Ctrl, shift, +) select the number of rows you want to add and click on the right button to insert them. You can also use the “Insert sheet rows” command, as long as you first select the rows you are going to add.

3. Fast filling (30 minutes)

“The Flash Fill in English is like magic and can be used in different situations,” reads the Harvard Business Review . “It’s a fantastic time saving when you need to enter or change a lot of data quickly and accurately, it’s an amazing method.”

It is a system that allows Excel to learn certain things from the work you do and identify patterns to complete some cells, filling them out automatically.

For example, suggest a full name if you put names and surnames in two different columns, or you can separate them if you put them together.

excel
Copyright of the GETTY IMAGES image

Image caption The fast fill automatically completes the data when it detects a pattern.

How to execute it ?: you must press Ctrl + E, or go to the “Data” tab and activate “Quick Fill”. If that option does not appear, try “Advanced” and “Automatic Fast Filling”, says Microsoft Office on its 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 you combine them you can appreciate their power, say Harvard specialists.

Both allow you to extract data and specific values ​​that you need from large spreadsheets, so that “a tedious and routine task is quick and easy,” they explain from the university.

This method requires a little more time (and patience) to learn it, but it will allow you to be much faster when looking for certain data.

How to execute it ?: “Index” has two syntax: matrix and reference. The first (array; num_file; [column_num]) is used for searching. “Match” (search_value, search_array, [match_type]) is used to find the position of a value in a list.

5. Quick addition (2 minutes)

According to Harvard, this is one of the first functions that you probably learned in Excel: how to add a row or column. But, did you know that you can select the cell at the end of a row or column in just a few seconds?

How to execute it ?: Select the cell and click on Ctrl + Shift + = to activate the function. Equivalent to “Autosuma”.

man writing formulas on a blackboard
Copyright of the GETTY IMAGES image
Image caption When you handle large amounts of data, it is useful to use the appropriate formula.

6. CTRL Z / CTRL Y (1 minute)

You probably already know that Ctrl + Z is used to revert errors. Well, there is another option to recover what you deleted: the Ctrl + Y command.

It’s like a free pass that saves you from any problem, says the American university.

How to execute it ?: very easy: press Ctrl + Y to revert any “error” that you deleted with Ctrl + Z.

7. Remove duplicates (10 minutes)

This formula is simple and quick to use. Obviously, it serves for what one would expect: eliminate repeating values ​​in any data set in Excel.

Harvard University recommends placing on another sheet what you wish to erase. The system leaves by default the first value that appears in the table.

How to execute it ?: select the table, go to the “Data” tab of Excel and click on “Remove Duplicates”. The program will ask you which column you will apply that change to.

woman using laptop
Copyright of the GETTY IMAGES image
Image caption 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 that you have away from others in the spreadsheet. By doing so, you will see them on the screen when you browse through it.

How to execute it ?: choose the part of the spreadsheet you want to freeze and click on the “view” or “visualization” menu. Then select “freeze upper row” and “freeze column” by clicking on what you want to freeze.

9 F4 (10 minutes)

F4 has two specific functions in Excel: create an absolute reference and repeat your most recent action or command.

How to execute it ?: simply, press F4 when you want to do one of those two things. Most of the time it will repeat the last action. If you press the key while you have highlighted a cell with a formula, it will give you options for possible references.

10 Ctrl + arrows (5 minutes)

This option is especially practical for large databases, because it allows access to the last figure in a matter of seconds, without going through the entire table manually.

How to execute it ?: Execute the command with Ctrl + Down to reach the end of the document, or scroll through it with the arrows. It will also serve to select data in seconds and maximize your productivity.

About author

Rava Desk

Rava is an online news portal providing recent news, editorials, opinions and advice on day to day happenings in Pakistan.

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

Your email address will not be published. Required fields are marked *