Formatting grids and other report formatting related posts.
//Recently a customer raised a support issue asking if it was possible to highlight a row within a table based on a selection made in a chart.
Interactive charting being a core XLCubed feature, of course it is!
Continue reading “Chart-driven Table Highlighting”
//In the last few months we have significantly extended the charting elements within XLCubed. We wanted to get the product formally certified by IBCS, and I’m delighted to say we achieved that in early June. We’ve been aware of IBCS for several years now through some of our customers and partners, but it was really only at the beginning of this year that we started to look in detail, and it really resonated with us.
Continue reading “XLCubed and IBCS”
//Consider this simple grid. It shows a ranking of the top 10 products by Sales Amount. We are going to add our own headers to this grid allowing us to create our own column names as well as implement custom functionality for dynamic ranking.
Continue reading “Custom Grid & Table Headers For Dynamic Sorting”
//This week’s blog is a quick tip on Grid formatting options. Did you know that XLCubed comes with ten pre-formatted grid styles which you can easily switch between?
These are available from the Workbook Format button on the XLCubed ribbon (the option remains greyed out until an XLCubed grid is added).
//I have been working with XLCubed for approximately two weeks now and when I first started, I wasn’t sure what to expect. I had come from a Mathematics background and so had no in-depth technical IT background.
Over the last two weeks, my main priority has been to get to know XLCubed like the back of my hand. At first, I was advised to run through all the XLCubed YouTube Tutorials and I must admit, it was a nice way to start learning this brand-new software. The videos were not complicated and gave a good introduction to all the different features. While watching the videos, I would try and recreate some of the things shown in the video such as grids and slicers, etc. and so was able to get a good feel for the software. However, the videos alone were not enough to get a full grasp of XLCubed and this was where the Partner Packs came in very useful. These are sample reports we provide for our partner network, and the steps to build them are documented.
//Last week’s post looked at the fundamentals of formatting XLCubed grids. A lot can be done with these techniques but sometimes more interactive formatting is desired. Within an XLCubed grid you can set Interaction Options to track which cell has been selected and output the selection and/or format it. In this post, we will look at how to apply this to highlight a row across multiple grids.
//Most formatting in XLCubed can be handled though the formatting options available on the right-click menu, and today’s blog will cover some of the common use cases.
//Today’s blog will show you a really quick and easy way to format your grid to show different display units.
This approach is ideal for dynamic Grids where the size of the values can vary considerably based on the selected filters, or where the user has drilled down to lower levels in the data. For example, if country level numbers are in hundreds of millions, but customer level numbers are in hundreds or thousands, it can be useful to have the ability to quickly change the display units.
//Today we’re revisiting one of our more popular guides, Creating rounded corners in Excel Tables, and have updated it for v7.1. When Igor Asselbergs was contemplating the value of round corners in design, he came to the conclusion that in many cases they added real value to the user experience.
The effect can be explained by the Gestalt Law of Continuity. Gestalt is a set of rules based on research into perception psychology, and a very powerful tool for Excel table design. In table design this effect can help us to see the table columns as a unit.
The previous process to create rounded corners in Excel tables required quite a bit of persistence and patience. In Version 7.1, we’ve introduced a feature to enable adding rounded corners in a few seconds rather than several minutes, so while the theory is identical the implementation is much improved. Take this report showing sales KPIs, where we would like to add rounded corners to the header row in the table.
To do this we first highlight the required area:
Then we go to Extras -> Add/Edit Round Corners:
The Colours and Border thickness will be picked up from the selected cells. Select the corners to be made round (in this case the Top Left and Top Right corners):
Click OK to apply the borders
To edit existing corners which were created by XLCubed then you can just highlight the cell or range and Go to Extras -> Add/Edit Round Corners. The changes will be applied to the existing corners (or the corners can be removed by unselecting them).
It’s a simple addition to the product which would have saved us quite a bit of time in customer implementations over the years, and hopefully now does the same for our users.
//Today’s blog is going to show you how to use XLCubed’s custom calculation functionality to create column breaks in a grid. Imagine that you have a report that shows you Reseller Sales across Product Model Categories over a 12-month time period.
There’s nothing wrong with this report but don’t you think it would be nicer if there was a way to separate out each quarter block ie put in a divider column between March and April, June and July, September and October. That would make it much easier to read and show clearly where each quarter period started and ended.
So let’s start by creating a custom calculation. Click the highlighted icon and give your custom calculation a name – let’s call it ColBreak. It’s connected to the Date.Calendar hierarchy.
Now in the Expression area enter a blank string starting and ending with ” (double-quote). Click OK.
To insert this into our report we now go to the Hierarchy Editor for Calendar Date – expand the All member and you will see ColBreak.
Drag this across and insert it into the report. We will insert it after March, June and September and click OK.
The report now looks like this:
Now let’s format this column break so that the we don’t see ColBreak appearing as a column heading. You need to right-click to get XLCubed’s right-click menu and then choose Format This Member.
We will choose white for the Font colour before clicking OK.
The report now looks like this with clear demarcations between each quarter:
