3 Ways Excel Can Help You Track Your Freelance Income and Expenses

As a freelancer, it can be overwhelming to think about all the hats you have to wear and all the information you need to track. But Microsoft Excel can be a big help, especially when it comes to tracking your income and expenses.

In this article, I’d like to show you three features you can use to help you get the most out of your data. These three features are data validation, pivot tables, and pivot charts. All of these features work together to uncover insights from your data and help you make business decisions.

But, as you’ll see, how you enter your data in Excel is a significant factor in the type of information you can get out of your spreadsheets.

Inconsistent Data

As an example, let’s say that you had a list of expenses, and you weren’t consistent with how you categorized the data. You might have something like the following for the first three months of 2021:

Now, if you tried to create a pivot table to summarize your data, you’d end up with something like this:

And if you created a pivot chart from the pivot table, the output would look like this:

While this information may be valuable, it doesn’t clearly represent what’s in your data. There are multiple entries for items that should be grouped as “Advertising” expenses, for instance.

Consistent Categories

To correct the problem above, you would want to be consistent with the category names you use in your expense list. So, if you changed the data so that it used consistent categories like this:

You would then be able to create a pivot table like this:

And a pivot chart like this:

These outputs create a clearer picture of your expenses and group individual entries in a small subset of categories.

Categories and Subcategories

In the previous example, you could clearly see the data for each of the three categories. To make your output even more helpful, you could add subcategories that break down the data even further. In the image below, you can see that there are several consistent categories, and each category has one or more consistent subcategories:

With the data above, you have an even clearer vision of where your money is going. From this data you can create a pivot table like this:

And a pivot chart like this:

This information is much more valuable and can help you make the decisions you need to make to improve your business.

The examples in this article include small amounts of data, but you’ll have much more data. So, how do you keep your entries consistent? This is where Excel’s data validation feature comes into play.

Data Validation to the Rescue

With Excel’s data validation feature, you can create a list of possible valid entries for a cell (or multiple cells), and Excel will only allow you to enter one of the valid values in the cell. For our example, you could set up a validation list for your category names to ensure consistent data was entered.

There are a couple of ways you can set up a data validation list:

• Use an internal list in the Data Validation’s source.
• Use an external list that is referenced by the Data Validation’s source.

Internal Data Validation List

To set up a data validation list within the Data Validation dialog box, follow these steps:

1. Select a cell where you want to use a data validation list.
2. Click the Data Validation icon (see image below) within the Data Tools section of the Data tab on the command ribbon.
3. Select “List” from the “Allow” dropdown.
4. Enter the list of valid entries as comma-separated text within the “Source” textbox.
   (Ex.: Advertising, Rent, Travel Expenses)
5. Click “OK”.

Once you have the validation list set up in one cell, you can copy it to other cells in your worksheet by:

1. Copying the cell that contains the data validation.
2. Selecting the cell(s) where you want to add the same data validation.
3. Choose “Paste Special” from the Clipboard section of the Home tab on the command ribbon.
4. Choose “Paste Special” from the menu.
5. Click the “Validation” radio button.
6. Click “OK”.

Now your validation list will be applied to all the cells you selected.

When you want to enter something in a cell with a validation list, you will see a dropdown arrow to the cell’s right.

You can click the dropdown arrow and select an item from the list.

Or, you can type the entry. If you enter a value that is not in the validation list, you’ll receive an error and will have to make a new entry.

You can make a change to the validation list at any time. But one of the drawbacks to using an internal validation list is that you’ll need to update every cell that uses that validation list by following the Copy/Paste special steps above.

An alternative, and possibly better solution, is to use an external source for the validation list.

External Data Validation List

One of the best ways to set up an external data validation list is to create a separate worksheet for all the values and use that list as the source in the Data Validation dialog box.

So, for our expense category example, you would create a “Categories” worksheet in your expense tracking workbook and add all the valid categories to that worksheet.

Then, when you create the data validation, you would reference the Categories worksheet’s cells instead of typing the categories in the dialog box.

Here are the exact steps to set this up:

1. Create a Categories worksheet.
2. Add all valid category names to the Categories worksheet.
3. Sort the list of category names (this makes it easier to find a category when using the list for data entry).
4. Switch to the worksheet where you want to add data validation.
5. Select the cell where you want the data validation added.
6. Click the Data Validation icon.
7. Select “List” from the “Allow” dropdown.
8. Place your cursor in the “Source” textbox.
9. Click the “Categories” worksheet tab.
10. Select all the cells containing category names.
11. Click “OK”.

You will need to follow the Copy/Paste Special steps above to add the data validation to all the appropriate cells on your Expenses worksheet.

If you need to change the list of valid category names, you will simply make that change on the Categories worksheet. The easiest way to do this is to add a cell somewhere in the middle of the list of category names, add the new category name, and sort the category names.

If you add a category name outside the range currently selected for data validation, you will have to update the data validation source. But if you follow the previous paragraph’s steps, all the cells that use the data validation will update automatically.

Conclusion

In this article, we’ve only looked at expenses, but these same features can very easily be applied to tracking your income. Just think about how your data should be categorized and use data validation to help you keep those categories intact.

Data validation is an excellent tool for keeping your data clean. Pivot tables summarize your data, and pivot charts visualize your data. All three of these tools help you gain insights from your data.

Have you used data validation, pivot tables, or pivot charts in your Excel spreadsheets? How do you plan on using these features in the future?

Brenda Keller is a software coach who helps people feel comfortable with and learn how to use software. She has been an Excel user since it was first introduced in 1985 and has taught the program in various settings. Her new Microsoft Excel: Quick Start course was designed to help those new to Excel or those wanting a refresher to get a handle on Excel’s key features quickly. Check out her course on Udemy here or view her YouTube channel here to get more help with Excel. To learn more about Brenda Keller, check out her official website here.