Excel 2013: Display hidden rows columns and data on an Excel Chart

Standard

If you hide rows, columns and data on excel, the chart that’s uses this data also hides it — while this is the default behavior, you can override this by following the steps below.

Let’s reproduce the behavior first.

I have a simple excel chart like shown below:

Excel Chart Hide Data 1Now, if I hide the data that is selected for this chart then the chart stops showing this as well:

Excel Chart Hide Data 2To fix this and if you want the cells (rows, columns and data) to be still hidden but still have the chart show up, then follow the steps:

  1. Select the chart
  2. Under Chart Tools > Design > Select Data Excel Chart Hide Data 3
  3. Click on Hidden and Empty Cells Excel Chart Hide Data 4
  4. Check the Show data in hidden rows and columns check-boxExcel Chart Hide Data 5
  5. Go back to excel and you should see the data on the chart now even though the data is hidden Excel Chart Hide Data 6

Hope that helps!

Cheat Sheet to Pick the right graph or chart for your data:

Standard

I have two resources that I use sometimes to pick the right graph or chart for data visualization.

#1: Chart Suggestions:

chart data

#2: Online Tool

(By Juice Labs)

chart pick choose online tool

Qlik sense: How to see Data Load Editor scripts for apps developed by your Team members?

Standard

(This post first appeared on the Qlik Community. here)

Problem:

So you just joined a Business Intelligence Team and one of the responsibilities include building apps for your business users. Eventually, you would have a need to see Data Load editor scripts for apps developed by other members in the team. So what permission do you need to be able to do that?

Credits: darkhorse

Qliksense Version: Enterprise Server 2.0

Source: can’t see a peer’s data load editor scripts

Solution:

This a two-step process.

1) Get “content admin” access (or “higher” level access)

2) Double check if you have access to see data load scripts for ALL apps

Step 1:

The short answer is that you need “Content Admin” permission from your Qlik sense admin…But with this access level, you will have access to other developer’s app via QMC. If you need to do this via HUB as well then you will have to change the content admin role.

Here’s how Serhan ( darkhorse ) explained how to get this done:

QMC–> Security Rules–>Content Admin–> Edit–> Context–> Both in Hub and QMC

Qlik sense management console

Step 2:

Now, once you get the “content” admin access, you might want to double two things:

1) You can get access to data load scripts on published apps — (I was able to do this but there still seems to some open questions around some folks not being able to see the data load scripts for published apps. If this is the case for you, you need to duplicate the app on your “my work” area and see the scripts)

2) You can duplicate apps on your “my Work” area and see scripts — this is also useful if you want to make changes to published apps that are out there.

Conclusion:

I hope this helps you resolve the permission issues and help you collaborate with your team members!

Data puking and how T-mobile alienated a potential customer:

Standard

I saw this ad on a highway earlier today and my reaction: why would I switch to a network that has just “96%” coverage.

T mobile ad — example of data puking

…instead of converting a potential buyer, this ad actually made me more nervous. You know why? Its a case of what I like to call “data puking” where you throw bunch of numbers/stats/data at someone hoping that they will take action based off of it. So what would have helped in this ad? It would have been great to see it compared against someone else. Something like: we have the largest coverage compared to xyz. My ATT connection is spotty in downtown areas so if it said something like we have 96% coverage compared to ATT’s 80% then I would have been much more likely to make the switch.

I wrote about this adding benchmark in your analysis here

Takeaway from this blog: don’t throw data points at your customers. Give them the context and guide them through the actions that you want them to take.

SQL Server reporting services: How to add a seconday axis on a chart?

Standard

Problem Statement:

Need a chart on a SQL Server Reporting Services chart with a secondary axes.

Need measure #2 on a secondary axes:

SQL Server Reporting Services Secondary AxesSolution:

1. In the Design Mode > Open the Chart Date Pane > For Measure #2, navigate to Series properties:

SQL Server Reporting Services Series Properties Chart Data2. From the Series Properties Dialog box, navigate to “Axes and Chart Area” and choose the option “Secondary” under vertical axes.

SSRS Secondary Axis Axes3. Click OK to go back to the design mode and preview the report to test it:

SSRS SQL SERVER SECONDARY AXIS LINE CHART BAR CHART

4. Make sure to rename the axis title of the secondary axis, format the number to make it consistent with the report layout.

Note:

SSRS Version needed: SQL Server 2008 and above.

Conclusion:

In this post, you saw how to add a secondary axis on a SSRS Chart.

SQL Server reporting services: How to customize the Legend Text on a chart?

Standard

Problem Statement:

How do you customize the Legend Text on a chart? by default, it’s going to show you the name that you have in the Data Set, but sometimes that’s not what you want to display so how do you change the name?

It’s really useful when you don’t want to change the name of field in the dataset or when you are using an expression as an item on the chart and you need to display a business user friendly name on the report:

SSRS Custom Legend TextSolution:

1. Go to Chart Data Properties:

2. To change the Legend Text of “Sales Amount CY” – Navigate to series properties of the “Sales Amount CY” series:

SSRS Chart Series Legend Properties

3. Go to “Legend” and enter the Text in the “Custom Legend Text“:

SSRS Custom Legend Text Series Properties legend custom4. You can customize text for all legends on your report by navigating to the series properties.

SSRS Custom Legend Text Change Name

Conclusion:

In this post, I walked you through how you can customize the legend name/text in SQL Server Reporting Services.

SQL Server Reporting Services: Why am I not seeing every axis label in a chart?

Standard

Problem:

SSRS chart didn’t show all axis labels. Here’s an example.

Note: it does NOT show all country names:

axis labels sql server reporting services

Solution:

So what do you do if you want to show all axis labels in the report and do not want to skip the axis labels? Here are the steps:

1. Go to the Chart Axis properties

2. Under Label, change the value of Label Interval from Auto to 1

ssrs chart aix label properties

3. Preview your report to see if you see ALL axis labels now:

axis label ssrs issue solved

Conclusion:

The above chart is NOT perfect. There are other things that can be done but the goal of the blog post has been achieved! We have changed the axis label property so that all axis labels now show up on charts.

 

How to add secondary axis in a chart in Excel 2010?

Standard

In this post, I’ll show you how you can add secondary axis in a chart in Excel 2010:

First up, Why do you need secondary axis?

Look at the Data and the chart that I just created:

creating a chart from Data excel 2010

Notice something? Column A is not “visible” – Is it? So Now let’s see the effect how adding a Secondary Axes:

secondary axis in a chart in excel 2010

Looks better? Here are the steps:

1. Select the chart

2. can you see charts tool options in the menu bar? Yes? Great!

3. Switch to Format Tab

4. select the column that you want to put on a secondary axes:

select column in format area data excel chart

5. After selecting the column, click on Format Selection

6. In the dialog box, select secondary axes:

secondary axis excel 2010 chartclick on close.

7. Can you see the secondary axis now? Great!

secondary axis in a chart in excel 2010

8. Let’s do one more thing!

Let’s change the chart type of the secondary axis. Here are the steps:

a. Make sure the column is selected from the format Tab

b. Switch to the Design Tab under the charts Tool

c. Click on change chart type and select the chart of your choice. I select a column chart and this is how it looks:

more than one chart type on a single chart in excel 2010

Conclusion:

In this post, we saw how to add a secondary axis. We also saw how to change the chart type of the secondary axis.

What Chart should I use for effective graphical representation of data?

Standard

Data Visualization is an art. No doubt about it –  I admire professional artists that can create “beautiful” data visualizations. And Data visualization involves more than one technique – one being representing data using charts. And if you have experience in this domain then you know that there are Many charts out there.

Resource: You can browse Google Charts Gallery here to see various options you have.

Now, how do you choose between these options when confronted with the challenge of creating effective charts? wouldn’t it be great if we knew of a resource that could help us get started?

well, I found this resource which I think can help you get started on which charts you can use:

chart chooser data visualizationSource: http://www.extremepresentation.com/design/charts/

Online Tool: http://labs.juiceanalytics.com/chartchooser.html