Office hacks: not standardized Excel tables so change

Data organization is the most important function of Excel, but we often have to deal with different sources of forms, which often differ greatly in format, expression, and organize these data is very tedious. How to be able to efficiently deal with these Excel files in the irregular data, content, I believe that many readers want to know. Here the author will share with you some of the skills to quickly deal with irregular data.

What about irregular dates

For example, we have a table that looks messy and not well organized or easy to navigate because of the irregular formatting of dates.

不规范001

To organize this data, we can first select the date data to be organized, then click on “Data” above and then find “Data Tools” and click “Breakdown”.

不规范002

In the pop-up window, the first two steps of the wizard can directly click “Next” to skip, in the column data format, click to select “Date”, and then drop down to select “YMD” that is year, month and day, and then click “Finish”.

不规范003

At this time, all the irregular data in the date column will be unified into the “YMD” that is, the year month day display format, no longer need to manually modify one by one.

不规范004

How to unify the row height and column width

Or just the contents of the table as an example, if its row height, column width is uneven, want to quickly adjust it and how to deal with it?

不规范005

First, press Ctrl + A to select all the table content, and then click the “Start” above, find the “cell – format” one.

不规范006

At this time, respectively, click on the operation of which “automatically adjust the row height, automatically adjust the width of the column” two, the form of rows and columns will automatically adapt to the content of the form, adjusted to the appropriate degree, much more convenient than manual adjustment.

不规范007

Splitting data is more intuitive

Sometimes the statistics up data is completely unformatted, put into Excel tables simply can not be organized into categories. Many people at this time may think of manual processing, in fact, Excel has a way to make these mixed data automatically classified.

不规范008

To deal with this type of data, we first press the shortcut Ctrl + A full selection of table content, and then click on the top of the “data” to find the “Data Tools” in the “Breakdown” function .

不规范009

The first page of the wizard page skipped, and then in the second page, that is, the “separator” page, click “Other”, and then enter the form data in the separator number “, “, this is to fill in the form according to the specific content of the separator number, and then click Finish, and then the content of the non-uniform separator number manually adjusted, and adjust the ranks can be much more convenient than manually extracted.

不规范010

Numbers with units cannot be calculated

Some statistical forms need to calculate the sum of numbers, but because when entering the form, it is likely to be entered with the units, such as XX yuan, XX, etc., this time even if the calculation formula is filled in, the results can not be calculated correctly.

不规范011

Generally encountered in this case, more than the removal of units, either replacement method batch replacement or manual deletion are very troublesome, in fact, it is not necessary. We just need to add the corresponding units in the number format of the cell will do. First, left-click drag and drop to circle all the numbers to be calculated, and then right-click the mouse, pop-up menu select “Set Cell Format”.

不规范012

In the “Set cell format”, switch to select “Custom”, and then enter “# yuan” (Note: specific units such as yuan, fill in the case) click OK.

不规范013

After this setup, we can then fill in the calculation formula to sum up, so that there is no need to adjust the units.

不规范014

Delete extra blank lines like this

In Excel tables, often encounter the case of empty lines, manually delete a line or more or less troublesome, we can solve this trouble through a very simple operation.

不规范015

Left-click and drag to select all the table content, and then press the shortcut key F5 to call out the positioning table, and click the “positioning conditions”. In the “positioning conditions” click to select the “empty value” and then OK.

不规范016

不规范017

At this point the form has been all the empty lines selected, right-click on any of the empty lines, a pop-up menu to select Delete, you can clear these redundant empty lines.

不规范018

Leave a Comment