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:
Now, if I hide the data that is selected for this chart then the chart stops showing this as well:
To 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:
Select the chart
Under Chart Tools > Design > Select Data
Click on Hidden and Empty Cells
Check the Show data in hidden rows and columns check-box
Go back to excel and you should see the data on the chart now even though the data is hidden
As a data professional, you would invariably end up spending a lot of time on data cleaning & transformation and a lot of times, you might be doing your work in Excel — if so, then check out Power Query if you haven’t already! It will save you a LOT of time and unlock Jedi powers that you didn’t know you had!
BUT…
if you are using a Mac — and there’s a lot of data scientist and data analyst who are on this platform then you are unfortunately out of luck! So for Mac users out there, I had shared this feedback which has 50 comments & 337 votes (as of 6/16/17) on the official Power BI ideas site; If you are one of the Mac users, then I encourage you to check it out and vote! Microsoft does take it seriously and their roadmap is heavily influenced by ideas site.
What-if Analysis is a pretty common analysis done by decision makers. Often, they would just create simple excel tables and adjust their variables manually until they get an answer that works. But instead of doing it manually there are features available in excel that will make your life much easier and analysis much more accurate. So, the goal of this blog post is to introduce you to the Goal Seek and Solver feature to help you do what-if analysis in Excel.
#1. Goal Seek:
Let’s say you are a CEO of an e-commerce startup and wondering about what factors you need to focus on to increase revenue. Here’s what the data (*assume per month) looks like when you start out:
And you want to increase the Revenue to $150K from $125K. The three levers you can pull are website visitors, conversion and revenue per customer.
Now you could manually tweak the values for this variables till you get to $150K but as I promised earlier, there’s a better way!
Let’s start with Goal Seek.
You need to set two variables for Goal Seek.
a. Your goal — which in this case is 150K
b. The variable that needs to be changed to achieve that goal — note that you can specify just one variable to do so. So you need to choose out of the three above what you would like to focus on. Let’s say you want to focus on conversion rate.
So once you have these two things — from the Data Tab in Excel, Go To What-if Analysis, Goal Seek:
Now, specify the values. For this example, we want to figure out what should be the new conversion rate so that our revenue will be $150K. So here’s an example of how that would look on Goal-seek:
After entering the values, you will see the status — you can click “OK” to keep the solution and cancel to go back to what you had:
Perfect! So you need to increase the conversion rate from 1.25% to 1.5% to get to the goal that you had set!
#2: Solver add-in
So, you worked on improving the conversion rate for next month or two and you & your team found out that it’s getting really hard to increase it above 1.35% — And also you found that with the less effort you can move the needle on other variables (website visitors & revenue per customer). Now Goal Seek allows you just set one variable so if you more variables than it doesn’t serve the purpose that well! That is where Solver add-in helps.
Think of Solver as advanced Goal seek where you can set more than one cell that can change. You can also set constraints on what the values could be for all the variables that can change.
Now, for our scenario, the conversion rate is at 1.35% but you want to see the possible changes that you can make for website visitors and revenue per customer to reach $150K.
You also know that you can’t above 1,100,000 Website visitors per month and also need to have less than $11 as revenue per customer.
You will need to enable the Solver add-in in Excel and once you do that you will see that in the Data Tab.
Once you have it, open it and fill up the information needed in the dialog box:
a,. Set objective to Total Revenue with value of 150000
b. By changing cells to: Website Visitors and Revenue per Customer
c. Constraints. Website Visitors <= 1,100,000 and Revenue Per Customer < $11
After that click on Solve.
if it found a solution, it would show you that on Excel and also give you additional options to whether you want to keep the solver solution or restore it to original values:
For our scenario, it suggesting that with website visitors to 1,010,101 and revenue per customer to $11, we should hit our goal.
Click on OK when you’re done.
Conclusion:
In this post, we saw how you can use Goal Seek and Solver add-in using an e-commerce scenario but you these techniques can be applied to wide variety of data analysis problems that can be solved using “what-if” techniques.
Hope this was helpful and I would love to hear from you about how will you use this in your work? Or if you use it already then what do you use it for?
Are you trying to import an Excel file into SQL Server using SQL Server Integration services…And ran into error that has words like “Non unicode” and “unicode”? Then this blog is for you.
Why does this error occur?
Well it turns out that things like SQL Server and Excel have encoding standards that they follow which provides them a way to process, exchange & store data. BUT turns out that SQL Server and Excel use different standards.
Solution:
So, the solution is simple right? Import the data from Excel into non-Unicode format because that’s what you need for SQL Server.
So how do you that? Between your Source and Destination tasks, include a task called “Data conversion” and do the following for all columns that have text:
And in the destination task, you’ll have to make sure that the mapping section using the new output aliases that you defined in the “data conversion” step.
Conclusion:
In this post, we learned about how to solve a common error that pops up when you try to import excel file to sql server using SSIS. Hope that helps.
I like using spark lines data viz when it makes sense! It’s a great way to visualize trends in the data without taking too much space. Now, I knew how to add sparklines in Excel but recently, I wanted to use that on Google sheet and I had to figure it out so here are my notes:
1. Google has an inbuilt function called “SPARKLINE” to do this.
2. Sample usage: =SPARKLINE(B2:G2) — by default you can put line chart in your cells.
4. One of the best practices that I advocate when you spark-line to “compare” trends is to make sure that you have the consistent axis definition. So the sample usage for that could like this:
Spark-line is a very handy data visualization technique! It’s great when you are space constrained to show trends among multiple data points.
Here’s an example:
But there’s an issue with above chart! Axis values for these group of spark-lines do not seem match – it could throw someone off if they didn’t pay close attention. So a good practice – when you know users are going to compare segments based on the spark-lines – is to assign them same axis values so it’s easier to compare. Here’s the modified version:
And…here are the steps:
1. Make sure that spark-lines are grouped.
Select the spark-lines > go to toolbar > Sparkline Tools > Design > Group
2. On the “group” section, you’ll also find the “Axis” option – select that and make sure that “same for all axis” is selected for Vertical axis minimum and maximum values:
That’s about it. Just a quick formatting option that makes your spark-lines much more effective!
Power Query is amazing! It takes the data analysis capabilities of Excel to whole new level! In this post, I am going to share three reasons:
1. it enables repeatable mash-up of data!
Have you every had to do your data analysis tasks repeatedly on the data with same structure? Do you get “new” data every other week and need to go through the same data transformation workflow to get to the data that you need?
What’s the solution? Well, you can look at MACRO’s! Or you can request your IT department to create a Business Intelligence platform. However, what if you need to modify your data mashup workflow then these solutions don’t look great, do they now?
Don’t worry! Power Query is here!
It enables repeatable mashup of data like you might have never seen before! You need to try it to believe.
It’s very easy to input new data to Power Query and it enables you to retrieve final output based on new data using a “refresh” feature.
Each data-mashup is recorded as steps which you can go back and edit if you need to.
2. It’s super-flexible!
Any data mashup performed using Power Query is expressed using its formula language called “M”. You can edit the code if you need to and as you can imagine such a platform enables much-needed flexibility for the analyst’s.
3. It has awesome advance features!
Do you want to Merge data? How about Join? Are you tired with VLOOKUP’s! Don’t worry! it’s super easy with Power Query! Here’s a post: Join Excel Tables in Power Query
How about searching for online & open data sets? Done!
How about connecting to data sources that “Data” section of Excel doesn’t support yet? (Example: Facebook) – DONE! Power Query makes that happen for you.
And That’s not a complete list!
Plus you can unlock the “Power” (pun intended) of Power Query by using it with other tools in Power BI Stack. (Power Pivot, Power View, etc…) OR you can use the your final output from Power Query with other tools too! After all it’s an excel file.
Action-Item!
If you haven’t already then check out Power Query! it’s free and works with Excel 2010 and above.
Thanks everyone who attended, I hope it was helpful!
Here are some ways to follow the Virtual Chapter: Website: http://bavc.sqlpass.org/ Youtube: https://www.youtube.com/channel/UCOiRAA4gBxEeVxwmEZ1qy1w Twitter: https://twitter.com/passbavc LinkedIn: https://www.linkedin.com/groups/PASS-Business-Analytics-Virtual-Chapter-6701113
This post is a quick summary for all Business Analytics related updates that I saw at PASS Summit’14:
1. Theme of the Keynote(s)/Session(s) seemed to be around educating the community about the benefits of the NEW(er) tools. I saw demos/material for cloud-based tools like SQL databases, Azure stream analytics, Azure DocumentDB, AzureHDInsight & Azure Machine learning. The core message was pretty clear: A data professional does two things – 1) Guards data OR 2) helps to generate Insights from Data – And they will need to keep up-to-date on the new tools to future-proof their career.
4. Azure Machine Learning adds a free-tier! You won’t need a credit-card/subscription to sign up for this.
5. I also saw sessions proposing new way of thinking about an architecture for “Self Service BI” and “Big Data” which might be worth following because since these are newer tools, it’s definitely worth considering an architecture that’s designed to make the most of the investments in these new tools. That’s it & I’ll leave you with a quote from James Phillips from Day 1’s keynote:
Data on its own is just a bucket of potential. People convert data to opportunities – James #sqlpass#summit14
Now, if I hide the data that is selected for this chart then the chart stops showing this as well:
To 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:
