Learn how to save time and effort the next time you work in Microsoft Excel.
Microsoft Excel is an incredibly useful program to perform all kinds of management, reports, and operations. However, some of its secrets have not yet been discovered by the majority of students and workers who must deal regularly with this tool. Below, we show you the 10 Excel tricks that will turn you into an expert. A website called SoftwareKeep, From there you can Purchase Microsoft Excel.
- Restrict certain values with the Data Validation function
To ensure that all data in your spreadsheet is valid, it may be useful to restrict the values and set certain data entry parameters. For example, all numbers must be entered in whole numbers. Also, imagine that the spreadsheet of the image collects data from people who participated in a survey, which can only be 20 to 25 years of age. To make sure that people outside of that age range have not been entered, go to Data -> Data Validation -> Settings, enter the conditions “Whole Numbers” and then 20 as a minimum and 25 as a maximum.
If other people enter their data to the form, you can go to the option “Input message” and there write “Please enter their age in whole numbers, which must be 20 to 25 years.” Users will receive an alert if they enter information incorrectly.
- QUICK NAVIGATION WITH “CTRL + DIRECTION KEY”
If you press Ctrl + any of the arrow keys on the keyboard, you can jump to the edge of the sheet in different directions. If you want to go directly to the end to the last row of data, simply press Ctrl + down arrow.
- TRANSPOSE DATA FROM A ROW TO A COLUMN
If you want to see your data in the form of a column instead of a row, there is a much more practical way than writing them by hand. First, copy the area you want to transpose with Ctrl + C, then click on a blank cell. Go to Start -> Paste -> Transpose.
- HIDE DATA
Almost all Excel users know how to hide data by right-clicking and selecting the “Hide” option in a row or column. However, this may be notorious if it is a matter of little data. The best way to completely hide the data is by using the Format Cells function. Go to Start -> Format -> Format of cells -> Number -> Custom -> Enter “;;;” -> Accept. After doing this, all the values in this area will be hidden. These will be visible only in the preview area.
- COMPOSE TEXT WITH “&”
The “&” symbol allows you to compose any text freely, without the need to use complicated formulas. In the image, there are three columns with different texts, but if you want all these values to be in the same cell, you must do this: in the cell where you want the compound data to appear, use the formula that appears below using the “&”. Click on Enter, and the texts in A2, B2 and C2 will become Manuela22Puerto Rico in E2.
- UPPERCASE AND LOWERCASE
There are some simple formulas that you can use to transform the format of the text: MAYUSC converts all characters in uppercase, MINUSC in lowercase and NOMPROPIO will only leave the first character of the word in upper case.
- ENTER VALUES THAT BEGIN WITH “0”
When you enter a value that begins with zero, Excel will erase this zero by default. This problem is easily solved by adding a quote in front of the first zero.
- USE THE AUTOMATIC CORRECTOR TO FACILITATE DATA ENTRY
If you have to enter the same data repeatedly, but this is complicated to write and takes a lot of time, try the function of the automatic corrector, which will replace the text you enter with the correct data. For example, instead of entering “Puerto Rico”, you can enter “pr”, and this will be modified automatically. Click on File -> Options -> Review -> AutoCorrect Options and enter the desired texts in the “Replace” field.
- GET MORE VALUES WITH ONE CLICK
You probably know how to check certain values such as Average or Sum in the status bar at the bottom edge of the page. However, did you know that by right clicking on it you have more options?
- RENAME THE SHEET WITH A DOUBLE-CLICK
Instead of wasting time the “Rename” option, simply double-click on the name of the sheet and write the new name.