No one can ignore the important role Excel plays our daily work. However, both for beginners and advanced users, there are still many useful tips and tricks that are inevitably overlooked. Here are 10 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, but few know that with only one click of the corner button (top left where row 1 meets the A column) all data will be selected in seconds.
2. Open Excel Files in Bulk
Rather than open files one by one when you have multiple files you need to handle, there is a handy way to open them all with one click. Click whilst holding the Shift key to individually select the files you would like to open, then press Enter on your keyboard to open all files simultaneously.
3. Create a New Shortcut Menu
When Excel is first installed, there are generally three shortcuts in the top menu, which are Save, Undo Typing and Repeat Typing. However, if you want to use more shortcuts, such as Borders, you can set them up as follows:
Click File > More > Options > Quick Access Toolbar, then Borders from the left column to the right and save it. You will see two more shortcuts added in the top menu.
4. Shift Between Different Excel Files
When you have different spreadsheets open, it can be confusing shifting between different files and working on the wrong sheet can disrupt the whole project. Using Ctrl + Tab you can shift easily between different files.
5. Add More Than One New Row or Column at a Time
You may know how to add one new row or column, but it can take time to insert more than one by repeating this action many times over. Instead, simply drag and select X rows or columns (X is two or more) then right click the highlighted rows or columns before choosing Insert from the drop down menu. Hey presto – it’s done.
6. Speedily Delete Blank Cells
For various reasons, large spreadsheets may accumulate high numbers of blank cells. If you need to delete these to maintain accuracy, especially when calculating the average value, the speedy way is to filter out all blank cells and delete them with one click. Choose the column you want to filter, go to Data->Filter, after the downward button shows, undo Select All and then pick up the last option, Blanks. All blank cells will show immediately. Go back to Home and click Delete directly, all of them will be removed.
7. Vague Search with Wild Card
You may know how to activate the speedy search by using the shortcut Ctrl + F, but there are two main wild cards—Question Mark and Asterisk—used in Excel spreadsheets to activate a vague search. This is used when you are not sure about the target result. Question Mark stands for one character and Asterisk represents one or more characters.
8. Fast Navigation with Ctrl + Arrow Button
When you click Ctrl + any arrow button on the keyboard, you can jump to the edge of the sheet in different directions. If you want to jump to the bottom line of the data, just try to click Ctrl + downward button.
9. Transpose Data from a Row to a Column
You start organising your data in rows only to find it would work better as columns. No need to retype your entries, simply use the Transpose function in Paste. First you must copy the area you want to transpose, then move the pointer to another blank location. Go to Home > Paste > Transpose (it’s the icon with the vertical and horizontal arrows). Please note this function will not activate unless you copy the data first!
10. Input Values Starting with 0
You probably know that 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.
