Home Archives for Pivot Table for Water Year
March 26, 2011 Leave a Comment

How to Take Advantage of Report Filters in Excel Pivot Tables

Starting with Excel 2007, Pivot Tables now include a “drop field” labeled “Report Filters.” The Report Filters area replaces the area labeled the “Pages” drop area in Excel 2003 and earlier. Report Filters is a more descriptive and intuitive name. So , this is a welcome change.

How to Organize Pivot Table

Use Report Filters in Pivot Table

In this lesson, I am responding to an additional request from one of my viewers who wants me to demonstrate how the “Report Filters” drop zone will provide incredible reporting power in a Pivot Table.

Request from a Viewer

In this lesson, my viewer is a “hydrologist” who analyzes average Water Flows by day and by month in a “Water Year.” If you use a “Fiscal Year” reporting structure, you will want to pay attention to this lesson. In this case, the “Water Year” begins on October 1 and ends on September 30. My viewer needs my help in separating out individual Pivot Table Reports organized by Water Year – placed in the Report Filters Drop Zone.The Pivot Tables show the average daily water flow by date. He told me that he is concrned that that Pivot Table Report may not be able to “account for Leap Years.”

Not to worry!

Watch this Excel Video Tutorial, as I demonstrate how to answer my viewer’s query and also give you some “solid tips” to get the results that you need when using Excel!

Watch this Excel Video Tutorial in HD Mode on my YouTube Channel

Here is the link to my YouTube video channel for this video.

Learn how to “Quickly Create Pivot Table Reports!”

Taking Advantage of Report Filters in Excel Pivot Tables [ 8:51 ] Play Now | Play in Popup | Download (959)
Link to this post!
Filed Under: Excel Training, Pivot Tables, Podcast Tips & Timesavers Tagged: , , , , , , , , , , , ,
March 6, 2011 1 Comment

How to Sort Pivot Table Field Values in Excel

With a Pivot Table, you have more options and more flexibility when it comes to sorting your field values than you do with a regular Excel data set. However, many of my clients and viewers seem to get “lost” when it comes to creating custom sorts for their Pivot Table Field Valuess. So, let me demonstrate four ways to sort fields to suit your needs.

Sorting Fields in Pivot Tables

Sort Pivot Table Fields

Sort the Order of Months

 

If you create a Pivot Table for a Fiscal Year or,as in this example a Water Year, you will want to sort the Month Fields to match the starting and ending months in your FY. There are at least three ways to do this:

  1. Right click on the month that you want as your first month. Choose Move from the shortcut menu and select Move (in this case) Oct to Beginning.
  2. Another way to relocate a field value is to select the bottom part of the cell. When you see the 4-heard black cross, just move the field value to the new location. You will see a Gray Horizontal Bar as you drag the field.
  3. Most amazing way to move a field value  – and seeing is believing – is to go to the cell where you want to place the field value and just “type in the name of the field value. Incredibly, Excel moves the field values to accommodate the value that you typed in! This only works with a Pivot Table.

Sort Using a Custom List

You can create a Custom List and use that for sorting the values in your Pivot Table Field. After you create your Custom List (I show you how in this video) you choose the Pivot Table Options Sort command and then choose the direction – Ascending or Descending.

In Pivot Tables, Excel remembers your custom Field Value Settings when you move – or Pivot – field from Rows to Columns for example.

 After you watch this short five minute video lesson you will know how to sort field values in Pivot Tables to suit your needs.

Pivot Table Resources

Pivot Table Training Resources

Pivot Table Training Resources

I have published 90 Minute Video Tutorials to help you to quickly master Pivot Tables. No matter which version of Excel that you are using (Excel 2010, 2007, or 2003), I offer video training that is specific to your version. You can learn more about these resources at my secure online shopping website – http://shop.thecompanyrocks.com

Learn how to “Quickly Create Pivot Tables and Charts”

Related Excel Video Lesson

My YouTube Channel – DannyRocksExcels

Click here to watch this video in High Definition, Full-screen mode

How to Sort Pivot Table Fields in Excel [ 5:01 ] Play Now | Play in Popup | Download (1124)
Link to this post!
Filed Under: Excel Training, Pivot Tables, Podcast Tips & Timesavers Tagged: , , , , , , ,
March 4, 2011 1 Comment

Create a Pivot Table for a Water or Hydrological Year

A viewer asked for my help in creating a Pivot Table Report that is based on a Water Year – also known as a Hydrological Year. In North America, a Water Year begins on October 1 and ends on September 30. If you use a Fiscal Year in your reporting, you will want to watch this Excel Training Video Lesson.

Pivot Table for Water Year

Pivot Table for Water Year

Add Two Fields to Source Data

In order for a Pivot Table to summarize non-calendar years and quarters you will need to have two additional fields in your source data:

  1. A Fiscal Year (Water Year) Field – Use the IF(), MONTH() and YEAR() Functions to produce this.
  2. A Fiscal Quarter (Hydrological) Quarter Field – Use the CHOOSE() Function to produce this.

Now you are ready to create your Pivot Table Report.

Group Dates by Month and Year

Begin by grouping the Date Field by Month and Year – not, in this case, by Quarter. Now, remove the new Year field that you just created and add in the Water Year Field and the Hydrological Quarter to the Pivot Table. Continue to arrange the Pivot Table to suit your needs.

Since there is no direct way to change a Calender Year into a Fiscal Year or Water Year, you must prepare your data prior to beginning to create your Pivot Table. Now that you have watched this video lesson, you know how to do this!

Learn how to “Quickly Create Excel Pivot Tables and Charts”

Related Excel Video Lessons

Follow this link to watch this video in High Definition mode on my YouTube Channel – DannyRocksExcels

Create a Pivot Table for a Water or Hydrologic Year [ 9:37 ] Play Now | Play in Popup | Download (1249)
Link to this post!
Filed Under: Excel Functions, Excel Training, Pivot Tables, Podcast Tips & Timesavers Tagged: , , , , , , , , , ,