Three Power Pivot Installation FAQ’s:

Standard

Q1: How Can I upgrade Power Pivot on my machine?

A1: Uninstall Existing version (Yes, it’s not intuitive, but you have to uninstall existing version). Download the version that you want to upgrade to. Install it.

Q2: I’ve SQL 2008 R2 on my machine. Can I install “Microsoft® SQL Server® 2012 SP1 PowerPivot for Microsoft Excel® 2010”?

A2: Yes, go ahead! “SQL server 2012 sp1” in the name is confusing 🙂 It doesn’t matter which version of SQL server you’ve on your machine – In fact, Power Pivot does not need a SQL server to be installed on your machine.

Q3: How do I check Power Pivot version?

A3: Excel > Power Pivot > Settings > You’ll see version # on the top part of the screen.

Power Pivot Settings Excel

I hope this helps!

Excel: Swapping (reversing) the Axis of a Table Data

Standard

Data preparation (or call it pre-processing) is an essential and time-consuming part of any data analytic’s project. To that end, I was working on a data set needed some changes before I could plot it on an effective data visualization. Here’s what I did:

My Challenge:

I was working on a data set that looked like this:

DateAbu Dhabi, United Arab EmiratesAdalaj, Gujarat, IndiaAddison, TX
1/1/2013142
1/2/2013142
1/3/2013143
1/4/2013333
1/5/2013224
1/6/2013234
1/7/2013233
1/8/2013224
1/9/2013223

BUT: I wanted my data to look like

Date1/1/20131/2/20131/3/20131/4/20131/5/20131/6/20131/7/20131/8/20131/9/2013
Abu Dhabi, United Arab Emirates111322222
Adalaj, Gujarat, India444323322
Addison, TX223344343

What did my real data looked liked?

it has 380 columns and 500+ Rows and so MANUAL copy pasting was NOT an option!

Excel 2010 Solution:

It’s so simple!

Step 1: Select the data > COPY (Shortcut: ctrl + c)

Step 2: Switch to a new/different excel sheet

step 3: Paste Special > Transpose (T)

excel paste special transpose swap axis data

So After doing this, This is how the Input & output looks:

excel paste special reverse axis

Conclusion:

In this post, We saw how to swap or reverse the axis of a table data in Excel 2010.

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

Standard

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:

excel data numbers

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 

link and merge formatting

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

link and keep source formatting

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:

linked word file to excel data

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?

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.

Playing w/ the Occupational Employement Statistics Data-Set:

Standard

I found some data-sets on Occupational Employment Statistics on Bureau of Labor Statistics site and I played with it to see if I can find something interesting:

Few things about the data & visualization that I am going to share

  • US only
  • I downloaded the national level data But there’s also state level data available if you’re interested to drill down.
  • The reports that you see where created after I got a chance to “clean” the data-set a bit and created a data model that suited basic reporting on top of it.
  • For this blog post, I am going to play w/ May 2010 & 2011 data
  • With the help of original data-set, you can drill down to get statistics about a particular Job Category if you want. For this blog-post, I am going to share visualizations that correspond to Job categories.
  • click on images to see the higher resolution image.

With that, Here are some visualizations:

1) Job Category VS mean hourly salary:

1 Job category vs hourly salary mean bureau of labour statistics

2) Job Category VS number of employees:

2 Job category vs number of employees bureau of labour statistics

3) Scatter Plot:

X Axis: Number of employees

Y – Axis: Wage (Mean Hourly Salary May 2011)

Size of Bubble: Wage (Mean Hourly Salary May 2011)

*Note: This may not be the best approach to create the Scatter Plot as I have used the same value (Mean Hourly Salary May 2011) twice – But since I was just playing w/ it, I went with what I had in the model.

Here’s the visualization:

3 scatter plot number of employees vs mean hourly wage may 2011 employment statistics

Some of the things I observed:

1) I belong to an Industry (Computer and Mathematical occupations) which has relatively higher mean hourly wage.

2) There are few people working in “farming, fishing & forestry occupations” that do not get paid much.

3) There are lots of people working in “office administrative support occupations” that do not get paid much.

4) Management Occupations, Legal Occupations and computer & mathematical occupations have relatively higher mean hourly wages.

Conclusion:

In this post, I played w/ Occupational Employment statistics data-sets and shared some visualizations.

How to clean similar textual data in Excel via Fuzzy lookup add-in?

Standard

In this post, we would see how to get started with Fuzzy look-up add-in for excel.

First up, Which problem does Fuzzy Lookup add-in for excel solve? It cleans similar (a.k.a matching) textual data in Excel. E.g. “Mr Paras Doshi”, “Doshi Paras”, “Paras A Doshi” are similar and may refer to the same person – Fuzzy look-up helps you detect such similar textual data. This add-in is really handy if you are “combining” data from different systems where the data is not in the same format – using this add-in you can detect similar looking text and clean the data-set at hand. With that, Here are the steps to download, install and play with this add-in:

1) Download “Fuzzy Lookup add-in for Excel. Read: Over view, system requirements and Instructions

URL: http://www.microsoft.com/en-us/download/details.aspx?id=15011 

(If this doesn’t work – search for “Fuzzy lookup add in for excel”

2) Note the sample excel file called “Portfolio” that comes with the download files. We’ll open it after the Successful installation of the add-in:

1 excel 2010 sample excel file to play with fuzzy lookup

3)Install it.

I left the default on all dialog boxes.

4) Open the sample file: “portfolio

5) Click on Install if you see a dialog box “Microsoft Office customization folder”

(please read the message in the dialog box too)

6) Can you see Fuzzy Lookup in Excel Toolbar? Yes? Great!

7) Now if you have opened the Portfolio file then you’ll see a tutorial on how to get started on the worksheet named portfolio:

3 excel 2010 tutorial to play with fuzzy lookup

8) follow the above tutorial to get started. I just did!

2 excel 2010 tutorial to play with fuzzy lookup output

Conclusion:

In this post, we saw how Fuzzy Lookup add-in for excel 2010 help you find matching text from two data sources. I hope that helps.

 

Access Fundamentals: What are Tables, Queries, Reports & Forms?

Standard

Microsoft_Access_2010_icon A short Blog-Post explaining what each “term” means in Access. If you are interested, introduction” to Access is here (via Wikipedia).

Here are the details:

1. Tables:

The place where data is stored. Access is a “data store” which allows us to store data in Rows & Columns format. Here are couple of things that you should know about Tables:

1a. Tables can be related: If you come from the database world – you know that tables can also have relationships among them. If not, just think of relationships as a way to link similar data items. For example, Product Table having Product-ID column can be related to Product-Category table having Product-ID column. Benefit? The Product-Category column can have details about the particular category and since it is linked to the Product Table you do not have to enter the details about the category in the product table again & again. Saves time (and storage space) by eliminating redundancy.

1b. Columns in the Tables have Data-Types: You can specify the data-type of a column/field. So you can say that Column A will contain text data, Column B will contain Numeric Data.

1c. Each row added in a table is called a record

2. Forms:

Forms are used to “input” data into Tables. Think of forms as “cards” that allow you to enter data into tables one field at a time. Now, if you are new to Access, you know that you can enter data while creating/designing tables without creating forms – then why do you need forms? Let me give you an example: In an organization, Person A designs Access Tables and Person B who is not access-savvy uses it to enter data. Now, it makes sense to abstract/hide the “technology details” from the person who is not access-savvy and in that case, creating forms helps person B in entering data without worrying about the underlying table-structure.

3. Queries:

Queries gets it data from “Tables”. Why do you need queries?

3a. Queries help you “find” data from your tables. You can specify criteria like fetch data for month of January 2011.

3b. Combine data from more than one table

3c. Edit/change data. (adding a criteria is optional)

3d. You can delete data. (adding a criteria is optional)

4. Reports:

Once you have your queries/Tables that need to be “outputted” (or say printed) – you can create reports. Access has a nice Report Wizard that would walk you through steps that are needed to create a report.

That’s about it for this Post on a Database Management System! How do you use Access in your Organization or personally? Speak up in the Comments section!