Propagate across Sheets
Today’s blog will run through Propagate across Sheets – it’s an XLCubed feature that’s been around for some time but is definitely one of our hidden gems!
Propagate across Sheets is some great functionality that allows users to quickly replicate a report onto additional sheets within the same workbook or a new workbook.
So why would you want to use propagate across sheets?
For example, imagine you have a number of legal entities across your business and you want to create a organisation-standard P&L report to be used across all the different entities. That’s easily done using Propagate across Sheets – simply build the report as normal and when done, right-click on the hierarchy member you want to propagate. Choose the entities to report and XLCubed will quickly create additional sheets/workbook(s) for these.
On the propagated sheets the formatting and print layout will be exactly the same as on the original sheet – the only difference is that there will be a different member on the propagated hierarchy,
Here’s a simple grid report showing sales for all Geographies across all Product Categories:
Let’s do a simple Propagate across Sheets and create a separate worksheet for each country.
From XLCubed’s right-click menu > Propagate Across Sheets.
Expand All Geographies, drag selected countries across to the right and click OK.
You can see that XLCubed has created a separate sheet for each country – the grid on each sheet shows the specific sales figures for each country.
There are some additional options that will change the results:
Insert as Text: if a formula was right-clicked then the resulting worksheets will have all formulae converted to values. If a grid was right-clicked then the corresponding grid on the target worksheet will be converted to values.
Keep all grids active on new sheet: with a multi-grid source sheet even if only one grid is propagated all grids will be copied and be active on new sheet
Move grid reference to new sheets: grids that are copied will have all Excel range references moved to new worksheet
Create new workbook: creates a new target workbook rather than just additional sheets in existing workbook
Advanced propagate: this allows the user to create a results set based on a particular data query
Let’s try using the Advanced tab to filter when we propagate – we have a request to produce a report that shows only those countries where Bike Sales in 2015 were above £1M.
Click Advanced and the option to Filter result:
Set the filter as below:
Click OK and XLCubed creates a separate grid report for the four of the six countries that fit the above criteria:
It’s that simple! We hope you’ve found this blog useful and feel inspired to try out the propagate feature when you get the opportunity. As always, we value your feedback on any enhancements we can make!