Excel Tip #23: Adding a Trendline to Your Chart

31 03 2015

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

24 03 2015

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 Tip #21: Hiding Scrollbars in Excel Services Web Part

17 03 2015

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!

The Issue – Scrollbars in Excel Services Web Part

In SharePoint and Office365, we have the ability to add Excel dashboards we have created to our BI site. This is easily done by editing your dashboard page and adding the Excel Services web part. Here is the example I use based on the MyVote application analytics. When deployed as an entire workbook, you will see the tabs at the bottom and the vertical and horizontal scrollbars.

image

The first place to look to solve this issue is in the web part properties.

image

In the first group of settings, we can hide the toolbar. In our view there is no recognizable or relevant changes. We had navigation only turned on, but have now changed it to None. We will skip the Navigation options and check out the next two sections – Appearance and Layout.

image

Here we can adjust the size in appearance. We made this change, but the scroll bars did not go away.

So what do we do now?

Using Named Ranges

After doing a lot of searching online, I came across this option. By using Named Ranges in Excel we can hide scrollbars. While other options were sometimes brought up, the named range option has worked as expected.

Creating a Named Range

Our first step is to create a named range. Highlight the cells you want to include in your web part. In my case, I am highlighting the A1 through M39 range. Once you have the area selected, choose the FORMULAS tab and click Define Name. You can also create ranges using the Name Manager. The name manager is most helpful when modifying or removing existing ranges.

image

In the New Name dialog, specify a name. In my case I use Dashboard if only one Excel range will be used. If you plan to create more ranges for implementation throughout SharePoint, the key is remembering what you named the range. In the web part property settings, there is no look up for the ranges. You will need to get the spelling correct in order for it to be used.

image

Making the Named Range Visible

In order to make named ranges available in Excel Services, you need to change your Browser View Options. You can find the Browser View Options on the FILE menu in Excel. When you open the FILE menu, you will see Browser View Options at the bottom. This option controls how Excel operates in Excel Services on SharePoint and Office 365.

image

In the dialog you will see two tabs – Show and Parameters. For this post we are only concerned with Show. I will expand on this fully in a later blog. In our scenario, we need to change from Entire Workbook to Items in the Workbook. Then we can select the named range we created in Excel. Once you have done this, the only part of the workbook that will be visible in a web part is the named range. You will get an error unless you specify the range.

image

Once you make the change, save the workbook back to Excel.

Updating the Web Part to Use the Named Range

The next step is to update the web part to use the named range. Go back to the page we are working in and edit the web part. (Be aware if you only have one named range exposed to Excel Services, it will update the site accordingly.) In the Named Item area add the name of your named range and it will be what is shown in this web part.

image

Resetting the Size to Eliminate Remaining Scrollbars

The final step is to go into the Appearance section and updating the Height and Width settings until the scrollbars are gone.

Limitations Using Named Ranges

While this does solve a very annoying visual issue and user experience is improved, we do lose some functionality. For example, if you happen to use links in your worksheets to link to other worksheets within the workbook, these no longer work. Also, if you want to use multiple sheets on your dashboard or portal, you will need to add additional web parts or pages to support other named regions. Overall, the user experience trumps these limitations and lead to a really nice dashboard.

Here is the cleaned up dashboard view:

image





Check Out Josh’s Blog on Creating Percentile in DAX

24 02 2015

I would like to introduce a “new” blogger to you. Joshuha Owen has restarted his blog and will be covering topics on business intelligence and data. I have worked with Josh for years at a Magenic and now Pragmatic Works. I look forward to seeing what he will be writing about in the future as well on Bits, Bytes, and Words.

Here is his most current post. Enjoy!

Replicating Excel Percentile in DAX

Currently, DAX has no native percentile function so if you want to replicate a version that matches what the Excel Percentile.INC (inclusive) function does you have to jump through a few hoops. This will involve having to create several measures to hold some intermediate values to apply a final formula. In theory you could do it all in one DAX expression but it would very difficult to read and test.

Continue reading…





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

10 02 2015

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.





Excel BI Tip #19: Using the TEXT Function

3 02 2015

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!

Formatting In Cells with the TEXT Function

This is a shorter tip. Sometimes the data you use in a cell needs to be formatted. This is particularly helpful when coupled with the CUBEVALUE function from the previous tip. The TEXT function allows you to format values to the pattern you want. Here are some of examples of using the TEXT function.

  • Number formatting – 1000 to 1,000
    • =TEXT(1000,”#,#”)
  • Number formatting with a decimal – 10.1 to 10.10
    • =TEXT(10.1,”#,#.00″)
  • Date formatting – 10/1/2014 to 2014-Oct
    • =TEXT(“10/01/2014″,”YYYY-MMM”)

As you can see, the standard Microsoft formatting can be applied to values. Here is what it would look like when used with the CUBEVALUE function.

=TEXT(CUBEVALUE(“ThisWorkbookDataModel”,$B$4,$B8,G$5,Slicer_Poll_Has_Submissions), “#,#.0#”)

This function will not work in the pivot tables themselves because cell formulas cannot be created in pivot tables. However, when you decompose a pivot table, you can use the CUBEVALUE and CUBEMEMBER functions as values to be formatted.

One caveat is that the TEXT function returns the value as a string or text data type in Excel. That means this function is best used to create headers and text with values in it. If you wanted to create a label plus a value in a field for a list of various metrics, you can concatenate the text with the resulting TEXT function.

=”Average Poll Count for Men: ” & TEXT(CUBEVALUE(“ThisWorkbookDataModel”,$B$4,$B8,G$5,Slicer_Poll_Has_Submissions), “#,#.0#”)

This would return a value such as “Average Poll Count for Men: 8.0” in the cell.

While a simple tip, it can add significantly to the look and feel of your Excel based dashboards.





Part 3 Adding Tab Controls – Create your own SQL tools with PowerShell and windows forms

30 01 2015

Steve:

James recently published part 3 of his PowerShell and Windows Forms series for creating your own SQL Tools. In this post, he guides you through the process of adding tab controls. Enjoy!

Originally posted on JamesDataTechQ:

Using Tab Controls can help you organize and define your work space. In this blog post, I am going to be adding Tab Controls with the following Tab Pages for CPU, Disk, Memory to my SQL Tool app. I am going to highlight how you can take my code and add your own Tab Pages to meet your needs. Drop me a comment and let me know how your SQL Tool app is coming along or if you want me to cover a topic. Check out Part One where I introduced the SQL Tool app and in Part Two were I went over how to add a new Check Box.

TabControl

Adding New Tab Pages

Step 1.

For example, you want to add a new Tab Page called Tempdb , under the heading called #Form Setup, you will need to add a new system Tab Page object.

$TempDBPage = New-Object…

View original 735 more words








Follow

Get every new post delivered to your Inbox.

Join 891 other followers

%d bloggers like this: