Microsoft Excel is one of the most used productivity tools out there. The things that you can do with it is endless. Many people use this tool every day to improve the accuracy and productivity of their works. If you are also one such person who use this tool regularly, knowing the following simple yet powerful Microsoft Excel tips will help you to work faster.
If you are using Microsoft Excel regularly, knowing the following simple yet powerful 10 methods, you can save your time and will help you work faster.
01. Edit the active cell (F2)
Use the F2 key to edit the active cell and put the insertion point at the end of its contents. For example, as shown in the following animation, press the F2 key when the cell is active. Then you can see that the cursor start flashing at the end of the content of the cell (after “AA” in the example). Also, you can use arrow keys to move the cursor to the position you wish to edit. If you use left arrow when writing a formula, the left cell will activate instead of moving the cursor. But, if you press the F2 key once before using the arrow key, you can move the cursor as expected.
02. Fill Selected Cells with Same Data or Formula using Ctrl+Enter
Ctrl+Enter is another powerful shortcut which you can use to fill the selected cell with the same data or Formula. This shortcut works even if the selected cells are not in a continuous range.
03. Generate Random Values
You can use the RAND() function to generate a random value between 0 and 1. Use RANDBETWEEN(bottom, top) to get a random number between two specific numbers. For example, use RANDBETWEEN(100, 200) to generate a random number between 100 and 200.
04. Convert Functions to Values (Mouse Right-Click Drag and Drop)
Sometimes you may want to convert cells with functions to just values. This can be easily done with your mouse right button. To convert functions to values, first select your data range. Then move your mouse pointer to the border of the data set. When the mouse pointer appearance changes as show below, click the right button of the mouse. While holding down the mouse button, drag the selected dataset a little bit of away and bring back to the required position and release the mouse. When you release the mouse, you can see a new menu appeared. Select “Copy Here as Values Only” option.
05. Moving Among Cells (Ctrl+Arrow)
When you are working with a large dataset, you may want to move to the end or beginning of a row or column. You can do this easily with Arrow Keys while pressing down the Ctrl key. See the bellow gif to learn how to use this shortcut.
06. Selecting Data (Ctrl+Shift+Arrow)
You can use Ctrl+Shift+Arrow to select a column or a row. After selecting a row or a column, you can extend the selection further using arrow keys as appropriate. Watch the following self-explanatory gif to learn how to do it.
07. Always use Tables
Excel tables are a powerful tool which can help you to save your time by a considerable amount. Excel table includes many attractive features like easy sort and filtering, auto-filling of formula, quick formatting, easy data entering, meaningful cell referencing, quick statistics, dynamic charts, always visible headers in scrolling and much more…
For more information about Excel tables, read “10 Benefits of Converting Your Data into Excel Tables“.
08. Lock Cells, Rows and Columns with F4
When you are dragging a formula to fill adjacent cells, your cell references are changing accordingly. If you want an absolute reference, you must lock that specific cell or cells. You can use the “$” mark in front of the column reference or row reference or front of both as appropriate. However, doing this manually is time-consuming. Instead, you can use the F4 key as described in the self-explanatory gif given below.
09. Copy Formatting (Mouse Right Key Drag and Drop)
You can use the same method used in the 4th tip, for converting functions to values to copy the cell formatting to another cell easily. For this select, the option “Copy Here as Formats Only” as shown in the following gif.
10. Repeating the Previous Action (F4)
Most of the time, we need to repeat the same action number of times when working with Excel. You can use the F4 key to do repeat the previous action easily as shown in the below gif.