Posts Tagged ‘excel 2007’

How to track your business in excel part 4

November 18, 2009 Leave a comment

The template for this web cast is available in our Learning Center for $11

This is the final chapter in the 4 part saga (with one bonus in part 2).

Here is the link to watch this web cast (Video):

In part 3 we were not able to get into pivot tables too deeply so I wanted to devote another web cast to that part.

We show you how to use the transaction data to run pivot tables so that you can quickly and concisely report on the data to give you really meaningful information.

If you’ve missed the first 3 parts, be sure and get caught up here:

How to track your business in excel – part 1 –

How to track your business in excel – part 2 –

How to track your business in excel – part 2 – Bonus –

How to track your business in excel – part 3 –

And this one again

How to track your business in excel – part 4 –

The pivot tables are the key to this when it is all said and done because this is how you accomplish what any accounting system should be designed to do – take what you’ve put into it and give you something valuable that you can take out of it. This is what any system’s reporting feature is all about. You won’t be able to do a well formatted P&L and Balance sheet with this, but you can get close by running the pivot table on the transactions and then adding the destination account to the filter, then filter for all Balance sheet accounts. Keep in mind it won’t be perfect. If, for example you have payments to “Accounts Payable” then this will show up on your report, but does not represent what is “Due” in accounts payable. Instead it represents what has been “Paid” on accounts payable. So you may want to exclude that one. You can enter bills in your transaction register and then you would have an accurate payable, because the bills would go in as a negative (credit) to Accounts Payable and the payments on accounts payable would be entered as a positive number. Then you could pivot on accounts payable to get an accurate balance.

Next week we’re back on to QuickBooks so visit us there at


How to track your business in excel part 3

November 11, 2009 Leave a comment

For a better quality version of the video web cast:

Visit our Excel blog for more:

Here we really complete the picture for you by showing you how to enter and then report on your financial transactions.

We show you how to set up the hyperlinks so that if you expand upon this concept you will be able to incorporate any new items in the ‘Home’ or ‘Menu’ Tab.

We review the setup of a list and data validation to update the way the source account works, because that account really needs to represent sources of payments and deposits. This also makes it possible to rely on the “Destination” accounts for tracking all income, expenses, asset purchases and payments on liabilities.

Now that the template is set up we can show you how to enter your transaction data:

This is what the template looks like with everything entered. If there was a deposit to be entered the Source account would be the bank account where the money was deposited and the destination account would be the appropriate income account. This is not set up to show invoices to and payments on accounts receivable. This can be done but would have to be done on a private consult because it would be very involved.

Once we have data in we can begin to report on it. We show you in the last 3 minutes of the web cast how to create a pivot table on this data reporting on expense by category and then expanding upon that by showing you how to further report the expenses daily based on the date of the transaction.

Figure 1 – Pivot Table – Daily Expenses click for larger image

We will post a bonus web cast going into more detail on these pivot tables and how you can use them to report quickly on the information in the sales and transactions.

How to track your sales in MS Excel

October 28, 2009 Leave a comment

Click here to watch the web cast:

How to track your sales in MS Excel

Some of us have small businesses and cannot yet afford to go to the expense of purchasing and learning a program like QuickBooks. Especially if our background is in something non-financial (like the products or services we offer). In these cases we can use a spreadsheet to track our business’. As with everything in life we have to consider what information we need and in general we want to keep these things as simple as possi/ble. So in this week’s web cast we are going to look at tracking sales. In the next few weeks we will be building on this theme in short 10 minute web casts (so they can fit on youtube J. for all of our tutorials on both QuickBooks and Excel.

The simple answer on sales is setting up a template that provides a foundation that fosters consistency and simplicity. All too often we get caught up in the details and wind up creating something too complicated to maintain. The phrase keep it simple REALLY applies here. Then you can expand on it as you go – that’s the beauty of doing things in excel – you are the developer so you create the things you need as you need them and you don’t need to wait for the next release!

Figure 1 – Simple column layout

Notice in the example above I kept it simple. Sales tax is calculated separately. You will see in the web cast I was about to make it more complicated by combining the Sales Tax calc with the total. Then I realized I had actually laid it out better than that. It might seem more efficient to do it the other way, but how will I be able to see my total sales tax for a given period? I will have to write complicated formulas to back it out of my total. Then if I have multiple sales tax rates that computation will start to get really complicated. The way it is laid out here, later on I will be able to create a simple pivot table that shows me Sales Taxes by rate for a specified time period. Simple – then I can write my check to the State Board of Equalization based on a pivot table which can be created once and updated when I need to. For non-taxable “Labor” I simply set the sales tax rate as ‘0.00%”.

Sometimes we like to make things more complicated to impress people and then it costs us. That’s right our vanity based on the desire to impress people with our excel skills will cost us dearly in the end. Much more impressive is to show people something that was so well thought out that it was kept simple enough for anyone to use!

In this web cast I go over how to set up a simple template to track sales for your business. Next week we will look at how to track the expenses.

This question came in from my new friend Julio Rios. He has a great product and plans to put more out there. Visit his website at (make sure pop-ups are enabled).

Visit the Ask us Page here and post your questions and we will try and get your answer up quickly and/or record a web cast for you. Got a QuickBooks question? Visit our QuickBooks blog at

Finally!… Click here to watch the web cast:

How to track your sales in MS Excel

How to set the number of sheets in a new workbook

September 2, 2009 Leave a comment

Step I Click the MS Office Button and click Excel Options:

Step 2 – set the number of sheets as shown below:

Timesheets – Conditional Formatting, Vlookup, & Weekday formula

August 26, 2009 Leave a comment

MS excel’s conditional formatting feature allows you to highlight data based on certain criteria and especially in MS Excel 2007 there are many options in terms of what you can bring to light and how.
Since time allowed we also gave you a bonus tip on this one – how to write a simple Vlookup formula on a named range so you can being in data from a set list based on other criteria.

This is useful in creating a timesheet as you will see because timesheets are very much dependent on having a date in there, and it is handy to have today’s date light up to bring attention to it. When designing forms it is always good to make it as easy as possible to use. Adding little bells and whistles like this can help your end user. Formatting goes a long way to, but we did not get into this. E-mail us at for a copy of the timesheet we use at Nerd Enterprises, Inc. as well as the sheet created in this video tutorial.

Please enjoy the video tutorial: