Percentage charts with conditional formatting

By teylyn on February 25, 2013

Last week I saw some amazing Excel spreadsheets while I was attending the MVP Summit in Seattle and I thought I’d share some ideas. So here are step by step instructions on how to create a percentage chart with just the Excel grid and a sprinkle of conditional formatting. The goal we’re after looks like this:

We’re looking at a grid of ten by ten cells that form a square (roughly). Each cell in this grid represents 1% of the whole. The number below the grid is represented visually by an according number of filled squares (or cells).

Here is how to do it:

Build the grid

First we need the numbers from 0.01 to 1.00 in steps of 0.01 in a grid of 10 by ten cells. If you want, you can type these 100 numbers in manually, but there’s a faster way if you use the fill handle (the little dark square at the lower right corner of the active cell). We only need to type in four numbers. Let’s start in cell C4 and enter 0.91, then manually enter 0.92 in D4. Cells C5 and D5 will get the numbers 0.81 and 0.82 respectively.

Select all four cells, then grab the lower right corner of cell D5 and drag it all the way across to cell L5.

Release the mouse button and you will see the numbers filled in and incremented accordingly:

Leave the cells selected, grab the fill handle in L5 and drag it down to row 13:

Release the mouse button and your 10 x 10 grid is filled with the correct numbers.

That was a bit faster than typing them all in, right?

Prepare the Total cell

Leave one blank row below the grid, then select cells C15 to L16 and use the command Merge cells from the Alignment group of the Home ribbon.

After merging the cells, let’s enter a number, say 38% and do some number formatting. I’d like the cell to show the text “Total:” as well as the percentage number. Click the lower right corner of the Number group on the Home ribbon to open the Format Cell dialog. In the left hand pane select Custom at the bottom of the list. In the Type field enter the text
Total\: 0%

Note: The “\” in front of the “:” sign is needed make sure it appears correctly. If we did not add this, Excel would get confused, since the “:” has a special use in time formats

Hit OK and see how the cell shows text and number, even though the formula bar clearly shows that the cell only contains a number. Crank up the font size a bit. In this example I have used 28 pt Century Gothic.

Format the grid

Now we need to apply some TLC to the grid. Let’s hide the spreadsheet gridlines. On the View ribbon, untick the Gridlines box and the spreadsheet appears in uniform white.

The 10 x 10 grid should have light grey borders, so select the 100 cells …

… and open the Format Cells dialog on the Border tab. Select a light grey colour from the colour drop-down and after that click the Inside and Outline boxes.

The cells now feature a faint grey grid, but the numbers are still showing. Here’s how to hide them for good: Keep the 100 cells selected, open the Format Cells dialog again and activate the Number tab. Choose the Custom option from the number categories and in the Type field enter three semicolons

;;;

A number format in Excel consists of four parts to define positive, negative, zero and text values. The different parts are separated by semicolons. If the format definitions between the semicolons are missing, then the number will not be shown at all.

Now the grid is empty and we’ll set row height and column width to make it as close to square as possible:

Select columns C to L and use the Format drop-down on the Home ribbon, where you will find the Width command. Then select rows 4 to 13 and use the same dropdown to select the Height.

Apply Conditional Formatting

The last step is the conditional formatting of the 100 cells. Again, this can be done in one go. Select the grid, then click the Conditional Formatting drop-down on the Home ribbon and select New Rule.

In the next dialog click Use a formula to determine which cells to format. In the formula box enter this formula:

=C4<=$C$15

Note the position of the $ signs. Only the $C$15 should have them. Don’t put them anywhere near C4.

Click the Format button and select a darker grey as a fill colour.

Hit OK on all open dialogs.

That’s it. You’re done.

Change the number in the merged cell below the table and see the grid update with more or less grey blocks.

Of course, you can use other colours. Look at my Fruit Machine below. Click the blue cell and hit the Delete key to make the numbers change. Can you get all of them to display the same number?

Depending on your browser and/or screen resolution, you may see some of the grid lines wider than others. To download the workbook just click on the Excel icon in the dark bar below the spreadsheet.

Enjoy!

cheers, teylyn

Share this:

Posted in , , | Leave a response

Where did the MSDN SharePoint 2010 files go? All replaced with SharePoint 2013?

By teylyn on December 21, 2012

A few months ago I clicked a bookmarked link to an MSDN article about setting up the development environment for SharePoint 2010. Here is the link:

http://msdn.microsoft.com/en-us/library/ee554869.aspx

To my amazement, I found that the article now contains content about setting up the development environment for SharePoint 2013. I tried to navigate to the same tree node in the SharePoint 2010 section, but it does not exist. That change must have happened not too long before, though, because I still found the 2010 article in the Google cache and quickly saved it to OneNote. Phew.

But of course, that cannot work forever. And other saved links, and lots and lots of links on web sites that refer to SharePoint 2010 MSDN pages now show 2013 version content. And no way of selecting a drop-down to show the content for another version.

When I looked through some of the Excel help, I noticed that every 2013 help page I visited had a drop-down to select the version, for example http://msdn.microsoft.com/en-us/library/ff194068.aspx . When the drop-down is selected, the URL changes, but not by much. The string “(v=office.14)” is added before the .aspx to produce http://msdn.microsoft.com/en-us/library/ff194068(v=office.14).aspx , which will show the 2010 version of the page.

The drop-down is not there on the SharePoint MSDN pages, but the URL change works all the same. So, if your saved MSDN bookmarks or links to MSDN about SharePoint 2010 now show SharePoint 2013 content, just edit the URL and insert

(v=office.14)

before the .aspx

For the article about setting up the development environment, I changed the URL from

http://msdn.microsoft.com/en-us/library/ee554869.aspx

to read

http://msdn.microsoft.com/en-us/library/ee554869(v=office.14).aspx

and hey, presto, there it was in all its glory and original content.

Now I can delete my OneNote page with the copy. (Or maybe I’ll keep it. Just in case…)

Cheers, teylyn

Share this:

Posted in | Leave a response

Combine a stock chart with a line chart

By teylyn on November 19, 2012

Stock charts can take some time to get used to and are among the more unwieldy charts in the Excel chart palette. They get even more challenging if you want to extend the out of the box chart. Recently, a user asked how to combine a stock chart with a line or XY scatter chart. Trying this out I created a stock chart, then copied the data for an additional series and pasted it into the chart with Paste Special > New Series. I then tried to change the chart type of the selected series to an XY chart, but found that a stock chart cannot be combined with another chart. Excel presents an error information message, saying that “Some chart types cannot be combined with other chart types. Select a different chart type”.

Playing around a bit more, though I found that it is actually possible to just select the newly created data series and format it.

After adding the new “data” series to the chart, select the chart. Then click the “Layout” tab in the Chart Tools ribbon.

On the left hand side of that ribbon, select the newly inserted series in the drop-down of chart elements:

Then click “Format selection” just below the drop-down:

In the format dialog, the series can be given a line color and markers, just like an XY or line chart would take, so there’s really no reason to assign a different chart type to the series. The result looks like this:

Here’s the workbook. Tried and tested in Excel versions 2007, 2010 and 2013. You can change the values in the cells below and see the chart update right here and now. Depending on the speed of your interweb connection, you many need to take a breath or two, before the chart updates. :-) . You can download a copy of the workbook by clicking the green Excel icon in the black bar below the workbook.

cheers, teylyn

Share this:

Posted in , , | Leave a response

Showcase high and low values in Excel charts – Part 4

By teylyn on November 11, 2012

Introduction

In the last part of this mini-series I’m showing how to draw attention to high and low values in Excel charts by using background fill. The first part of this mini-series showed how to present minimum and maximum values of a chart by simply sorting the data values in ascending or descending order. Part 2 looked at conditional formatting the columns themselves if they were the highest or lowest value in the chart. In Part 3, an image was used as a background fill on a stacked series.

Background fill

So let’s look at how to use a background fill for the highest and lowest data points. This can be achieved by plotting a column chart on the secondary axis. First, we need to prepare the data.

The minimum values are identified with

=IF(B2=MIN(Value),1,0)

The formula for the maximum values is

=IF(B2=MAX(Value),1,0)

The prepared data table looks like this:

Plot all three data columns as a clustered column chart. Then select the value series and send it to the secondary axis. Format the “min” and “max” series to have 100% overlap and a very small gap, like 10%.

Format the primary Y axis to a maximum of 1.

The tricky thing with this variant is to have the secondary Y axis appear on the left. To do this, follow these steps:

  • On the Layout tab, select the Axes drop-down and show the secondary X axis. Now all the columns will be hanging upside down.
  • Format the secondary X axis and let the vertical axis cross at category number 1
  • Select the primary Y axis and format it to have no tick marks and no labels
  • Format the secondary Y axis (which is now positioned at the left of the chart), and format it to let the Horizontal axis cross at axis value 0 (zero), which will put the columns on the base line again.
  • Delete the primary X axis and fine tune the chart formatting as desired.

The result will look something like this:

Take your pick

I hope you have found this mini-series useful and will be able to apply the techniques to your charts.

The attached Excel workbook illustrates all charts above on separate sheets.

Enjoy.

Conditional Chart Highlights Part 4

Share this:

Posted in , , | Leave a response

Showcase high and low values in Excel charts – Part 3

By teylyn on August 12, 2012

Introduction

In this mini-series I’m showing how to draw attention to high and low values in Excel charts. In the first part of this mini-series, minimum and maximum values of a chart were showcased by simply sorting the data values in ascending or descending order. Part 2 looked at conditional formatting the columns themselves if they were the highest or lowest value in the chart.

This post shows a different approach.

Stacked columns with image fill

If you would like to retain the original colour of the chart columns, you can use another technique to highlight the maximum and minimum value: an image, for example an arrow, can sit on top of the maximum or minimum value bar and draw the reader’s eye to it. You will need two images, like coloured arrows or happy/unhappy faces or similar in a folder on your hard drive.

First we need to prepare the data table. The original data table looks like this:

Now we add two columns to the source table. The minimum value can be calculated with this formula:

=IF(B2=MIN(Value),MAX(Value)*0.1,0)

To calculate the maximum, adjust the formula to this:

=IF(B2=MAX(Value),MAX(Value)*0.1,0)

Why multiply by 0.1?

The objective is to have a data point on top of the high and low values that has a consistent size. It can then be formatted with an image fill and that image always remains the same size, regardless of the value or height of the data value it is stacked on. Of course, we could use a constant value, like 10 or 150, but if the values of the underlying data have a very wide range, a constant may not be the best choice. A value of 150 stacked on a column that shows 3 Million will not be very visible. Instead, this formula shows a percentage of the maximum value in the chart. Adjust it to your needs.

This formula returns a value of 10 percent of the maximum number in the “value” series, and that will be a consistent size across the chart. Applied to our sample data, the source data now looks like this:

Select this data table and create a stacked column chart. After some minor tweaking (delete the legend, remove the grid lines, decrease the gap width) the result will look similar to this:

Now we can replace the red and green fill for the stacked data points with an image. Select the data point for the maximum value, which will be stacked on one data column. Open the formatting dialog and on the “Fill” panel tick the “Picture or texture fill” option. Browse to the image on your hard drive and select it.

Repeat the formatting for the minimum data series.  In this example, I have used a red arrow for the minimum and a green arrow for the maximum data value. The result looks like this:

A sample file with this chart technique is attached.

Conditional Chart Highlights Part 3.xlsx

In the next and final part of the series we’ll explore background fills.

Cheers, teylyn

Share this:

Posted in , , | 1 Response

Showcase high and low values in Excel charts – Part 2

By teylyn on July 29, 2012

Introduction

In the first part of this mini-series, minimum and maximum values of a chart were showcased by simply sorting the data values in ascending or descending order

But…

There may be situations, where the order of the data points may not be changed, for example because they are in a chronological order, or because the data points are sorted alphabetically, so they can be seen in relation to other charts with the same alphabetical order.

With the data values in alphabetical or chronological order, it can be hard to identify the highest and/or lowest data point, so a few visual pointers may be called for.

So, here’s an alternative to sorting the values:

Conditional colouring of the columns

To highlight the minimum and maximum values of the data set, we can colour the minimum values red and the maximum values green. This solution is also dynamic, of course, so that any changes in the base data set will be reflected in the chart without user intervention.

We need two additional data series in helper columns, one for the minimum and one for the maximum data value. These will also show if a max or min value is present more than once.

The minimum value of the data set can be obtained with a formula like

=IF(B2=MIN(Value),B2,0)

The maximum data value can be calculated with

=IF(B2=MAX(Value),B2,0)

These two columns are added to the base data table to provide a scenario like this (I’ve applied conditional formatting to highlight the minimum and maximum values in the source table):

Data table for conditional highlights

Plot all three data columns as a clustered column chart, with the town names as the X axis labels.  That should produce a chart like this:

Clustered column chart with default settings

Double-click any of the columns to format the data series. Set the series to 100% overlap and decrease the gap width to 50%.

Format the data series

Delete the legend and the grid lines to arrive at this:

Chart with conditionally formatted collumns

The maximum data points are green, the minimum data points are red — and that even applies to duplicate values.

View the Excel file in Excel Web Apps or download a sample file.

In the next post I will show how to use an image to highlight the minimum and maximum value.

cheers, teylyn

Part 1: Sort the data

Part 3: Stacked series with an image fill

Share this:

Posted in , , | Leave a response

Showcase high and low values in Excel charts – Part 1

By teylyn on July 27, 2012

Introduction

When charting large data sets, we sometimes may want to draw attention to the maximum or the minimum value of a dataset, or both.

Let us assume a data set like this in a sheet called “Base data”:

For ease of use in follow-on formulae let’s create two range names, one for the labels and one for the values:

Label =’Base data’!$A$2:$A$17
Value =’Base data’!$B$2:$B$17

A simple column chart created from that data set will look like this:

Chart with unordered values

Unordered

At a glance, it can be difficult to determine the minimum or maximum values in that chart. How can we make it easy for the reader to identify the minimum and/or maximum values? Grid lines spring to mind, but even with grid lines it may still be difficult.

In the following I’ll show a few different approaches.

Ranking by data value

The most obvious way to showcase minimum and/or maximum values in a chart is to sort the data so that the chart displays the data in ascending or descending order, like in the two following charts:

Chart with values in ascending order

Ascending

Chart with values in descending order

Descending

To sort the data, we can use a helper column to determine the rank of each value and then use another helper column to order the values based on their rank. This sorted data will then be used as the chart source.
Ranking the values can be achieved with the following formula, which includes a tie breaker to assign individual ranks to cells with exactly the same value:

=RANK(B2,Value)+COUNTIF(B$2:B2,B2)-1

Applied to the sample data, we can see the following ranking (I’ve used conditional formatting to highlight the lowest values in red and the highest values in green:

In the attached example file, you will find a sheet called “Ranked descending” with this formula in place. For use in follow-on formulas, I’ve created a named range for the ranking results, called Rank, which refers to

='Ranked descending'!$C$2:$C$17

To sort the labels and the data in ascending order (small to large) we can use these two formulae in helper columns:

Label =INDEX(Label,MATCH(LARGE(Rank,ROW(A1)),Rank,0))
Value =INDEX(Value,MATCH(LARGE(Rank,ROW(A1)),Rank,0))

For descending order (large to small), the formulae for label and value are

Label =INDEX(Label,MATCH(SMALL(Rank,ROW(A1)),Rank,0))
Value =INDEX(Value,MATCH(SMALL(Rank,ROW(A1)),Rank,0))

In a single data table, the results will look like this:

Now it’s very easy to plot a simple column chart based on the data sorted ascending or descending. Note that the solution is dynamic, i.e. when the original data value changes, the sort order will adjust automatically, as will the chart.

But…

There may be situations, though, where the order of the data points may not be changed, for example because they are in a chronological order, or because the data points are sorted alphabetically, so they can be seen in relation to other charts with the same alphabetical order.

With the data values in alphabetical or chronological order, it can be hard to identify the highest and/or lowest data point, so a few visual pointers may be called for.

Some alternative charting ideas will be discussed in the following parts.

View the file in Excel Web Apps, or download a copy

cheers, teylyn

—

Part 2:  Conditional colour formatting a chart

Share this:

Posted in , , | Leave a response

SharePoint “There was an error in the callback” in “Manage Content and Structure”

By teylyn on June 25, 2012

The other day, I had this error when I tried to point a Content by Query Web Part (CQWP) to a list on a sub site. I clicked the + sign in the tree view to open a sub site so I could see its lists, but what I saw instead was:

“There was an error in the callback.”

Ah! Ok. That really helped clarify the issue.

Since I know my site collection structure, I decided to ignore the error and the browse button. Instead, I entered the path to the desired list manually in the field provided in the CQWP properties. Testing the CQWP I found that it works just fine. Lesson: You don’t need to browse to a list if you know its URL!!

But I wanted to get to the cause of the error. The problem apparently does not lie with actually accessing the sub site or the list by the CQWP itself, but instead with rendering the tree view to open the sub site and expose its lists.

The tree view triggered by the “Browse” button when selecting site or a sub-site reminded me of the view that is presented when looking at the “Manage Content and Structure” link in the Site Collection tools (Site Actions > Site Settings).

So I tried that as well.

Sure enough, the site could not be opened here either. After persisting a few times, I got the unloved screen informing me that “An unexpected error has occurred”, followed by a correlation ID. Now, I’m an end user.  I’m not blessed with access to the server logs, and don’t know how to process that correlation ID. So I sent it off to our server team to find out more about it.

Meanwhile, searching the mighty InterWeb, I found suggestions that either a list in the site or the site itself may have become corrupt. The best solution I found was to delete the site and start from scratch.

Uhmm. No. I spent a week building the site, the lists, the content. There’s got to be a better way.

Then I tried this:

  • One by one, I deleted every list in the sub-site, knowing that I could always restore them from the recycle bin.
  • After each list deletion, I went back to the Site Collection and ran the “Manage Content and Structure” link again, testing if I still got the error.
  • Three or four lists into the process I hit the problem piece, because after I deleted “ListX” , the error no longer showed in “Manage Content and Structure”. I could now use the tree view buttons just fine.
  • I went back to the sub-site and restored each list from the recycle bin, leaving “ListX” until last.
  • After each restore, I ran “Manage Content and Structure” in the Site Collection. Every time it behaved just fine. No errors.
  • Finally, I dared restore “ListX” list from the recycle bin, too. Holding my breath and starting “Manage Content and Structure”.
  • Exhale. It WORKS! The list no longer causes the error. I can open the tree view and drill down into the Content and Structure just fine.

Selecting the sub-site’s list as a source from the CQWP property panel now also works without error.
Whatever it was that caused the callback error (whatever that is), it simply healed itself with a delete/restore cycle of the lists involved.

I’m especially happy that this list now works, since its form has been customised with InfoPath. After the restore, the InfoPath list form still works as intended.

Now, that was easy, for a change.

cheers, teylyn

Share this:

Posted in , | Tagged | 1 Response

Link to edit item (ECB) on another column than Title – no code!

By teylyn on May 1, 2012

In SharePoint, any custom list will have a default column called “Title”. This column will have the edit menu attached to it when it is shown in a List View. The edit menu is also referred to as the “Edit Control Box” or ECB. The Title column can be renamed if another column name is preferred. This is easy and will be suitable for many situations.

What’s not so easy, though is to change the type of the Title column to something other than “Single line of text”, or to move the link to the edit menu to another column altogether.

For example: I would like a field called “Dropdown” to have the link to the edit menu, and the field “Dropdown” is a choice field with  - you guessed it – a dropdown list. At the same time, I don’t want to show the Title field at all. Not in my list view and not on my form. So, looking at the current state in the following screenshot: The link should be on the “Dropdown” column, not on the “Title” column.

Now, there are several ways of tackling this. I can turn on the content type management setting for the list, and then set the Title column to “Hidden” to remove it from the form.  I can edit the List View Web Part with SharePoint Designer. In a SharePoint 2010 XsltListView it is possible to designate any visible column to show the link to the edit menu by clicking a value in SharePoint Designer and selecting the chevron that pops out the options.

This seems like a lot of work though, and I’d also like the end user to be able to use the out of the box List view without any manipulation with SharePoint Designer.

Another approach involves using PowerShell. Can’t do that, though. I am an end user and have neither access nor permissions to run PowerShell scripts. I could put a request in our IT department’s long queue of things to do, but that will take time, involve a lengthy approval process, and I don’t want to wait for a week or more.

Yet another approach involves creating the list via Visual Studio and deploying it as a feature. Again, that is not a viable option for me. I’m not a developer, I don’t have access to these tools, and in general, I don’t want to litter my farm with features and solutions that are only used in one puny list.

So, I had to come up with a better way, and I think I found one.

Enter InfoPath.

I want to jazz up my form with InfoPath anyway, so while I was working on the list form in InfoPath, I did the following:

I set the default value for the “Title” field to the value of the “Dropdown” field.

Here’s the original form after clicking “Customize Form” in the List Tools:

Select the Title field and click the “Control Properties” in the Control Tools ribbon. Then click the formula button for the default value and insert the Dropdown field.

After that, delete the table row with the Title field. Now, whenever the Dropdown field is changed in the InfoPath form, its value will also be saved in the Title field.

An alternative method is to trigger an action when the “Dropdown” field is changed: Add a rule to the “Dropdown” field that changes the value of the “Title” field to be the same as the current value of the “Dropdown” field.

Publish the form and test it.

As an added twist, the Title column can be renamed to “Drop down” and the actual “Dropdown” column can be removed from the view. The result looks like this:

So there now: no custom code, no feature that needs to be deployed, no PowerShell, no SharePoint Designer. Instead, with  just a touch of InfoPath we have achieved the behaviour and look and feel of moving the “link to item with edit menu” to another column. At the same time, we’ve apparently changed the data type of the “Title” column to a choice column. Well, not really, but it sure looks this way. The user can create new views of the list and see that behaviour without involving SharePoint Designer or other tools.

Will that work for you?

cheers, teylyn

Share this:

Posted in , | 6 Responses

Side by side trendlines in a column chart

By teylyn on April 24, 2012

The challenge: A column chart needs to show two sets of columns and have a trendline for each set of columns.

The screenshot below shows the desired outcome.

This post describes how to create this chart in 10 simple steps.

First, let’s arrange the underlying chart data:

 

Step 1

Create a column chart with two data series: simply select a cell anywhere in the table in columns A to C and click Insert > Column > 2D Column. Accept the defaults and delete the legend.

 

Step 2

Add the data for the blue series again, but this time select only the populated cells in column B, i.e.  =B2:B7

 

Step 3

Change the chart type of the new series to XY scatter.

 

Step 4

Edit the data source of the green series and use the values in column E for the X values, i.e.  =E2:E7

 

Step 5

Add the populated cells from column C as a new series with X values from column E, i.e.

X values =E8:E13
Y values =C8:C13

 

Step 6

Adjust the maximum value of the secondary X axis. The maximum value needs to be identical with the number of columns in the chart. In this example, the chart has 12 columns, so the maximum value for the secondary X axis needs to be set to 12.

 

Step 7

Format the columns to have 100% overlap and a smaller gap.

 

Step 8

Add a trendline to each of the XY series.

 

Step 9

Hide the XY series by formatting them to have no line and no marker.

 

Step 10

Hide the secondary X and Y axes by formatting them to have no tick marks and no axis labels.

 

Done. Download the sample file here: SideBySideTrendlines.xlsx

cheers, teylyn

Share this:

Posted in , , | Leave a response

Note to spammers

Don't bother posting your links or ads. All comments are moderated. Your spam will never make it to the site.
Next »