Do Monarch’s Summaries Leave You Wanting More?

You’ve learned how to use Monarch’s Summary feature (there’s a primer in this video), and you’re now comfortable with building summaries. They’re great tools in and of themselves.

But now that you have a general understanding of the concept, you may find that as useful as they are, the process of creating a summary is a bit cumbersome. The end result is a little static too. Sure, you can drill up and down, but if you want to change the basic structure, well, that takes a little more effort.

Wouldn’t it be great if your summary was a bit more interactive? What if you could reposition your fields just by dragging them around with your mouse? What if you could filter the data displayed in the summary on the fly, based on the live content?

These are the types of things that you can do with Excel’s pivot table feature.

Building a Pivot Table is Simple

It’s interesting. I’ve demonstrated the benefits of using pivot tables to literally hundreds of people, and what I usually hear is “I need to learn how to do that”. But when I follow up with them later, they’ve made no progress. Why is that?

People are somewhat intimidated by pivot tables. To some, it seems so difficult that they don’t believe that they can create pivot tables themselves, so they don’t even try.

That’s a real shame, because creating and using them can be very simple.

Start with the Basics

Pivot tables are a bit like Excel and Monarch in general: they’re packed with features, but you don’t need to use every feature all the time.

Let’s look at using a pivot table to perform a little monthly sales analysis. Let’s say that you’re reporting for a retail chain, and you want to prepare an overview of both the location unit counts and total revenues for multiple product categories and sub-categories. For instance, you might sell music CD’s and DVD (your product categories) and Classical, Rock, Pop, Action and Soundtracks might be your sub-categories.

Pivot Tables are Reusable Structures

It might take you few minutes to initially build the structure for your table, to get just the right layout. But when done right, it’s a one-time investment of your time. That’s because pivot tables are like Monarch summaries - once they’re built, they can be reused over and over again with new data.

This is a huge time saver. Think about it. Every time you want to update that fancy, complicated report all you need to do is make a couple of mouse clicks (once you have the data of course!). And if your computer is up to the task, you’ll have an up to date report in seconds.

Automate the Data Range for the Table

If you’re already a little familiar with pivot tables, you know that when you create a table based on an Excel list, the data is read into the table from a specific range which includes fixed columns and rows, such as $A$1:$Z:100. The next time you update your data, you may have more or fewer rows of data. So to get the table to point to the right data, you must edit the data range manually.

There’s a better way. You can create a named range that will always adjust itself to your current data set automatically - you won’t need to adjust it anymore.

Create a range named PivotData, or even better SalesData, as being clear lets you manage your data better.

Now for the “Refers to:” area, where you normally supply a range, type the following formula instead:

=OFFSET('Sheet1'!$A$1,0,0,COUNTA('Sheet1'!$A:$A),COUNTA('Sheet1'!$1:$1))

  • The Offset function returns a range.
  • ‘Sheet1′!$A$1 tells the function that cell A1 is the top-left corner of the area.
  • Then go over zero rows, and
  • zero columns from there.
  • COUNTA(’Shee1′!$A:$A) tells the function the number of rows that have data, including the heading, and finally
  • COUNTA(’Sheet1′!$1:$1) tells the function the number of columns that have data

As a test, hit the F5 key, type the range name you created (SalesData in our example), and hit Enter. You should see the data range highlighted.

Here is a short video of the process of creating a dynamic range name, and a little demonstration of its functionality.

Once the data is ready (best prepared with Monarch, then exported to Excel), you’re ready to build a pivot table. The second video today shows you how to create a pivot table and an overview of how you can manipulate the content.

Using pivot tables to summarize your data is an absolute necessity for great reporting that needs to be updated regularly. Building the right content in a manner that your audience will understand should make you very popular.

Pivot tables allow you to summarize and analyze a great deal of data, and there’s no better way to supply that data to the pivot table than to start the process with Monarch.