4 related Excel column operation tips

4 related Excel column operation tips

1, adjust column width and another table the same

Purpose: To adjust the column widths of the two tables, without a single column of manual adjustment.

How to: Select the column (A:D) for table 1-copy-Select the column (F:I) for table 2-paste-column width.

2. Quickly select the specified column

Purpose: In a table with dozens of columns of data, quickly jump to the specified column.

How to: Select an area of the first 2 rows of a table (do not select an entire row)-formula-creates the first row based on the selection.

When the setting is complete, open the upper left corner address box and click to jump to the specified column.

Cancel method: Formula-Name Manager-press SHIFT to select a batch name-delete.

3. Multi-column content merged into one column

Usage: Multiple columns of content are merged into one column, typically with a & connection, and then the formula is converted to a numeric value. The following method

example, as shown in the following illustration, requires that the four columns of the left table be merged into a column, as shown in the yellow area of the right table.

Action method:

Copy the table and paste it into any Excel annotation. Copy the contents of the annotation, and then glue to the cell.

4, a row of multiple tables

Use: A column of data, divided into N-column display.

The columns are shown below to convert the contents of the A1:A20 to 5 columns (4 rows per column).

Action method:

In B1, set the formula =A5 (if each column is =a6 in 5 rows), and then copy the formula to 5 column 20 rows.

Copy formula-paste-a value, and convert the formula to a numeric value.

Delete data from line 5 and below

Note: If you need to convert four rows of five columns into one column, set the formula =B1 in A5, and then copy the formula down to the right.