Shulou(Shulou.com)11/24 Report--

In our work, we often encounter some messy tables, such as irregular data, irregular rows and rows, data in a cell, and so on. This kind of tables often affect us to calculate and analyze the data, so we'd better standardize these tables.

1, the date is not standardized as shown in the figure, the dates in the table are 2019-9-1, 2020.11.10, 20200119, how should we standardize it?

We first select the date column, then go to "data"-"data tools"-"Division"-"next Page"-"next Page", check "date", default "YMD", and finally click "finish" to adjust it.

2. Irregular rows and rows, table row height and column width are inconsistent, how to standardize the adjustment?

First of all, use the shortcut key "Ctrl + A" to select all the tables, then enter "start"-"Cell"-"format"-"automatically adjust row height", and then "automatically adjust column width" will be able to adjust row height and column width adaptively.

3, split data sometimes in the external import of Excel data, is not separated into a separate cell, but separated by symbols, how to split it?

We can use the fragmentation function to split the data quickly. After selecting the content, go to "data"-"data tools"-"separate", select "Delimiter symbol"-"next", check "other", then enter the delimiter, and click "finish".

4, extra blank rows sometimes copy some data to the table and find that there are many blank rows. How to quickly deal with these extra blank rows?

Select the table, press the "F5" key, click "location condition"-"null value"-"OK", then we have selected all the blank lines, right-click the blank line, and click "Delete".

5, the merged cells are merged, there is no way to sort normally, how to quickly cancel the merged cells?

Select the merged cells, click "start"-"alignment"-"Center after merging", then press the "F5" key, click "positioning conditions"-"null value", and then enter = A2 (that is, the previous cell), and finally press "Ctrl + enter" to get it.

6. Adding units cannot use the formula to manually add units to the data in the cell is a wrong practice, which will make it impossible for us to use the formula operation normally. As shown in the following figure:

We can only remove the units through the "find and replace" function, and then calculate the formula.

So how to add units to the data correctly? In fact, we can add units through the digital format, which does not affect our formula at all.

7. Get rid of the green triangle in the cell. Sometimes we copy the data into the Excel. There will be a green triangle in the upper left corner of the cell. These cells cannot be operated. What is the situation? In fact, this is because the Excel table mistook these cells for text.

All we have to do is select the cell with a green triangle, then drag the mouse to select all the other cells, click the exclamation point next to it, and then select convert to number.

This article comes from the official account of Wechat: Word Alliance (ID:Wordlm123), author: Wang Wangqi

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.