Tag Archives: Excel 2013

Excel Tip #25: Overlaying 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!

Making a cooler Excel chart by overlaying charts

This is one of the coolest techniques when creating compelling chart visualizations in Excel. The concept is to overlay one chart on another such as line chart over a bar chart which is our example. This is really helpful when showing data that has very different granularity or scale. In the example we will do below, we are going to show the number of tickets each hour of a day with the % spending on alcohol for that time period. Let’s get started.

Step 1 – Create the bar chart

We are creating a bar chart that shows the number of tickets created during each hour of the day at my fictional restaurant – Sensational Servings. In my bar chart, you can see that I added a title, removed the legend, and hid the field buttons. Here is the resulting chart.

image

Step 2 – Create the line chart

Next, we create a line chart that shows what % of the total ticket amount for that hour of the day was on alcoholic beverages. I will not add a title to this one and I will hide the buttons and the legend as well. I will also convert this to a line chart so we can see the trend through the day as a line.

image

Step 3 – Set up the line chart to be overlaid on the bar chart

In this step, we will walk through the settings that make line chart ready to overlay. In my scenario, you may have already noticed that the list of hours are different on the Y-axis. In order to overlay the charts, they must represent space that can be overlaid. In this example, we need to make sure that the Y-axis on both charts match. We expect that the X-axis will be different as we have a count and a percentage we want to compare. In most cases you need to have one of the axis represent the same values so you can compare the other. However, you may have a situation where this does not matter. As we move through the rest of the example, you can adjust to your data visualization needs accordingly.

In order to set the hours the same, we will show empty values for the Hour of the Day. (NOTE: you may need to do this with both charts to prevent unexpected results.) You can make this change in the PivotChart Options dialog on the Display tab.

image

Here is the result:

image

Here are the other changes we will make:

  • Make the background transparent
  • Remove the Y-axis label
  • Move the X-axis label to the right side
  • Remove the lines in the chart

Let’s do the labels first. To remove and move the respective labels, you can use the cross that is displayed on the upper left corner of the chart. image  The first change is unselecting the Primary Horizontal axis from the Axes menu. From here you click the Axis option and then go to More Options. That will open a side panel with more options from which you can make the necessary changes. On this panel, you need to open the Format Axis page. You will see an option for Labels near the bottom. To move the X-axis to the right side you need to select Low or High. This will depend on the data in your axis. The change is applied immediately so you should see the change. In the example we need to change it to High.

image

While leaving the format panel open, select the one of the gridlines. This will open the Format Major Gridlines menu. Select the No Line option and these will be removed. Once you have completed that, select the entire chart and set the fill to No Fill in the Format Chart Area and select No Line for the border in the same section.

As with many Microsoft products there are other ways such as ribbon commands to change fill and line options. You should explore other options to see which method is the most intuitive for you. However, the format panel has the most complete set of options which are grouped for the section of the chart you are modifying which is why I used that for most of the changes here.

Once all these formatting changes have been made you should see the Excel gridlines behind your line chart and it should look as follows:

image

Now the line chart is ready to be overlaid on our bar chart.

Step 4 – Overlaying the charts and finishing touches

The final step is both the simplest and most annoying. You will need to drag the line chart on to the bar chart. Making no changes here is what my initial attempt looks like.

image

You will notice that the axis labels from the line graph are outside the bar chart border, the line is not sized to be meaningful and the points on the line chart are not matching the Y-axis labels on the underlying chart.

Here are the steps I took to get this chart the way I wanted.

  1. Made the bar chart wider. I did this to get the space I wanted to include the labels in the chart.
  2. Made the plot area on the bar chart smaller. This opened up white space on the right side for the line chart labels
  3. Next, I made the line chart shorter. Keep in mind that without a chart title the chart expands to fill. By reducing the height of the line chart it will fit in with the title and the labels will fit into the plot height of the bar chart.
  4. This step is the most painful. I lined up the points with the bars and made sure that 0% was lined up with the bottom of the bar chart. This takes patience to get it looking right.
  5. Finally, I changed the color of the line to read so the contrast can be easily seen.

Here is the result.

image

Now we can see alcoholic beverages account for much more of the overall ticket amount as the day goes one. By adding slicers for thinks like day of the week we do further analysis such as seeing if Fridays are the biggest percentage and so on.

Wrap Up

This illustration shows one of the most common scenarios to overlay charts. Using a percentage to see a trend related to the underlying values shown in the bar chart helps understand correlation. If you use slicers on your dashboard, make sure that you connect them to both charts (if you want them both filtered by the slicer of course).

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

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