2

So I've had a look at similar answers but they're not working for me. Basically I have a lot of related charts of monthly data, and I'm wanting to plot them on a chart that only shows up the current year's data and shows some "target" points for data that's for the rest of the months of the year.

I've used formulas to create running totals of my data, which is what I need to compare to the targets, but I want to be able to show the whole year on the chart, but just have no visible data points for months that we haven't recorded data for.

I've created a pivot table which uses the running totals I've calculated elsewhere (if I use the pivot table running total option, it fills in blanks with the total from the previous month, e.g. prev month running total+0, which gives me a data point on the chart which I don't want, but if you've got a solution that tackles this issue, please let me know!), plus the 2 target columns of data. But, I need a way to return a truly blank value in that field in my pivot table, even though there's a formula in the field I'm calling on (the formula needs to stay, because I need it to calculate the value and update the chart once the data is entered for the month).

I've tried using an IF function to return:

  1. 0 (which is treated like a zero by the pivot table and therefore the pivot chart and plots a zero)
  2. "" (which is treated like text by the pivot table, and therefore it won't return the sum of the column value because it can't run that operation on a text data type)
  3. NA() (also treated like text)

Would love any and all ideas on how to get it to see my data in the way I need it to.

p.s. I'm using a pivot table rather than a normal table because I want to be able to switch between years on the chart easily, using the filters.

CORCSIR
  • 21

2 Answers2

0

A similar question has already been asked earlier here. In short, a formula cannot return a truly empty cell.

slightlym
  • 1
0

As slightlm stated, there is absolutely no way for Excel to do this with "cell side" tools.

However.

You want to output to a pivot table. Power Query, built into Excel, can provide the data for said pivot table. PQ can take your source data from the range or Excel Table it is in and operate upon it without ever changing anything in the actual source material.

Said operating upon it can include stripping out anything from the source data that you do not want, in this case the material you mention in your question. The only real limit is your being able to characterize it to PQ so that it, and only it, is stripped.

That then is provided to the pivot table, and any time you want it refreshed, you refresh the query and the pivot table is refreshed from the refreshed material.

Refreshing can be done by macro. For instance, one attached to a button you lable "Refresh"... but also can be triggered a variety of other ways.

So your original material is never changed. Your pivot table takes its material from the (intermediate) data set produced for it by PQ which has your modifications applied. (Many more things are possible than just stripping certain material, and one of those would be to add columns that do various things to the data which PQ can do fairly easily but might be hard to achieve from inside the traditional pivot table itself.)

Best of both worlds.

As an alternative, given one must step outside of cell-side workings, is VBA. VBA can also "clean" the table and provide a resulting data set that would actually feed the pivot table. Harder, perhaps, in that each little bit must be programmed, but depending upon your skill set and temperment, the (likely) added particularity, fine-grainedness if you will, of a VBA solution might appeal.

But for quick-to-market, so to speak, PQ as the automated middleman is surely the champion.

Jeorje
  • 1