Excel has just made life a little easier for anyone working with Pivot Tables. A new Auto Refresh feature, currently rolling out in select beta versions, automatically updates your Pivot Tables when your data changes. That means no more right-click > refresh every time your source data is updated or extended. In this quick tutorial, [...]
58 Crazy Excel Stories (that are probably happening in your company right now)!Crazy things happen when you mix untrained users with Excel.It’s not really rocket science. Crazy things would happen with any complex system if you don’t train people properly.But many companies simply assume that everyone knows Excel, so they don’t need much (if any) [...]
3 Bonus Pivot Buttons you really need Free Add-in By Wyn Hopkins If you’re a regular Pivot Table creator then download this free add-in to save yourself some time. There are a number of steps involved when creating a Pivot Table that are often repetitive and frustrating. So much so I’ve created [...]
Pivot Tables and Grouping Dates I use a Pivot Table to summarise a sales transaction database. I want the data summarised by month. When I use the date to summarise the data, it adds all the years together. How do I fix this? If you just summarise your data into a Pivot Table, it [...]
My Pivot Table won't Refresh! How to Solve Problems in Protected Workbooks By Jeff Robson You may come across some interesting behaviour (aka a real "gotcha") when working with Pivot Tables and VBA Macros in protected workbooks and sheets. When working on a file where each sheet has been protected using the userinterfaceonly:=true flag, [...]
Speed up your Pivot Tables By Wyn Hopkins Refresh All Pivot Tables If you need a better way to quickly refresh all the Pivot Tables in your workbook, here's a short macro that'll do just that: Sub RefreshALL 'Refresh all Pivot Tables and SQL queries Activeworkbook.RefreshAll End sub Or alternatively, if you don't want [...]
Pivot Filter Pages By Wyn Hopkins A little known but very useful functionality in pivot tables is the ability to automatically create a new sheet for each item in a filter. For example, if you have a pivot table showing the monthly results for all Cost Centres, you can very easily create a separate [...]
Sometimes our source data isn't in a format that can be readily analysed by a "normal" pivot table However, there is a trick you can try that may well save a lot of time and effort. By Wyn Hopkins For, example, the data set below gives the brand name of each purchase made by [...]
Use a VBA Macro to Automate this By Wyn Hopkins If you use Pivot Tables regularly then you’ve probably been frustrated by the default layout you get when you initially create one For example to get from the BEFORE view to the AFTER view below takes about 10 clicks of the mouse. [...]
Contact us today to learn more about our Power BI training options
08 6210 8500
