How conditionally formatting your data in Excel can help you save time in answering business questions?

Posted on May 6, 2013 by Paras Doshi • Posted in All about Data!, Data Analysis, Data Visualization • Tagged Data Warehousing, Databases, DataSet, Excel, Excel 2010, Microsoft Excel, Spreadsheet, Visual analytics • 1 Comment

Visual analytics is amazing – it helps “data enthusiasts” save time in answering questions using Data. Let’s see one such example. For the purpose of the blog post, I am going to show how to do it in Excel 2010:

Problem:

Here’s the Business Question: What was sales of Tea in North Region in 2012 Q1

Here’s the data:

SALES DATA(2012 Q1)	East	West	Central	North	South
Coffee	$ 7,348.00	$ 7,238.00	$ 1,543.00	$ 9,837.00	$ 1,823.00
Tea	$ 9,572.00	$ 8,235.00	$ 3,057.00	$ 8,934.00	$ 13,814.00
Herbal Tea	$ 5,782.00	$ 8,941.00	$ 9,235.00	$ 392.00	$ 1,268.00
Espresso	$ 9,012.00	$ 2,590.00	$ 4,289.00	$ 7,848.00	$ 340.00

So it’s easy to give out answer using the data: $8934

But let me CHANGE the business question:

WHICH Products in WHAT regions are doing the best?

Now this questions is not as easy as the previous one? WHY? because you’ll have to manually go through each number in a linear fashion to answer the question. Now imagine a bigger data-set. It’ll take even more time.

Solution

What can Excel Power users and Data Enthusiasts do to answer the new business question in an efficient way? Well, let’s see what conditional formatting can do it:

Now with the Data Bars, it’s easier to just glance at the report and see best performing products and regions. For instance, it’s very easy to spot that Tea is performing best in South among all products and region.

So how do you create data bars?

1. Select the data

2. Home > Conditional Formatting > Data Bars

3.Done! you’ll see this:

4. You can play with other options here to see what suits the best for your needs. But I just wanted to point out that there is a way for you to highlight the data in a way that helps you save time in answering business questions using data

Conclusion:

Visual analytics is a great way to quickly analyze data. In most cases, Human brain is much faster at interpreting the visual results as oppose to text/numbers – so why not use it to your advantage. And tools like Excel have inbuilt functionality to help you do that!

Remove Duplicates in Excel Tables using Data Explorer Add-in: (parasdoshi.com)

Unpivoting data using the data explorer preview for Excel 2010/2013:

Posted on April 25, 2013 by Paras Doshi • Posted in Business Intelligence, Data Analysis, Microsoft Office • Tagged Business intelligence, Column, Data Explorer, DataSet, Excel, Excel 2010 & 2013, Excel 2010/2013, Microsoft, Spreadsheet, Tables • 2 Comments

Introduction:

Data Explorer add-in is amazing! It’s helps you: combine, find and re-shape your data in Excel 2010/2013. I’ve blogged about: 1) How to merge Table Data and 2) How to clean duplicate data and now in this blog post, I want to share a step-by-step on Unpivoting data using the Data Explorer add-in.

Before we begin, If you haven’t downloaded and installed the data explorer add-in for Excel 2010 & 2013, you can find Information about it here: http://office.microsoft.com/en-us/excel/download-data-explorer-for-excel-FX104018616.aspx

Problem:

What is un-pivoting? I hear you ask. Instead of explaining it, let me share an Image:

BTW, the above data is from my Facebook Page Insights.

So our problem statement is (please refer to above Image): we are given table blue and we need to output table green. In other words, we need to Unpivot the data.

Solution:

Here are the steps:

1) Open Excel, Open Data Explorer add-in. And Connect to your data. Wait when you see the Query Editor.

2) (Optional) In the Query Editor, Rename the query. I renamed it to “Unpivot Data”. And this how my query editor looks:

3) Now, Select the columns that need to be unpivoted > Right Click > Unpivot Column

Note that I’ve selected all columns that I want to UnPivot:

4) You’ll see the updated results in the query editor window. I renamed the columns “Attribute” to “Age and Gender” and “value” to “reach”. If you want to rename the columns, select the column > Right click > rename.

If everything looks OK, click on Done in the bottom right corner

5) There you have it, Unpivoted data in Excel 2010/2013 using Data Explorer add-in!

And then its super easy to create charts, Here’s one I created after I had unpivoted the data:

Insight: For my blog, my Target Audience seems to Male between the age of 18-24 and then 25-34.

FYI: The Date Range of the Data Set of 1st Jan 2013 – 25th Apr 2013.

That’s about it for this post, Here are some Related articles:

Remove Duplicates in Excel Tables using Data Explorer Add-in: (parasdoshi.com)
Merging/Joining datasets in Excel using Data Explorer add-in (parasdoshi.com)
PASS Business Analytics Conference Keynote Day #1 (parasdoshi.com)

Your comments are very welcome!

Merging/Joining datasets in Excel using Data Explorer add-in

Posted on April 24, 2013 by Paras Doshi • Posted in Business Intelligence, Data Analysis, Microsoft Office • Tagged Data Explorer, DataSet, Excel, Excel 2010 & 2013, Microsoft, Microsoft Excel, Spreadsheet, Tables • 2 Comments

Problem:

Merging/Joining/Combining data-sets in Excel has not been an easy task. There are third-party add-ins that makes it easy but out of the box, excel didn’t have an easy way to merge/join table data. But now with the Data Explorer add-in, we have an add-in that let’s us merge/join data in excel w/ few clicks.

If you haven’t downloaded and installed the data explorer add-in for Excel 2010 & 2013, you can find Information about it here: http://office.microsoft.com/en-us/excel/download-data-explorer-for-excel-FX104018616.aspx

Situation:

Input is Table 1 & Table 2. The output we need is merged Table.

Table 1:

Date	Daily New number	Month
1/1/2012	0	1
1/2/2012	0	1
1/3/2012	0	1
1/4/2012	0	1
1/5/2012	0	1
1/6/2012	0	1
1/7/2012	0	1
1/8/2012	0	1
1/9/2012	0	1

………………………………..

Table 2:

Month	Month Name
1	January
2	February
3	March
4	April
5	May
6	June
7	July

Merged Table:

Date	Daily New number	Month	Month Name
1/1/2012	0	1	January
1/2/2012	0	1	January
1/3/2012	0	1	January
1/4/2012	0	1	January
1/5/2012	0	1	January
1/6/2012	0	1	January
1/7/2012	0	1	January
1/8/2012	0	1	January
1/9/2012	0	1	January

………………………………

Solution:

Let’s see how data explorer can help us Join/Merge Table 1 & Table 2.

1) create query that connects to Table 1 & Table 2.

2) Once you have queries that connect to the tables need to be merged, then click on Merge

3) Once you click on Merge, you’ll see a dialog:

Here you need to configure three things:

a) First Table

b) Second Table

c) Columns that will be used to merge/join data

In this case, this is how my merge dialog looks:

4) Once configured correctly, click on OK. You’ll see a dialog box where you can configure the output of the merged table. click on the new column to see the options that are available to you to configure the output of the merged table:

5) In this case, I’ve selected just one column month name that needs to be merged. You can also explore the aggregate tab in case you’ve numbers that needs merging.

6) This is how the output looks:

7) Rename the new column.

Select the new column > Right Click > Rename

8) Click Done if it looks OK.

9) The merged data is now available to you in Excel!

And one can analyze it!

Let’s see before and after. Note that instead of month numbers, we now have month names

In this post, we saw how to merge/join/combine data from two different sources in Excel 2010.

Remove Duplicates in Excel Tables using Data Explorer Add-in: (parasdoshi.com)

Remove Duplicates in Excel Tables using Data Explorer Add-in:

Posted on April 23, 2013 by Paras Doshi • Posted in Business Intelligence, Microsoft Office • Tagged Column, Data Explorer, Data set, DataSet, Excel, Microsoft, Microsoft Excel, Spreadsheet, Spreadsheets, Tables • 3 Comments

In this blog post, we’ll see how you can remove duplicated and clean data in excel tables using Data Explorer Add-in.

Problem:

Our Excel Table has following Data:

Month	Month Name
1	January
1	January
1	January
2	February
2	February
3	March

And we want to remove duplicates to make the data-set look like this:

Month	Month Name
1	January
2	February
3	March

In real world data-sets, we wouldn’t have few rows but lot’s of rows and doing it manually wouldn’t be the wisest option. With that in mind, let’s look for a few-clicks solution that can help us remove duplicates.

Solution:

If you haven’t already, download the Data Explorer add-in preview available for Excel 2010 & 2013. It can do a lot more than removing duplicates – it’s a great add-in and it’ll save you lots of time especially if your job involves discovering, cleaning and combining data for analysis purposes. After you’re done installing the add-in, use the steps below to remove duplicates in an excel column:

1. Open Data in Excel. Switch to Data Explorer Tab

2. For the purpose of the demo, I am assuming that you already have the data in excel file. If not, you can connect to other sources via the add-in.

3. Data Explorer add-in > Excel Data> From Table

4. After you’ve clicked on the From Table, a query editor will pop up:

5. Select both columns

(you can select both columns by: select first column > hold down the ctrl key and then click on second column)

6. Right click > Remove Duplicates

7. click on done if you see that the duplicates have been removed correctly

Conclusion:

In this blog post, we saw how to remove duplicates and clean data in Excel using the Data Explorer Preview add-in.

If you’ve not downloaded and installed the data explorer add-in for Excel 2010 & 2013, you can find Information about it here: http://office.microsoft.com/en-us/excel/download-data-explorer-for-excel-FX104018616.aspx

Note:

1) URL to download the add-in may change in future

2) The steps that I described may also change because as of today the ad-in is in “preview” stage and things may change in future.

PASS Business Analytics Conference Keynote Day #1 (parasdoshi.com)
Data Explorer for Excel: An experiment with Sachin Tendulkar (romitmehta.com)
Found something interesting by exploring a “List of companies by revenue” Data Set: (parasdoshi.com)

PASS Business Analytics Conference Keynote Day #1

Posted on April 11, 2013 by Paras Doshi • Posted in Business Intelligence, Microsoft Business Intelligence • Tagged Business analytics, Business intelligence, Data Explorer, Databases, Excel, Microsoft, Microsoft PowerPoint, Spreadsheets, Tools • 8 Comments

In this post, I’ll summarize the PASS Business Analytics Conference’s Keynote Day #1:

The structure of the Keynote:

One of the NEW challenges that Data Pros face today is complexity involved in building a BI solution. Following slides nicely represent the challenge from the Tools standpoint:

Image Courtesy: https://twitter.com/SQLGal/status/322342662013321216

Microsoft’s Goal is to SIMPLIFY the above situation

We need to do the same thing with BI that PowerPoint did to the slide projector. SIMPLIFY IT. #passbac

— Mark Vaillancourt (@markvsql) April 11, 2013

NEW Tools:

> Data Explorer (Excel add-in)

> Power View in Excel 2013

> Geo Flow

Key Take away from the demo’s was:

Power View is a great tool that you could use to extract insights from data.

E.g. Insights about Music Charts from Germany:

#MilliVanilli for Germany?JS Bach just rolled over #passbac

— Mark Tabladillo (@MarkTabNet) April 11, 2013

Now combine the power of Power View w/ the new capabilities like Data Explorer that let’s you find, combine & refine data via Data Explorer.

In the Demo, they combined data in hadoop w/ data in relational sources. This is Powerful!

And Also

The Preview for GeoFLow in Excel was announced!

They had a great demo on a pretty big touch device:

Sorry for the poor image – but imagine a touch device of that size w/ an interactive data visualization that has 3D geo maps!

Conclusion:

They had a nice message at the end of the keynote:

Nice Message: EVERYONE should be able to extract insights from data. It should not be business intelligence but BASIC intelligence #passbac

— Paras Doshi (@paras_doshi) April 11, 2013

An auto-refreshing Word Document that fetches data numbers from Excel:

Posted on March 5, 2013 by Paras Doshi • Posted in Microsoft Office 2010 • Tagged Data, Excel, Microsoft, Microsoft Excel, Microsoft Word, numbers, Spreadsheets, Word • Leave a comment

In this post, I’ll show you how you can create a word document that:

1) Gets numbers from Excel

2) auto refreshes when the data gets changed/updated in Excel

Note: I am using Excel 2010 & Word 2010 for the purpose of this blog.

Here are the steps:

1. Scenario:

Let’s say we have a line in word that says “The revenue for year _____ was $ ______”

and we want to fetch data numbers for Year and revenue from Excel.

2. Let’s go!

here’s the data in Excel:

3. In excel; copy A2 that has value 2012

4. switch to WORD:

now this is IMPORTANT. do NOT just paste it.

move your cursor to the destination position > right-click > paste options > Link and Merge formatting

There’s also an option of link and keep source formatting, this is how it looks:

5. repeat the steps for revenue

6. Close Word.

7. Open Excel and change the value of year to 2011 and revenue to 4000

8 Now open Word 2010, say yes if a dialog box pops up

And here you go:

Conclusion:

In this post, we saw how to create an auto-refreshing word document that fetches data numbers from Excel.

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

Posted on February 28, 2013 by Paras Doshi • Posted in Microsoft Office 2010 • Tagged Chart, Data, DataModel, Excel, Excel 2010, Microsoft, Microsoft Excel, Microsoft Office, Record chart, Spreadsheet • 1 Comment

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:

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

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:

5. After selecting the column, click on Format Selection

6. In the dialog box, select secondary axes:

click on close.

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

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:

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.

Adding a TrendLine to a Time Series Line Chart in Excel 2010: (parasdoshi.com)
Trying out FLASH FILL which is a new Excel 2013 Feature: (parasdoshi.com)
Need to combine two chart types? Create a combo chart and add a second axis (blogs.office.com)

Adding a TrendLine to a Time Series Line Chart in Excel 2010:

Posted on February 12, 2013 by Paras Doshi • Posted in Microsoft Business Intelligence, Microsoft Office • Tagged Excel, Excel 2010, Math, Microsoft Excel, Spreadsheets, Statistics, Time series, Trend line (technical analysis) • 1 Comment

I was playing w/ a time series data set in Excel 2010 and learned how to add a Trend-line and in this blog post, I’ll share how I added it:

First up, How is Trend-line useful? Here are few answers:
- It helps us see how data is changing over time, in other words, it helps us find “trends”
- It helps us forecast future.

With that, here is the chart without Trend-line:

Now let’s add the trend-line and you’ll be able to compare on your own how Trend-line makes it easier to spot “trends”. Here are the steps:

1. select the line > right-click > add trend line

2. configure the trend-line options

3. I also changed the line style

4. And Here’s the chart w/ trend-line

Conclusion:

In this post, we saw how to add trend-line in the time series chart in excel 2010

Need to combine two chart types? Create a combo chart and add a second axis (blogs.office.com)

Trying out FLASH FILL which is a new Excel 2013 Feature:

Posted on February 6, 2013 by Paras Doshi • Posted in Microsoft Business Intelligence, Microsoft Office • Tagged Cell, Excel, Excel 2013, Flash, Microsoft Excel, Microsoft Office, Spreadsheet • 1 Comment

I learned about an interesting new feature in Excel 2013 which they call “FLASH FILL”. Instead of trying to explain it, Let me demo it:

1. I’ve following data in Excel 2013:

2. Now the requirement is to split the FULL NAME into FIRST NAME and LAST NAME.

So I entered Kim in cell B2

Then I typed just Mi in the cell B3 and Excel 2013 predicts correctly for cell B3, B4 and B5 that I am filling up the first name from the Full Name column.

See:

I hit enter for the column First Name.