Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account


Excel table data is at sixes and sevens, how to quickly standardize processing?

2024-07-13 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > IT Information >


Shulou( 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.

01, 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.

02. The ranks are not orderly

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.

Split data sometimes imports data from Excel externally, but is not separated into separate cells, but is separated by symbols. How to split it?

We can take advantage of 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".

04, extra blank rows sometimes copy some data to the table and find that there are a lot of 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".

05, 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.

06. Adding units cannot use the formula to manually add units to the data in the cell, 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.

07. Get rid of the green triangle in the cell. Sometimes when 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 yet. 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 Technology Alliance (ID:Wordlm123), author: Wang Qi

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.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

IT Information


© 2024 SLNews company. All rights reserved.