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:
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:
Have you ever tried copying parts of one workbook to another and been restricted by column widths? Or maybe you’re almost done with a report layout only to find that the last table you need to add has 4 columns, where there is only room for 3? Today we’re going to show you how to use Excel’s Camera tool to get around any Excel column width limitations to achieve your dashboard goals! Here we have an Excel heat Map on a separate sheet in our workbook.
It has been inserted into the dashboard below where the first thing to notice is the workbook’s variable column widths, in particular columns J and K. If we had just inserted our heat map as it was, the column widths in our dashboard would determine the width size of the heatmap. Instead we used Excel’s camera tool to insert our heatmap sized at exactly what we wanted, regardless of the destination sheet’s column widths.
We follow these simple steps:
- select the heat map in the source sheet
- click the Camera Tool icon
- navigate to the destination sheet
- click and insert exactly where you want
The Excel Camera Tool is also a great way creating dynamic screenshots of particular groups of data. The Camera Tool takes a picture of a selected area, and you can then paste that picture wherever you want it. It updates automatically, and because it is a picture rather than a set of links to the original cells, any formatting or data change in the source is automatically reflected in the picture.
The heat map chart source figures have been updated to show Europe’s higher sales – as you can see Europe now has the greater sales:
The dashboard heat map has updated automatically to reflect this value change.
If you can’t see the Camera Tool on your Excel menu you can easily attach it to your Quick Access Toolbar by performing the following steps:
- Click the File Tab
- Click Options
- Choose the Quick Access Toolbar Option
- In the ‘Choose Command From’ dropdown, select Commands not in Ribbon
- Find the Camera Tool from the alphabetical list of commands and add it to the Quick Access Toolbar.
We’ve had some great feedback from our Newsletter announcing the release of v7. A number of users have asked how we created the example in the Newsletter:
So, today we’re going to show you how to achieve some of the formatting that is now available when using SQL tables in v7.
Let’s create a SQL table from Grids & Tables tab. You’ll see the Create connection window:
Click Connect and you will see the databases you have access to….we’ll create our query based on Bicycle Sales and the fctData view.
Our SQL query returns the following data which is great but clearly is not that easy to read.
Let’s format this table. We’ll get rid of any borders currently set on the workbook by going to the format sheet and using Format Cells on the default cell format cell as below:
Back to the table, right-click and refresh table.
Now for the actual formatting of the table. Let’s format entries in the first column cPOS. Right-click on Car and Bike Stores, right-click and select Format Column and let’s set the font to be bold, size 12 with a double border on both top and bottom.
Now the second column cProduct. Again right-click Format Column and set the top border to be double, bottom border thick and the font italic.
We now go to Properties tab and on Appearance tab set Sections as below:
Check the box ‘Use columns as sections’, the column count is 2 in our example and Display style is set as ‘Sections in separate rows’.
We’ll also hide the first row of the workbook showing the table column headings.
The report now looks like the screenshot below which is much easier to read.