When Excel was developed to the 2010 version, Microsoft provided more surprises than ever. You can never ignore Excel which helps you hugely to deal with tons of big data. Whether you are a beginner or an advanced user, you definitely would have overlooked many tips and tricks that are very useful in Excel. So, here are some Excel tricks which will make you and Excel guru.
1. One Click to Select All
We are all very familiar with the shortcut key, Ctrl + A which is used to select all. But, there is a button on the left hand side at the top of the spreadsheet, which can be used to select all. Just click the button, and within seconds, all data will be selected.
2. Open Excel Files in Bulk
Maybe you have multiple files which you need to open. Instead of opening them one by one, you can open all of them easily with just one click. All you need to do is select the files you want to open and just press the Enter key on your keyboard. You can see that all files will open simultaneously.
3. Shift Between Different Excel Files
When you are working on multiple spreadsheets, it is really annoying to shift between them as you might end up on the wrong sheet and in turn, spoil the whole project. So, you can easily shift between the different files by using Ctrl + Tab.
4. Create a new Shortcut Menu
You might have observed the three shortcuts at the top of the page in the Menu bar – save, undo typing and repeat typing. But, you might be willing to have more short cuts like copy or cut. You can set them up in the following ways: File > Options > Quick Access Toolbar. Now, you can add copy or cut from the left column to the right and save it. You can see two more shortcuts which will be added in the top menu.
5. Add a Diagonal Line to a Cell
While create an address list, you might need a diagonal link in the first cell to separate the different rows and columns. We all know that by clicking on Home > Font > Borders, we can change the different borders for a cell. But, when you click on More Borders, you will get more options, like a diagonal line. Just click it and save and you can make it immediately.
6. Add More than One New Row or Column
You are familiar with the way of adding a new row or column. But, when you have to add more rows and columns, you’ll just end up wasting a lot of time. So, the best way to add multiple rows or columns is to drag and select the rows or columns where you want to enter the new ones. Later, just right click on highlighted rows or columns and choose Insert from the drop down menu. You will see that they will be added.
7. Speedily Move and Copy Data in Cells
You can move a column of data in a spreadsheet in an easy way. Just choose the column and move the pointer to the border and when it turns to a crossed arrow icon, you can drag to move the column. If you want to copy the data, press the control button before you drag and the new column will copy the selected data.
8. Speedily Delete Blank Cells
For many reasons, some default data will be blank and if you need to delete these to maintain accuracy, the fastest way is to filter all the blank cells and delete them with a single click. Select the column that you want to filter and then choose > Data > Filter. When the downward button shows up, undo Select All and select the last option, Blanks. When you do this, all the blank cells will show automatically. Now, go back to home and click Delete. You can see that all of them will be removed.
9. Vague Search with Wild Card
There are 2 main Wild Cards – Question Mark and Asterisk which are used in Excel spreadsheets to activate a vague search. This is used when you aren’t sure about a target result. Question Mark stands for one character and the Asterisk represents one or more characters.
10. Generate a Unique Value in a Column
Some people use the Advanced filter which will be repeatedly applied when you want to filter a unique value from data in a column. Click to select the column and go to Data > Advanced. Now, you will see a pop-up window. When the screenshot shows, click Copy to another location. Now, you will have to specify the target location by typing the value or by clicking the area choosing button. In this particular example, the unique age can be generated from Column C and shown in Column E. Remember to choose Unique records only, and then click OK. The unique value in column E might be the contrast of the original data in C. Therefore, it is recommended to copy to another location.
11. Input Restriction with Data Validation Function
If you want to retain the validity of data, you have to limit the input value and provide some tips for more steps. You can take this example. If the age in a sheet has to be a whole number and the people in the survey should be 18 – 60 years, you need to reassure that data outside the range is not entered. So, go to Data > Data Validation > Setting. Then you have to input the conditions and shift to Input Message to give prompts like this: “Please input your age with whole number, which should range from 18 to 60.” When users are hanging the pointer in this area, they’ll get this prompt and a warning in case the input message is unqualified.
12. Fast Navigation with Ctrl + Arrow Button
13. Transpose Data from a Row to a Column
To transpose data to a better display, all you need to do is this: first, copy the area you wish to transpose. Then shift the pointer to a different blank location. Go to Home > Paste > Transpose. Just keep in mind that this function will not be activated until you copy the data first.
14. Hide Data Thoroughly
By using the Format Cells function, one can easily hide data thoroughly. Select the area you want to hide and go to Home > Font > Open Format Cells > Number Tab > Custom > Type ;;; > Click OK. Now, you can see that all the values in the areas will be invisible and will only be found in the preview area next to the Function button.
15. Compose Text with &
If you know how to use &, you can compose almost any text freely. If you have 4 columns with different texts and want to compose them to value in 1 cell, first, you should locate the cell which would show the composed result, use the formulation with & (shown below. Then click Enter.
16. Transforming the Case of text
Some of the simple and easy to use formulations are UPPER, LOWER and PROPER which will transform texts for different purposes. UPPER can capitalize all characters. And, LOWER can change text to all lower case. Using PROPER, you can capitalize the first character of a word.
17. Input Values Starting with 0
18. Speed up Inputting Complicated Terms with AutoCorrect
If you wish to repeat the same value and it feels complicated to input, the best way is to use the AutoCorrect function. Go to File > Options > Proofing > AutoCorrect Options and input Replace text with correct text in the red rectangular area as shown in the picture.
19. One Click to Get More Status
20. Rename a Sheet Using Double Click
Well, there are quite a many ways to rename sheets and most of you will use the option where you will have to right click and then choose Rename. But, the easiest way is to just click twice so you can rename it directly.