# Running total in - hidden columns in chart based on date

#### nielf

##### Board Regular
I'm trying to create a PivotChart like the one below. As we're not yet not into December, I would like to hide the column for month 12 - but still display the target for the month as shown below.

The 'actual' values (the columns) are shown as 'Running Total In' while there's no calculation for the 'target' values (line). When I use 'Running Total In' all of the months will be filled out even though there's no data for the month - and I guess that is also exactly what a 'running total' is supposed to do.

Is there anyway I can hide the columns based on today's date? In the chart above I have simply hidden the column manually.

My data looks like this:

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
On way to tackle the issue is create another column that calculates your rules to use as a report filter. For example, assuming you only want complete months for the current year, starting in row 2:
<month(today())
<month(today())
<year(today()),and(month(a2)<month(today()),year(a2)=year(today())))[ code]
<year(today()),and(month(a2)<month(today()),year(a2)=year(today())))
'if we're only dealing with this year:
<month(today())

=MONTH(A2)'<'MONTH(TODAY())
<month(today())
'If we're dealing with multi-years in the A column:
<year(today()),and(month(a2)<month(today()),year(a2)=year(today())))[ code]
=OR(YEAR(A2)'<'YEAR(TODAY()),AND(MONTH(A2)'<'MONTH(TODAY()),YEAR(A2)=YEAR(TODAY())))
'pull the apostrohe's, would't display the code without them
</year(today()),and(month(a2)<month(today()),year(a2)=year(today())))[></month(today())

Returns TRUE for any month less than the current month. So you'd just set your pivot chart to use a filter of TRUE, and you can always hide this column if it's disruptive.

(It really doesn't like the code display)</month(today())
</year(today()),and(month(a2)<month(today()),year(a2)=year(today())))
</year(today()),and(month(a2)<month(today()),year(a2)=year(today())))[></month(today())
</month(today())

Last edited:

My initial thought was to use a filter like that. However, the problem is that it hides the entire month:

I would like to display all the months at all times but the columns (actual) should only be visible if we have reached that month. E.g. now that we're in November the chart should display columns for the first 11 months but not for month 12 (even though it is calculated because of the 'running total in' calculation used).

I was wondering if it would be possible to use conditional formatting for this somehow.

Last edited:
I found a workable solution.

Instead of using the 'Running Total In' Excel Pivot function I created a helper column calculate the running total. Afterwards I used an IF formula to filter the dates that are greater than today. That way I could 'hide' the colmn for the dates that are in the future.

Replies
0
Views
114
Replies
7
Views
81
Replies
0
Views
113
Replies
3
Views
62
Replies
1
Views
226

1,203,634
Messages
6,056,457
Members
444,866
Latest member
cr130

### 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.

### Which adblocker are you using?

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

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