MS Excel — Accountants Should Understand These Basic Data Management Techniques!

“I don’t know” is no longer acceptable…

Don Tomoff
Sep 1, 2018 · 4 min read
Image for post
Image for post

Overview

I received an email last week from a CPA client of mine who asked me…

“I have a REALLY large text file I downloaded from the IRS website listing exempt organizations. Is there a way I can get this into Excel and use it?”

It’s 2018. Every accountant should ABSOLUTELY UNDERSTAND how to approach this simple data management challenge!


Dataset Review

There are 4 basic steps to consider (consider the “ETLR” process):

Extract and Transform

  • Download or connect to the dataset.
  • Import the data into Excel and “shape” it.

Load

  • Load the final data into Excel.

Report

  • Prepare analysis.

I looked at the dataset and quickly figured out that the text file download is >76 megabytes. This means is is LIKELY going to have more than 1,048,576 and I won’t be able to load it into an Excel worksheet.

No problem. And we will do this in LESS THAN 10 MINUTES!


Stepping Through The Process (less than 10 minutes!)

EXTRACT The Dataset

TIME — 1 minute, 30 seconds (01:30)

Image for post
Image for post
  • Download the ZIP file and extract the .txt file.
Image for post
Image for post

TRANSFORM the Dataset (using Excel’s Power Query Tool)

TIME — 5 minutes (05:00)

  • Open Microsoft Excel (I’m using Excel 2016) and connect to .txt file to import it.
Image for post
Image for post
Image for post
Image for post
  • Data layout screen — select “Transform”.
Image for post
Image for post
  • Power Query editor opens — start transformation process.
Image for post
Image for post
  • Completed “basic” transformation. Time to “Load” the data.
Image for post
Image for post

LOAD The Data (Into Excel’s “Data Model”)

TIME — less than 1 minute (01:00)

  • I will load this data into the Data Model — this loads your data into the “background” in Excel. I AM NOT restricted on number of records since I am not loading it into the Excel worksheet (i.e., I won’t lose any data).
Image for post
Image for post
  • 1,067,173 records are loaded into the Data Model. Had I loaded this into the Excel worksheet, over 20,000 records would be dropped.
Image for post
Image for post

REPORT On The Data (Develop Analysis)

TIME — 2 minutes (02:00)

As shown above, to start analysis, simply “Insert” a Pivot Table and Excel automatically expects that you will use the data contained in the Data Model and makes that selection for you.

Then just start getting the information you want!

  • In the pivot table below, I have indicated the COUNT of exempt entities in Ohio, by City.
Image for post
Image for post

If this is new to you, it may be confusing.

But in 2018, this is a very BASIC dataset and Excel data conversion process.

If you want to look at the Excel file, you can download it here.

Now, you now longer have to say “I don’t know…”


About Don

Image for post
Image for post

“It’s time for DIFFERENT”

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

“What Do You Do?”

Connect with Don!

LinkedIn, Flipboard, Twitter, Snapchat

Let’s Excel

Up your Excel game — Tips, tricks and efforts that have…

Don Tomoff

Written by

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

Let’s Excel

Up your Excel game — Tips, tricks and efforts that have exponentially increased my productivity!

Don Tomoff

Written by

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

Let’s Excel

Up your Excel game — Tips, tricks and efforts that have exponentially increased my productivity!

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch

Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore

Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade

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