Warning: Excel Can Get Volatile (revised)

This is a revised version of this post: Warning: Excel can get Volatile

Excel is a great tool for dashboard/report delivery and design (it’s why we created our addin in the first place!), but there is a hidden performance trap:

Offset, Now, Today, Cell, Indirect, Info and Rand

If you’ve ever used any of these formulae, you may have noticed that whenever you change a cell, or collapse/expand a data grouping, Excel recalculates. That is because these are VOLATILE formulae, as soon as you use one of these, Excel will enter a mode where everything is always recalculating, and for good reason.

Continue reading “Warning: Excel Can Get Volatile (revised)”
/

As we’ve seen in the past, XL3Link is a flexible formula that is really worth knowing about to help easily build interlinked reports. To some extent it’s like a Swiss army knife – multi-purpose!  From highlighting a series on a chart to hosting a tooltip,  XL3Link is a flexible formula that can help make your reporting more interactive at multiple levels.

In this post we’ll be discussing a few more novel ways to use XL3Link.

/

A snappy fix for layout problems in Excel

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.

• 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.

• Click the File Tab
• Click Options
• 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.
/

Drive Excel Chart Min/Max from Range

How do I drive the min and max values of an axis from an Excel Range? This is one of the most commonly asked questions about Excel and with each new release it always amazes me that this feature hasn’t been added to the base product.

It’s a very common scenario to come across, you are building a line chart and it’s all looking ok until Excel suddenly decides to set the min value to 0, all of the detail is lost and you have gone from a nice detailed set of lines to a mishmash of colours a few pixels high.

There are some pretty sophisticated techniques Excel is using when working out what min & max to use, but sometimes we just want to set them to a particular value (normally anything other than 0!).

Here’s a pretty simple set of numbers and the resulting chart we get from Excel (just with all the defaults).

This all looks fine, but let’s change  “C” Monday’s value to 86, now look what happens:

Excel has applied its rules and decided that 0 is a good place to start the chart from, but in this case I lose a lot of the detail and end up with all the lines grouped together.

We could, of course, change the Axis min value to something a bit more sensible, so we’ll use the Format Axis option to set a minimum value of 84:

That looks better!

The base numbers had been entered manually, so being able to type a fixed value into the minimum axis is fine, but what if the numbers were coming from a cube or Sql database? Wouldn’t it be really helpful to be able to drive the minimum value from a range; I can change just about every other thing about the chart but after so many years and so many different version I still can’t do this.

Luckily for me (and our customers!) we already have an Excel addin so we can simply add the functionality to do this using one of the new formulae in 6.5:

`XL3SetProperty( ObjectType, ObjectName, Property, Arg1, [Arg2],…, [Arg27] )`

The formula to drive the chart axis from a range is simply:

`=XL3SetProperty("Chart","Chart 1","YMin",\$C\$1)`

Other options are:

PropertyDescriptionValue
“YMin” or “YMax”Sets the limits of the Y Axis.Numeric
“Y2Min” or “Y2Max”Sets the limits of the Y2 Axis.Numeric
“XMin” or “XMax”Sets the limits of the X Axis.Numeric
“X2Min” or “X2Max”Sets the limits of the X2 Axis.Numeric

Now finally we can build reports (and publish them to the Web), confident that regardless of the data or criteria selected  we aren’t going to end up with a line chart starting at 0 and bunching all the lines together.

This formula can also be used to modify various aspects of our own grids, slicers & small multiples based on the values of excel cells. The kind of things that we and our customers wanted to achieve were things like:

• Move  dimensions between axes
• Change the member selection types
• Modify various grid properties based on different formulae

Lets look how the formula works to do some of these things:

`=XL3SetProperty("Grid","My Grid","HierarchiesOnColumns","[Products]","[Regions]", \$a\$1)`

Would move the Product, Region and whichever hierarchy is in \$a\$1 to the columns (I could use a slicer or drop down to update \$a\$1 to let the user switch between various hierarchies)

`=XL3SetProperty("Grid","My Grid","RemoveEmptyRows",\$b\$1)`

Would toggle whether to display rows without data based on the value of \$b\$1

If there are any aspects of Excel that you think would be useful to drive from a range, please let us know!

/

Warning: Excel can get Volatile

There is a revised version of this post here.

Excel is a great tool for dashboard/report delivery and design (it’s why we created our addin in the first place), but there is a hidden performance trap:

Offset, Now, Today, Cell, Indirect, Info and Rand

If you’ve ever used any of these formulae, you may have noticed that whenever you change a cell, or collapse/expand a data grouping, Excel recalculates. That is because these are VOLATILE formulae, as soon as you use one of these, Excel will enter a mode where everything is always recalculating, and for good reason.

Offset & Now are the formulae we see used most often. Let’s look at each of these in turn and talk about some alternate approaches to avoid this issue.

Offset

This is by far the most common of these danger formulae that we see in use. Here’s the formula definition:

`=Offset(reference,rows,cols,height,width)`
`Returns a reference to a range that is a given number of rows and columns `
`from a given reference.`

We typically see these as part of a named range definition for driving chart source data – it allows the number of rows/columns driving the chart data to change automatically; a not unusual requirement when it comes to building reports (especially when a report contains some user defined filters or slicers). Here’s an example:

A very simple spreadsheet – we can type the number of months to display in the chart. In reality the number of months to display will probably be driven by the data available for the criteria selected. The screenshot already shows the issue we have –  the chart is setup to display a max of 12 months, but we only have 3 months of data available.

The most obvious approach is to use the Offset formula to pick the chart area to use automatically, we could create a named range such as:

Now we just change the chart data source to be the named range:

The chart is now plotting 3 months, but will automatically update to show the required number of months:

BUT we have now used a volatile formula –  although this is a simple workbook, we are now in a position where Excel is going to have to recalculate everything all the time. It’s probably a good time to look at why Excel is going to do that. Let’s have a look at very simple formula to understand how Excel recalculates things.

Consider the formula:

`C1    =A1 + B1`

We can see that C1 is dependent upon A1 & B1 – so whenever a value in either of these cells changes C1 will need to be recalculated to show the correct answer. Excel knows about this dependency because it maintains a dependency tree; it knows which cells need to be recalculated whenever any other cell changes. This is a very efficient way of working, if a workbook has thousands of formula, but only one values changes, and this only needs 10 of these formula to recalculate, then only 10 will be calculated.

If C1 contained:

`C1    =Sum(A1:A20)`

We know that C1 depends upon any of the cells A1:A20, and so does Excel. But what if C1 was:

`C1    =Sum(Offset(A1,0,0,B1,1))`

Which cells is C1 dependent upon? At a glance you could say A1 & B1.

but  B1 contains the number 20, so actually C1 is dependent upon A1:A20 and B1 (I’ve highlighted the additional cells that are dependent):

Just as we can’t see at a glance which cells C1 needs – Excel also can’t easily decide that. Therefore, Offset is volatile because, if it wasn’t then there is a danger that Excel would take so long to work out if it needs to be calculated that it might as well always calculate it.

There is an easy solution to this, INDEX. Here’s the formula definition (be careful, there are 2 ways to use Index, we want the REFERENCE one):

`=Index(reference,row_num,column_num,area_num)`
`Returns a value of reference of the cell at the intersection of a `
`particular row and column, in a given range`
The big difference, compared to Offset, is that Index is going to return a single cell reference, so you need to use it as part of a range selection A1:Index(…). Here’s the same “Offset” Sum redefined as an “Index”:
`C1    =SUM(A1:INDEX(A1:A20,B1,0))`

The formula is simply saying the range we want starts at A1 and goes down the number of rows set in B1. The crucial difference is that the Index functions knows that A1:A20 is the maximum range we are likely to look at and therefore the dependencies are known just by looking at the formula itself:

We can now update the Named Range to use the Index function instead:

`=Sheet1!\$C\$6:INDEX(Sheet1!\$C\$6:\$C\$17,Sheet1!\$D\$2,0)`

Now/Today

The Now and Today functions return the current date to a cell – this is generally used so that when a report is loaded it will always show the data based on “Today”. Whilst this is not an unreasonable thing to want to do,  in reality what most people want is for the report to run for the most recent data, which could actually mean a number of things:

• Yesterday (if the data is built in a nightly process)
• The last working day (if the source transactional system is only used during office hours)
• Current month etc.

The easiest solution is to let the data determine the date to use – if we use an XLCubed Grid or Query Table to retrieve the data we can simply setup a grid to retrieve the days/months where there is data:

And use the Sort option “Reverse” to display the most recent data first:

With the grid set to “Refresh on Open”  we know that A6 will always have the most recent date available in the cube and can base the rest of the report off that cell.

Incidentally, V6.2 of XLCubed introduces a new option to Slicers to automatically select the most recent date member when the report is loaded:

/

Heatmap Tables with Excel

The following article shows how to create Heatmaps in Excel 2003, for Excel 2007 & 2010 please see the updated article “Heatmap Tables with Excel – Revisited

This Heatmap Table shows you the revenues and the discounts of a company over the course of one year per product group. The size of a bubble shows the revenue made in a particular month and the bubble color shows the discount rate given. The discount rate has been encoded as a range of green colors, ranging from a light green, for low discounts to a dark green for high discounts. The years and product totals are shown at the right and bottom as an integrated part of the table.

Tufte often talks about the integration of number, images, and word, and I think he’s quite right. A way archive this in Excel is to integrate charts into tables, so called graphical tables, a very effective means to archive More Information Per Pixel. I already wrote about graphical tables here, here, and here.

/

Interactive English League Bumps Chart

This bumps chart shows you the English Premier League 2007 – 2008. It allows you to highlight and compare two teams by clicking a team in in the table or a line in the bumps charts.

Interactively highlighting a data series in a large data set is very powerful. All lines in the data set are set to light Gray to show you the big picture and the patterns and general tends in the data set. Once you have identified an interesting data series you click it and we highlight it with a bright, saturated color. This has the effect of bringing it into the foreground and allows the user to see the details in the context of the other data series.

As a bumps chart has the lines equally spaced on the value axis you can put an Excel table next to the chart that serves as a legend on one side, and as a detailed ranking table on the other side.

/

Hyperlink Legends to Highlight a Series

So I picked up Jon’s idea and tried to combine it with ParamLink. In  The Missing Link I introduced the (free) ParamLink Add-In. It implements a new hyperlink formula ParamLink(). When the hyperlink is followed the formula can set cell values or define names.

/

Creating rounded corners in Excel Tables

Igor Asselbergs was contemplating the value of round corners in design. Is it eye-candy? Or does it add value to the user experience? He comes to the conclusion that rounded corners clearly make a difference.

“On the left side, you see one surface divided by a line. On the right side your eye interprets the image as two adjoining boxes.”

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.

Did you ever wonder how to create rounded corners in Excel tables?

The basic idea of rounded corners is putting some shape objects into the corners of the column headers.

Here are the steps to create rounded corners:

• To show the Drawing toolbar, click on the “Drawing” icon in the main toolbar
• Go to Drawing Bar > Auto Shapes> Basic Shapes and insert an arc and a rectangle

• Right-click the shapes and select Format Auto Shapes > Colors and Lines. Give the arc the fill color of your table headers, and the rectangle the fill color White. Set “no line” for the both.

• Right-click the shapes and select Format Auto Shape > Size and assign the arc and the rectangle the size 0.5″x0.5″ and set Lock aspect ratio.
• Select the rectangle and move it to cell B2 keeping the ALT key pressed. This ensures that shape snaps to the Excel grid. Do the same for the arc so that it overlaps with the white rectangle.

• Select the arc and the rectangle and select the right-click command group.
• To get the corner for the left side copy-paste the shape and go to Draw > Rotate / Flip / Flip Horizontally

• Set the Size to 0.18″ if you want rounded corners that have the Excel standard row height

• Set the Size to to 0.09″ for corners with 50% row height

• If your table headers have a different border or fill color, click the corner twice to select the rectangle arc object in the grouped corner object and format the arc with your fill or border color

/