There are a lot of websites with Excel tips and tricks on the web, but there are some tips I use regularly to save time.
Select All Button
This may seem obvious to experienced Excel users but if you haven’t discovered it yet, it can save you a lot of time when you are copying or moving data. It’s that small grey triangle between Column A and Row 1.
When you click the triangle, it will select all the cells on a worksheet. Pressing CTRL and A together also does this, except when the active cell is to the right or above the data on the sheet. In which case, you need to press CTRL and A again to select all the cells. This is why I prefer to use the Select All Button.
Auto Filling Part 1
When you click on a cell and hover over the small green square in the bottom right corner, the pointer will change to a solid black cross. If you then click and drag (hold the mouse button down), Excel will intelligently autofill the cells you select until you let go of the mouse button.
Auto Filling Part 2
The above technique is useful if you’re not filling too many cells, but what if you want to autofill a lot of cells? Simply double-click when the pointer is a solid black cross. This will fill a column until an empty cell is reached.
You can change how Autofill works using the Fill drop-down menu in the Editing section on the Ribbon at the top of the Excel window.
Removing Formulas from a Group of Cells
This is a handy trick, particularly when you want to sort a group of cells. Sometimes Excel does not sort them as you would like if they have formulas in them.
Select the range of cells you want to remove formulas from and click Copy in the Clipboard section on the Ribbon. Now click on the drop-down menu below the Paste icon and select Paste Special. A Paste Special pop-up window will appear. Select “Values and number formats” and click the OK button.
Excel will overwrite the cells you selected with the same values and number formats, but the formulas will be removed.
Editing Multiple Worksheets at The Same Time
Did you know you can make the same changes to multiple worksheets simultaneously? For example, you can insert the same column in the same place on all the sheets you select.
Click on a worksheet tab at the bottom of the Excel window, then hold CTRL down while clicking on the tabs of the other worksheets that you want to make the same changes to. A green line should appear on the tabs you have selected.
Now if you make a change on one sheet, it will appear on all of them.
CTRL and click again on a selected tab to stop it from being included in the edits.
Important Note: Not all edits work well with this trick. For example, Autofill may overfill a column if there are a different number of rows of data on the selected sheets.