Custom View does not apply the same filtering when the view is reapplied

AlexG2490

New Member
Joined
May 3, 2020
Messages
1
Office Version
  1. 2016
  2. 2013
Platform
  1. Windows
Hello! I'm hoping that this is not the default behavior of the Custom Views function, and that I have instead done something wrong.

I am working on a budgeting spreadsheet. I already have the version I use and have used for several years but many people have said they liked it, and have asked for a copy. So I decided to make a blank version I could pass to anyone who wanted it. People appreciated it but some people had more expenses than me, or in different categories. So, I added more expense lines, but now there are a ton of extra lines that most people won't use - 150 rows that most people will only use a fraction of.

Custom Views seemed like a great solution - make one view that has no filter applied, make a second view that filters out lines with a value of $0.00, and let the recipient switch between them. But the results doesn't play out that way. The blank sheet has some dummy data to illustrate how to use the chart:
Housing$800.00
Groceries$150.00
Water$20.00
Electricity$115.00
Natural Gas$25.00
Enter Other Expense Here$0.00
Enter Other Expense Here$0.00
Enter Other Expense Here$0.00
Enter Other Expense Here$0.00
Enter Other Expense Here$0.00

The real sheet is much more complicated of course but this will illustrate what is happening. So, I applied filtering to the second column, unchecked $0.00 from the list, and the new table is displayed accordingly.
Housing$800.00
Groceries$150.00
Water$20.00
Electricity$115.00
Natural Gas$25.00

So far so good so I made a custom view and gave it a name. Now, suppose the recipient gets the chart, and modifies it to suit their expenses. They like the default values but they add a line item for gas in their car, and one for entertainment.
Housing$800.00
Groceries$150.00
Water$20.00
Electricity$115.00
Natural Gas$25.00
Gasoline$115.00
Entertainment$100.00
Enter Other Expense Here$0.00
Enter Other Expense Here$0.00
Enter Other Expense Here$0.00

When they apply the view I created before, this is the result:
Housing$800.00
Groceries$150.00
Water$20.00
Electricity$115.00
Natural Gas$25.00
Gasoline$115.00

It took me a while to troubleshoot that what Excel is doing is not applying the same filter I created for the view - "Filter column 2 to show all values except $0.00". Rather it is applying the filter, "Show the values $800.00, $150.00, $20.00, $115.00, and $25.00". It's a subtle distinction but, obviously, displays an incorrect result. The Gasoline line shows properly because it happened to match one of the values in the list already but any other value will not.

Is there a way to tell Excel precisely the criteria that I want to use and, if not, is there a better way to accomplish what I am trying to accomplish?
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Watch MrExcel Video

Forum statistics

Threads
1,122,364
Messages
5,595,724
Members
414,013
Latest member
tnobbs

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top