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

Original title: "Who says horizontal data can't be filtered?!" Two ways, five seconds, so easy!

Yesterday, two hours before work, I stretched and thought about what to eat tonight.

But suddenly I heard the leader calling me: Xiaolan, I just sent you a form, you deal with it, give me the monthly sales figures, give them to me as soon as possible!

Me: Okay, okay.

Before opening the form: Isn't it just screening monthly sales? It can be done immediately and won't delay me from work.

After opening the form: OMG, who designed this form?!

(There's more to come.)

To find out monthly sales, we can use filters.

But here you need a horizontal screen, conventional screening can only be vertical screen, how to do?

Let's start with the usual thinking:

Since the data needed to be sifted vertically, and the current data was horizontal, why didn't we just put the table vertically?

It's like this:

No, no, no. It's wrong. It should be like this:

Let's take a look at how to operate it ~

Method 1: Transpose If you want to swap rows and columns of a table and stand it up like the one above, how do you do it?

There are two methods: copy-paste transpose and formula transpose, collectively known as transpose.

Let's look at the first method.

Copy Paste Transpose:

Select cell A1, press Ctrl+A to select all, then copy and paste, click the floating icon in the lower right corner of the data, and select Transpose.

After that, you can use the [Filter] function to filter out the monthly sales.

Select cell A11, press Ctrl+A to select all, press Ctrl+T to convert to Super Table, and check Include Table Title;

Click the Column 2 filter button and uncheck the Quantity-OK box.

That's it!

However, there is a drawback to this transpose method. If data is added or deleted from the source data, you need to copy and paste it again.

So, how do you get the data in the target area to update as the data in the source area changes?

Using the function formula transpose is OK.

Formula transpose:

Select the data range corresponding to the source area in the new area and keep it selected.

For example, if the source area here is 9 rows and 13 columns, then the new area needs to select 13 rows and 9 columns.

(In fact, it is to change the number of rows and columns ~)

Enter formula:

=TRANSPOSE(A1:M9)

Press Ctrl+Shift+Enter and it will be fine ~

Just like before, normal screening can be done!

Also, say something here!

If you feel that the source data and transposed data are placed in the same worksheet, it will look messy.

You can create a new worksheet and transpose the data into the new worksheet. As follows:

Copy Paste Transpose:

Operating method:

Create a new sheet (Sheet6), press Ctrl+A in Sheet2 to select all sheets, and copy;

Switch to Sheet6, paste, click the floating icon in the lower right corner of the data, and select "Transpose".

Formula transpose:

Operating method:

Select the data range corresponding to the source area in the newly created worksheet (Sheet7), and keep the selected state;

Then enter the function:

=TRANSPOSE Click the worksheet (Sheet1) where the source data is located, select the source data, enter ")", press the combination key [Ctrl+Shift+Enter].

Method 2: positioning method transpose method needs to change the table structure first, then, if you want to directly horizontal screening, do not change the table structure?

Of course, it is also possible to locate the "row content difference cell" on the line.

Operating method:

Select cell B2, press Ctrl+Shift+→ to select the current row, press Tab to move to cell C2 (Sales);

Press Ctrl+G to bring up Positioning Window, click Positioning Condition;

In the pop-up window, select the Row Content Difference Cell and click OK.

Press Ctrl+ to hide other columns.

PS: Chinese ")" or English ")" is OK.

Monthly sales are filtered out.

Compared to the transposition method, this method was simpler and saved the step of filtering the data using the [Filter] function.

To sum up, the above is the solution to "horizontal screening". In general, there are two methods:

Transposition method: copy paste transpose, formula transpose;

Positioning method: Position the "row content difference cell."

This article comes from Weixin Official Accounts: Autumn Leaf Excel (ID: excel100), author: Zhu Lan

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.