Working with small spreadsheets in Excel is pretty easy. There are many editors that can make life easier, including the online paper editor, for example.
But when you have to deal with large amounts of data, there are all kinds of difficulties that slow you down. We decided to make a selection of useful features that will help avoid problems and greatly speed up the process of working with tables.
Moving between different Excel files
When there are several spreadsheet files open in the program, it’s easy to get confused with moving between them. Using the Ctrl+Tab keys, you can easily move from one file to another without having to navigate to other programs open on your computer.
Typically, a menu with three shortcuts is placed in the upper left corner of the screen: Save, Cancel, and Return. You can add other icons to this menu, such as Copy or Cut. To do this, go to File-> Preferences-> Quick Access Toolbar and select the items you want. Move the desired items from the left column to the right column and save. The icons you selected will appear in the top menu.
Selecting unique values in a column
The advanced filter feature is used to search for all unique values in a column. Highlight the desired column and click the Data-> Advanced tab. A popup window will appear. Select “copy result to another location”, then specify the target column in which you want to see the result of the selection, check the “Only unique records” box, and click “OK”. The target column will display all unique values in the source column.
Limiting input with the data validation feature
Sometimes when you have more than one person working with a table, you’ll want to limit the input values and provide guidance to users. For example, the age of the participants in the table must be between 18 and 60. To avoid typing errors, go to Data->Check Data->Parameters, choose the data type “Integer”, the value “Between” and enter a minimum and maximum value. Then go to the “Entry Message” tab in the same window and write, for example, “Please enter your age as an integer between 18 and 60.” Users will see this message when they hover over the appropriate area.
Transpose data from row to column
This feature is used to display the data better. Copy the area you want to transpose, move the cursor to an empty space, and click the Home -> Insert -> Transpose tab. Note that this function will not be activated until you copy the data first.
Combining data from different cells
If you need to combine data from different cells into solid text in the same cell, use the “&” symbol. Place the cursor on the target cell, put the “=” sign, and then place the cursor on the cells whose data you want to combine, placing the “&” sign between them. You should end up with a formula similar to =A2&B2&C2&D2. Press Enter. The text from the selected cells will appear in the target cell.
Converting Text Display
Simple text conversion functions often make working with tables seriously easy. For example, the functions PROPIS (converts all letters to uppercase), PROPNACH (converts the first letter of a word to uppercase, the rest to lowercase), STRONG (converts all letters of a word to lowercase).
Displaying values that start with “0”
When the initial force value is 0, Excel deletes it by default. To avoid this situation, add a single quote in front of the value.
Speeding up the input of complex terms with auto substitution
If you need to enter the same complex value more than once, use the AutoCorrect feature. To do this, go to File-> Preferences-> Spelling-> Auto-Conversion Options-> Auto-Conversion. In the “replace” column enter a short text that is easy to type (e.g. ZAO), in the “to” column enter the full text (e.g. Closed Joint-Stock Company). Online tutoring jobs for teen to earn extra money.
