Microsoft Excel: #Data — How to Stand Out As a Business Professional
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
Data Table and “Shaping” Process Walk-through
Lookup Reference Tables Build Process
Analysis Development Build Process
Start Report & Chart Building
Introduction to Automation with Macros
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!
References:
(1) Analytics Trends 2015: A Below-the Surface Look — Deloitte
(2) Data Driven: What Students Need to Succeed In A Rapidly Changing Business World (PwC)
About Don
“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.
“What Do You Do?”
I frequently get this question. My response (it’s not what you think!)? Check it out here!