During the session there were a few similar questions. I have answered them below.
Session recording
The webinar recording link is: https://cpaaustralia.webex.com/cpaaustralia/ldr.php?RCID=156b302e0585fc4d8129c91d7c6e2d4f and password is: ExcelWebinarSeries2025.
Questions:
|
When "auto-fresh" will come out?
|
|
Looking for a timeframe for "auto-fresh".
|
|
For the auto refresh feature, do i need to have the latest Excel version?
|
Plus.
Do changes to the underlying data automatically update the pivot table?
In the session I mentioned that there will soon be an AutoRefresh option for PivotTables. This option would mean the PivotTable will update automatically whenever the data changes. Woohoo!
Currently PivotTables do NOT get updated when the data changes. You need to manually refresh the PivotTable to use any updated data. Right click the PivotTable and Refresh. Or press Alt + F5.
Sorry - I donβt have a timeframe for this new option.
It is in the testing phase in the Beta version of Excel.
When it is released, you will need the latest subscription version to access it.
π€ Hopefully this will be released soon - it has been requested for decades.
In the session I shared a formula hack to return a financial year from a date.
Questions:
|
Does it eed to changed in Leap years ?
|
|
Is the 184 hack affected by the leap year?
|
|
If 184 is the number to use to convert calender years to financial years, what would be the number to use for 31st March year end?
|
|
if there is 9 days of February, does the year formula is also +184?
|
|
Addig 184 to he date to gte the fin year , dioes that need to change in a leap year ?
|
|
how we set financial year? just now add 184, is this fixed amount?
|
If cell A1 has a date. The following formula will return the Financial Year for the date.
=YEAR(A1+184)
Yes - the magic number 184 works for leap years.
If your Financial Year ends on 31 March, the magic number is 275. Use the following formula.
=YEAR(A1+275)
Again, this is not affected by leap years.
PivotTables work with Calendar years NOT Financial years (hence to hack for the year above). You need some advanced techniques to work with Financial Years.
Questions:
|
Hi Neil, I work with Period within Financial Year, example: April being 1 and March being 12. I often encounter the difficulty in reporting within Pivot to put the summary of hearder in sequential order. Example: Pivot Header always present 1, 10, 11, 12 instead of 1,2,3,...12. Is there any trick to fix this? Thank you
|
|
if my financial year end is 1 Apr to 31 Mar - would the financial year formula changes?
|
|
How do we create a financial year Date view from Jul to Jun and Qtr 1 is Jul to Sep?
|
I covered how to report on Financial Years in an InTheBlack Article nearly nine years ago. Link below β it has a video and example file.
https://intheblack.cpaaustralia.com.au/technical-skills/use-excel-relationships-enable-pivottables-report-financial-year