Excel tips
Input Restriction with Data Validation Function
To retain the validity of data, in some cases it is required to restrict the input value. And offer some tips for further steps. For Example, Age in the excel sheet should be in whole numbers and the participant in the survey should be between 18 and 60 years old. So to make sure, data outside of this age range should not be entered. Go to Data > Data Validation > Setting, input the circumstances and modify to Input Message to give prompts. Like (Please input age in the whole number and should in the range from 18 to 60). Users will automatically get this prompt while hanging the pointer in this area as well as get a warning message if given information is unqualified.
Generate a Unique Value in a Column
Many of the Excel users are well aware of the key function of Filter but few of them utilize the Advanced Filter. This helps the users to repeatedly apply when you need to filter a unique value from data in a column. Follow the Process:
- Click to select the column and go to Data > Advanced > a pop-up window appears
- Next click Copy to another location, which is in the second rectangular area
- And after that specify the target location by entering the value or clicking the area choosing button.
- Now the unique age is generated from column C and will appear in Column E.
- You also need to select Unique records in the pop-up
- Click OK Now, the unique value showing in column E could be a contrast of the original data in C. This is the reason why it is recommended to copy another location.
Speed up Entering Complicated Terms with AutoCorrect
While working in an Excel file if you need to repeat same value and it is quite complicated to enter it. Then here know how to do it easily by using the AutoCorrect function. Doing this will replace the text with the correct text. For example: Take any name like Amy Jackson, and replace it AJ, and after that every time you enter AJ, it will autocorrect to Amy Jackson. This is really an interesting and useful Excel trick. To do so: Go to File > Options > Proofing > AutoCorrect Options and enter Replace text with correct text in the red rectangular area,
Transform the Case of Text
In this article, I have tried by best to avoid complication formulation. But there are yet some simple and handy formulations like UPPER, LOWER and PROPER that can transform texts for different purposes. Upper will capitalize entire characters; LOWER changes the text to lower case and PROPER only capitalizes the first character of a word. Really amazing…
Compose Text with
The & symbol is very necessary to compose any text freely. For example: below you are having four columns with different texts, but if you want to compose them to one value in one cell. Then first locate the cell that is to display the composed result, utilize the formation with & and hit Enter. The entire text in cell, A2, B2, C2 and D2 will be composed together in the F2 cell.
Rename Sheet Using Double Click
There are numerous ways for renaming sheets and many users will right click for selecting Rename, this is a process. The easiest and fast way to rename is to click twice and rename it directly without wasting time. This is not the end. Check out more interesting EXCEL TIPS.
Input Values Starting with
This is very important for the regular Excel users. When any input value starts with zero, Excel by default deletes the ZERO. So rather than reset the Format Cells, this problem is easily solved by adding a single quote mark ahead of the first zero.
Fast Navigation with Ctrl + Arrow Button
Here is a trick to make fast navigation in Excel many of us know that when we click Ctrl+ any arrow button on the keyboard, you can jump to the edge of the sheet in different directions. But if you want to jump to the bottom line of the data, just click Ctrl+ downward button and easily go to the last cell.
You can Draw Equations
This feature is available in Excel 2016 (useful if you are having a touchscreen PC). To draw equations you need to go to the Insert tab on the ribbon menu and select Equation and Ink Equation. And after that, you can sketch in the yellow box.
Apply Some Conditional Formatting
In Excel, you can add Conditional formatting this can add some pop to the sheets and helps you pick out data easily. It is simple to use. Select the data that is needed to be formatted > click Conditional Formatting (in Home) > and build your rules accordingly from the drop-down options.