## Turn CSV data into insight like a pro with Excel 2016

### Without a single input in formula bar.  UI only.  No kidding!

What’s even more unbelievable?  The above can be done in just a couple of minutes.

The technique discussed in this blog post is applicable to Excel 2016 or later.  And the techniques used are mainly Flash Fill and Quick Analysis, which are available since Excel 2013.  But why I said it’s applicable to Excel 2016 or later? Because the chart types Histogram and Pareto were introduced in Excel 2016.

I am not saying that we cannot perform the above transformation using Excel 2013/2010.  It’s just a matter of time + higher skill level is required.

Let’s see how it can be done in Excel 2016!

### Before we dive into it, let’s examine the data first:

OMG… the data we are dealing with are residing in a single column:

2. Department
3. Year of Tenure
4. Salary

A closer look at the email, we see a partner of First Name and Last Name, which is denoted by capital letter.

To most regular users, data like this is a nightmare.

#### A) Separate the data into a table showing

1. First Name
2. Last Name
3. Department
4. Year of Tenure
5. Salary

### But you know what? With Excel 2016, we can do it in a couple of minutes without a single input in formula bar!

By the way, CSV is good. 🙂

Last see it in action:

### 1) Extract data using Flash Fill

Thanks to the clear pattern, Flash Fill performs the task with ease

### 2) Get averages using Quick Analysis

Did you know… The icon was always there… please check it out

### 3) Insert Histogram via Quick Analysis

Please observe the changes on the chart when I input the values for bins

### 4) Insert Pareto via Quick Analysis

Note: Pareto chart takes one series, thus the chart first created show the “Year of Tenure” on primary Y-axis.  The trick is to manually adjust the data series to “Salary”.

### 5) Final touch up using Table via Quick Analysis

DONE!  Excel 2016 makes the tasks never easier!

A video with narrative is now ready 🙂 Enjoy! under production.  Will share here once it is ready.  Stay tuned!

Cantonese VO:

An Excel nerd who just transition into a role related to data analytics at current company......😊 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Excel in Action and tagged , , , . Bookmark the permalink.

### 2 Responses to Turn CSV data into insight like a pro with Excel 2016

1. Larnon says:

Wow thanks

Like

• MF says:

Wow welcome 😄

Like

This site uses Akismet to reduce spam. Learn how your comment data is processed.