Useful tricks for working in Excel

Useful Excel practices significantly reduce the time spent on handling spreadsheets and increase the efficiency of using the application. With the advent of new versions, it becomes more and more convenient to work with MS Excel, which is why it is so useful to know the useful functions and features of the program that have appeared.
How to quickly convert cell data
How to convert columns to rows
Formatting into a data table
Recover Unsaved Excel Workbooks
Comparing two ranges

How to quickly convert cell data

Newer versions of Excel have an interesting feature that works automatically. This is an intuitive data transformation. For example, you have tables with a column where the last names of employees are recorded, as well as the full name and patronymic. You need to convert this column so that only last names and only initials remain.

  1. Create an empty column next to the column to be converted.
  2. In the first cell of an empty column, the first part of the email.
  3. Please note that Excel itself offers converted data options in the following cells. Convert cell data
  4. Press the Enter key and the entire column will be filled with the converted data.
  5. Delete original column.

Such a trick can be done not only with initials, but also with any other data. For example, you can transform a column so that only the first three letters remain in each word or only part of the email address is displayed.

How to convert columns to rows

Manually converting columns to rows or rows to columns is very labor intensive. There are several ways to automate this process. Here is one of them.

  1. Select the column data you want to place in the row.
  2. Copy this data to your clipboard.
  3. Place the cursor in the first cell of the row where the converted data will be located.
  4. Right-click on the cell and select the line “Paste Special”.Paste Special
  5. In the window that opens, select the “Transpose” option.Transpose
  6. Click OK. Row into a column

Similarly, you can convert a row into a column.

Formatting into a data table

Within any Excel table, it is possible to create a nested table in which many functions can be used. How to do it?

  1. In the source table, select the rows and columns that will be included in the nested table.
  2. On the Home tab, click the “Format as Table” button .Format as Table
  3. Select a color for the nested table.
  4. Transform the data in the nested table if necessary. Transform

In the resulting table, you can enter formulas, and they will automatically be copied to the entire column. It automatically expands when new rows are inserted. The table header has a button for filters and sorting. Note that when you click on a nested table, the Table Tools Design tab appears on the Excel main menu. Table Tools Design
Using the constructor, you can perform transformations on a nested table, for example, change its name.

Recover Unsaved Excel Workbooks

Everyone had such situations when, when closing a file with a report, you accidentally pressed the “Do not save” button when asked whether to save the file, or the light in the room went out and the computer turned off while you were working. In recent versions of Excel, the unsaved file can be reopened and saved.

  1. Open MS Excel and click the “File” button.
  2. Open the “Recent Workbooks” tab and click the “Recover Unsaved Books” button. Recent Workbooks

The same can be done if you click on the “File” button, then open the “Info” section in the menu and click on the “Manage Book” button.Manage Book
Then click the “View and Restore previous Version” button. Clicking this link opens the folder where temporary copies of Excel workbooks are saved.

Comparing two ranges

Sometimes you need to compare multiple columns in a table to find cells with mismatched values.

  1. Use the Ctrl key and the mouse to select the columns to be compared. Select the columns
  2. On the “Home” tab, click the “Conditional Formatting” button. Conditional Formatting
  3. Click “Highlight Cell Rules”.
  4. Click on “Duplicate Values”.Duplicate Values
  5. In the window that appears, set the value to “Unique” and click “OK”.Unique

Unique cells will be highlighted in color. Unique cells
When working with important Excel documents, remember to encrypt them. Even if you forget your password, a simple program will help you Excel password recovery documents.