//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”
//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.
//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.
//One of the main reasons we use Excel is to analyse and display our data, for either our own consumption, or to show to others. In both cases, we want our data to be easily readable, and any important patterns to be immediately obvious.
We use colours, borders and other formatting to highlight important characteristics of our data, and to de-emphasize those features that should stay in the background (see The Dashboard Squint Test for more). In just the same way, we can use number formats to highlight numbers that are unusual in some way, decrease the focus on uninteresting numbers, or to remove excess detail. Here we recap the essentials of numeric formatting in Excel.
To apply or change a number format, select the cell or range that needs to be altered, then either:
From the dialog, you can select some common and very useful formats, including:
We’ll first go through some simple examples, including some of the standard formats mentioned above, so that in the next section we can build up more complex ones. To enter these formats, follow the steps listed above, then select Custom from the list on the left of the dialog.
The formats that we have used so far only use one format for all numbers. In fact, Excel lets us specify four formats: one for positive numbers and one format for negative numbers, one for zeros and one for text – all in one cell. To do this, we use the semicolon to separate the different formats. For example, to format only negative numbers as blue, we can use 0.0;[Blue]-0.0;; In this example, because we have left the other sections blank, zeros will appear as empty cells on the worksheet.
If we combine the pieces of information from the last paragraph, we can find another useful format. Any cells with the ;;; format applied will hide any data in the cell. This can be useful if you want a formula in a particular cell, but don’t want to hide an entire row or column for it.
Another common case is where you have large numbers, but don’t need to see all the digits. In this case, it can be useful to just emphasize the important part of the number, by using this format: 0, This format will round to the nearest thousand, and remove the excess zeros. You can extend this to millions by using another comma, and it’s even possible to include an indicator that the number is shortened like this: 0,,”M”
There are many more special formats available, including changing the boundaries between the semicolons and date and time formats. Have a look at the further reading, below, for more information.
XLCubed, being tightly integrated with Excel, allows you to specify number formats in two ways.
| Example | Format | Result |
|---|---|---|
| 0.2356 | 0.00 | 0.24 |
| 0.2356 | #.00 | .24 |
| 1235679 | 0 | 1235679 |
| 1235679 | #,### | 1,235,679 |
| 0.2534 | 0.0% | 25.3% |
| 435 | [Red]0.0 | 435.0 |
| 50 | 0.0;[Blue]-0.0;; | 50.0 |
| -50 | 0.0;[Blue]-0.0;; | -50.0 |
| 6541.21 | ;;; | |
| 15984.125 | 0, | 16 |
| 654915165.515 | 0,,”M” | 655M |
