Moving around Excel like a fish in water is essential if you want to advance your work. Although it is true that today we have multiple alternatives to create spreadsheets, Microsoft's still continue to be one of the most powerful tools to create data files .
What we propose today are ten simple, but practical Excel tricks that will help you master this useful tool . Or at least to start. They will help you manage your files and data in a more agile way and without the need to complicate yourself.
Here are 10 useful tips for mastering Excel . We have used the latest version of Excel 2016 as a basis for most of the captures, although most of the tips also work with previous versions of the program.
1. Move data
Let's start with something fundamental. Transfer data from one cell to another . One of the actions that we perform the most when we are working with an Excel document. It is actually a very basic gesture, but one that should be known.
Thus, if what we want is to move a column with all the data it contains , it would be enough to select it and move the mouse to the edge, until a cross of arrows appears. Then you just have to drag the column to place it in the right place. If we also want to copy this data, it will only be necessary to press the Control key. And then drag.
Once we have selected an Excel column, simply move the cursor to one of the edges until the crosshairs appear and then drag the column
2. Select all
When you have to select an entire data table in Excel, the work can be very cumbersome. Especially if the document is large. The utility of selecting everything is very practical when exporting data to another table, for example. The only thing you have to do if you are using a version prior to Excel 2016 is to press the combination of keys Control + A. You can then carry out all the actions you consider (copy, cut ...). In case of using Excel 2016, the shortcut that has worked for us is Control + Shift + space bar.
You should also know that there is an even faster way to select everything that works in these versions. And it is to press the arrow that is right in the corner of the Excel table (above the number 1 and next to the letter A). One click is enough. It's not one of the smartest Excel tricks by any means, but it will save you time.
3. Add quick access to a tool
If you regularly work with Excel, you will know how important it is to have all the tools at hand . Otherwise, a lot of time is wasted and you spend more time trying to find buttons and shortcuts than doing your own work. Then what do we do?
Well, very easy. Create quick shortcut buttons for the tools we use the most. In principle, with the native version of Microsoft Excel you will have three quick access buttons , which you will find located at the top of the menu. They are the Save, Undo writing and Redo writing.
To change or add new shortcuts, all you have to do is access the File> Options> Quick Access Bar section . Add the buttons that you consider appropriate and remove the ones that are not useful to you. When you're done, choose OK.
Another option to access this function is to click on the button with the arrow just to the right of the quick command buttons. Then a contextual menu will open with the most common accesses that we can place. In case of clicking on "More commands ..." we will open the options window that we talked about just before.
4. Restricts the data that can be entered in the spreadsheet
Imagine that you are working with data and it only moves in a series of values. For example, with price tables whose maximum value will never exceed 10,000. In similar cases, it can be beneficial to put a series of limits on the input of data so that a warning appears every time we go outside this range.
To do this, you have to go to the Data option at the top and then Data validation. Within the Configuration tab, the different limits can be established, both through numerical values and the length of the text that will allow us to enter in the cell. If we are sharing work with other people (or want to set a reminder), we can include an input message and customize the error message itself.
5. Hide rows
Another useful Excel trick that can give us a lot of play is hiding rows that we do not want to be visible. It is about that you can hide some columns with results that are not interesting or that can / should remain private . This is especially useful when sharing a working document, but on which it is not important to see the "back room".
To hide rows, the best thing to do is select the cells in question and then click on the combination Control and 9. You will see that they disappear instantly. If you want to recover them, you have it quite easy. You just have to combine the following keys: Control + Shift + 8 . They will appear again immediately.
Fuzzy Search View in Excel 2010
6. Perform a rough search
As its name suggests, fuzzy searches are a mechanism to find elements or values that we are not entirely clear or that we do not remember with precision. Usually to perform a search in Microsoft Excel only need to press Control + B . A menu is immediately activated so that we can enter the parameters.
If you are not sure what exactly you are looking for, you can use the question marks and the asterisk . The first is used to represent a character and the asterisk can represent one or more.
7. Add multiple rows or columns at once in Excel
Inserting a new row or column can be seemingly easy. But what happens when we need to add more than one row or column and we don't want to waste time? Don't worry, you don't have to repeat the operation several times. In this case, all you have to do is select the number of rows or columns you want (two or more, in this case).
Then, right-click on the highlighted rows or columns. What you have to do next is choose the Insert option, within the drop-down . Instantly the rows or columns will have been added, just to the left of the one you originally selected.
Comment view in Excel 2010
8. Insert a comment in a cell
Inserting comments in a cell can be very useful if we are working on a collaborative document or are going to send it to someone. Or even if, by making the document alone , we want to include guidance notes for later revisions.
To insert a comment, it would be enough to place ourselves on the cell that interests us and press the Shift key and F2 . You will see that a small box is activated in which you can include all the text you need. This box can be resized by dragging the mouse on one of the border points.
Once you have finished with the comment, it will disappear from the default view and will be indicated by a small icon in the cell itself . Every time you hover over it the cursor will be shown again. By the way, to edit or delete the comment, you just have to click with the right mouse button and choose the option.
9. Browse quickly
Navigating through a data-heavy Excel document can end up being very tedious. The best thing to do in these cases is to use the quick navigation shortcut. Press the Control key at the same time that you press any other arrow key on the keyboard . You will see that the movement is fast and agile. The interesting thing about this way of moving is that it only does it between cells that have data, so you will quickly jump those that are empty.
10. Include values starting with 0
And we end with a simple Excel trick, but very useful in certain cases. Usually when we add a value that starts with zero, Excel removes it. The only thing to do in these cases is to include a quotation mark in front of the first zero . That easy. Once the number is entered, it will be shown without the quotation mark and with the zero.
These are just some of the Excel tricks that we can discover when using the program. It hides many more, and with an increasing level of complexity.