For beginners and advanced users, there are still many useful tips and tricks that are forgotten about. Here are some of the useful Excel spreadsheet secrets you may not know. Please note that all these functions are based on Microsoft Excel 2010.
1. One Click to Select All
You might know how to select all by using the Ctrl + A shortcut, another way is a simple click of the corner button, as shown in the screenshot below, all data will be selected in seconds.
2. Shift Between Different Excel Files
When you have different spreadsheets open, it’s really annoying shifting between different files because sometimes working on the wrong sheet can ruin the whole workbook. Using Ctrl + Tab you can shift between different excel files freely. This function works with other applications as well, try some others out.
3. Add More Than One New Row or Column
You may know the way to add one new row or column, but it really wastes a lot of time if you need to insert more than one of these by repeating this action X number of times. The best way is to drag and select X rows or columns (X is two or more) if you want to add X rows or columns above or left. Right click the highlighted rows or columns and choose Insert from the drop down menu. New rows will be inserted above the row or to the left of the column you first selected.
4. Fast Navigation with Ctrl + Arrow Button
When you click Ctrl + any arrow button on the keyboard, you can jump to the edge of the next range in different directions. If you want to jump to the bottom line of the data, just try to click Ctrl + downward button.
5. Input Values Starting with 0
When an input value starts with zero, Excel will delete the zero by default. Rather than reset the Format Cells, this problem can be easily solved by adding a single quote mark ahead of the first zero, as shown.
6. Speed up Inputting Complicated Terms with AutoCorrect
If you need to repeat the same value and it is complicated to input, the best way is to use the AutoCorrect function, which will replace your text with the correct text. Take my name, Matthew Ross, for example, which can be replaced by MR. If I input MR into a cell, it can autocorrect to Matthew Ross. Go to File->Options->Proofing->AutoCorrect Options and input Replace text with correct text in the red rectangular area.
7. Rename a Sheet Using Double Click
There are multiple ways to rename sheets, and most users will right click to choose Rename, which actually wastes a lot of time. The best way is to just click twice, then you can rename it directly.