1. Navigating your Worksheets
The next time you are looking to move through the worksheets in your workbook using the navigation keys (buttons found to the left of sheet 1), don’t left click, right click instead, this should bring you a list of all the worksheets in that workbook.
2. AutoFill (The Fill Handle)
Unfortunately, there are still people out there that either are copying one cell at a time, or clicking and dragging down to copy formulas all the way down a list/column. No Need, let Excel do the work for you.
Double-click the fill handle (little box down the bottom right hand corner of cell selected) to automatically fill down when your formula resides aside of other data. It’ll stop when there’s no data alongside it any more or the first break in the data.
3. Creating the same Table/List in Multiple Sheets (Grouping Worksheets)
The Next time you’re looking to create a table in Excel but have the exact same table in multiple Worksheets.
Start by selecting the First Sheet (click on sheet Tab) and while holding the Shift Key, select the last sheet in the group.
Now whatever task you perform in the first sheet gets applied across the board.
Make sure when you are done to deselect the sheets, so they become individuals again, just click on a sheet that’s not part of the group.
4. Duplicating a Worksheet(s)
You have created a table in a worksheet and now you are looking to copy the contents into another worksheet. The most common way of copying this is to either try copy and then paste or Right click on Worksheet tab and select create a copy.
There is a however a quicker way, left click on worksheet tab as if your about to move the worksheet but hold down the CTRL key and the same time. Drag or move this sheet (keeping your finger on the CTRL key). This will duplicate the contents of the worksheet.
5. Quickly Transposing a Table or List
Select the data (List or Table) you want to transpose.
Select Copy from the top left of the Home tab or use Ctrl + C as an alternative option.
Next click on a blank cell within your worksheet, now click on the Paste dropdown button on the Home tab.
A list of options will appear. Choose Transpose from this menu (last option on the second row)
Excel copies the selected cells into the new cells
6. Shortcut Key for AutoSum
To sum up a group of cells without having to look for the AutoSum button, try hitting Alt + =
7. Removing blank cells from a List
Highlight all cells (See shortcuts), switch on filters. From one of the drop-down menus, deselect everything and then choose just the blanks option. Then Highlight and Delete
Highlight all Cells, this time go to the home tab and the Find & Select button. From there select go to Special; in that dialog box choose blanks. Right click on any highlighted area and delete. (Make sure these are blank rows or columns not individual cells).