Skipping Excel 1

Excel is used for many different tasks, such as budgeting, reporting, comparing bank statements, and other things. It can also be used to export, clean, and import data between two separate systems. For example, suppose you export transactions from an e-commerce system, open them in Excel, tidy up the information a little, and then transfer them to an accounting system such as QuickBooks. This kind of process is common, where we basically use Excel as a conduit to transfer information from one system to another. In this 3-part series, I’ll provide a detailed walkthrough of my journey for getting this process from 30 minutes per day, to 15 minutes, to 5 minutes, and ultimately to 0 … by skipping Excel. What … what?! Yes, by skipping Excel. Now, it is time to walk through this journey. Buckle up!

Video

Manual (30 minute approach)

Online orders are accepted by my business using an e-commerce platform. The orders need to be entered into QuickBooks Online, my accounting program. My initial strategy was manual, which took roughly 30 minutes per day depending on the number of orders.

I would open the e-commerce system in one browser window and QuickBooks Online in another. I would copy the name field from the e-commerce system and paste it into QuickBooks Online. I would then copy and paste the address field, and all of the other remaining fields. Then, I would save the transaction, and repeat for the next order. This would take about 30 minutes in total. This manual method was tedious, and so I wanted to make it faster. This led to my next strategy, batches.

Batches (15 minute approach)

From my e-commerce system, I would export the daily batch of sales transactions into Excel. I would then manually clean the data to get it into the correct format for QuickBooks. For example, I needed to remove unnecessary columns, set the column headers correctly, add a few calculated columns, and delete extra rows. Once the data was in the correct format, I was able to import the all of the transactions for the day into QuickBooks at once. This was a significant improvement! I cut the processing time in half, and now it only took 15 minutes per day, regardless of the number of transactions. Yay!

But then I discovered Power Query. Using it would allow me to complete the task even faster. Let’s check it out.

Power Query (5 minute approach)

The overall data flow was still the same. I would export the transactions from my e-commerce system, format them for import using Excel, and then upload them to QuickBooks Online. However, instead of manually cleaning and preparing the data in Excel, I had Power Query do it.

If you haven’t explored Power Query yet, it is pretty amazing and we’ve written a ton of free Power Query tutorials to get you started.

To create the query, start with:

  • Data > Get Data > From File > From Text/CSV
  • Browse to the exported transactions file
  • In the resulting dialog, click Transform Data

The data preview is displayed in the Power Query editor:

Then, I simply:

  • Removed the unnecessary columns (select column and Delete)
  • Removed extra rows (Home > Reduce Rows > Remove Rows)
  • Renamed the column labels (double click and rename) to match the QuickBooks requirements
  • Added a couple of calculated columns (Add Column > Conditional Column)

Once the data was ready for upload, I clicked Home > Close and Load. Power Query exported it to a worksheet. Then I simply selected that worksheet and did a File > Save As > CSV. This would save that worksheet as a csv file that could then be imported into QuickBooks.

Now, here is what is great about this. Once I had set up the query, I could simply click Refresh going forward. That is, I didn’t need to go through all of the steps of setting up the query again. Once it is set up, Power Query will perform the series of applied steps to the next batch of transactions.

As you can imagine, having all of the transactions prepared for QuickBooks with a single click was dramatically faster than the manual approach. It was now taking me only 5 minutes per day. Which was pretty good considering it used to take 30 minutes.

But, I discovered a couple of tools that helped me skip the Excel processing step all together. And I figured out how to use these tools to get e-commerce transactions moved into QuickBooks online in real-time, without any manual effort. Specifically, these tools are Zapier and Mailparser, and I’ll dig into those tools in the next post.

Posted in , ,

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

Excel is not what it used to be.

You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.

Want to learn Excel?

Our training programs start at $29 and will help you learn Excel quickly.

2 Comments

  1. Arlette S on November 23, 2022 at 11:07 am

    I appreciate how you continue to find innovative efficiencies using Excel. I can’t wait to see which tools provided the zero time solution!

  2. Esmer on December 14, 2022 at 9:01 am

    Thank you Jeff! Thought you’ll provide some of the materials you’ve used here 🙂 If not, excited to explore Power Query more.

Leave a Comment