Category Archives: Office 2013

Excel Tip #29: Forcing Slicers to Filter Each Other when Using CUBE Functions

As I mentioned in my original post, Exploring Excel 2013 as Microsoft’s BI Client, I will be posting tips regularly about using Excel 2013 and later.  Much of the content will be a result of my daily interactions with business users and other BI devs.  In order to not forget what I learn or discover, I write it down … here.  I hope you too will discover something new you can use.  Enjoy!

Scenario

You have went to all the trouble to build out a good set of slicers which allow you to “drill” down to details based on selections. In my example, I have created a revenue distribution table using cube formulas such as:

=CUBEVALUE(“ThisWorkbookDataModel”,$B6, Slicer_Date, Slicer_RestaurantName, Slicer_Seat_Number, Slicer_TableNumber)

image

Each cell with data references all the slicers. When working with pivot tables or pivot charts, the slicers will hide values that have no matching reference. However, since we are using cube formulas the slicers have no ability to cross reference. For example, when I select a date and a table, I expect to see my seat list reduce in size, but it does not. All of my slicers are set up to hide options when data is available. There are two examples below. In the first, you can see that the seats are not filtered. However, this may be expected. In the second example, we filter a seat which should cause the tables to hide values and it does not work as expected either.

image

image

As you can see in the second example, we are able to select a seat that is either not related to the selected table or has no data on that date. Neither of these scenarios is user friendly and does not direct our users to see where the data matches.

Solving the Problem with a “Hidden” Pivot Table

To solve this issue, we are going to use a hidden pivot table. In most cases we would add this to a separate worksheet and then hide the sheet from the users. For sake of our example, I am going to put the pivot table in plain sight for the examples.

Step 1: Add a Pivot Table with the Same Connection as the Slicers

In order for this to work, you need to add a pivot table using the same connection you used with the slicers. The value you use in the pivot table, should only be “empty” or have no matches when that is the expected result. You want to make sure that you do not unintentionally filter out slicers when data exists. In my example, I will use the Total Ticket Amount as the value. That will cover my scenario. In most cases, I recommend looking for a count type value that will always have data if there is a potential match of any kind.

image

Step 2: Connect the Slicers to the Pivot Table

Using the Apply Filters button on the Pivot Table ribbon, you need to select all the slicers you want to interact with each other.

image

Once these changes are applied, you will see how my data changed.

image

Now, let’s test this for real. We will keep the date and table, but now we will see that the other slicers are now filtered to match the data that is available.

image

As you can see, the solution is fairly simple, but not intuitive. You will be able to create more creative dashboards with this technique. Keep in mind this issue is primarily a problem when using cube formulas in your Excel dashboard.

Until next time…

Excel Tip #24: Removing “Buttons” from Excel Charts

As I mentioned in my original post, Exploring Excel 2013 as Microsoft’s BI Client, I will be posting tips regularly about using Excel 2013 and later.  Much of the content will be a result of my daily interactions with business users and other BI devs.  In order to not forget what I learn or discover, I write it down … here.  I hope you too will discover something new you can use.  Enjoy!

Annoyed by the Buttons, Remove Them

This is one of my shortest tips, but many users who are new to Excel charts look to remove the field buttons on the charts. See the image below to see what we are talking about.

image

It is really simple to hide them or turn them off. You can manage the field buttons on the ANALYZE tab on the POWERCHART TOOLS. If you want to turn all the buttons off, you only need to click the Field Buttons button which will look “unselected” and all the buttons are gone. If you want to remove only some of the buttons, expand the selection on the button and select or deselect what you want to see. (Although I am not a fan of buttons in dashboards because they are ugly.)

image

Here is the same chart without buttons. Be sure when you are designing your dashboards, you turn the buttons off so you can see how the chart looks for deployment. Buttons take up a lot of real estate in the chart. When they are removed your chart could look very different.

image

Chart Filter Buttons

One use case that I want to highlight is using filters on chart. When you add a filter for your chart, you need to use a field button to interact with the filter. The nice part about hiding the buttons and deploying to Excel Services, you can apply an underlying, hidden filter. However, if you want to change the filtered option you need to unhide the field buttons. If you want users to apply filters, you should use slicers not the filter option. Slicers are a much better user experience.

Excel Tip #23: Adding a Trendline to Your Chart

As I mentioned in my original post, Exploring Excel 2013 as Microsoft’s BI Client, I will be posting tips regularly about using Excel 2013 and later.  Much of the content will be a result of my daily interactions with business users and other BI devs.  In order to not forget what I learn or discover, I write it down … here.  I hope you too will discover something new you can use.  Enjoy!

Value of a Trendline

Adding a trendline to a chart visually projects the current trend over time or other axis. Adding a trendline to a chart is very easy. I am going to look at two usages – one over time and one over age ranges. Excel supports a variety of trendlines to meet specific data stories. I typically use linear and logarithmic depending on which seems to show the trend the best. However, Excel also supports polynomial, power, exponential and moving average trends. If you want to know more about the formulas and the properties applied check out Microsoft’s support article on trendlines.

Adding a Trendline Over Dates

This is the most common use of a trendline. As you use columns or bars to represent data for each time period, you add a trendline to see how the data is changing. First, create a chart with data represented over time. In my example, I am looking at game usage on cell phones as a percent of polled users over time. Here is my chart without the trendline.

image

In order to add a trendline, click the cross image next to the chart. It will open up the Chart Elements menu. In this menu click the arrow by Trendline to see your options. If you just select Trendline, it will add a linear trendline by default.

image

Once you select trendline, you can hover over the various trendline types to see how they will be displayed on the chart. In order for this to work, you must have the Trendline option selected. Here is my chart with the linear trendline applied.

image

Adding a Trendline Over Age Ranges or Non-Date Data

The other option is to use a non-date based trendline. In order to make sense the axis should still be sequential in nature. In my example, I am going to swap the date out for the age range of the responses. I am also using the Exponential trendline in this.

image

As you can see here, we can see that younger users are more apt to play games. The trend works because the data being reviewed is sequential.

Multiple Trendlines

You can also have more than one trendline on a chart. There are two scenarios that I will show here. In the first scenario, you can add two types of trendlines to your chart. To add another trendline, you go to the cross and select the trendline type you want to add. If you get the wrong one created, unselect the trendline option and reapply. Here is my Game Usage by Age example with the linear trendline added.

image

You can also do this with multiple data points. I am going to add Internet Usage % to the chart and compare as well. When you select the Trendline option, it will open a new dialog box allowing you to select the metric you want to trend. So, in our use case we select Game Usage % for the first trendline. It will match the trendline color to the bar color. Now open the Chart Elements menu and select the arrow and choose linear again. This will let you choose the value to be trended. We then select the Internet Usage % and we get two linear trendlines on the chart.

image

image

Here you can see the year the trends “cross”. It allows for some other interesting visualizations you can add to your Excel dashboard.

Excel Tip #22: Combo Charts – Out of the Box Functionality

As I mentioned in my original post, Exploring Excel 2013 as Microsoft’s BI Client, I will be posting tips regularly about using Excel 2013 and later.  Much of the content will be a result of my daily interactions with business users and other BI devs.  In order to not forget what I learn or discover, I write it down … here.  I hope you too will discover something new you can use.  Enjoy!

Creating a Combo Chart

Excel supports a couple of options for overlaying lines on bars or columns. The most common method is to use the combo chart. In this option, you would put metrics into the chart and then select which is the bar and which is the line. This is particularly helpful when using different types of metrics such as counts and percentages. To set this up, you create the initial chart with the metrics you want and then change the type. In the example below I have text and email usage counts with internet usage as a percentage in a column chart.

image

Select the Change Chart Type option on the PIVOTCHART TOOLS menu.

image

In the dialog that is opened, choose Combo Chart at the bottom. Excel will separate the values into lines and columns. In my case, it picked correctly, but be sure to check as it does not do what you want all the time. Because I am using percentages and counts, I want to have a secondary axis. My result is below. We can now look at percentages and counts in the same chart.

image

This functionality can only be used with column charts and lines or areas. The value is being able to show two types of data on the same chart. Up next, we will look at overlaying charts in Excel which gives you more design options for your dashboard.

Excel BI Tip #20: Wingdings–an Excel Services Supported Indicator Alternative

As I mentioned in my original post, Exploring Excel 2013 as Microsoft’s BI Client, I will be posting tips regularly about using Excel 2013 and later.  Much of the content will be a result of my daily interactions with business users and other BI devs.  In order to not forget what I learn or discover, I write it down … here.  I hope you too will discover something new you can use.  Enjoy!

Wingdings? Really? How did we get here?

As you have seen from previous tips, I have been working with customers to build dashboards using Excel 2013 in SharePoint 2013. I am a big fan of conditional formatting. However, one of my customers wanted to use a specific design which used triangles as images on their dashboard to indicate whether the trend was improving or worsening. What you may not know is that drawing shapes and textboxes are among the objects not supported in Excel Services.

Here is what it looks like in Excel:

image

Here is what it looks like in Excel Services – note the warning bar at the top:

image

This led me to my first option – use indicators in conditional formatting, it has a similar image.

image

As you can see it starts out fairly small, not the large shape we want to display. So, we added the indicator into a merged cell and increased the font size. You can see the image is pixelated.

image

Making matters worse, when you upload it to Excel Services it will not honor the font size.

image

Winging it with Wingdings

For some reason, yet unknown, it occurred to me to use Wingdings. Wingdings are TrueType fonts which make them “scalable” because you can specify the font size. In this case we are looking for an upward facing triangle and down facing triangle. So the first thing we needed to do was try to find out if those symbols existed. Here is one of the clearest cheatsheets I found for Wingdings font set: http://speakingppt.com/2011/10/31/finally-a-printable-character-map-of-the-wingdings-fonts/. Bruce has created a PowerPoint slide which is easy to follow. Whether you use, his or look it up on your own, you will find that in Windings 3, the letters “p” and “q” are the directional triangles that we need (p and q). Now let’s build our visualization with Wingdings (and no, I can’t believe I said that as well).

The key to using wingdings is that you need place the font representing what you are looking for in the field as shown here. You can see that the value in the cell is “p” but the wingding font gives us the triangle.

image

Not only can you affect size, you can change the color.

image

But the end goal was to have this work in Excel Services. So I can save this sheet to my Office 365 SharePoint site. As you can see here, it works as desired.

image

Here is how you can put this to practical use. Let’s say we want to use a green smiley (Wingdings – J) and a red frown (Wingdings – L) based on our value. Greater than or equal to .5 or 50% will be smiling and less than .5 will be frowning. Assuming that the value we are evaluating is in K5, we would use the following formula to set the value:

=IF(K5>=0.5, “J”, “L”)

This sets the text value that we want to use. Because we are using a character value in the field, we can use conditional formatting to set the appropriate color by using the Highlight Cells Rules – Text That Contains… option. You will create two rules, one for J and one for L. You can use a default setting or create a custom format to change the color.

image image

By using Wingdings and Webdings, you will be able to further enhance your dashboards with a variety of symbols. I hope you have fun with your dashboards and get to tell your users or designers that, yes, you do use Wingdings! Enjoy winging it!

Note: The target environment needs to support the Wingding fonts. We have seen this not work when using iPads for instance. Be sure to consider and test your target environments for this solution.