Make Changes to Filtered Tables without Changing Hidden data underneath.

**Barry34** · 10-29-2016, 02:50 PM

I have a rather large data set of simple Person ID, Name, Organization ID, Institutional Status, Professional Member type, etc.

I want to filter by organization ID for example, and then edit the values in the Institutional Member column for all of the institutional members that are under that Organization ID- it can be several thousand. I try editing one cell at the top and then double clicking the bottom right of the cell to change the value all the way done- the problem is that Excel applies that change to all the cells (including the ones that are hidden- not filtered for.)

Is there a way to just change the cells that are filtered without impacting the cells are hidden/not-filtered once I filter the whole thing the way I like it? I thought I just held shift down or something, but I can't find a clue.

I have excel 2016. This is driving me crazy. Thank you

**tim201110** · 10-29-2016, 03:18 PM

maybe you should not double click but pull down the bottom righ cross

**newdoverman** · 10-29-2016, 03:33 PM

You could add 2 columns to your data. The first one would have the data numbered in order from 1 to whatever you need and the second column would have this formula (with the appropriate changes to suit your data).
I chose column B to be the ID that you are wanting to work on (replace the X in the formula with the ID to be worked on). This will enter a 1 for every row that has the ID that you want to work on and a 0 for the rest.
Enter in the first row of data and fill down.

Formula:

=IF(B2="X",1,0)

Now, sort on the column with 1s and 0s from Smallest to largest. This will place all the data for the chosen ID at the bottom of your list. Make your changes to the first record of the chosen ID and fill down the column. Sort on the column that contains the consecutive numbers from Smallest to Largest to re-order the list to the original order.

Make Changes to Filtered Tables without Changing Hidden data underneath.

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Make Changes to Filtered Tables without Changing Hidden data underneath.

Re: Make Changes to Filtered Tables without Changing Hidden data underneath.

Re: Make Changes to Filtered Tables without Changing Hidden data underneath.

Thread Information

Users Browsing this Thread

Similar Threads

Extracting filtered data selected using slicers and pivot tables

Using Data validation list with a filtered tables results

Keep hidden rows hidden, in a protected, filtered worksheet

How to make a dropdown list pull data from another sheet and autofill underneath the list.

Multiple pivot tables covering the same data but filtered in different ways. Change auto?

copying filtered data WITH the hidden columns

Pivot tables: display filtered data in another worksheet

Bookmarks

Bookmarks

Posting Permissions