Excel 2007 Pivot Table Basics Playlist
Excel Video 1 Create a Pivot Table
It’s not Disney, folks, but I think you’ll find these videos very helpful. I hope you enjoy the latest addition to MooreSolutionsInc.com. I’ve been working for some time on a way to create training videos to illustrate how to use Excel’s Pivot Tables in a medical practice. I’m going to start by creating and posting an introductory video that shows how to create a basic Pivot Table from sample medical practice data. From there, I’ll add additional videos on filtering, sorting, and doing a variety of different calculations in Pivot Tables. Pivot Tables are an incredible way to manage medical practice data. A well designed Pivot Table can replace dozens of canned reports and is much easier to use in carving up and drilling down to analyze a practice’s revenue cycle. I hope you find these training videos useful as you incorporate Pivot Tables in your medical practice.
Please feel free to make suggestions on other Excel topics you’d like to see in these training videos. While I’m starting with Pivot Tables, I intend to add videos on other Excel topics as well, such as VLOOKUP, Conditional Formatting, Charts and Graphs, etc.
Excel Video 2 Sorting a Pivot Table Video
Here’s another video I hope you’ll find helpful. Today’s video shows how to sort both values and labels in a Pivot Table. You’ll see how to sort ascending (A-Z) and descending (Z-A) amounts, and I’ll also show you how to manually sort data that you may need sorted in a way other than ascending or descending.
Next time we’ll filter the same data and look at several filtering options. As always, please feel free to comment or suggest future topics, simply by adding a comment to the bottom of this blog.
Excel Video 3 Filtering a Pivot Table Video
Today’s video introduces the basic filtering options in a Pivot Table using the same sample medical practice data. Filtering data is key to carving up your data into smaller slices for better analysis. You’ll see how easy it is to filter your data and what Excel does to show you which fields are filtered and what the filters are.
Excel Video 4 Grouping Pivot Table Fields
Today’s video demonstrates how to group dates in Pivot Tables fields. As long as Excel sees the dates are in a date format, the group function allows you to easily group dates by a variety of time intervals, such as days, months, quarters, and/or years. It’s easy to pick multiple time periods (months and years, for example) as well. You’ll also see three different ways to display Pivot Tables, which may help when you’re trying to format a certain way for printing.
Stay tuned. The next video will discuss how to group text fields instead of dates.
Excel Video 5 Grouping Text in Pivot Tables
Excel Video 4 demonstrated the way Excel groups dates in a Pivot Table. Excel Video 5 demonstrates how to apply the same grouping tools to text fields. You can also learn how to hide and show details of what you’ve grouped, which is helpful when printing or summarizing a large amount of data. Learning how to group and ungroup fields and how to show and hide the detail in the group will go a long way toward creating powerful Pivot Tables that manage a lot of medical practice data.
Excel Video 6 Drilling Down in Pivot Tables
If you’ve ever been challenged on whether a number in your report is correct, you need to know how to drill down to see the details behind that number. Excel Video 6 shows how to drill down to look at the data underlying a cell in a Pivot Table. All you have to do is double click on the cell where you want the detail and Excel creates a new tab with just the information you selected. In the example in the video, I first filter the Pivot Table to only show the information I want to drill down and examine, then double click to see the filtered detail. Remember that the extra tabs Excel creates are duplicates of your data, so when you’re done analyzing the drill down data, simply right click the new tab and delete it. Drilling down is one of the simplest, most useful features in Pivot Tables.
Excel Video 7 Multiple Rows and Columns in Pivot Tables
Pivot Tables have a clever feature that allows you to drag multiple rows and columns to the same Pivot Table to add a whole new level of flexibility to your analysis. Excel Video 7 starts by showing you a way to clear all of the filters in a Pivot Table at once. We then add multiple rows to the same Pivot Table and see the difference between the outer row (on the far left) and the inner row (on the far right). Watch the way Excel handles subtotaling the rows based on which row is on the outside and which row is on the inside. The same trick works with columns, and with rows and columns together.
Excel Video 8 Label Filters in Pivot Tables
Excel Video 8 demonstrates a cool new feature in Excel 2007, label filters. Label filters allow you to filter rows and columns based on the text of the label. Excel offers a variety of logical choices to filter with (Begins with, Doesn’t begin with, Ends with, Doesn’t end with, Contains, Doesn’t contain, etc.) Also notice what happens when you put date fields in a Pivot Table instead of text. Label filters turns into Date filters, and your filter options turn into a wide variety of date based choices (Before, After, Between, This week/month/quarter/year, Last week/month/quarter/year, Next week/month/quarter/year, Year to date, etc.) These filtering options make it very easy to keep Pivot Table filters current as the data refreshes.
Excel Video 9 Value Filters in Pivot Tables
Excel Video 9 picks up where Excel Video 8 left off. Excel Video 8 showed how label filters can filter Pivot Table data based on the row or column labels in the table. You can also filter based on the values in the data section of the Pivot Table, and these value filters offer a variety of ways to select data. You can select all of the data than equals a given amount (x) , does not equal x, is greater than x, is greater than or equal to x, is less than x, is less than or equal to x, or between x and y. Combining label filters and value filters should make your Pivot Tables even more powerful.
Excel Video 10 Multiple Pivot Table Value Fields
Excel Video 10 shows how to add calculations to a Pivot Table by dragging more than one field to the values area of a Pivot Table. In this example, we use the same field twice in the same Pivot Table, once to count the number of new patients with each E&M code level and the second time to calculate the percentage of each E&M code level by physician. There are a variety of calculations that can be made by adding the same field twice to the Pivot Table values area. The video also shows you how to just work with percentages in a Pivot Table.
Excel Video 11 Sum and Count Pivot Table Values
I’ve created new set of sample data to learn with, sample receivables data. My experience is that analyzing accounts receivable is one of the most powerful, money saving ways to use a Pivot Table. We start Excel Video 11 by analyzing accounts receivable by adding the balance due field to the Pivot Table twice. The trick is to sum the balance due the first time and count the balance due the second time. The other trick you’ll learn is how to move the sum of the values from columns to rows and back, depending on what you’re analyzing. There are a variety of uses for having the same data field in the data field twice. Look at the value field settings for more calculations (average, max, min, etc.) that are available in Pivot Tables.
Thanks for watching!
Excel Video 12 Pivot Table Design Tricks
Excel Video 12 demonstrates a couple of formatting tricks to make your Pivot Tables look really good. Excel has set up a variety of Pivot Table styles in the Design toolbar that make it easy to select any of a variety of formats for your data. If you’ve used styles in the past or in other Office products, you’ll recognize that all of the examples in the video are based on the default Office style. If you want to see more style options that will change these Pivot Table design options, select Themes on the Page Layout tab of the Office 2007 ribbon. Play with the other design options, as well as row and column headers/bands, to get the look you want. There’s no reason impressive data can’t look impressive, too. I’m willing to bet these formatting options are a little more robust that what your practice management software offers.
