Microsoft Excel — The BASICS of Data Transformation

Key Concepts Essential to Shaping Data For Analysis

Image for post
Image for post
Apple Mobile Product Sales History Charts
Image for post
Image for post
Presentation to Penn State Weather Risk Management Group
  • VLOOKUP function
  • Pivot Tables (future post…) — Part 2

Data Transformation / Shaping Process

Excel Data Tables

Here is a excellent summary from TechRepublic of key reasons you want to use Excel Tables. Key considerations mentioned include:

  • Always visible headers
  • Formula autofill (my favorite!)
  • Dynamic range

Step 1-Convert raw data input into an Excel Table

From this —

Image for post
Image for post
Raw data compilation…
Image for post
Image for post
Raw data converted to an Excel Table

Step 2 — Create additional fields in the data table

This will enable the analysis we are potentially interested in getting. In this session, I have anticipated a need for three additional fields.

Image for post
Image for post
Data Table — 3 extra fields to be populated
  • (2) — Category Summary. This field will “condense” Apple’s classification (which changes over the years) to a more summary grouping. This will enable higher level insight which would not be available from the raw dataset. The VLOOKUP function, with EXACT match, is used.
  • (3) — Stratification of the Amount field. This may be useful to report aggregated data and develop charts regarding frequency of occurrence. The VLOOKUP function, with APPROXIMATE match, is used.

Step 3 — Populate the Calendar Year field

Image for post
Image for post
Enter YEAR function and reference Date field
Image for post
Image for post
Table “formula auto fill” automatically populates all rows

Step 4 — Populate the Category Summary field

We will use a VLOOKUP with EXACT match to do this. Here is the formula syntax for VLOOKUP.

Image for post
Image for post
VLOOKUP Function syntax
Image for post
Image for post
Table VLOOKUP to return a Summary Category field
  • Translation…Look at the Category2 field, go to the SummaryLookupTable2, if you find a match — return the value from column 2, and I want an EXACT match.
Image for post
Image for post
VLOOKUP populates the CategorySummary field

Step 5 — Populate the Stratification field

Getting this field populated is more complicated. Here’s why:

  • Nesting functions is used to resolve this. The formula performs the following:
Image for post
Image for post
Nested (IF, VLOOKUPs — Approximate Match) used to populate Stratification field

About Don

Image for post
Image for post

“On a mission to challenge the status quo to a more productive and effective end…”

Don is passionate about helping professionals and organizations keep up and adapt to the changing business world that we operate in.

Connect with Don!

LinkedIn, Flipboard, Twitter, Snapchat

It’s time for DIFFERENT— On a mission to challenge the status quo to a more productive and effective end… #digital #Excel #data #analytics

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store