Hiring and Growth

Free Excel Hacks and Templates for New Business Owners

Andi Smiles Small business financial consultant 
Free Excel Templates for Small Business Owners

Apps. Browser extensions. Desktop software. Web platforms. There are SO MANY snazzy tools out there for new business owners. And while these tools can work wonders for our businesses, it can be hard to learn sleek new software when what you really need to do is get stuff done. 

Simple time tracking that syncs with payroll.

This is when it pays to go old school. Because sometimes even the niftiest software can’t stand up to an old standby: the spreadsheet.

Spreadsheets have evolved so much since the days of pivot tables and nested formulas. Nowadays, there are a bunch of fresh new ways to turn the old school spreadsheet into a business management tool. In fact, you could probably run your entire business with just spreadsheets. 

The best part? Spreadsheets are super inexpensive. You can opt to make a one-time investment in Excel or use Google Sheets, which is free with any Google account. 

Spreadsheet hacks

You don’t have to be a numbers genius to create a super useful spreadsheet. These are some of my favorite spreadsheet formulas and tips:

Math symbols

Use the following symbols to perform basic mathematical functions in your spreadsheet. Pick a cell where you want the final value to appear, enter an equal sign (=), and follow the examples below for each function:

  • Addition: + (ex. =A1+B1)
  • Subtraction: – (ex. =A1-B1)
  • Multiplication: * (ex. =A1*B1)
  • Division: / (ex. =A1/B1)
  • Exponents: ^ (ex. =A1^B1)

Parenthesis

Just like in your seventh-grade math class, adding parentheses to your formulas allows you to create more complex formulas. Here’s a translation of the following formula:

=((A1+B1)-(C1+D1))*.50 

Translation: Sum of cells A1 and B1 minus the sum of cells C1 and D1. Then the difference is multiplied by 0.50 (aka 50%).

In spreadsheets, the function in the innermost parentheses happens first, and then the order works its way outwards. 

SUM

The SUM formula adds up a selected range of cells. This is awesome if you have a long column of numbers you need to add up. For example, if you enter the formula =SUM(A1:A25), cells A1 through A25 will be added up. Think of the “:” as a shortcut for “through.” 

Copying formulas

Let’s say you want to add up column A1 through A25, and then you want to add column B1 through B25, and column C1 through C25. Instead of having to type in the sum formula every time, you can drag the formula across multiple cells. 

To do this:

  1. Click on the cell with the formula you wish to copy.
  2. On the bottom left of the cell, a small square will appear. Hover your cursor over the square until it turns into a cross.
  3. Click and drag the formula down or across the cells you want to copy it to.

IFERROR

A SUPER annoying thing happens if you copy a formula with division in it and the cells involved in the function are empty. You get something that looks like this: #DIV/0! This is the spreadsheet’s way of telling you that it doesn’t have the information it needs to complete the function. 

To get rid of this warning while maintaining the formula, use the IFERROR function, which tells the spreadsheet what to put in the cell if there’s an error.

For example, the formula =IFERROR (A1/B1, 0) tells the spreadsheet to divide B1 by A1 if there’s no error. If there is an error, it tells the spreadsheet to enter a value of zero. 

Dropdowns

Want to cut down the amount of typing you do into your spreadsheet? Add a dropdown list! 

In Google Sheets:

  1. Select the cell you want the dropdown to appear in.
  2. Go to Data –> Data Validation.
  3. Under Criteria, select List of Items.
  4. Enter the items you want to be able to select from the dropdown.
  5. Press save.

In Excel:

  1. Enter the items you want to appear in the dropdown list in the cells in column A. Each item should be entered into its own cell.
  2. Select the cell where you want the dropdown to appear.
  3. Click the Data tab and then the Data Validation button.
  4. In the Allow field, select List.
  5. In the source field, click the small icon and select the list of values for the dropdown.
  6. Press OK.

Spreadsheet templates 

Now that you know the basics of making an epic spreadsheet, check out these genius ways to use spreadsheets to manage and grow your business.

For managing your finances…

Most people associate spreadsheets with numbers, so it should come as no surprise that spreadsheets are an amazing tool for tracking your finances. This is especially true for new business owners who don’t have the time or money to invest in professional accounting software. 

Here are my favorite ways to use spreadsheets for financial management:

Income and expense tracking

You’ve probably heard by now that you need to keep track of your business income and expenses, but you may be wondering HOW to do that. You can use a spreadsheet for basic income and expense tracking. It’s very easy to set up. You just need the following columns:

  • Date
  • Payee (who you paid or who paid you)
  • Amount
  • Income category or Expense category

Every time you spend or earn money, log the information in your spreadsheet. At the end of the year, filter the spreadsheet by income or expense category, and get your grand totals for each category to send to your tax preparer. 

Budgeting

If you’ve ever made a budget by hand you know that it’s tedious and involves A LOT of addition. Budgeting is hard enough as is without throwing math fatigue into the mix. Using a spreadsheet to make a budget takes all the heavy lifting out of adding up your budget categories. 

Plus, you only need to know one formula, the SUM formula, which adds up a range of cells as we mentioned earlier. If you can get that far, you can make a simple budget for your business. 

Invoice tracking

Figuring out which invoices are paid and which are still open is a nightmare that leads to a lot of late nights matching bank deposits to invoices. Or you blow it off altogether and miss out on income. 

The solution? Create an invoice tracking spreadsheet! Format your invoicing tracking spreadsheet with the following columns:

  • Customer name
  • Invoice number
  • Invoice amount
  • Due date
  • Sent
  • Paid
  • Deposited
Invoice Tracking Sheet Screenshot

Every time you start an invoice for a customer, add it to your spreadsheet. When you send the invoice, note the sent date in your spreadsheet. When the invoice is paid, fill in the payment date. And finally, when you deposit the payment, add that date to your tracker. You’ll never miss an invoice payment (or lose a check) again. 

For tracking your metrics…

Do you ever try a bunch of stuff in your business without really knowing if it’s working? And then you try some more stuff and eventually something works… you’re just not sure what?

If this sounds familiar then tracking your metrics with a spreadsheet is going to seriously level up your business. 

Metrics can mean a lot of things. There are financial metrics, but there are also other types of metrics that track your marketing and advertising efforts. These metrics help you make better, more informed decisions in your business. 

Here are some ways you can use a spreadsheet to track your metrics:

Social media growth and engagement

Which social media channels is your audience hanging out on? Which channels are growing the quickest? What type of content does your audience like the best? Tracking this information in a spreadsheet helps you focus your time and energy where it counts and grow your following. 

Some columns you could add to a social media tracker are:

  • Monthly social media followers
  • Follower growth rate (How many new followers do you get each month?)
  • Reach for each post
  • Engagement rate for each post
  • Number of link clicks
  • Reposts or mentions
Social Media Tracker Screenshot
Free social media tracker template.

Ad campaign results

Entering the world of paid advertising can be overwhelming and expensive. From Facebook to Instagram to Google to Pinterest, there are a lot of places where you can advertise your business and a lot of advertising options within each platform. 

The trick to getting the most out of paid advertising is tracking your results and what’s working. You DEF don’t want to be spending money on ads that aren’t working—and you DEF want to put your hard-earned dollars toward ads that are. 

You can use a spreadsheet to track the results of your ad campaigns on each platform by using the following columns:

  • Ad or campaign name
  • Type of ad (video, image, carousel, Instagram story, etc.)
  • Dates run
  • Total spent
  • Reach
  • Engagement
  • Clicks
  • Cost per click
  • Type of conversion (purchase, video view, sign up, etc.)
  • Conversions
  • Cost per conversions
  • Notes about your campaign
Ad Campaign Tracker Screenshot

For growing your business…

While using a spreadsheet to track your metrics helps you see the results of your decisions,  you can also use a spreadsheet to grow your business by tracking your goals and projections. This is a bird’s eye view of your business and shows you if you’re heading in the right direction. 

If you’re not, you can use this information to pivot and get back on track. You can even use a goal tracking spreadsheet to understand which areas of your business are the most profitable—and which are not. 

You can track your goals and progress for just about anything, but my favorite goal tracking spreadsheets are:

Monthly sales goal tracker

You likely know how much of your products or services you need to sell to keep your business afloat. And you probably have an idea of how much you’d like to sell to grow your business.

Whether you are trying to increase your sales or maintain consistent sales, a monthly sales tracking spreadsheet can help you see if you’re hitting your targets, and if not, by how much. 

You can format a monthly sales tracking spreadsheet with these columns:

  • Product or service name
  • Monthly goal: Number of items sold
  • Monthly goal: Total revenue made from sales
  • Monthly actual: Number of items sold
  • Monthly actual: Total revenue made from sales
  • Difference between goal and actual
  • Notes (for recording anything that could have affected your sales)
Monthly Sales and Goals Tracker Screenshot
Free monthly sales goals tracker template.

Lead generation and conversion goals tracker

Every business needs customers to purchase their products or services, and potential customers are called leads. Depending on the type of business you have, leads can come from inbounding marketing (for example, someone opting into your email list) or outbound marketing (for example, cold calling or cold emailing potential customers). 

Setting lead generation and conversion goals helps you understand how many leads you actually need to hit your sales goals. Your conversion rate shows you if you are generating quality leads or if you need to change your lead generation strategy. 

You can make a monthly lead generation and conversion goals spreadsheet with these columns:

  • Lead generation activity
  • Lead generation goal
  • Sales goal
  • Conversion rate goal
  • Actual leads generated
  • Actual sales
  • Actual conversions
Lead Generation and Conversion Tracker Screenshot

If you have different stages of your lead generation process—for example, email funnel, call scheduled, contract signed—then you can add your goals and actuals for each step.

For project management and productivity…

With so much to do as a small business owner, it can feel near impossible to keep up with every task on your to-do list. Let’s not even think about keeping track of key dates in your business, like when you’re going to launch something or publish a social media post. 

Enter the spreadsheet! Spreadsheets are powerhouses when it comes to sorting and organizing data. When your ideas, projects, and tasks are organized, it’s WAY easier to execute on them. 

Here are a few ways to use spreadsheets to GSD: 

Project management

You’ve probably got a ton of projects you need to work on. And you’ve probably got a ton of tasks related to each project with different due dates and priority levels. A project management spreadsheet gives you a single place to organize your task list for all your projects. 

You can set up a project management spreadsheet like this:

  • One header row for each project
  • Additional rows under each project for each task
  • Columns for:
    • Task name
    • Priority
    • Deadline
    • Status
    • Assignee
    • Notes

Start each day by reviewing your project management spreadsheet and you’ll easily be able to allocate your day-to-day work to the most urgent and time-sensitive tasks. Plus you’ll know if you actually did a task, or just fantasized about it being done already. 

Project Management Spreadsheet Screenshot
Free project management spreadsheet template.

Editorial calendar

Keeping track of what content you’re going to release and when can be overwhelming. So overwhelming that you might find yourself in a Netflix-Cheetos hole instead of posting on social media. 

Using an editorial calendar spreadsheet to organize your weekly content for each platform means that you’ll never miss a post. You’ll also be more intentional about the type of content you post. Plus you can still each Cheetos.

Here’s how to format an editorial calendar spreadsheet:

  • Date
  • Platform
  • Text or Title
  • Link URL
  • Scheduled?
Editorial Calendar Screenshot

Have I convinced you of the glory of spreadsheets yet? Keep in mind these are just a *few* of the ways you can use spreadsheets for your new business. And since spreadsheets are virtually free to make, you can experiment to your heart’s content and start living your best spreadsheet life… with Cheetos.

Updated: July 19, 2019

Andi Smiles
Andi Smiles Andi is a small business financial consultant and coach who teaches business owners to take control of their finances. She’s helped hundreds of self-employed folx organize and understand their business finances, while also uncovering their emotional relationship with money.

Comments

*Required fields

Your email address will not be published.

Back to top