PivotTable Months

PivotTable Months

This short tutorial video demonstrates how to create monthly PivotTable columns

Hi,
Before I get to the video, let me continue my story because I want to share a bit of good news.

Stress that resulted in CFO showdown

As you recall, I had more work than time. I was anxious and stressed out all the time. I felt like I was drowning in work and couldn’t breathe. My work life imposed on my personal life because I had to work late nights and weekends. During the monthly close, I would wake up in the morning with a knot in my stomach because of the amount of work. Overall, I was a grouch, and ultimately, I totally lost my composure with my CFO after being assigned an additional project.

Meet my new employee, Excel

I knew things couldn’t continue like this. This low point is what caused me to look desperately for a solution. That is when I turned to Excel. Or, as I like to think of it, that is when I hired Excel.

Instead of delegating my work to staff people (who I couldn’t hire because I didn’t have the budget), I started a slow but rewarding process of delegating recurring manual tasks to Excel. This idea may seem funny at first, but I assure you that Excel is a very good and fast worker. Let me give you a few examples to illustrate.

  • When you add new data to a worksheet by typing or pasting values under an existing range, you need to manually update formulas so they include the new data. Well, the manual step of updating formulas can be delegated to Excel by storing the data in a Table instead of an ordinary range.
  • When you add a new item to a report, for example adding Inventory to a balance sheet, you need to manually update the Total Assets formula to include the new item. That manual step can be delegated to Excel by using a SUBTOTAL function.
  • When a new account is included in the transactions, you need to manually update the report by adding a line for the new account. Excel can do that task as well when you use a PivotTable report instead of a formula-based report.
  • When you need to update a workbook with new transactions from the accounting system, you export the data, copy, and paste it into the workbook. That manual step can be delegated to Excel when you use the Get External Data feature.
  • When you export an updated trial balance, you need to manually combine the account balances to create the balance sheet. That step can be delegated to Excel by using a mapping table.

I could go on, but, I think you get the idea.

Excel, please do my work

By thinking about Excel as a platform to automate recurring accounting tasks, I soon discovered many ways to have Excel do my work. Each month during the close, I focused on automating just one more report. One more workbook. One more journal entry. Over time, this approach paid off, big. My 2 week close? Yeah, it was down to 2 days. That left plenty of time for other tasks and projects. Late nights and weekends were a thing of the past. And, I wasn’t so grumpy anymore, and I could breathe!

The best news? I’m no smarter than anyone else. I just dedicated myself to really understanding how Excel works, and more importantly, figured out how to apply Excel to my accounting tasks. And, it worked. This is what ultimately lead to Excel University.

Not only did this process of delegating tasks to Excel work for me, it has worked for many others over the years as well. In fact, in my next email I’ll talk about a powerful Excel technique that illustrates how one of my students shaved 36 hours off of a monthly project. But, until then, let me leave you with one final example of a manual task that can be delegated to Excel: creating monthly PivotTable columns.

Monthly Columns

To create monthly columns in a PivotTable, I used to manually define the month groups with a helper column. That is, until I learned that Excel can do that step as well!

Here is a short video that shows how to create monthly columns in a PivotTable.

Hope this helps!

Thanks,

jl_sig2

Additional Resources