Microsoft Excel: #Data — How to Stand Out As a Business Professional

Published in

Let’s Excel

4 min readSep 26, 2016

Overview

“Put simply, analytics is becoming both the air that we breathe — and the ocean in which we swim,” the report says. (1)
“Today, we recognize skills in data analytics, information management, and programming languages as more valuable than ever, along with the leadership skills that enable students to effectively lead, communicate, and collaborate,” stated Mike Fenlon, US and Global Talent Leader at PwC (2)

Any successful business professional today needs to be equipped with the basic skills to manage, analyze and present data — turning data into information! Not necessarily the “how-to” skills, but a fundamental awareness of trends, as well as the technologies and processes that are becoming ubiquitous in business today.

In many businesses, this starts with Microsoft Excel! A solid understanding of the capabilities, key techniques and concepts to aggregate, manage and present data, is the foundation for building data analytics skills and capacity within our organizations.

On September 21, 2016, I had the pleasure of speaking at the IMA Heartland Conference in Springfield, MO. Both sessions were data and information reporting related for accountants and organizations. Although targeted to accounting professionals, the topics pertain to any business professional with data/information management responsibilities. The sessions were:

“MS Excel: How to Stand Out as an Accounting Professional”, and;
“MS Excel: Five Reports Your CFO Wants (and Probably Doesn’t Have)”

Presentation Content Review

This post recaps the presentation “MS Excel: How to Stand Out as an Accounting Professional”. The session included a presentation (download here) and an macro-enabled Excel workbook (download here) that started with a dataset (in this case, the sales for Apple, Inc for the years 2008–2016) and journeys through the creation of a two basic dynamic reports.

You can view the presentation here below:

To recap, the KEY skills highlighted — for data analysis purposes — consist of:

Data aggregation and shaping

Excel Data Tables — here’s a Twitter Moment with a few resources:

Creating LOOKUP tables
Lookup functions, including the use of EXACT and APPROXIMATE match criteria (VLOOKUPs)
Nesting functions to facilitate data shaping

Data analysis

Pivot Tables
Pivot Table Slicers

Reporting & Visualizations

Dynamic schedules (via the use of VLOOKUP & HLOOKUP functions)
Data Validation list use
Formula error capture / presentation considerations
Creating a “waterfall” or bridge chart

Workbook simplification tips

Use of an Index page, with “Return to Index” hyperlinks on other worksheets
Automation tips — using macros to simplify key tasks

Excel Workbook Content Review

A few visuals to provide a sense of what the user will see and learn through this workbook process are included below.

Index page and “Return to Index” links

Index page — linked to each worksheet. Each worksheet contains “Return to Index” button

Data Table and “Shaping” Process Walk-through

Raw data loaded-several analysis fields created

Lookup Reference Tables Build Process

Lookup tables — EXACT and APPROXIMATE Match Utilized

Analysis Development Build Process

Pivot tables for data analysis — Slicer demonstrated to enable filtering flexibility

Start Report & Chart Building

Dynamic waterfall (or “Bridge” chart) Visualization example build

Introduction to Automation with Macros

Introduction and Description of 3 basic macros used to automate work — Links provided to VBA code which can be copied and pasted for users own workbooks

A basic understanding of the topics discussed above AND how to manage Excel to manage data and develop reporting will definitely set you apart from your peers — and put you on a path to future analytics skill set growth!