Turn CSV data into insight like a pro with Excel 2016

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

Get insigth with FlashFill and QuickAnalysis

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:

Get insigth with FlashFill and QuickAnalysis 1

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

  1. The email address
  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.

Get insigth with FlashFill and QuickAnalysis 2

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

Think about the tasks we want to perform:

A) Separate the data into a table showing

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

B) Calculate the averages

C) Show distribution of Year of Tenure

D) Aggregate Salary by Department, and identify which department takes up the most salary cost

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

 

Excel Tips - Turn CSV into insight with Excel 2016

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

2) Get averages using Quick Analysis

Excel Tips - Turn CSV into insight 2

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

3) Insert Histogram via Quick Analysis

Excel Tips - Turn CSV into insight 3

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

 

4) Insert Pareto via Quick Analysis

Excel Tips - Turn CSV into insight 4

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

Excel Tips - Turn CSV into insight 5

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!

You may download a to Sample File to follow along.  Hope you like this post. 🙂

About MF

An Excel nerd who doesn’t work on Excel most of the time at current job...... 🤔 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

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

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