Do you use Excel Pivot Tables? If you do, then you know that Pivot Tables are a great way to categorize, sum, and average a table with many rows of numbers. If you aren’t yet using them, you really should. This article assumes you already use Pivot Tables comfortably.

But more importantly, Pivot Tables are very important for automating your reports. This may seem obvious, but to automate your reports, you must eliminate the manual steps. Duh!

The #1 Excel Telecom Trick for automation is to use Pivot tables and the GETPIVOTDATA() function.

GETPIVOTDATA() allows you to place Pivot Table data anywhere in your report. Once you see it in action it will make complete sense to you.

Follow Along

I’ve created a workbook which shows you exactly how to do what I’ve written about. I’ll refer to it a lot during this article. You can download it to follow along. The workbook will help you understand what I’ve written, and also allow you to copy/paste into your own workbooks.

I do ask for your email address so that we can keep in touch. I dislike SPAM as much as you, and promise I won’t ever send any. You can unsubscribe at any time.

Download the Workbook

The downloaded workbook is a skeleton of my Long Distance Dashboard, which I’ve shown you before. The Invoices worksheet has a table of manually-entered Long Distance usage and cost information, and it’s summarized by a Pivot Table.

Approach

The Subscribers worksheet shows the subscriber count by month, again manually entered, with a Pivot Table to summarizes it.

The Dashboard worksheet is from my Long Distance Dashboard. It has a column of dates, labeled “Month” and is shown a month at a time. 

These dates are derived automatically from the invoice data.  The image shows the mostly blank Dashboard template, with only the monthly dates pre-populated in the left-most column.

The next column shows subscriber counts from the Subscribers Pivot Table, and the column after that shows the count of MOU for domestic (in-country) long distance calls.

To use the GETPIVOTDATA() function we need to understand the syntax.

GETPIVOTDATA() Syntax

Here is the general syntax of GETPIVOTDATA()

=GETPIVOTDATA(Data_Field,Pivot_table,Field1,Item1,Field2,Item2,…)

• Data_Field. The name of any of the columns you dropped in the Values area of the Pivot Table wizard. A single Pivot Table can have many possible Data Fields. But each GETPIVOTDATA() function can have only one.
• Pivot_table. The address or location of the Pivot Table.
• Field_n. Each GETPIVOTDATA() function will have as many Field/Item pairs as the Pivot Table requires to specify a single data element.
• Item_n. For each Field entry, there will be a corresponding item, which is the value of the field.

The easiest way to create a GETPIVOTDATA() function is to enter a “=” sign in a blank cell, then click in the Pivot Table values area. Let’s do that now.

Populate the Dashboard

In this case, I want the count of subscribers to correspond to the date on the same row of the Dashboard. To see the power of GETPIVOTDATA(), enter “=” in the first row of the Subs column, then click in anywhere in the subscribers Pivot Table.  When I click cell L3, Excel displays this:

=GETPIVOTDATA("Subscribers",Subscribers!$K$2,"Date",DATE(2015,9,1)))

The 4th argument is the date from the Pivot table, 1 September 2015. But the date in the 1st row of the Dashboard is “Oct-16”. Remember that I want the displayed subscriber count to match the date in the same row of the Dashboard.

To replace the GETPIVOTDATA() date value, highlight the DATE() function in the 4th argument to GETPIVOTDATA(), and then click in the Dashboard cell with the first date, B6. Now the GETPIVOTDATA() formula looks like this:

=GETPIVOTDATA("Subscribers",Subscribers!$K$2,"Date",B6))

Check the Subscribers Pivot Table to confirm the subscriber count corresponds to Oct-16. Cool!  Now we can use Excel’s fill handle to populate every cell in this column for which there is a Dashboard date.

The exact same procedure will give us the corresponding values in the MOU column when we click in the Invoices Pivot Table.

The image shows all the formulas for the monthly dates and the Subscriber counts.  By linking the Item for the “Date” field in the GETPIVOTDATA() formulas, the fill handle allows the whole column to be populated from the first cell.

Benefits

There are 2 main benefits of this approach:

First, the GETPIVOTDATA() functions will return the subscriber count or MOU value for the date in the Month column. You might ask “why is that a big deal, I had to enter the subscriber count anyway.”

The big deal is that you can automatically select any specific piece of data from a Pivot Table and place it anywhere you want. I call that “automation”.

Second, by linking the GETPIVOTDATA() function to the column of TTM date values, the dates can change independently of the GETPIVOTDATA() function. This is completely automated, and here’s how it works.

1. The Trailing Twelve Months (TTM) dates are automatically calculated from the user-inputted invoices. This is done on the Lookups worksheet.
2. The TTM date locations are entered in the Month column on the Dashboard worksheet.
3. The GETPIVOTDATA() functions link to the dates in the Month column to display the corresponding subscriber count and MOU value
4. The TTM dates, and also the subscriber and MOU values, change automatically when new data is added and the Pivot Table refreshed.

The net result of this automation is that to update the workbook, simply enter new data, “Refresh All” the Pivot Tables, and that’s it!  You’ve already updated the Dashboard! No additional steps!

I’ll bet that this is a much different approach from how you update your current Dashboards. 

Conclusion

My goal is for Dashboards and other recurring reports is to conform to a 3-step process: Update, Refresh, Share. 1) Update with new data. 2) Refresh the Pivot. 3) Share as you normally would. For this process to work, the workbook must be set up properly. But once it is, there are no manual steps at all! It is 100% automated.

The benefits of automation are great. First, you’ll save time with every update. This is the benefit most people think of, but I believe it’s probably the least important one.

Second, your mind will be free to actually consider the data and what it means. This is highly significant.  Eliminating boring, repetitious, mind-numbing steps will free your mind.  The Dashboard transforms itself from a mess of data to crystal clear information. You’ll begin to better understand the data and synthesize meaningful insights. Prepare to be lonely, because not many of your peers get this far.

Third, coworkers and managers will begin to ask your opinion, to hear your insightful analyses. This is why I see Dashboard automation as a key career-builder for Telecoms Engineers. You will be seen as an expert in your field, making your star rise with decision-makers.

Give this technique a try, and post a comment with your thoughts or questions. Good luck!

Here is the link to download the workbook.

Download the Workbook

 

If you’d like to see more of my Telecoms Tools and Career-Building tips, follow me on LinkedIn

Russell Lundberg

or follow me on Twitter

TelecomVoices