• Home
  • About
  • Our Shop
  • Resources
  • Podcast
    • Love
  • Topics
    • Productivity
    • Blogging
    • Social Media
    • Tutorials
    • Book Writing + Selling

Excel for Dummies…er Brilliant Business Moms!

March 17, 2016 by Beth Anne Schwamberger 2 Comments

The Best Etsy SEO Tips for Etsy Sellers - finally some help on getting found in search!


 

Great tutorial on using excel for business. I finally know how to track expenses and income for my etsy shop! Great business finance tips for anyone with an online shop. | brilliantbusinessmoms.com
Excel for Dummies Brilliant Business Moms!
Business income and expense tracking is always last on my list. I know that at some point I’ll cram it all in! But I also know I can do it super fast, thanks to Microsoft Excel!
If Microsoft Excel is not your cup of tea, let me help you. Get out your nerdiest glasses, open up a blank spreadsheet, and as Sarah Kornhak says, “let’s get started!”

DOWNLOAD AND FORMAT YOUR ORDERS

 
I want to show you how to take the raw data from your Etsy shop and crunch the numbers. This will also work with orders from any other platform as well, but they will be formatted a little differently.
  1. To download your orders in Etsy, you will go to “Orders”, and at the bottom of the page “Download a CSV file with the details for each order.” Decide which date spread to download.
  2. Once you’ve downloaded your data, save your spreadsheet.
  3. Next, it can be helpful to clean up your columns so you are looking at less information. To do this, you will hide the columns you don’t want to see. I’m going to hide some of the columns that are blank. Select the columns by clicking on the column letter, and dragging over to the last column you want to hide. Right-click, select “Hide”.
  4. Note that every selected column will be hidden, so if you want some to show, just hide them in sections. To unhide the columns, follow the same steps and click “Unhide”. You’ll know that you have hidden columns if you see missing column numbers at the top.

Hide Columns photo output_0XO1Oy_zps8t4xqern.gif

QUICKLY FILTER ORDERS

Maybe you want to see how many of one item you’ve sold over the last few months. Easy peasy!
  1. Double-click on the bar to the right of my item title column to expand it to show the full item descriptions. (Mine are looong, because I’m sure to maximize my title SEO!)
  2. Click on the number to the left of your Header Row. It should be the first row, row 1. This will highlight the entire row.
  3. Click on the “Data” Menu at the top, and select “Filter”. Now you should see a little arrow by each row heading.
  4. When you click on the little arrow to the right of your Item Header, a black box pops up with all the items in your item row. Uncheck “Select All” (top of the list), and select one of your items to view. Once you click away from the black box, you’ll see only the items you filtered out.
  5. THE CHEATER MATH WINDOW (not his real name): If you want to quickly sum the totals for the items you’ve filtered, click on the column letter above your “Price” column. This selects the entire column. Now you’ll look at the bottom of your Excel window. There is a little cheater math window that says “Sum” (or it may say “Count”). Click on the little black arrow on the right side of the box and you’ll see the variety of information you can get out of this little guy. It’s a great quick way to sum a column without actually entering formulas. photo Screen Shot 2016-03-09 at 3.15.42 PM March09-2016_zpsk2srwref.png
  6. To clear your filter, click on the little funnel (formerly an arrow) at the top of your column, and click the checkbox that says “Select All”. Now all your rows will reappear.

TOTAL UP YOUR SALES

Here’s the moment of truth! Your sales totals! We are going to make another worksheet in your Excel Workbook to hold your totals.
  1. Click on the little + on the tab at the bottom of your window to create a new worksheet.
  2. Rename the Sheet “Totals” by double-clicking on the title.
  3. Select a Cell, any Cell, and type “Total Sales”.
  4. In the next column, type “  =sum(  ” and then click the tab at the bottom of your window that will take you to your orders worksheet.
  5. Click the column letter above your order totals column, then hit “enter”.
  6. Voila! You have your total sales in your “Totals” sheet. If you download and paste in more orders, this total will continue to update, because you’re totaling the entire column from your orders worksheet.

Sum Sales photo Screen Shot 2016-03-09 at 2.59.43 PM March09-2016_zpslv2ajivc.png

TRACK YOUR EXPENSES

Grab another cup of coffee and a square of chocolate and sit back down – time for expense tracking!
      1. Add two more blank worksheets to your spreadsheet by again clicking the little + sign on the tab at the bottom of your window. Label one “Expenses”, and the other “Expense Categories.”
      2. On the Worksheet “Expense Categories”, type a list, all in Column A, of categories you can think of for your business expenses. It’s ok if you can’t think of all of them now, you can add more later.

Expense Categories photo Screen Shot 2016-03-09 at 7.55.43 PM March09-2016_zps6uugzyzh.png

      1. Go to the Worksheet “Expenses” and create three columns. (You can add more later if you want more detail.) Column A: “Date”, Column B: “Expense Category”, Column C “Amount”.
      2. Click on the “B” at the top of Column B to select the column. Go to the Data Menu and select “Validation”.
      3. Under “source”, click the emblem to the right of the blank. Click on your tab “Expense Categories” to go to that worksheet. Select a large chunk of Column A – as many rows as you think you’ll need for Expense Categories in the future. I’m selecting 30 rows of Column A. Hit “Enter”. Then hit “OK”.

 photo Screen Shot 2016-03-09 at 8.02.38 PM March09-2016_zpsly4hyjzh.png

      1. Now you should be back in your “Expenses” worksheet. Click on the top blank cell of the column “Expense Category.” You’ll see an up/down arrow to the right of the cell. Click on this arrow and you will see a box displaying your categories, and you can select the appropriate category for the expense your are entering. This will keep your expenses categorized correctly so you can easily sort them just like we did above for orders. You can also get fancy and learn to use VLOOKUP or SUMIFS formulas within this spreadsheet to easily show you totals for each category.
      2. Go to your “Totals” worksheet, and follow the instructions for totaling up your sales, but this time do it for your expenses.
Phew! That was a lot of information. But I really hope you feel more prepared to track your earnings and expenses on your computer.
If you aren’t interested in making your own Excel business expense/income tracking spreadsheet, or would like more reporting capabilities, I sell a Home Business Planner for Excel in my Etsy Shop, Perennial Planner. It is super user-friendly to enter your income and expenses, and easy to view your daily, monthly, and yearly income/expense/profit at a glance. Other worksheets include a mileage tracker, invoice, contacts sheet, hourly custom work tracker, schedules, goal tracker, and more!
excel-business-planner

Mamapreneur Sarah Gilcher of Perennial planner on Etsy - building a business that works for her family.
Sarah Gilcher is a pastor's wife and mom to three girls. She believes in making life more organized with printables and tools, which she sells in her Etsy shop, Perennial Planner. And Sarah believes in making those tools beautiful and inspiring too! She lives in Washington state. You can learn more about Sarah on her blog, PerennialPlanner.com.

Find this information helpful? Look at these posts, next!

  • Track Business Mileage & Keep More Money (+ a Free Printable!)

  • What’s the Deal with Assets and Depreciation?

  • Episode 22: Pricing Your Products – The Details

Filed Under: Tutorials Tagged With: business expenses, business expenses for etsy sellers, excel, finances, how to track expenses as a shop owner

« 4 Surprising Productivity Hacks for Mompreneurs
How to Create Amazing Landing Pages using Leadpages’ Drag & Drop Builder »

brilliantbusinessmoms
I'm a big dreamer, mom to the most amazing, fun boys and...an occasional nitpicker!

A little secret...I don't think I'm particularly brilliant. I've just been given the opportunity to interview some brilliant business moms.

Join me as I discover how to be brilliant in business too!

Learn More About Me!

  • Email
  • Facebook
  • Instagram
  • Pinterest
  • RSS
  • YouTube
FB Ads Intensive

Products

Brilliant Sales Page Templates Product Promo Deluxe Template Pack Ultimate List Builder's Template Pack

The Book

This book has been a lifesaver.  I've gone from stressed and overwhelmed to focused and in-control.  Love Time Management Mama!
Features on brilliant business moms such as mompreneur interviews, blogging advice, etsy seller advice, and productivity Blogging Advice for Mompreneurs Etsy Seller Advice Writing and Selling Books - how to do it as a stay-at-home mom Social Media Marketing for Mompreneurs Productivity - learn how to be more productive as a work-at-home mom Service-based Business Advice for Mompreneurs Mompreneur Interviews Photography tips for mompreneurs

Search the Site

“Wondering
Privacy Policy

Copyright © 2025 · Tasteful theme by Restored 316

Privacy Policy

We use cookies to ensure that we give you the best experience on our website. If you continue to use this site we will assume that you are happy with it.OkRead more