Excel Magic Trick 1559: PivotTable: Year Totals, YOY Change, % YOY Change from Daily Sales

In this video learn how to use a PivotTable to create reports for Yearly Total Sales (from daily date transactions), YOY Change in Sales & % YOY Change in Sales. Learn that PivotTables are easier to create than Worksheet Formulas, but require a refresh when source data changes to get report to reflect the changed data.

[blue-oceandreamer]

Dear Michael,
Always love to watch and learn from your master teaching.

[sallyghlee]

Thank you, I learn from every video whether it's 4 minutes or 40.

[johnborg]

Thanks Mike. Simple and fast. Beautiful staff. :)

[chrism]

Good refresher on Pivot table capabilities, nice video Mike!

[katerina]

Thank you again for the great video Mike

[mohamedchakroun]

The power of excel...many alternatives to solve problem.

[jamierogers]

Spooky you should post this video this weekend; it's a pivot table function I rarely use but have used it 3 times in the last week for quick reconciliations. Thanks Mike.

[EricaDyson]

Great. Didn't know about the options for previous, next etc. I do now!

[simfinso]

Wow Great Help From this Amazing channel.

[planxlsm]

Mike, There is a macro to automatically update Pivot Table ?

[wayneedmondson]

Thanks Mike.. another excellent lesson.. fun with Pivot Tables. Thumbs up!
PS - Just for fun, you can get auto updating of a Pivot Table with a simple macro on the Worksheet_SelectionChange event of the subject worksheet as follows:
Private Sub Target As Range)
If Not Intersect(Target, Range("B:C")) Is Nothing Then
ActiveWorkbook.RefreshAll
End If
End Sub
The above will force a Refresh All with any changes made in column B or C.. so.. makes the Pivot Table dynamic with the help of a little VBA. Might create a performance issue for large data set. But, it's an option for certain circumstances.

[byDsign]

When you want Resulting information to automatically refresh.... Use formulas.
Other wise.... use Pivot Tables and manually Refresh.

[annpiccarreto]

My data has 8 qtr worth of data for 4 different LOB. I created my pivot to show % diff from the previous qtr, but need to also display it in a chart. When I use the insert Pivot chart feature, it only shows my sales, not the % growth. Can you help?

[ogwalfrancis]

Thanks Mike, i better use the pivot table and refresh. Thank you

[sachinrv]

Hi Sure pivot tables are useful; but in a scenario like this; I prefer IFS variant functions for their ability to instantly update the summary. In pivot as you correctly said if the user forgets to refresh, the pivot table will carry the same old result. Thanks for sharing video. I have a request will you please post a video on LOOKUP (ARRAY and VECTOR form) function or if you already have please share the link.

[GeertDelmulle]

Those pivot table features sure are super-handy!
Although, I’m not used to those features anymore since I make all formulas as explicit measures.
Where is the time when all data came in just one table?... doesn’t seem to occur anymore.
Since I need that function so often the last months I’ve put the Refresh All button on my QAT-walk.
Because: pushing that button will refresh all pivot tables in my dashboards at once.
BTW, Mike is there any reason why you did these two videos? They seem like re-iterations of previous videos to me (I’m sure you’ve done this before — must be, right?...).

[Dipakbohora]

Sir, how could we change the data format from row to columns in power query? I mean unpivot columns to pivot columns. Your help in this regard is highly appreciated.

[satyamsharma]

Kindly consider the data as given.
Col A (amount )-22, 45, 45, 68...30 value
Col B (Dates)- DD/mm/yy
Required day("ddd") of which maximum sale has done are -

1. No helper column allow
2. Formula required only in one cell .

Say only C1 has formula in it .

Like total sale on Monday is 67,
Tuesday-98,
Sunday-109 and so on...

Our answer is Sunday.

Sir please help...

[frankgovers]

This should also be this quick in powerbi